Simon Willison’s Weblog

Weeknotes: datasette-export-notebook, PyInstaller packaged Datasette, CBSAs

10th January 2021

What a terrible week. I’ve found it hard to concentrate on anything substantial. In a mostly futile attempt to distract myself from doomscrolling I’ve mainly been building some experimental output plugins, fiddling with PyInstaller and messing around with shapefiles.

Packaged Datasette with PyInstaller

A long running goal for Datasette has been to make it as easy to install as possible—something that’s not particularly straight-forward for applications written in Python, at least in comparison to toolchains like Rust, Go or Deno.

Back in November 2017 Raffaele Messuti suggested using PyInstaller for this. I revisited that issue while looking through open Datasette issues ordered by least recently updated and decided to try it out again—and it worked! Here’s the resulting TIL, and I’ve attached a bundled datasette macOS binary file to the 0.53 release on GitHub.

There’s one catch: the binary isn’t signed, which means it shows security warnings that have to be worked around if you try to run it on macOS. I’ve started looking into the signing process—I’m going to need an Apple Developer account and to jump through a bunch of different hoops, but it looks like I should be able to get that working. Here’s my issue for that: GitHub Actions workflow to build and sign macOS binary executables—it looks like gon is the missing automation piece I need.

One thing that really impressed me about PyInstaller is the size of the resulting file. On both macOS and Linux it was able to create a roughly 8MB file containing Datasette, all of its dependencies AND a working Python environment. It’s pretty magic!

datasette-css-properties

I wrote this up in detail a couple of days ago: datasette-css-properties is an amusingly weird output plugin that turns the results of a SQL query into CSS custom property definitions which can then be used to style or insert content into the current page.

sqlite-utils 3.2

The big new feature in this release is cached table counts using triggers, which I described last week. Full release notes here.

I’ve opened an issue to take advantage of this optimization in Datasette itself.

datasette-export-notebook

This is an idea I’ve been bouncing around for a while, and during a bout of attempted-coup-induced insomnia I decided to sketch out an initial version.

datasette-export-notebook is a plugin that adds an export-to-notebook option to any table or query.

This provides a page of documentation with copy-and-paste examples for loading data from that table or query into a Jupyter or Observable notebook.

Here’s a live demo. The interface currently looks like this:

As often happens when building even simple plugins like this I identified some small improvements I can make to Datasette.

cbsa-datasette

Core-based statistical areas are a US government concept used for various statistical purposes. They are essentially metropolitan areas, based on central cities and the commuting area that they sit inside.

I built cbsa.datasettes.com this week to provide an API for looking up a CBSA based on a latitude and longitude point. Here’s a location within San Francisco for example:

https://cbsa.datasettes.com/core/by_lat_lon?longitude=-122.51&latitude=37.78

This returns San Francisco-Oakland-Berkeley, CA. Add .json and &_shape=array to the above URL to get a JSON API version.

The data comes from a shapefile published by the Bureau of Transportation Stastics. I’m using shapefile-to-sqlite to import it into a SpatiaLite database, then publishing it to Cloud Run using this GitHub Actions workfow. Full details in the README.

I built this mainly to act as a simple updated example of how to use Datasette and SpatiaLite to provide an API against data from a shapefile. I published a tutorial about doing this for timezones three years ago, but shapefile-to-sqlite makes it much easier.

Releases this week

• datasette-export-notebook: 0.1.1—2021-01-09
  Datasette plugin providing instructions for exporting data to Jupyter or Observable
• datasette-css-properties: 0.2—2021-01-07
  Experimental Datasette output plugin using CSS properties
• sqlite-utils: 3.2—2021-01-03
  Python CLI utility and library for manipulating SQLite databases

TIL this week

• Packaging a Python app as a standalone binary with PyInstaller