Drupal Mysql Query to Fetch User Field Details and its Alias
Introduction In this posr, we will see how to prepare mysql query to fetch user…
March 30, 2020
I had to develop a small automation to query some old mysql data, with given product names. The mysql database had a table name products, and I was intersted in three fields:
name, created_at, updated_at
I had a text file with product names in one line, and had to query the two dates from the table. I wrote a small nodejs automation for this.
The expected ourput I wanted is a csv file:
ProductName, created_at, updated_at
MySql The core module which has apis to connect to mysql. All the apis are callback based, not in promise form.
es6-promisify This module is used to convert mysql module’s apis into promised form. This module is very handy.
async I want to perform async loop with promises in between.
momentjs I wanted to format some dates from mysql table.
I wrote a mysql client class to query this.
const mysql = require('mysql');
const {promisify} = require("es6-promisify");
const async = require('async');
const moment = require('moment');
class Client {
init(config) {
console.log('Connecting to mysql...');
this.__connection = mysql.createConnection({
host : config.host,
user : config.user,
password : config.password,
database : config.database
});
this.__connection.connect();
this.__query = promisify(this.__connection.query.bind(this.__connection));
console.log('connected');
return Promise.resolve();
}
testQuery(lines) {
return new Promise((resolve, reject) => {
async.eachLimit(lines, 1, (line, callback) => {
if (line) {
const query = `select * from products where name like \'%${line.trim()}%\'`;
return this.__query(query)
.then((res) => {
if (res && res.length > 0) {
const cd = moment(res[0].created_at).format('DD-MM-YYYY');
const ud = moment(res[0].updated_at).format('DD-MM-YYYY');
console.log(`${line.trim()},${cd},${ud}`);
}
else {
console.error('ERROR', line.trim());
}
callback();
})
.catch((err) => {
callback(err);
});
}
else {
callback();
}
}, function (err) {
if (err) {
reject(err);
} else {
console.log('Done');
resolve();
}
});
});
}
}
module.exports = new Client();
And, a main app file
// my above client class
const client = require('./src/product_mysql/client');
const fs = require('fs');
const async = require('async');
// file from where to read product names
const data = fs.readFileSync('<path>/prod_names.txt', 'UTF-8');
const lines = data.split(/\r?\n/);
const config = {
host : 'localhost',
user : 'xxxxx',
password : 'xxxxxxxx',
database : 'xxxxxx'
};
return client.init(config)
.then(() => {
return client.testQuery(lines);
})
.then( () => {
console.log('Success');
})
.catch(err => {
console.error(err);
});
Note: For logging purpose, I just used simple console.log statements. I should have winston module. But, this was a small automation and I don’t need that much effort.
To run program
node app
Output
Android-DEXI Framework,03-11-2016,12-03-2018
Mobile-Files,03-11-2016,12-03-2018
Sekhmet,03-11-2016,12-03-2018
Sample,03-11-2016,12-03-2018
Codex and Build 2 Ship,03-11-2016,12-03-2018
Test Touch,03-11-2016,12-03-2018
Let me know if you have any query.
Introduction In this posr, we will see how to prepare mysql query to fetch user…
Introduction In this post, we will discuss 3 different ways to import a…
Read file all in one shot Above code read whole file at once, but if the files…
Goto your command terminal. Type: This will expose port: 27017 by default. You…
If your youtube video looks like:https://www.youtube.com/watch?v=g0kFl7sBdDQ…
Introduction When I migrated all of my drupal-7 website to drupal-8, I wrote…
Introduction This post has the complete code to send email through smtp server…
Introduction In a normal email sending code from python, I’m getting following…
Introduction In one of my app, I was using to talk to . I have used some event…
Introduction So you have a Django project, and want to run it using docker image…
Introduction It is very important to introduce few process so that your code and…
Introduction In this post, we will see a sample Jenkin Pipeline Groovy script…