SQLite
SQLite is a database engine that stores all its data in a file on disk, rather than running a separate database server. I’ve used it for a lot of small projects.
2 articles
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.
Fast and random sampling in SQLite
I tested four approaches, from
ORDER BY RANDOM()to picking randomrowidvalues in Python, and found one that’s both fast and diverse.
14 notes
The SQLite command line shell will count your unclosed parentheses
If the prompt starts with
(x1or(x2, it means you’ve opened some parentheses and not closed them yet.Use SQL triggers to prevent overwriting a value
A trigger lets you run an action when you
INSERT,UPDATEorDELETEa value.How to play with SQLite functions without real data
You can run a
SELECT function(…);query without any tables.To filter the results of a SQLite tally for values with a certain frequency, use a
HAVINGinstead of aWHEREclauseSELECT col_name, COUNT(*)
FROM tbl_name
GROUP BY col_name
HAVING COUNT(*) > 100;Why isn’t
delete_where()deleting rows in sqlite-utils?The
delete_where()function doesn’t auto-commit, so you need to wrap itwith db.connor something else to trigger the commit.You can set an output mode for SQLite
Python 3.13 throws a
ResourceWarningfor SQLite databases that aren’t explicitly closedHow to tally the attributes of the top N rows in a SQLite table
Use a
WITHclause to do a nested query for the top N rows in the table, then do a tally over that result.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.
The COUNT(X) function only counts non-null values
How to tally combinations of values across multiple columns
Getting a tally of SQLite values as a CSV
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.
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.