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'}
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
More recent articles
- Prompts.js - 7th December 2024
- First impressions of the new Amazon Nova LLMs (via a new llm-bedrock plugin) - 4th December 2024
- Storing times for human events - 27th November 2024