Motivation
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.error(err.message); } 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.error(err.message); } 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, open, high FROM candles_USDT_ETH; .quit
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.
-Matthew
No comments:
Post a Comment