sqlite-utils: a nice way to import data into SQLite for analysis
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. ```
