three ways to run SQL locally

When I was doing my masters in Data Science, one of the things I found frustrating was that we were not really taught how to interface with SQL databases and queries. This really puts the people that leave that program at a disadvantage, because more and more it seems that SQL is the lengua franca of any data analysis task.

This article is a small group of solutions on how one could interface with SQL locally and without much hassle. I’ve found them to be quite useful, and enjoy exploring data this way.

Solution 1: Simon Willinson’s solution in Bash

This is probably the easiest of the solutions to work with, since all it requires is sqlite3 to be installed for this to work.

sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode column' \
    'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

The script:

• Creates an in-memory SQLite database
• Imports taxi.csv as a table called taxi
• Runs an aggregate SQL query
• Prints the results in a readable, column-aligned format

It’s a very Unix-y way to treat a CSV like a database table for fast exploration.