https://simonwillison.net/2022/Jan/11/sqlite-utils/

Simon Willison's Weblog

What's new in sqlite-utils

sqlite-utils is my combined CLI tool and Python library for
manipulating SQLite databases. Consider this the annotated release
notes for sqlite-utils 3.20 and 3.21, both released in the past week.

sqlite-utils insert --convert with --lines and --text

The sqlite-utils insert command inserts rows into a SQLite database
from a JSON, CSV or TSV file, creating a table with the necessary
columns if one does not exist already.

It gained three new options in v3.20:

      + sqlite-utils insert ... --lines to insert the lines from a
        file into a table with a single line column, see Inserting
        unstructured data with --lines and --text.
      + sqlite-utils insert ... --text to insert the contents of the
        file into a table with a single text column and a single row.
      + sqlite-utils insert ... --convert allows a Python function to
        be provided that will be used to convert each row that is
        being inserted into the database. See Applying conversions
        while inserting data, including details on special behavior
        when combined with --lines and --text. (#356)

These features all evolved from an idea I had while re-reading my
blog entry from last year, Apply conversion functions to data in
SQLite columns with the sqlite-utils CLI tool. That blog entry
introduced the sqlite-utils convert comand, which can run a custom
Python function against a column in a table to convert that data in
some way.

Given a log file log.txt that looks something like this:

2021-08-05T17:58:28.880469+00:00 app[web.1]: measure#nginx.service=
4.212 request="GET /search/?type=blogmark&page=2&tag=highavailability
HTTP/1.1" status_code=404 request_id=
25eb296e-e970-4072-b75a-606e11e1db5b remote_addr="10.1.92.174"
forwarded_for="114.119.136.88, 172.70.142.28" forwarded_proto="http"
via="1.1 vegur" body_bytes_sent=179 referer="-" user_agent="Mozilla/
5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko)
Mobile Safari/537.36 (compatible; PetalBot;+https://
webmaster.petalsearch.com/site/petalbot)" request_time="4.212"
upstream_response_time="4.212" upstream_connect_time="0.000"
upstream_header_time="4.212";

I provided this example code to insert lines from a log file into a
table with a single line column:

cat log.txt | \
    jq --raw-input '{line: .}' --compact-output | \
    sqlite-utils insert logs.db log - --nl

Since sqlite-utils insert requires JSON, this example first used jq
to convert the lines into {"line": "..."} JSON objects.

My first idea was to improve this with the new --lines option, which
lets you replace the above with this:

sqlite-utils insert logs.db log log.txt --lines

Using --lines will create a table with a single lines column and
import every line from the file as a row in that table.

In the article, I then demonstrated how --convert could be used to
convert those imported lines into structured rows using a regular
expression:

sqlite-utils convert logs.db log line --import re --multi "$(cat <<EOD
    r = re.compile(r'([^\s=]+)=(?:"(.*?)"|(\S+))')
    pairs = {}
    for key, value1, value2 in r.findall(value):
        pairs[key] = value1 or value2
    return pairs
EOD
)"

The new --convert option to sqlite-utils means you can now achieve
the same thing using:

sqlite-utils insert logs.db log log.txt --lines \
  --import re --convert "$(cat <<EOD
    r = re.compile(r'([^\s=]+)=(?:"(.*?)"|(\S+))')
    for key, value1, value2 in r.findall(line):
        pairs[key] = value1 or value2
    return pairs
EOD
)"

Since the --lines option allows you to consume mostly unstructured
files split by newlines, I decided to also add an option to consume
an entire unstructured file as a single record. I originally called
that --all but found the code got messy because it conflicted with
Python's all() built-in, so I renamed it to --text.

Used on its own, --text creates a table with a single column called
text:

% sqlite-utils insert logs.db fulllog log.txt --text
% sqlite-utils schema logs.db
CREATE TABLE [fulllog] (
   [text] TEXT
);

But with --convert you can pass a snippet of Python code which can
take that text value and convert it into a list of dictionaries,
which will then be used to populate the table.

Here's a fun example. The following one-liner uses the classic
feedparser library to parse the Atom feed for my blog and load it
into a database table:

curl 'https://simonwillison.net/atom/everything/' | \
  sqlite-utils insert feed.db entries --text --convert '
    feed = feedparser.parse(text)
    return feed.entries' - --import feedparser

The resulting database looks like this:

% sqlite-utils tables feed.db --counts -t
table      count
-------  -------
feed          30

% sqlite-utils schema feed.db
CREATE TABLE [feed] (
   [title] TEXT,
   [title_detail] TEXT,
   [links] TEXT,
   [link] TEXT,
   [published] TEXT,
   [published_parsed] TEXT,
   [updated] TEXT,
   [updated_parsed] TEXT,
   [id] TEXT,
   [guidislink] INTEGER,
   [summary] TEXT,
   [summary_detail] TEXT,
   [tags] TEXT
);

Not bad for a one-liner!

This example uses the --import option to import that feedparser
library. This means you'll need to have that library installed in the
same virtual environment as sqlite-utils.

If you run into problems here (maybe due to having installed
sqlite-utils via Homebrew) one way to do this is to use the
following:

python3 -m pip install feedparser sqlite-utils

Then use python3 -m sqlite_utils in place of sqlite-utils--this will
ensure you are running the command from the same virtual environment
where you installed the library.

--convert for regular rows

The above examples combine --convert with the --lines and --text
options to parse unstructured text into database tables.

But --convert works with the existing sqlite-utils insert options as
well.

To review, those are the following:

  * sqlite-utils insert by default expects a JSON file that's a list
    of objects, [{"id": 1, "text": "Like"}, {"id": 2, "text":
    "This"}].
  * sqlite-utils insert --nl accepts newline-delimited JSON, {"id":
    1, "text": "Like"}\n{"id": 2, "text": "This"}.
  * sqlite-utils insert --csv and --tsv accepts CSV/TSV--with
    --delimiter and --encoding and --quotechar and --no-headers
    options for customizing that import, and a --sniff option for
    automatically detecting those settings.

You can now use --convert to define a Python function that accepts a
row dictionary representing each row from the import and modifies
that dictionary or returns a fresh one with changes.

Here's a simple example that produces just the capitalized name, the
latitude and the longitude from the WRI's global power plants CSV
file:

curl https://raw.githubusercontent.com/wri/global-power-plant-database/master/output_database/global_power_plant_database.csv | \
  sqlite-utils insert plants.db plants - --csv --convert '
  return {
      "name": row["name"].upper(),
      "latitude": float(row["latitude"]),
      "longitude": float(row["longitude"]),
  }'

The resulting database looks like this:

% sqlite-utils schema plants.db
CREATE TABLE [plants] (
   [name] TEXT,
   [latitude] FLOAT,
   [longitude] FLOAT
);

~ % sqlite-utils rows plants.db plants | head -n 3
[{"name": "KAJAKI HYDROELECTRIC POWER PLANT AFGHANISTAN", "latitude": 32.322, "longitude": 65.119},
 {"name": "KANDAHAR DOG", "latitude": 31.67, "longitude": 65.795},
 {"name": "KANDAHAR JOL", "latitude": 31.623, "longitude": 65.792},

sqlite-utils bulk

      + New sqlite-utils bulk command which can import records in the
        same way as sqlite-utils insert (from JSON, CSV or TSV) and
        use them to bulk execute a parametrized SQL query. (#375)

With the addition of --lines, --text, --convert and --import the
sqlite-utils insert command is now a powerful tool for turning
anything into a list of Python dictionaries, which can then in turn
be inserted into a SQLite database table.

Which gave me an idea... what if you could use the same mechanisms to
execute SQL statements in bulk instead?

Python's SQLite library supports named parameters in SQL queries,
which look like this:

insert into plants (id, name) values (:id, :name)

Those :id and :name parameters can be populated from a Python
dictionary. And the .executemany() method can efficiently apply the
same SQL query to a big list (or iterator or generator) of
dictionaries in one go:

cursor = db.cursor()
cursor.executemany(
    "insert into plants (id, name) values (:id, :name)",
    [{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]
)

So I implemented the sqlite-utils bulk command, which takes the same
import options as sqlite-utils but instead of creating and populating
the specified table requires a SQL argument with a query that will be
executed using the imported rows as arguments.

% sqlite-utils bulk demo.db \
  'insert into plants (id, name) values (:id, :name)' \
  plants.csv --csv

This feels like a powerful new feature, which was very simple to
implement because the hard work of importing the data had already
been done by the insert command.

Running ANALYZE

      + New Python methods for running ANALYZE against a database,
        table or index: db.analyze() and table.analyze(), see
        Optimizing index usage with ANALYZE. (#366)
      + New sqlite-utils analyze command for running ANALYZE using
        the CLI. (#379)
      + The create-index, insert and upsert commands now have a new
        --analyze option for running ANALYZE after the command has
        completed. (#379)

This idea came from Forest Gregg, who initially suggested running
ANALYZE automatically as part of the sqlite-utils create-index
command.

I have to confess: in all of my years of using SQLite, I'd never
actually explored the ANALYZE command.

When run, it builds a new table called sqlite_stats1 containing
statistics about each of the indexes on the table--indicating how
"selective" each index is--effectively how many rows on average you
are likely to filter down to if you use the index.

The SQLite query planner can then use this to decide which index to
consult. For example, given the following query:

select * from ny_times_us_counties
where state = 'Missouri' and county = 'Greene'

(Try that here.)

If there are indexes on both columns, should the query planner use
the state column or the county column?

In this case the state column will filter down to 75,209 rows, while
the county column filters to 9,186--so county is clearly the better
query plan.

Impressively, SQLite seems to make this kind of decision perfectly
well without the sqlite_stat1 table being populated: explain query
plan select * from ny_times_us_counties where "county" = 'Greene' and
"state" = 'Missouri' returns the following:

SEARCH TABLE ny_times_us_counties USING INDEX
idx_ny_times_us_counties_county (county=?)

I've not actually found a good example of a query where the
sqlite_stat1 table makes a difference yet, but I'm confident such
queries exist!

Using SQL, you can run ANALYZE against an entire database by
executing ANALYZE;, or against all of the indexes for a specific
table with ANALYZE tablename;, or against a specific index by name
using ANALYZE indexname;.

There's one catch with ANALYZE: since running it populates a static
sqlite_stat1 table, the data in that table can get out of date. If
you insert another million rows into a table for example your
analyzye statistics might no longer reflect ground truth to the point
that the query planner starts to make bad decisions.

For sqlite-utils I decided to make ANALYZE an explicit operation. In
the Python library you can now run the following:

db.analyze() # Analyze every index in the database
db.analyze("indexname") # Analyze a specific index
db.analyze("tablename") # Analyze every index for that table
# Or the same thing using a table object:
db["tablename"].analyze()

I also added an optional analyze=True parameter to several methods,
which you can use to trigger an ANALZYE once that operation
completes:

db["tablename"].create_index(["column"], analyze=True)
db["tablename"].insert_rows(rows, analyze=True)
db["tablename"].delete_where(analyze=True)

The sqlite-utils CLI command has equivalent functionality:

# Analyze every index in a database:
% sqlite-utils analyze database.db
# Analyze a specific index:
% sqlite-utils analyze database.db indexname
# Analyze all indexes for a table:
% sqlite-utils analyze database.db tablename

And an --analyze option for various commands:

% sqlite-utils create-index ... --analyze
% sqlite-utils insert ... --analyze
% sqlite-utils upsert ... --analyze

Other smaller changes

      + New sqlite-utils create-database command for creating new
        empty database files. (#348)

Most sqlite-utils commands such as insert or create-table create the
database file for you if it doesn't already exist, but I decided it
would be neat to have an explicit create-database command for
deliberately creating an empty database.

Update 13th January 2022: I wrote a detailed description of my
process building this command in How I build a feature.

      + The CLI tool can now also be run using python -m
        sqlite_utils. (#368)

I initially added this to help write a unit test that exercised the
tool through a subprocess (see TIL Testing a Click app with streaming
input) but it's a neat pattern in general. datasette gained this
through a contribution from Abdussamet Kocak a few years ago.

      + Using --fmt now implies --table, so you don't need to pass
        both options. (#374)

A nice tiny usability enhancement. You can now run sqlite-utils rows
my.db mytable --fmt rst to get back a reStructuredText
table--previously you also needed to add --table.

      + The insert-files command supports two new columns: stem and
        suffix. (#372)

I sometimes re-read the documentation for older features to remind me
what they do, and occasionally an idea for a feature jumps out from
that. Implementing these was a very small change.

      + The --nl import option now ignores blank lines in the input.
        (#376)
      + Fixed bug where streaming input to the insert command with
        --batch-size 1 would appear to only commit after several rows
        had been ingested, due to unnecessary input buffering. (#364)

That --nl improvement came from tinkering around trying to fix the
bug.

The bug itself was interesting: I initially thought that my entire
mechanism for comitting on every --batch-size chunk was broken, but
it turned out I was unnecessarily buffering data from standard input
in order to support the --sniff option for detecting the shape of
incoming CSV data.

      + db.supports_strict property showing if the database
        connection supports SQLite strict tables.
      + table.strict property (see .strict) indicating if the table
        uses strict mode. (#344)

See previous weeknotes: this is the first part of my ongoing support
for the new STRICT tables in SQLite.

I'm currently blocked on implementing more due to the need to get a
robust mechanism up and running for executing sqlite-utils tests in
CI against specific SQLite versions, see issue #346.

Releases this week

  * sqlite-utils: 3.21--(92 releases total)--2022-01-11
    Python CLI utility and library for manipulating SQLite databases

  * sqlite-utils: 3.20--2022-01-05

  * stream-delay: 0.1--2022-01-08
    Stream a file or stdin one line at a time with a delay

TILs this week

  * Writing pytest tests against tools written with argparse
  * Testing a Click app with streaming input

Posted 11th January 2022 at 6:19 pm * Follow @simonw on Twitter

This is What's new in sqlite-utils by Simon Willison, posted on 11th
January 2022.

projects 230 sqlite 144 weeknotes 113 sqliteutils 55 
annotatedreleasenotes 11

Next: How I build a feature

Previous: Weeknotes: Taking a break in Moss Landing

    What's new in sqlite-utils - annotated release notes for my
    SQLite Python utility library and CLI tool, v3.20 and v3.21
    https://t.co/xdeHzIgrJH

    -- Simon Willison (@simonw) January 11, 2022

  * Source code
  * (c)
  * 2002
  * 2003
  * 2004
  * 2005
  * 2006
  * 2007
  * 2008
  * 2009
  * 2010
  * 2011
  * 2012
  * 2013
  * 2014
  * 2015
  * 2016
  * 2017
  * 2018
  * 2019
  * 2020
  * 2021
  * 2022