Simon Willison’s Weblog


Data analysis with SQLite and Python for PyCon 2023

20th April 2023

I’m at PyCon 2023 in Salt Lake City this week.

Yesterday afternoon I presented a three hour tutorial on Data Analysis with SQLite and Python. I think it went well!

Update: The 2hr45m video of the tutorial is now available on YouTube.

I covered basics of using SQLite in Python through the sqlite3 module in the standard library, and then expanded that to demonstrate sqlite-utils, Datasette and even spent a bit of time on Datasette Lite.

One of the things I learned from the Carpentries teacher training a while ago is that a really great way to run a workshop like this is to have detailed, extensive notes available and then to work through those, slowly, at the front of the room.

I don’t know if I’ve quite nailed the “slowly” part, but I do find that having an extensive pre-prepared handout really helps keep things on track. It also gives attendees a chance to work at their own pace.

You can find the full 9-page workshop handout I prepared here:

Screenshot of the handout. Data analysis with SQLite and Python, PyCon 2023      What you’ll need         python3 and pip         Optional: GitHub Codespaces     Introduction to SQLite         Why SQLite?         First steps with Python         Creating a table         Inserting some data         UPDATE and DELETE         SQLite column types         Transactions     Exploring data with Datasette         Installing Datasette locally         Try a database: legislators.db         Install some plugins         Learning SQL with Datasette

I built the handout site using Sphinx and Markdown, with myst-parser and sphinx_rtd_theme and hosted on Read the Docs. The underlying GitHub repository is here:

I’m hoping to recycle some of the material from the tutorial to extend Datasette’s official tutorial series—I find that presenting workshops is an excellent opportunity to bulk up Datasette’s own documentation.

The Advanced SQL section in particular would benefit from being extended. It covers aggregations, subqueries, CTEs, SQLite’s JSON features and window functions—each of which could easily be expanded into their own full tutorial.