Simon Willison’s Weblog


Weeknotes: More releases, more museums

4th November 2019

Lots of small releases this week.


I released two bug fix releases for Datasette—0.30.1 and 0.30.2. Changelog here. My Dogsheep personal analytics project means I’m using Datasette for my own data analysis every day, which inspires me to fix small but annoying bugs much more aggressively.

I’ve also set myself a Streak goal to land a commit to Datasette every day.

I landed a tiny new feature to master yesterday: a ?column__notin=x,y,z filter, working as an inverse of the existing ?column__in=x,y,z filter. See issue #614 for details.

More Niche Museums

I’ve been keeping up my streak of adding at least one new museum to every day. This week I added the Pirates Museum in Antananarivo, Madagascar, the David Rumsey Map Center at Stanford, Galerie de Paléontologie et d’Anatomie comparée in Paris, DEVIL-ish Little Things in Vancouver, Washington, Mardi Gras World in New Orleans, Environmental Volunteers EcoCenter in Palo Alto, the Evergreen Aviation & Space Museum (home of the Spruce Goose!) in McMinnville Oregon and Autoservicio Condorito in Mendoza.

Here’s that list of new museums with my photos of them (images rendered using datasette-json-html).


I released a new tiny CLI tool for manipulating SQLite databases. sqlite-transform lets you run transformation functions against the values in a specific column of a database. It currently has three sub-commands:

  • parsedate parses strings that looks like dates and turns them into YYYY-MM-DD, so they can be sorted.
  • parsedatetime turns them into ISO YYYY-MM-DDTHH:mm:ss timestamps.
  • lambda is the most fun: it lets you provide a snippet of Python code which will be executed against each value to perform a custom transformation. More details in issue #2.

Here’s how to use it to wrap the values in a specific column, including importing the textwrap module from the Python standard library:

$ sqlite-transform lambda my.db mytable mycolumn \
    --code='"\n".join(textwrap.wrap(value, 10))' \

Other releases


I’ve been having a lot of fun creating new features for my personal Dogsheep analytics site. Many of these take the form of simple HTML added to the private homepage. Most recently I added the ability to search through the people who follow me on Twitter (an evolution of this technique from last year). That feature is entirely implemented as the following HTML form:

  <form action="/twitter/users" method="GET">
      <input type="hidden" name="_where" value="id in (select follower_id from following where followed_id = 12497)">
      <input name="_search" type="search" placeholder="Search my Twitter followers"> <input type="submit" value="Search">

More tree data

I exported all 3.85 million 311 calls from the San Francisco data portal into a database, then extracted out the 80,000 calls that mention trees and loaded them into a separate Datasette instance. You can play with that here—it was the inspiration for creating the sqlite-transform tool because I needed a way to clean up the datetime columns.