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 puts graduates at a disadvantage, because more and more it seems that SQL is the lingua franca of any large scale 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 Willison’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. This solution comes from Simon Willison’s excellent blog, in particular, this post.

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.

Solution 2: DuckDB and a Notebook UI

Installing DuckDB is simple enough, and it’s also fairly straightforward to use. DuckDB is a great and portable database solution, one of my favorites. It is particularly wonderful because it processes operations column-wise rather than row-wise. Per DuckDB’s page on why to choose it:

DuckDB uses a columnar-vectorized query execution engine, where queries are still interpreted, but a large batch of values (a “vector”) are processed in one operation. This greatly reduces overhead present in traditional systems such as PostgreSQL, MySQL or SQLite which process each row sequentially. Vectorized query execution leads to far better performance in OLAP queries.

It is a very fancy-pants database-speak way of saying it is good at processing the types of queries traditionally associated with data analysis and data science tasks.

DuckDB has an excellent CLI client, but the magic really is in their UI implementation.

Having worked with Snowflake, Databricks, BigQuery and the like before, an interface such as this one will be familiar to a lot of users, and it can provide a very solid jumping off point for users wanting to learn how to use these tools without having to commit to accounts with them or even having access to them through a large organization.

The standard DuckDB UI looks like this:

While, for reference, the Databricks one looks like this:

They’re obviously not 1:1, but if you wanted to learn to use the modern tools of the analytics world in a local environment without much hassle, this seems like a pretty nice path forward.

Solution 3: Jupyter Notebooks with SQL Magics

If you are already working in a Jupyter environment (like JupyterLab or VS Code), you can run SQL queries directly inside your notebook cells using “magics.” This is incredibly useful for mixing data retrieval with Python analysis.

The jupysql library is the modern standard for this. You can install it along with DuckDB:

pip install jupysql duckdb-engine

Then, in your notebook, you can load the extension and connect to a local DuckDB instance (even an in-memory one):

%load_ext sql
%sql duckdb:///:memory:

Now you can write SQL directly in a cell by using the %%sql prefix:

%%sql
SELECT * FROM 'taxi.csv' LIMIT 5;

It’s the perfect bridge between raw SQL exploration and deeper Python-based data science, allowing you to stay in the flow of your analysis without switching tools.