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.