Saturday, November 4, 2017

Querying SQLite database using sqlite3 in JavaScript


I've never regretted studying mechanical engineering instead of electrical engineering or computer science, but sometimes by lack of formal programming knowledge does bite me. This happened a few days ago when I wanted to see if I could apply some machine learning techniques to some data in a SQLite database. Having no experience with SQL and minimal experience with javascript, I Googled how to get the data. The answers that I found seemed overly complicated. As such, this post will be a practical example of  querying the data from an SQLite database. It is as much for my memory as anything. I was using JavaScript (via Node) and sqlite3. I did this on a Virtual Machine running Ubuntu, but I believe all of the tools I use can be used in Windows as well.

Data Visualization using SQLiteStudio

Here is a visualization of the data. It is some historical trade data from the cryptocurrency exchange,  Poloniex. Having never used SQL before, I downloaded SQLiteStudio to see what the data actually looked like. As you can see, the database contains two tables - in this case one for each of the two trading pairs for which I had data. Inside those tables, there are labeled columns. Each row has a unique id. 

Querying the Database using sqlite3

I found a tutorial (THIS ONE) and tried to follow it, but I found that it took a frustratingly long amount of time to figure out what all of the values meant. Anyway, here is my javascript code to query a single line from the database. If you understand this, the above tutorial should be easy to adapt to pulling multiple lines. Of course, you need to already have sqlite3 installed. 

//Import sqlite to read database
const sqlite3 = require('sqlite3').verbose();
//Connect to database
let db = new sqlite3.Database('./history/poloniex_0.1.db', (err) => {
  if (err) {
  console.log('Connected to the database.');

// get columns start and label it as startval, open-> openval, etc from the appropriate table
// when the id = what we define it as below
let sql = `SELECT start startval,
                  open openval,
                  high highval
           FROM candles_USDT_ETH
           WHERE id = ?`;

let id = 2;
// Get only [id] row (in this case 2nd row)
db.get(sql, [id], (err, row) => {
  if (err) {
    return console.error(err.message);
  return row
    ? console.log(row.startval, row.openval, row.highval)
    : console.log(`No values found with the id ${id}`);

// Close the database
db.close((err) => {
  if (err) {
  console.log('Close the database connection.');

Converting the Table to CSV

Another useful thing I stumbled upon was how to convert a database from SQL to CSV in order to import it into some other program (in my case MATLAB). For my MATLAB example, I did not have the database toolbox, so this allowed me to play with this data without it. I copied THIS tutorial. It is more thorough, but here is the highlight. To save the start, open, and high columns from the candles_USDT_ETH table, use the following code.

sqlite3 ./history/poloniex_0.1.db
.headers on
.mode csv
.output data.csv
SELECT start,
FROM candles_USDT_ETH;

That's all I have for now. As I mentioned above, like many of my posts this is as much for my memory as anything, but I hope it helps someone.

No comments:

Post a Comment