Tags » sqlite

A faster way to copy SQLite databases between computers

Dumping a SQLite database to a text file can make it much smaller, which means you can download it faster.

TIL: To filter the results of a SQLite tally for values with a certain frequency, use a HAVING instead of a WHERE clause

SELECT col_name, COUNT(*)
FROM tbl_name
GROUP BY col_name
HAVING COUNT(*) > 100;

TIL: Why isn’t delete_where() deleting rows in sqlite-utils?

The delete_where() function doesn’t auto-commit, so you need to wrap it with db.conn or something else to trigger the commit.

TIL: You can set an output mode for SQLite

TIL: Python 3.13 throws a ResourceWarning for SQLite databases that aren’t explicitly closed

Fast and random sampling in SQLite

I tested four approaches, from ORDER BY RANDOM() to picking random rowid values in Python, and found one that’s both fast and diverse. Here’s what worked (and what didn’t).

Making inventory spreadsheets for my LEGO sets

Using the Rebrickable database downloads and sqlite-utils, I can quickly create spreadsheets that let me tick off the parts in each set.

TIL: How to tally the attributes of the top N rows in a SQLite table

Use a WITH clause to do a nested query for the top N rows in the table, then do a tally over that result.

TIL: How to get a tally of tally counts in SQLite

Using a nested query allows me to perform a two-level aggregation of the values in a column – how many values appear once, how many twice, and so on.

TIL: The COUNT(X) function only counts non-null values

TIL: How to tally combinations of values across multiple columns

TIL: Getting a tally of SQLite values as a CSV

TIL: Using sqlite-utils to convert a CSV into a SQLite database

You can use sqlite-utils on the command line to create a SQLite database from a CSV file.

TIL: Python’s sqlite3 context manager doesn’t close connections

The sqlite3.connect(…) context manager will hold connections open, so you need to remember to close it manually or write your own context manager.