SQLite Forum

sqlite-utils: a command-line tool and Python utility library
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 

There are [a ton of other command-line options](https://sqlite-utils.readthedocs.io/en/stable/cli.html) 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] (
        [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](https://simonwillison.net/2020/Jul/30/fun-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")
    {"id": 1, "name": "Sally"},
    {"id": 2, "name": "Asheesh"}
], pk="id")
    {"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] (
   [name] TEXT
CREATE TABLE [books] (
   [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.