Simon Willison’s Weblog

Subscribe

TILs

Filters: Sorted by date

TIL Django data migration using a PostgreSQL CTE — I figured out how to use a PostgreSQL CTE as part of an update statement in a Django data migration. The trick here is mainly understanding how to combine CTEs with a PostgreSQL update - here's the pattern for that:
18th May 2021, 12:04 am
TIL KNN queries with SpatiaLite — The latest version of SpatiaLite adds KNN support, which makes it easy to efficiently answer the question "what are the X closest records to this point".
17th May 2021, 1:12 am
TIL Enabling a gin index for faster LIKE queries — I tried using a gin index to speed up `LIKE '%term%'` queries against a column.
17th May 2021, 12:59 am
TIL Vega-Lite bar charts in the same order as the data — I've been puzzling over this one for a couple of years now, and I finally figured out the solution.
16th May 2021, 12:25 am
TIL The Wikipedia page stats API — Via https://alexgarcia.xyz/dataflow/examples/wiki-pageviews/ I found this API for retrieving daily pageview stats from Wikipedia for any article:
13th May 2021, 7:26 pm
TIL Scroll page to form if there are errors — For a Django application I'm working on ([this issue](https://github.com/simonw/django-sql-dashboard/issues/44)) I have a form that can be quite a long way down the page.
8th May 2021, 5:13 pm
TIL Running Datasette on Replit — I figured out how to run Datasette on https://replit.com/
2nd May 2021, 6:50 pm
TIL migrations.RunSQL.noop for reversible SQL migrations — `migrations.RunSQL.noop` provides an easy way to create "reversible" Django SQL migrations, where the reverse operation does nothing (but keeps it possible to reverse back to a previous migration state without being blocked by an irreversible migration).
2nd May 2021, 5:48 pm
TIL Django Admin action for exporting selected rows as CSV — I wanted to add an action option to the Django Admin for exporting the currently selected set of rows (or every row in the table) as a CSV file.
26th Apr 2021, 12:38 am
TIL Constructing GeoJSON in PostgreSQL — In order to efficiently generate a GeoJSON representation of a vast number of locations, I'm currently experimenting with generating the GeoJSON directly inside a PostgreSQL SQL query using `json_build_object()` and friends.
24th Apr 2021, 6:55 pm
TIL Filter by comma-separated values in the Django admin — I have a text column which contains comma-separated values - inherited from an older database schema.
21st Apr 2021, 4:31 pm
TIL Usable horizontal scrollbars in the Django admin for mouse users — I got a complaint from a Windows-with-mouse user of a Django admin project I'm working on: they couldn't see the right hand columns in a table without scrolling horizontally, but since the horizontal scrollbar was only available at the bottom of the page they had to scroll all the way to the bottom first in order to scroll sideways.
20th Apr 2021, 7:06 pm
TIL Enabling the fuzzystrmatch extension in PostgreSQL with a Django migration — The PostgreSQL [fuzzystrmatch extension](https://www.postgresql.org/docs/13/fuzzystrmatch.html) enables several functions for fuzzy string matching: `soundex()`, `difference()`, `levenshtein()`, `levenshtein_less_equal()`, `metaphone()`, `dmetaphone()` and `dmetaphone_alt()`.
18th Apr 2021, 7:32 pm
TIL Listing files uploaded to Cloud Build — Today while running `datasette publish cloudrun ...` I noticed the following:
15th Apr 2021, 6:09 am
TIL Using json_extract_path in PostgreSQL — The `json_extract_path()` function in PostgreSQL can be used to extract specific items from JSON - but I couldn't find documentation for the path language it uses.
14th Apr 2021, 12:29 am
TIL Using unnest() to use a comma-separated string as the input to an IN query — [django-sql-dashboard](https://github.com/simonw/django-sql-dashboard) lets you define a SQL query plus one or more text inputs that the user can provide in order to execute the query.
10th Apr 2021, 11:31 pm
TIL Efficient bulk deletions in Django — I needed to bulk-delete a large number of objects today. Django deletions are relatively inefficient by default, because Django implements its own version of cascading deletions and fires signals for each deleted object.
9th Apr 2021, 5:58 pm
TIL Language-specific indentation settings in VS Code — When I'm working with Python I like four space indents, but for JavaScript or HTML I like two space indents.
4th Apr 2021, 5:25 pm
TIL Useful Markdown extensions in Python — I wanted to render some markdown in Python but with the following extra features:
4th Apr 2021, 4:22 am
TIL Writing an Azure Function that serves all traffic to a subdomain — [Azure Functions](https://docs.microsoft.com/en-us/azure/azure-functions/) default to serving traffic from a path like `/api/FunctionName` - for example `https://your-subdomain.azurewebsites.net/api/MyFunction`.
28th Mar 2021, 3:49 am
TIL Redirecting all paths on a Vercel instance — I wanted to redirect all traffic to `https://something.vercel.app/` to a different domain - preserving the path and the querystring and serving a 301 status code.
27th Mar 2021, 3:45 pm
TIL Closest locations to a point — Here's a PostgreSQL SQL query that returns the closest locations to a point, based on a brute-force approach where the database calculates the distance (in miles) to every single row and then sorts by that distance.
23rd Mar 2021, 5:19 am
TIL Installing packages from Debian unstable in a Docker image based on stable — For [Datasette #1249](https://github.com/simonw/datasette/issues/1249) I wanted to build a Docker image from the `python:3.9.2-slim-buster` base image ("buster" is the current stable release of Debian) but include a single package from "sid", the unstable Debian distribution.
22nd Mar 2021, 9:42 pm
TIL Tracing every executed Python statement — Today I learned how to use the Python [trace module](https://docs.python.org/3/library/trace.html) to output every single executed line of Python code in a program - useful for figuring out exactly when a crash or infinite loop happens.
22nd Mar 2021, 5:51 am
TIL Running gdb against a Python process in a running Docker container — While investigating [Datasette issue #1268](https://github.com/simonw/datasette/issues/1268) I found myself with a Python process that was hanging, and I decided to try running `gdb` against it based on tips in [Debugging of CPython processes with gdb](https://www.podoliaka.org/2016/04/10/debugging-cpython-gdb/)
22nd Mar 2021, 5:48 am
TIL Querying for items stored in UTC that were created on a Thursday in PST — This came up as [a question](https://news.ycombinator.com/item?id=26443148) on Hacker News. How can you query a SQLite database for items that were created on a Thursday in PST, when the data is stored in UTC?
13th Mar 2021, 3:55 am
TIL Querying for GitHub issues open for less than 60 seconds — While [writing this thread](https://twitter.com/simonw/status/1370390336514658310) about my habit of opening issues and closing them a few seconds later just so I could link to them in a commit message I decided to answer the question "How many of my issues were open for less than 60 seconds?"
12th Mar 2021, 3:34 pm
TIL How to almost get facet counts in the Django admin — For a tantalizing moment today I thought I'd found a recipe for adding facet counts to the Django admin.
11th Mar 2021, 10:50 pm
TIL Converting no-decimal-point latitudes and longitudes using jq — I had some data with weird co-ordinates in it:
11th Mar 2021, 4:04 pm
TIL Flattening nested JSON objects with jq — I wanted to take a nested set of JSON objects and import them into a SQLite database using `sqlite-utils insert` - but I wanted to "flatten" some of the nested rows.
11th Mar 2021, 3:54 pm

Years