Getting JSON and CSV data into SQLite without hand-writing a schema

When you need to analyze data from an external API, the usual path involves writing a schema, coding an import script, and then fiddling with type conversions. sqlite-utils removes most of that work by generating a table schema for you and providing both a Python library and a command line interface for importing and updating data.

Automatic schema generation from JSON

If you have an array of JSON objects from an API, you can insert it directly into a new SQLite table. sqlite-utils inspects the data and creates the columns automatically—no need to declare fields up front:

import sqlite_utils

orders = ... # (some code to get the `orders` array here)

db = sqlite_utils.Database('orders.db')
db['shopify_orders'].insert_all(orders)

This is particularly helpful when your data payload has dozens or hundreds of fields and you don’t want to enumerate them all by hand.

Handling new fields mid-import

A common problem with paginated API pulls is that a later page contains a field you haven’t seen yet. Normally, that would cause the import to fail. sqlite-utils handles it with the alter flag, which updates the existing table schema to include any previously unseen fields rather than erroring out:

db['shopify_orders'].insert_all(orders, alter=True)

Updating rows without duplicates

When re-syncing data where some records already exist in the database, you want to insert new rows and update existing ones in a single operation. The upsert method does this, but it requires you to specify the primary key so sqlite-utils knows which records to match. Using pk="id" makes the operation update rows with matching IDs and insert rows that are new:

db['shopify_orders'].upsert_all(
    orders,
    pk="id",
    alter=True
)

Command line usage for CSV files

Beyond the Python library, sqlite-utils ships with a command line tool for quick imports. For instance, loading a local CSV file into a database table is a one-liner:

sqlite-utils insert plants.db plants plants.csv --csv

Type conversions for more accurate data

CSV imports often treat numeric-looking values as strings. sqlite-utils' format conversion feature lets you coerce specific columns to the right types—for example, converting a string column to floats. This is a straightforward way to clean up data during import:

sqlite-utils insert plants.db plants plants.csv --csv --convert '
return {
  "name": row["name"].upper(),
  "latitude": float(row["latitude"]),
  "longitude": float(row["longitude"]),
}'

A better data analysis pipeline

Once the data lives in SQLite, pairing it with an open source dashboard tool like Metabase gives you a way to turn SQL queries into charts quickly. The combination of sqlite-utils for data loading, SQL for querying, and a dashboarding tool for visualization is a simpler alternative to building a custom web app for graph generation.