Simon Willison’s Weblog

Datasette 0.46 (via) I just released Datasette 0.46 with a security fix for an issue involving CSRF tokens on canned query pages, plus a new debugging tool, improved file downloads and a bunch of other smaller improvements.

# 9th August 2020, 4:57 pm / projects, security, datasette

sqlite-utils 2.14 (via) I finally figured out porter stemming with SQLite full-text search today—it turns out it’s as easy as adding tokenize=’porter’ to the CREATE VIRTUAL TABLE statement. So I just shipped sqlite-utils 2.14 with a tokenize= option (plus the ability to insert binary file data from stdin).

# 1st August 2020, 9:19 pm / projects, search, sqlite, full-text-search, sqlite-utils

datasette-media 0.4. datasette-media is my Datasette plugin for serving media (e.g. images) directly from Datasette. The first version used file paths saved in a column and served the data from disk—this new version adds the ability to serve content from BLOB columns, such as those created by the new “sqlite-utils insert-files” command. It also adds configurable support for resizing images based on querystring parameters like ?w=100.

# 28th July 2020, 2:22 am / projects, datasette, plugins, images

sqlite-utils 2.12 (via) I’ve been experimenting with ways of improving BLOB support in Datasette and sqlite-utils. This new version of sqlite-utils includes a “sqlite-utils insert-files” command, which can recursively crawl directories for files and add their contents to SQLite with configurable columns containing their metadata.

I was inspired by Paul Ford who has been creating multi-GB SQLite databases of images and PDFs. It turns out that when disk space is cheap this is a pretty effective way of working with interesting corpuses of documents and images.

# 27th July 2020, 7:36 am / projects, sqlite, sqlite-utils

pypi-rename. I wanted to rename a PyPI package (renaming datasette-insert-api to datasette-insert as it’s about to grow some non-API features). PyPI recommend uploading a final release under the old name which points to (and depends on) the new name. I’ve built a cookiecutter template to codify that pattern.

# 25th July 2020, 11:07 pm / cookiecutter, projects, pypi

datasette-auth-passwords. My latest plugin: datasette-auth-passwords provides a mechanism for signing into Datasette using a username and password (which is verified in order to set a ds_actor authentication cookie). So far it only supports passwords that are hard-coded into Datasette’s configuration via environment variables, but I plan to add database-backed user accounts in the future.

# 13th July 2020, 11:39 pm / passwords, datasette, plugins, projects, authentication

sba-loans-covid-19-datasette (via) The treasury department released a bunch of data on the Covid-19 SBA Paycheck Protection Program Loan recipients today—I’ve loaded the most interesting data (the $150,000+ loans) into a Datasette instance.

# 7th July 2020, 2:42 am / projects, covid19, data-journalism, datasette

datasette-block-robots. Another little Datasette plugin: this one adds a /robots.txt page with Disallow: / to block all indexing of a Datasette instance from respectable search engine crawlers. I built this in less than ten minutes from idea to deploy to PyPI thanks to the datasette-plugin cookiecutter template.

# 23rd June 2020, 3:28 am / projects, robots-txt, plugins, seo, datasette

click-app. While working on sqlite-generate today I built a cookiecutter template for building the skeleton for Click command-line utilities. It’s based on datasette-plugin so it automatically sets up GitHub Actions for running tests and deploying packages to PyPI.

# 23rd June 2020, 2:21 am / cookiecutter, projects, python

sqlite-generate (via) I wrote this tool today to generate arbitrarily large SQLite databases, for testing purposes. You tell it how many tables, columns and rows you want and it will use the Faker Python library to generate random data and populate the tables with it.

# 23rd June 2020, 2:19 am / projects, sqlite

Serving photos locally with datasette-media. datasette-media is a new Datasette plugin which can serve static files from disk in response to a configured SQL query that maps incoming URL parameters to a path to a file. I built it so I could run dogsheep-photos locally on my laptop and serve up thumbnails of images that match particular queries. I’ve added documentation to the dogsheep-photos README explaining how to use datasette-media, datasette-json-html and datasette-template-sql to create custom interfaces onto Apple Photos data on your machine.

# 26th May 2020, 3:53 pm / projects, dogsheep, apple-photos, datasette, plugins

github-to-sqlite 2.2 highlights thread. I released github-to-sqlite 2.2 today with a new “stargazers” command for importing users who have starred one or more specific repositories. This Twitter thread lists highlights of recent releases and links to a live Datasette demo that shows what the tool can do.

# 2nd May 2020, 10:16 pm / projects, dogsheep, datasette, github