sqlite-utils: a nice way to import data into SQLite for analysis

submited by
Style Pass
2022-05-12 22:30:05

Recently I wanted to do some basic data analysis using data from my Shopify store. So I figured I’d query the Shopify API and import my data into SQLite, and then I could make queries to get the graphs I want.

But this seemed like a lot of boring work, like I’d have to write a schema and write a Python program. So I hunted around for a solution, and I found sqlite-utils, a tool designed to make it easy to import arbitrary data into SQLite to do data analysis on the data.

The Shopify data has about a billion fields and I really did not want to type out a schema for it. sqlite-utils solves this problem: if I have an array of JSON orders, I can create a new SQLite table with that data in it like this:

Next, I ran into a problem where on the 5th page of downloads, the JSON contained a new field that I hadn’t seen before.

Luckily, sqlite-utils thought of that: there’s an alter flag which will update the table’s schema to include the new fields. ```

Leave a Comment