SQLite Forum

sqlite-utils: a command-line tool and Python utility library
Login

sqlite-utils: a command-line tool and Python utility library

(1) By Simon Willison (simonw) on 2020-09-07 22:52:49 [source]

People on this forum may find this project useful: https://sqlite-utils.readthedocs.io/

It's a combination command-line tool and Python library that I've been working on for the last two years to help me work with SQLite databases.

As a command-line tool

Once installed (pip install sqlite-utils if you have a working Python 3 environment) you can use the sqlite-utils command-line tool to perform all kinds of useful actions.

Here are some examples running against my example database of data from GitHub: https://github-to-sqlite.dogsheep.net/github.db

The sqlite-utils tables my.db command shows you the tables in the database. Add --counts to get their row counts:

/tmp % sqlite-utils tables github.db --counts
[{"table": "users", "count": 4286},
 {"table": "repos", "count": 253},
 {"table": "licenses", "count": 7},
 {"table": "licenses_fts_data", "count": 34},
 # ...

Default output is JSON, but you can use -t to get a readable table, or --csv for CSV for TSV:

/tmp % sqlite-utils tables github.db --counts --csv 
table,count
users,4286
repos,253
licenses,7

There are a ton of other command-line options listed in the documentation. but my favourite is the ability to pipe JSON into the tool and have it automatically create a SQLite table with the correct columns to match the incoming JSON. For example:

echo '[
    {
        "id": 1,
        "name": "Cleo",
        "age": 4
    },
    {
        "id": 2,
        "name": "Pancakes",
        "age": 2
    },
    {
        "id": 3,
        "name": "Toby",
        "age": 6
    }
]' | sqlite-utils insert dogs.db dogs - --pk=id

This creates a dogs.db database with the following schema:

/tmp % sqlite3 dogs.db .schema 
CREATE TABLE [dogs] (
    [id] INTEGER PRIMARY KEY,
    [name] TEXT,
    [age] INTEGER
);

A few other useful commands:

  • sqlite-utils enable-fts can configure FTS for a table against specified columns, with a --create-triggers option to create triggers needed to keep the FTS table up-to-date with changes.
  • sqlite-utils add-foreign-key can add a foreign key to an existing table. SQLite doesn't support this natively but the command achieves it through careful use of the writable schema feature.
  • sqlite-utils insert-files can read files from disk and write them into a SQLite table as BLOB columns. More about this in my blog entry Fun with binary data and SQLite.

As a Python library

sqlite-utils started life as a Python library - I added the CLI part later on. It was initially designed to be used in a Jupyter notebook environment for quickly building SQLite databases.

I've since used it to build a whole family of tools for ingesting data from different sources (such as the GitHub or Twitter APIs, or Apple HealthKit data from my Apple Watch) - most of those are in my https://github.com/dogsheep GitHub collection.

A quick example script:

from sqlite_utils import Database

db = Database("books.db")
db["authors"].insert_all([
    {"id": 1, "name": "Sally"},
    {"id": 2, "name": "Asheesh"}
], pk="id")
db["books"].insert_all([
    {"title": "Hedgehogs of the world", "author_id": 1},
    {"title": "How to train your wolf", "author_id": 2},
], foreign_keys=[
    ("author_id", "authors")
])

This creates a table with the following schema populated with that example data:

CREATE TABLE [authors] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT
);
CREATE TABLE [books] (
   [id] INTEGER PRIMARY KEY,
   [title] TEXT,
   [author_id] INTEGER REFERENCES [authors]([id])
);
The full Python API documentation is here: https://sqlite-utils.readthedocs.io/en/stable/python-api.html - it includes all kinds of methods for introspecting existing databases, modifying foreign keys, configuring full-text search and more.

I continue to actively develop this library, so I'm always keen to hear feature requests for things that could be useful additions to it.

(2) By anonymous on 2020-09-08 03:32:01 in reply to 1 [link] [source]

Very nice.

Looks similar to csvs-to-sqlite: Convert CSV files into a SQLite database. Browse and publish that SQLite database with Datasette.

(3) By Simon Willison (simonw) on 2020-09-08 17:08:51 in reply to 2 [link] [source]

That's by me too! I'm evolving sqlite-utils to replace it - csvs-to-sqlite works by loading the entire file into memory which gets a bit slow with 100MB+ files. sqlite-utils streams the file into SQLite instead, but doesn't have as many useful options (yet) as csvs-to-sqlite in terms of things like extracting columns into separate tables.