Simon Willison: TILs on sqlite

sqlite SQLite timestamps with floating point seconds - 2024-03-13

Today I learned about this: …

sqlite Tracking SQLite table history using a JSON audit log - 2024-02-26

I continue to collect ways of tracking the history of a table of data stored in SQLite - see sqlite-history for previous experiments. …

sqlite Running Steampipe extensions in sqlite-utils and Datasette - 2023-12-20

Steampipe build software that lets you query different APIs directly from SQL databases. …

sqlite Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg - 2023-09-25

TG is an exciting new project in the world of open source geospatial libraries. It's a single C file (an amalgamation, similar to that provided by SQLite) which implements the subset of geospatial operations that I most frequently find myself needing: …

sqlite Trying out cr-sqlite on macOS - 2023-09-12

cr-sqlite is fascinating. It's a loadable SQLite extension by Matt Wonlaw that "allows merging different SQLite databases together that have taken independent writes". …

sqlite Compile and run a new SQLite version with the existing sqlite3 Python library on macOS - 2023-08-22

I've been trying to figure this out for years. Previous notes include Using LD_PRELOAD to run any version of SQLite with Python (Linux only), and Building a specific version of SQLite with pysqlite on macOS/Linux and Using pysqlite3 on macOS (both using the pysqlite3 package). But the dream was always to find a way to let me easily run a different SQLite version with the sqlite3 module from the Python standard library directly on my Mac. …

sqlite Calculating the size of a SQLite database file using SQL - 2023-08-21

I learned this trick today while browsing the code of Blacklite, a neat Java library for writing diagnostic logs to a SQLite database. …

sqlite A one-liner to output details of the current Python's SQLite - 2023-08-19

In investigating llm/issues/164 I found myself needing to know more precise details of the Python SQLite environment used by the reporter of the bug. …

sqlite Comparing two training datasets using sqlite-utils - 2023-05-23

WizardLM is "an Instruction-following LLM Using Evol-Instruct". It's a fine-tuned model on top of Meta's LLaMA. The fine-tuning uses 70,000 instruction-output pairs from this JSON file: …

sqlite Unix timestamp in milliseconds in SQLite - 2023-04-08

I wanted to retrieve the time in milliseconds since the Unix epoch in SQLite. …

sqlite Saving an in-memory SQLite database to a file in Python - 2023-04-08

I was messing around in Python with an in-memory SQLite database, when I decided I actually wanted to save my experimental database to a file so I could explore it using Datasette. …

sqlite Copy tables between SQLite databases - 2023-04-03

I figured out a pattern for doing this today using the sqlite3 CLI tool - given two SQLite databases in the current folder, called tils.db and simonwillisonblog.db: …

sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08

I figured out a single SQL query for the following today. Given a table of GitHub repositories, for each repository return: …

sqlite The SQLite now argument is stable within the same query - 2023-02-05

I stumbled across an interesting little detail of SQLite today, running the following query: …

sqlite Combining CTEs and VALUES in SQLite - 2023-01-29

Here's how to use SQLite's VALUES syntax with a CTE to create a temporary table that you can then perform joins against in a query: …

sqlite SQLite pragma_function_list() - 2023-01-27

The SQLite pragma_function_list() table-valued function returns a list of functions that have been registered with SQLite, including functions that were added by extensions. …

sqlite Loading SQLite extensions in Python on macOS - 2023-01-07

I finally found a workaround for this error when attempting to load a SQLite extension in Python on macOS: …

sqlite Geopoly in SQLite - 2023-01-04

I noticed this morning that one of my Datasette installations had the Geopoly SQLite extension enabled. I don't know how it got there - it has to be compiled specifically - but since it was there I decided to try it out. …

sqlite Comparing database rows before and after with SQLite JSON functions - 2022-12-14

Here's a trick I've been using to compare the rows in a table before and after I perform an operation against it. It works well for a few hundred (and maybe a few thousand) rows. …

sqlite SQLite can use more than one index for a query - 2022-12-11

I was trying to figure out if SQLite has the ability to use more than one index as part of executing a single query, or if it only ever picks a single index that it thinks will give the best performance. …

sqlite Finding the SQLite version used by Web SQL in Chrome - 2022-10-28

Google Chrome still includes support for Web SQL, the long since abandoned proposed standard for shipping a SQL engine in browser JavaScript. I was reminded of its existence today while reading about the new official sqlite3 wasm build. …

sqlite Returning related rows in a single SQL query using JSON - 2022-09-16

When building database-backed applications you'll often find yourself wanting to return a row from the database along with its related rows. …

sqlite SQLite VACUUM: database or disk is full - 2022-08-29

I was trying to run VACUUM against a large SQLite database file (~7GB) using sqlite-utils vacuum data.db and I got this error: …

sqlite Seeing which functions are unique to a specific SQLite / Datasette instance - 2022-08-23

In reading Scraping JSON, HTML, and ZIP Files with Pure SQLite by Alex Garcia I got curious to see a full list of functions he had registered in his sqlite-extension-examples.fly.dev Datasette instance that weren't available in a regular Datasette. …

sqlite Sort by number of JSON intersections - 2022-08-17

This post on Reddit asked how to run a query that takes a list of items (in this case ingredients) as the input and returns all rows with at least one of those items in a JSON list, ordered by the most matches. …

sqlite Trying out SQLite extensions on macOS - 2022-08-03

Alex Garcia has been building some really cool new custom extensions for SQLite, working in C and Go. So far he's released two: …

sqlite Related content with SQLite FTS and a Datasette template function - 2022-07-31

Today I added "related TILs" to this TIL website - so each TIL now shows five related TILs at the bottom of the page. …

sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20

I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility: …

sqlite Counting SQLite virtual machine operations - 2022-03-20

When SQLite executes a query, it does so by executing a sequence of virtual machine operations. …

sqlite The simplest recursive CTE - 2022-03-20

I found this really simple recursive CTE useful for ensuring I understood how to write recursive CTEs. …

sqlite Combining substr and instr to extract text - 2022-02-15

Derek Willis has a Datasette instance full of political campaign emails running on Heroku. …

sqlite Ordered group_concat() in SQLite - 2022-02-06

I was trying to use group_concat() to glue together some column values into a stiched together Markdown document. My first attempt looked like this: …

sqlite json_extract() path syntax in SQLite - 2022-01-18

Several of the SQLite JSON functions, such as json_extract() and json_array_length(), take a path argument. This uses custom syntax along the lines of $.a[2].b, as described in the documentation here. …

sqlite Track timestamped changes to a SQLite table using triggers - 2021-08-19

This is more of a "today I figured out" than a TIL. …

sqlite Building a specific version of SQLite with pysqlite on macOS/Linux - 2021-08-14

I wanted the ability to test my Python software against specific version of SQLite on macOS. I found a way to do that using pysqlite3. …

sqlite SQLite aggregate filter clauses - 2021-08-04

SQLite supports aggregate filter clauses (as of 3.30.0, released 2019-10-04), as described in this SQL Pivot in all databases tutorial. …

sqlite Importing CSV data into SQLite with .import - 2021-07-13

I usually use my sqlite-utils insert blah.db tablename file.csv --csv command to import CSV data into SQLite, but for large CSV files (like a 750MB one) this can take quite a long time - over half an hour in this case. …

sqlite Using pysqlite3 on macOS - 2021-07-10

While trying to use pysqlite3 on macOS I got the following error: …

sqlite Querying for items stored in UTC that were created on a Thursday in PST - 2021-03-12

This came up as a question 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? …

sqlite Splitting on commas in SQLite - 2021-02-01

I had an input string in x,y,z format and I needed to split it into three separate values in SQLite. I managed to do it using a confusing combination of the instr() and substr() functions. …

sqlite Fixing broken text encodings with sqlite-transform and ftfy - 2021-01-18

I was working with a database table that included values that were clearly in the wrong character encoding - values like this: …

sqlite Identifying column combination patterns in a SQLite table - 2021-01-12

Given a large, heterogeneous table I wanted to identify patterns in the rows in terms of which columns were not null. …

sqlite Replicating SQLite with rqlite - 2020-12-28

I tried out rqlite today, a "lightweight, distributed relational database, which uses SQLite as its storage engine". It uses the Raft consensus algorithm to allow multiple SQLite instances to elect a leader and replicate changes amongst themselves. …

sqlite Figuring out if a text value in SQLite is a valid integer or float - 2020-09-27

Given a table with a TEXT column in SQLite I want to figure out if every value in that table is actually the text representation of an integer or floating point value, so I can decide if it's a good idea to change the type of the column (using sqlite-utils transform). …

sqlite Compiling the SQLite spellfix.c module on macOS - 2020-09-19

I wanted to browse a backup copy of my Plex database, which is a SQLite file. I tried this: …

sqlite Enabling WAL mode for SQLite database files - 2020-08-09

I was getting occasional Error: database is locked messages from a Datasette instance that was running against a bunch of different SQLite files that were updated by cron scripts (my personal Dogsheep). …

sqlite SQLite BLOB literals - 2020-07-29

I wanted to construct a string of SQL that would return a blob value: …

sqlite Using LD_PRELOAD to run any version of SQLite with Python - 2020-06-17

I've been wanting to figure this out for ages. This thread on Hacker News plus this Stackoverflow post gave me some initial clues. …

sqlite List all columns in a SQLite database - 2020-05-06

Here's a devious trick for listing ALL columns in a SQLite database, using a SQL query that generates another SQL query. …

sqlite Compile a new sqlite3 binary on Ubuntu - 2020-04-30

I wanted to try the vacuum into backup command that was released in SQLite3 3.27.0 on 2019-02-07. …

sqlite Null case comparisons in SQLite - 2020-04-21

I wanted to say "output this transformed value if it's not null, otherwise nothing". The recipe I figured out was: …

sqlite Lag window function in SQLite - 2020-04-19

Here's how to use a lag window function to calculate new cases per day when the table just has total cases over time on different dates. …