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.