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.