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 [link]

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](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] (
        [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](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")
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

Very nice.

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


[1]:https://github.com/simonw/csvs-to-sqlite

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

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.