Using sqlite utils to Load CSV Fast Into a SQLite Database

SQLite is the database you already have: it ships with Python, every phone and most operating systems, and a database is one file you can copy. What it lacks is a pleasant way to get a CSV in. The shell's .import works and has quirks, and writing the CREATE TABLE yourself is a chore for a file with 40 columns. sqlite-utils fills that gap with a command-line tool that reads the CSV, creates the table, infers the types and inserts. I use it when I want a queryable copy of a file, with an index and perhaps full-text search, and I do not care whether the load takes ten seconds or a minute. This article shows the commands, one default that changed in version 4 and will surprise you, and the numbers for a 2 million row load. The tool sits among the others in CSV tools worth knowing, and the plain-shell route is in importing CSV files into SQLite from the command line.

One command

Install it into a virtual environment or with pipx install sqlite-utils. Then:

sqlite-utils insert orders.db orders orders-2m.csv --csv --pk order_id

The arguments are the database file (created if absent), the table name, the CSV and two options: --csv says the input is CSV (--tsv for tabs, and JSON is the default), and --pk names the primary key. The result is a table whose schema the tool wrote from the data:

CREATE TABLE "orders" (
   "order_id" INTEGER PRIMARY KEY,
   "customer_id" INTEGER,
   "zip" INTEGER,
   "amount" REAL,
   "created_at" TEXT,
   "status" TEXT,
   "country" TEXT,
   "note" TEXT
);

Run on the 2 million row file it took 93.35 seconds, used about 33 MB of memory, since it streams, and produced a database of 293.7 MB. That is roughly 21,000 rows per second, which is the speed of Python inserting inside transactions: fine for a file you load once, poor for one you load every night, where a bulk loader beats it by a wide margin.

The type default, and why it matters

Look at the zip line in the schema above: INTEGER. The tool read the column, saw digits, and made it a number. In version 4 type detection is on by default; earlier versions loaded everything as TEXT unless you passed --detect-types. A script written for the old behavior therefore changes meaning after an upgrade, which is a good reason to pin versions. For this file, a zip like 05398 is stored as the integer 5398. In a two-row test with 02134 and 90210, the query returned [{"zip":2134},{"zip":90210}].

To keep the text, pass --no-detect-types, and every column becomes TEXT:

sqlite-utils insert orders.db orders orders-2m.csv --csv --pk order_id --no-detect-types

Then convert the columns you want typed, one at a time and on purpose, with transform:

sqlite-utils transform orders.db orders --type amount float --type customer_id integer

transform rebuilds the table with the new types, and it is the right way to be explicit: identifiers stay text, quantities become numbers. The --pk column has the same issue: with detection off, order_id is TEXT PRIMARY KEY, so sorting it gives 1, 10, 100, 1000 and not 1, 2, 3, and a numeric key needs --type order_id integer too. When a column has empty strings that should be NULL, add --empty-null.

Query it

The database is an ordinary SQLite file, so any SQLite client works, and the tool has a query command for a quick look:

$ sqlite-utils query orders.db "select status, count(*) as n, round(sum(amount),2) as total from orders group by status order by status" --table
status         n        total
--------  ------  -----------
failed    399533  3.20179e+07
paid      400113  3.1991e+07
pending   400329  3.20779e+07
refunded  400135  3.20698e+07
shipped   399890  3.2018e+07

The aggregate over all 2,000,000 rows took 0.98 seconds. The totals print in exponent form because the --table output uses a general float format; use --csv or --json for exact digits. The point of the exercise is the shape: a long load, then fast answers. If you will ask one question, DuckDB, which answered the same group-by directly from the CSV in 0.20 seconds with no load, is the better tool (querying a CSV file with DuckDB instead of loading it). If you will ask a hundred, or want an index, a unique constraint, a foreign key or a file you can open in a dozen other tools, the database pays back.

Full-text search

SQLite has full-text search built in, and sqlite-utils makes it one line. On the note column:

sqlite-utils enable-fts orders.db orders note
sqlite-utils search orders.db orders "leave door" -c order_id -c note --limit 3 --table

  order_id  note
----------  -------------
         2  leave at door
        14  leave at door
        16  leave at door

Building the index over 2 million rows took 1.73 seconds in this data, which has very few distinct notes; free text would take longer. Add --create-triggers to enable-fts if the table will keep changing, so the index follows inserts and updates. The search syntax is SQLite's FTS5 query language, with OR, quotes for phrases and NEAR. For the larger idea of publishing this as a searchable site, see Datasette for publishing CSV as a searchable site.

Other options worth knowing

A few more flags earn their place. --batch-size changes the number of rows per transaction, --alter adds columns when later files have more, and --replace or --upsert (with --pk) updates rows that already exist, which turns repeated loads into an idempotent process (idempotent CSV imports that are safe to run twice). --encoding names the file's encoding, since the default is UTF-8, and --sniff detects the delimiter. Several CSVs can go into one table by running insert repeatedly. sqlite-utils can also dump a table back to CSV (sqlite-utils rows orders.db orders --csv), analyze columns (analyze-tables), create indexes (create-index orders.db orders status country) and convert columns with Python snippets (convert).

Everything the tool does is available from Python too (import sqlite_utils), so a script can load, transform and index in a few lines, with the same default for type detection to watch.

When the SQLite shell or DuckDB is faster

If load speed matters, use a bulk loader. The SQLite shell's .import --csv file table loads millions of rows in seconds, because it is C and runs one transaction, and the cost is that you create the table first and every column is text unless you declared otherwise. DuckDB can read the CSV and write a SQLite file through its SQLite extension, or you can load straight into DuckDB's own format and keep it. For a daily pipeline, use a loader built for the database you run in production and keep sqlite-utils for exploration and for files that fit comfortably.

It is also not the tool for data that must keep exact types the moment it lands: its inference is a convenience, and anything beyond a few thousand rows deserves an explicit schema. Write the CREATE TABLE yourself, load into it and let SQLite enforce it (setting column types in SQLite imports). The reference for every command is the sqlite-utils documentation, and the types SQLite uses are explained in its datatype page.