Intro
Recently, the project needs to crawl some data, the data is encrypted, the front-end js is confused, the ajax request is hook, some complex, and finally plans to use puppeter to crawl the data.
Puppeter is a project that the Google team is maintaining. The original intention is mainly to do automatic testing of web pages. The official Headless chrome tool of the Google Chrome team is a node library, which provides an advanced API to control the Headless chrome on the DevTools protocol. It can also be configured to use complete (non Headless) chrome. I won't go into details here. If you are interested, you can google it first, and then write an article to introduce it. Today, I mainly introduce node operation mssql.
node-mssql
node-mssql It is the npm package that we use to operate Ms Sql Server database. It supports promise and async/await syntax. This package is also recommended by Microsoft.
I prefer async/await syntax.
Basic usage:
let pool = await sql.connect(config); // sql let result1 = await pool.request() .input('input_parameter', sql.Int, value) .query('select * from mytable where id = @input_parameter'); // stored procedure let result2 = await pool.request() .input('input_parameter', sql.Int, value) .output('output_parameter', sql.VarChar(50)) .execute('procedure_name');
Please refer to the official documents for more usage https://www.npmjs.com/package/mssql
encapsulation
Although a relatively perfect method is provided, if it is used, it will still feel uncomfortable, not so smooth, not so simple, so I want to encapsulate one layer by myself
const mssql = require("mssql"); const log4js = require("log4js"); const logger = log4js.getLogger("dbUtil"); const connConfig = { user: "db user", password: "password", server: "server", database: "database name", connectionTimeout: 120000, requestTimeout: 3000000, retryTimes: 3, options: { encrypt: true }, pool: { max: 1024, min: 1, idleTimeoutMillis: 30000 } }; mssql.on('error', err => { // ... error handler logger.error(err); }); let connectionPool; var getConnection = async function(){//Connect to database if(!(connectionPool && connectionPool.connected)) { connectionPool = await mssql.connect(connConfig); } return connectionPool; } var querySql = async function (sql, params) {//Write sql statement to query freely await mssql.close();// close var pool = await getConnection(); var request = pool.request(); if (params) { for (var index in params) { if (typeof params[index] == "number") { request.input(index, mssql.Int, params[index]); } else if (typeof params[index] == "string") { request.input(index, mssql.NVarChar, params[index]); } } } var result = await request.query(sql); await mssql.close();// close return result; }; var add = async function (addObj, tableName) {//Add data if(!addObj){ return; } await mssql.close();// close var connection = await getConnection(); var request = connection.request(); var sql = "insert into " + tableName + "("; for (var index in addObj) { if (typeof addObj[index] == "number") { request.input(index, mssql.Int, addObj[index]); } else if (typeof addObj[index] == "string") { request.input(index, mssql.NVarChar, addObj[index]); } sql += index + ","; } sql = sql.substring(0, sql.length - 1) + ") values("; for (var index in addObj) { if (typeof addObj[index] == "number") { sql += "@" + index + ","; } else if (typeof addObj[index] == "string") { sql += "@" + index + ","; } } sql = sql.substring(0, sql.length - 1) + ")"; var result = await request.query(sql); await mssql.close();// close return result; }; var addIfNotExist = async function (addObj, whereObj, tableName) {//Add data if(!addObj){ return; } if(!whereObj){ return await add(addObj, tableName); } await mssql.close();// close var connection = await getConnection(); var request = connection.request(); let sql = `BEGIN IF NOT EXISTS (SELECT 1 FROM $ WHERE 1 > 0`; for(var index in whereObj){ if (typeof addObj[index] == "number") { request.input(index+'Where', mssql.Int, whereObj[index]); } else if (typeof addObj[index] == "string") { request.input(index+'Where', mssql.NVarChar, whereObj[index]); } sql += ` AND $ = @$Where` } sql+= ')'; sql += 'BEGIN '; sql += "INSERT INTO " + tableName + "("; for (var index in addObj) { if (typeof addObj[index] == "number") { request.input(index, mssql.Int, addObj[index]); } else if (typeof addObj[index] == "string") { request.input(index, mssql.NVarChar, addObj[index]); } sql += index + ","; } sql = sql.substring(0, sql.length - 1) + ") values("; for (var index in addObj) { if (typeof addObj[index] == "number") { sql += "@" + index + ","; } else if (typeof addObj[index] == "string") { sql += "@" + index + ","; } } sql = sql.substring(0, sql.length - 1) + ")"; sql += ` END END`; var result = await request.query(sql); await mssql.close();// close return result; }; var addList = async function (addObjs, tableName) {//Add data if(!addObjs || addObjs.length == 0){ return; } await mssql.close();// close var connection = await getConnection(); var sql = "INSERT INTO " + tableName + "("; if (addObjs) { let addObj = addObjs[0]; for (var index in addObj) { sql += index + ","; } sql = sql.substring(0, sql.length - 1) + ") VALUES"; addObjs.forEach(addObj => { sql = sql + "("; for (var index in addObj) { if (typeof addObj[index] == "number") { sql += addObj[index] + ","; } else if (typeof addObj[index] == "string") { sql += "N'" + addObj[index] + "'" + ","; } } sql = sql.substring(0, sql.length - 1) + "),"; }); } sql = sql.substring(0, sql.length - 1); // logger.info(sql); var result = await connection.request().query(sql); await mssql.close();// close return result; }; var update = async function (updateObj, whereObj, tableName) {//Update data await mssql.close();// close var connection = await getConnection(); var request = connection.request(); var sql = "UPDATE " + tableName + " SET "; if (updateObj) { for (var index in updateObj) { if (typeof updateObj[index] == "number") { request.input(index, mssql.Int, updateObj[index]); sql += index + "=@" + index + ","; } else if (typeof updateObj[index] == "string") { request.input(index, mssql.NVarChar, updateObj[index]); sql += index + "=@" + index + ","; } } } sql = sql.substring(0, sql.length - 1) + " WHERE "; if (whereObj) { for (var index in whereObj) { if (typeof whereObj[index] == "number") { request.input(index, mssql.Int, whereObj[index]); sql += index + "=@" + index + " AND "; } else if (typeof whereObj[index] == "string") { request.input(index, mssql.NVarChar, whereObj[index]); sql += index + "=@" + index + " AND "; } } } sql = sql.substring(0, sql.length - 5); var result = await request.query(sql); await mssql.close();// close return result; }; exports.query = querySql; exports.update = update; exports.add = add; exports.addIfNotExist = addIfNotExist; exports.addList = addList;
Contact
Contact me: [email protected]