Simon Willison: TIL

Things I've learned, collected in simonw/til. You may also enjoy my blog.

Recently added: Running OCR against a PDF file with AWS Textract, Ignoring errors in a section of a Bash script, One-liner for running queries against CSV files with SQLite, Registering new Datasette plugin hooks by defining them in other plugins, Using iconv to convert the text encoding of a file

aws

Running OCR against a PDF file with AWS Textract - 2022-06-28
Helper function for pagination using AWS boto3 - 2022-01-19
Adding a CORS policy to an S3 bucket - 2022-01-04
Recovering data from AWS Lightsail using EC2 - 2021-01-16
Display EC2 instance costs per month - 2020-09-06

bash

Ignoring errors in a section of a Bash script - 2022-06-27
Using awk to add a prefix - 2022-04-08
nullglob in bash - 2022-02-14
Finding CSV files that start with a BOM using ripgrep - 2021-05-28
Skipping CSV rows with odd numbers of quotes using ripgrep - 2020-12-11
Escaping a SQL query to use with curl and Datasette - 2020-12-08
Escaping strings in Bash using !:q - 2020-10-01
Looping over comma-separated values in Bash - 2020-09-01

sqlite

One-liner for running queries against CSV files with SQLite - 2022-06-20
Counting SQLite virtual machine operations - 2022-03-20
The simplest recursive CTE - 2022-03-20
Combining substr and instr to extract text - 2022-02-15
Ordered group_concat() in SQLite - 2022-02-06
json_extract() path syntax in SQLite - 2022-01-18
Track timestamped changes to a SQLite table using triggers - 2021-08-19
Building a specific version of SQLite with pysqlite on macOS/Linux - 2021-08-14
SQLite aggregate filter clauses - 2021-08-04
Importing CSV data into SQLite with .import - 2021-07-13
Using pysqlite3 on macOS - 2021-07-10
Querying for items stored in UTC that were created on a Thursday in PST - 2021-03-12
Splitting on commas in SQLite - 2021-02-01
Fixing broken text encodings with sqlite-transform and ftfy - 2021-01-18
Identifying column combination patterns in a SQLite table - 2021-01-12
Replicating SQLite with rqlite - 2020-12-28
Figuring out if a text value in SQLite is a valid integer or float - 2020-09-27
Compiling the SQLite spellfix.c module on macOS - 2020-09-19
Enabling WAL mode for SQLite database files - 2020-08-09
SQLite BLOB literals - 2020-07-29
Using LD_PRELOAD to run any version of SQLite with Python - 2020-06-17
List all columns in a SQLite database - 2020-05-06
Compile a new sqlite3 binary on Ubuntu - 2020-04-30
Null case comparisons in SQLite - 2020-04-21
Lag window function in SQLite - 2020-04-19

datasette

Registering new Datasette plugin hooks by defining them in other plugins - 2022-06-17
Crawling Datasette with Datasette - 2022-02-27
Reusing an existing Click tool with register_commands - 2021-11-29
Searching all columns of a table in Datasette - 2021-08-23
Running Datasette on Replit - 2021-05-02
Querying for GitHub issues open for less than 60 seconds - 2021-03-12
Serving MBTiles with datasette-media - 2021-02-03
Redirects for Datasette - 2020-11-25

linux

Using iconv to convert the text encoding of a file - 2022-06-14
Enabling a user to execute a specific command as root without a password - 2022-02-13
Basic strace to see what a process is doing - 2020-09-07
Piping echo to a file owned by root using sudo and tee - 2020-08-24

jinja

Formatting thousands in Jinja - 2022-06-08
Turning on Jinja autoescaping when using Template() directly - 2020-09-18

networking

Making HTTP calls using IPv6 - 2022-06-08

django

Using just with Django - 2022-06-06
Django data migration using a PostgreSQL CTE - 2021-05-17
Enabling a gin index for faster LIKE queries - 2021-05-16
migrations.RunSQL.noop for reversible SQL migrations - 2021-05-02
Django Admin action for exporting selected rows as CSV - 2021-04-25
Filter by comma-separated values in the Django admin - 2021-04-21
Usable horizontal scrollbars in the Django admin for mouse users - 2021-04-20
Enabling the fuzzystrmatch extension in PostgreSQL with a Django migration - 2021-04-18
Efficient bulk deletions in Django - 2021-04-09
How to almost get facet counts in the Django admin - 2021-03-11
Pretty-printing all read-only JSON in the Django admin - 2021-03-07
Show the timezone for datetimes in the Django admin - 2021-03-02
Writing tests for the Django admin with pytest-django - 2021-03-02
Adding extra read-only information to a Django admin change page - 2021-02-25
PostgreSQL full-text search in the Django Admin - 2020-07-25

fly

Wildcard DNS and SSL on Fly - 2022-05-25
Writing Fly logs to S3 - 2022-05-25
Using the Fly Docker registry - 2022-05-21
Using the undocumented Fly GraphQL API - 2022-01-21
Assigning a custom subdomain to a Fly app - 2021-11-20

github-actions

Optimizing PNGs in GitHub Actions using Oxipng - 2022-05-18
GitHub Actions job summaries - 2022-05-17
Deploying a live Datasette demo when the tests pass - 2022-03-27
Using the GitHub Actions cache with npx and no package.json - 2022-03-22
Testing against Python 3.11 preview using GitHub Actions - 2022-02-02
Storing files in an S3 bucket between GitHub Actions runs - 2021-12-07
Attaching a generated file to a GitHub release using Actions - 2021-09-07
Installing different PostgreSQL server versions in GitHub Actions - 2021-07-05
Running tests against PostgreSQL in a service container - 2021-02-23
Using Prettier to check JavaScript code style in GitHub Actions - 2020-12-31
Talking to a PostgreSQL service container from inside a Docker container - 2020-09-18
Open a debugging shell in GitHub Actions with tmate - 2020-09-14
Skipping a GitHub Actions step without failing - 2020-08-22
Using grep to write tests in CI - 2020-08-19
Updating a Markdown table of contents with a GitHub Action - 2020-07-22
Running different steps on a schedule - 2020-04-20
Commit a file if it changed - 2020-04-19
Set environment variables for all steps in a GitHub Action - 2020-04-19
Dump out all GitHub Actions context - 2020-04-19
Only run GitHub Action on push to master / main - 2020-04-19

python

Efficiently copying a file - 2022-05-13
Generating a calendar week grid with the Python Calendar module - 2022-03-31
Streaming indented output of a JSON array - 2022-01-17
Annotated explanation of David Beazley's dataklasses - 2021-12-19
Safely outputting JSON - 2021-12-17
Using C_INCLUDE_PATH to install Python packages - 2021-12-09
__init_subclass__ - 2021-12-03
Ignoring a line in both flake8 and mypy - 2021-11-30
Using cog to update --help in a Markdown README file - 2021-11-18
Planning parallel downloads with TopologicalSorter - 2021-11-16
Using the sqlite3 Python module in Pyodide - Python WebAssembly - 2021-10-18
Using Fabric with an SSH public key - 2021-10-06
Find local variables in the traceback for an exception - 2021-08-09
Check spelling using codespell - 2021-08-03
Tracing every executed Python statement - 2021-03-21
Using io.BufferedReader to peek against a non-peekable stream - 2021-02-15
Handling CSV files with wide columns in Python - 2021-02-15
Packaging a Python app as a standalone binary with PyInstaller - 2021-01-04
Relinquishing control in Python asyncio - 2020-12-29
Controlling the style of dumped YAML using PyYAML - 2020-12-07
Running Python code in a subprocess with a time limit - 2020-12-06
Decorators with optional arguments - 2020-10-28
Explicit file encodings using click.File - 2020-10-16
Understanding option names in Click - 2020-09-22
Debugging a Click application using pdb - 2020-09-03
Outputting JSON with reduced floating point precision - 2020-08-21
How to call pip programatically from Python - 2020-08-11
Password hashing in Python with pbkdf2 - 2020-07-13
Introspecting Python function parameters - 2020-05-27
Build the official Python documentation locally - 2020-05-08
Use setup.py to install platform-specific dependencies - 2020-05-05
Installing and upgrading Datasette plugins with pipx - 2020-05-04
Generated a summary of nested JSON data - 2020-04-28
macOS Catalina sort-of includes Python 3 - 2020-04-21
Convert a datetime object to UTC without using pytz - 2020-04-19

github

Setting up a custom subdomain for a GitHub Pages site - 2022-05-04
Configuring Dependabot for a Python project with dependencies in setup.py - 2022-01-14
Transferring a GitHub issue from a private to a public repository - 2021-12-22
Syntax highlighting Python console examples with GFM - 2021-01-18
Bulk fetching repository details with the GitHub GraphQL API - 2021-01-17
Searching for repositories by topic using the GitHub GraphQL API - 2020-10-09
Paginating through the GitHub GraphQL API with Python - 2020-07-09
Accessing repository dependencies in the GitHub GraphQL API - 2020-04-30

service-workers

Intercepting fetch in a service worker - 2022-04-30

macos

Atuin for zsh shell history in SQLite - 2022-04-26
Seeing files opened by a process using opensnoop - 2022-04-26
Trick Apple Photos into letting you access your video files - 2022-04-12
Installing Python on macOS with the official Python installer - 2022-02-28
Using lsof on macOS - 2021-12-11
Running Docker on an M1 Mac - 2021-05-25
Shrinking PNG files with pngquant and oxipng - 2021-02-07
Finding the largest SQLite files on a Mac - 2020-08-19
Fixing "compinit: insecure directories" error - 2020-04-26
Close terminal window on Ctrl+D for macOS - 2020-04-21
Get Skitch working on Catalina - 2020-04-21
Running pip install -e .[test] in zsh on macOS Catalina - 2020-04-21

sphinx

Format code examples in documentation with blacken-docs - 2022-04-24
Adding Sphinx autodoc to a project, and configuring Read The Docs to build it - 2021-08-10
Using sphinx.ext.extlinks for issue links - 2021-02-17

twitter

How to get credentials for a new Twitter bot - 2022-04-17
Exporting and editing a Twitter Spaces recording - 2022-03-23

spatialite

GUnion to combine geometries in SpatiaLite - 2022-04-12
Creating a minimal SpatiaLite database with Python - 2021-12-17
KNN queries with SpatiaLite - 2021-05-16

auth0

Logging users out of Auth0 - 2022-04-03
Simplest possible OAuth authentication with Auth0 - 2022-03-26

pytest

Treating warnings as errors in pytest - 2022-04-01
Async fixtures with pytest-asyncio - 2022-03-19
pytest coverage with context - 2022-03-04
Opt-in integration tests with pytest --integration - 2022-01-26
Testing a Click app with streaming input - 2022-01-09
Writing pytest tests against tools written with argparse - 2022-01-08
Quick and dirty mock testing with mock_calls - 2021-11-02
Using VCR and pytest with pytest-recording - 2021-11-02
Start a server in a subprocess during a pytest session - 2020-08-31
Code coverage using pytest and codecov.io - 2020-08-15
Registering temporary pluggy plugins inside tests - 2020-07-21
Asserting a dictionary is a subset of another dictionary - 2020-05-28
How to mock httpx using pytest-mock - 2020-04-29
Session-scoped temporary directories in pytest - 2020-04-26

docker

Allowing a container in Docker Desktop for Mac to talk to a PostgreSQL server on the host machine - 2022-03-31
Docker Compose for Django development - 2021-05-24
Installing packages from Debian unstable in a Docker image based on stable - 2021-03-22
Running gdb against a Python process in a running Docker container - 2021-03-21
Attaching a bash shell to a running Docker container - 2020-08-10

webassembly

Compiling to WASM with llvm on macOS - 2022-03-28

shot-scraper

Extracting web page content using Readability.js and shot-scraper - 2022-03-24

git

Rewriting a repo to contain the history of just specific files - 2022-03-22
Removing a git commit and force pushing to remove it from history - 2021-10-22

google-sheets

Concatenating strings and newlines in Google Sheets - 2022-03-15

gis

Natural Earth in SpatiaLite and Datasette - 2022-03-04
Downloading MapZen elevation tiles - 2021-02-04

homebrew

Running the latest SQLite in Datasette using Homebrew - 2022-02-28
Running a MySQL server using Homebrew - 2021-06-11
Upgrading Python Homebrew packages using pip - 2020-10-14
Browsing your local git checkout of homebrew-core - 2020-08-27
Packaging a Python CLI tool for Homebrew - 2020-08-11

graphql

Using curl to run GraphQL queries from the command line - 2022-02-21
get-graphql-schema - 2022-02-01

googlecloud

Recursively fetching metadata for all files in a Google Drive folder - 2022-02-16
Google OAuth for a CLI application - 2022-02-16
Publishing to a public Google Cloud bucket with gsutil - 2021-09-20

npm

Annotated package.json for idb-keyval - 2022-02-10
Publishing a Web Component to npm - 2021-11-28

readthedocs

Linking from /latest/ to /stable/ on Read The Docs - 2022-01-20
Promoting the stable version of the documentation using rel=canonical - 2022-01-20
Using custom Sphinx templates on Read the Docs - 2020-12-07
Read the Docs Search API - 2020-08-16
Pointing a custom subdomain at Read the Docs - 2020-08-14

pixelmator

Pixel editing a favicon with Pixelmator - 2022-01-20

javascript

JavaScript date objects - 2022-01-16
Using Tesseract.js to OCR every image on a page - 2021-11-09
Loading lit from Skypack - 2021-09-21
Preventing double form submissions with JavaScript - 2021-07-08
Scroll page to form if there are errors - 2021-05-08
Using Jest without a package.json - 2020-12-30
Dropdown menu with details summary - 2020-10-31
Writing JavaScript that responds to media queries - 2020-10-21
Manipulating query strings with URLSearchParams - 2020-10-04
Minifying JavaScript with npx uglify-js - 2020-08-30
Dynamically loading multiple assets with a callback - 2020-08-21
Working around the size limit for nodeValue in the DOM - 2020-08-21
Implementing a "copy to clipboard" button - 2020-07-23

webauthn

WebAuthn browser support - 2021-12-29

kubernetes

kubectl proxy - 2021-12-28
Basic Datasette in Kubernetes - 2021-11-05

cloudflare

Adding a robots.txt using Cloudflare workers - 2021-12-21

pluggy

Registering the same Pluggy hook multiple times in a single file - 2021-12-16

caddy

Pausing traffic and retrying in Caddy - 2021-11-24

cloudrun

Using build-arg variables with Cloud Run deployments - 2021-11-19
Tailing Google Cloud Run request logs and importing them into SQLite - 2021-08-09
Increasing the time limit for a Google Cloud Scheduler task - 2021-07-08
Switching between gcloud accounts - 2021-05-18
Listing files uploaded to Cloud Build - 2021-04-14
Using the gcloud run services list command - 2020-09-01
How to deploy a folder with a Dockerfile to Cloud Run - 2020-08-04
Use labels on Cloud Run services for a billing breakdown - 2020-04-21

deno

Annotated code for a demo of WebSocket chat in Deno Deploy - 2021-11-06

web-components

Understanding Kristofer Joseph's Single File Web Component - 2021-10-27

electron

Configuring auto-update for an Electron app - 2021-09-13
Bundling Python inside an Electron app - 2021-09-08
Signing and notarizing an Electron app for distribution using GitHub Actions - 2021-09-08
Open external links in an Electron app using the system browser - 2021-09-02
Using the Chrome DevTools console as a REPL for an Electron app - 2021-08-31

sql

Cumulative total over time in SQL - 2021-09-13
Building a Markdown summary of Django group permissions - 2021-06-03
Finding duplicate records by matching name and nearby distance - 2021-05-19

purpleair

Calculating the AQI based on the Purple Air API for a sensor - 2021-08-31

observable-plot

Histogram with tooltips in Observable Plot - 2021-08-21

imagemagick

Compressing an animated GIF with gifsicle or ImageMagick mogrify - 2021-08-05
Set a GIF to loop using ImageMagick - 2021-08-03

vscode

Search and replace with regular expressions in VS Code - 2021-08-02
Language-specific indentation settings in VS Code - 2021-04-04

jq

Extracting objects recursively with jq - 2021-07-24
Turning an array of arrays into objects with jq - 2021-05-25
Converting no-decimal-point latitudes and longitudes using jq - 2021-03-11
Flattening nested JSON objects with jq - 2021-03-11
Loading radio.garden into SQLite using jq - 2021-02-17
Converting Airtable JSON for use with sqlite-utils using jq - 2020-08-28

tesseract

Using the tesseract CLI tool - 2021-07-18

nginx

Using nginx to proxy to a Unix domain socket - 2021-07-10

reddit

Scraping Reddit via their JSON API - 2021-06-21

vim

Mouse support in vim - 2021-06-19

amplitude

Exporting Amplitude events to SQLite - 2021-06-06

vega

Vega-Lite bar charts in the same order as the data - 2021-05-15

wikipedia

The Wikipedia page stats API - 2021-05-13

postgresql

Constructing GeoJSON in PostgreSQL - 2021-04-24
Using json_extract_path in PostgreSQL - 2021-04-13
Using unnest() to use a comma-separated string as the input to an IN query - 2021-04-10
Closest locations to a point - 2021-03-22
Granting a PostgreSQL user read-only access to some tables - 2021-02-26
Show the SQL schema for a PostgreSQL database - 2021-02-23

markdown

Useful Markdown extensions in Python - 2021-04-03
Rendering Markdown with the GitHub Markdown API - 2020-08-22
Converting HTML and rich-text to Markdown - 2020-05-09

azure

Writing an Azure Function that serves all traffic to a subdomain - 2021-03-27

zeit-now

Redirecting all paths on a Vercel instance - 2021-03-27
Running a Python ASGI app on Vercel - 2020-04-19

mediawiki

How to run MediaWiki with SQLite on a macOS laptop - 2021-03-06

cookiecutter

Conditionally creating directories in cookiecutter - 2021-01-27
Testing cookiecutter templates with pytest - 2021-01-27

jupyter

Embedding JavaScript in a Jupyter notebook - 2021-01-22

digitalocean

Running Datasette on DigitalOcean App Platform - 2020-10-06

selenium

Using async/await in JavaScript in Selenium - 2020-10-02
Installing Selenium for Python on macOS with ChromeDriver - 2020-10-02

typescript

Very basic tsc usage - 2020-09-06

svg

Creating a dynamic line chart with SVG - 2020-08-22

ics

Providing a "subscribe in Google Calendar" link for an ics feed - 2020-08-21

heroku

Programatically accessing Heroku PostgreSQL from GitHub Actions - 2020-08-18
Upgrading a Heroku PostgreSQL database with pg:copy - 2020-07-20
Using heroku pg:pull to restore a backup to a macOS laptop - 2020-07-10

zsh

Customizing my zsh prompt - 2020-08-12

asgi

Writing tests for the ASGI lifespan protocol with HTTPX - 2020-06-29

pypi

Adding project links to PyPI - 2020-05-11

firefox

Search across all loaded resources in Firefox - 2020-05-05

node

Constant-time comparison of strings in Node - 2020-05-01

tailscale

Restricting SSH connections to devices within a Tailscale network - 2020-04-23

presenting

Using macOS stickies to display a workshop link on the screen - 2020-04-20