Simon Willison’s Weblog

Weeknotes: sqlite-utils updates, Datasette and asgi-csrf, open-sourcing VIAL

28th June 2021

Some work on sqlite-utils, asgi-csrf, a Datasette alpha and we open-sourced VIAL.

sqlite-utils

Last week’s sqlite-utils 3.10 introduced a huge new feature: the ability to run joins directly against CSV and JSON files from the command-line.

I’ve since released sqlite-utils 3.11 and 3.12, much smaller releases.

3.11 added a new --schema option to the sqlite-utils memory command which lets you see the schema you’ll be querying for the imported data:

$ curl 'https://api.github.com/users/dogsheep/repos' | \
  sqlite-utils memory - --schema
CREATE TABLE [stdin] (
   [id] INTEGER,
   [node_id] TEXT,
   [name] TEXT,
   [full_name] TEXT,
   [private] INTEGER,
   [owner] TEXT,
   [html_url] TEXT,
   [description] TEXT,
   ...
   [watchers] INTEGER,
   [default_branch] TEXT
);
CREATE VIEW t1 AS select * from [stdin];
CREATE VIEW t AS select * from [stdin];

3.12 focused on the Python library side of the package. It adds a new method, db.query(sql) which returns an iterator over Python dictionaries representing the results of a query.

This was a pretty obvious missing feature of the library: the rest of sqlite-utils deals with rows that are represented as dictionaries—you pass a list of Python dictionaries to db[table_name].insert_all(list_of_dicts) to create a table with the correct schema, for example. But if you wanted to execute SELECT queries you had to use db.execute() which would return a standard library cursor object which could then return tuples if you called .fetchall() on it.

It was only when I started to work on an interactive Jupyter notebook tutorial for sqlite-utils that I realized how weird it was not to have an equivalent method for reading data out of the database again.

Here’s what the new method looks like:

db = Database(memory=True)
db["dogs"].insert_all([
    {"name": "Cleo"},
    {"name": "Pancakes"}
])
for row in db.query("select * from dogs"):
    print(row)
# Outputs:
# {'name': 'Cleo'}
# {'name': 'Pancakes'}

Full documentation here.

asgi-csrf and a Datasette alpha

I’m building a custom Datasette integration for a consulting client at the moment which needs to be able to accept POST form data as part of an API. Datasette has CSRF protection but for this particular project I need to opt-out of that protection for this one endpoint.

I ended up releasing asgi-csrf 0.9 with a new skip_if_scope= mechanism for dynamically disabling CSRF protection based on the incoming ASGI scope. I then shipped a Datasette 0.58a1 alpha release with a new skip_csrf(datasette, scope) plugin hook for plugins to take advantage of that mechanism.

Expect another alpha release shortly to preview the new get_metadata plugin hook contributed by Brandon Roberts. I’ve decided that alphas are the ideal way to explore new plugin hooks while they are still being developed as it lets projects pip install the alpha while making it clear that the interface may not yet be fully baked.

Open-sourcing VIAL

VIAL is the project I’ve been working on for VaccinateCA/VaccinateTheStates—see previous posts. It’s a Django application which powers a crowd-sourced and scraper-driven effort to catalogue all of the places in the USA that you can get the Covid vaccine—77,000 and counting right now.

We had always intended to open-source the code and now we have! github.com/CAVaccineInventory/vial is the newly-made-public repository.

I still need to produce a bunch of extra documentation about VIAL, likely including a video introduction to the project. But it’s great to have it out there!

Releases this week

• sqlite-utils: 3.12—(80 releases total)—2021-06-25
  Python CLI utility and library for manipulating SQLite databases
• datasette: 0.58a1—(92 releases total)—2021-06-24
  An open source multi-tool for exploring and publishing data
• asgi-csrf: 0.9—(17 releases total)—2021-06-23
  ASGI middleware for protecting against CSRF attacks

TIL this week

• Scraping Reddit via their JSON API