The new sqlite-utils memory command can import CSV and JSON data directly into an in-memory SQLite datbase, combine and query it using SQL and output

Simon Willison’s Weblog

submited by
Style Pass
2021-06-19 23:30:05

The new sqlite-utils memory command can import CSV and JSON data directly into an in-memory SQLite datbase, combine and query it using SQL and output the results as CSV, JSON or various other formats of plain text tables.

sqlite-utils already offers a mechanism for importing CSV and JSON data into a SQLite database file, in the form of the sqlite-utils insert command. Processing data with this involves two steps: first import it into a temp.db file, then use sqlite-utils query to run queries and output the results.

Using SQL to re-shape data is really useful—since sqlite-utils can output in multiple different formats, I frequently find myself loading in a CSV file and exporting it back out as JSON, or vice-versa.

This week I realized that I had most of the pieces in place to reduce this to a single step. The new sqlite-utils memory command (full documentation here) operates against a temporary, in-memory SQLite database. It can import data, execute SQL and output the result in a one-liner, without needing any temporary database files along the way.

Here’s an example. My Dogsheep GitHub organization has a number of repositories. GitHub make those available via an authentication-optional API endpoint at https://api.github.com/users/dogsheep/repos—which returns JSON that looks like this (simplified):

Leave a Comment