source: simon willison: sqlite-utils 4.1
level: technical
sqlite-utils 4.1 is the first minor release after version 4.0, bringing several small but useful features. the insert and upsert commands now accept a --code option, allowing users to provide a python code block or a path to a .py file that defines a rows() function or iterable. this lets you generate rows programmatically instead of importing from a file. for example, you can yield dictionaries with id and name fields directly from the command line.
another addition is the --type option for insert and upsert, which overrides the automatically chosen column type when creating a table. this is handy for csv or tsv columns like zip codes that look like integers but should be stored as text to keep leading zeros. the release also includes a new table.drop_index() method and a corresponding cli command to drop an index by name, with an ignore flag to skip missing indexes. the query command can now read sql from standard input by passing a dash instead of a query string.
the upsert command can now infer the primary key of an existing table, so you can omit --pk when upserting into a table that already has one. the transform command and its python methods gained support for changing a table's sqlite strict mode via --strict and --no-strict flags or a strict parameter. this was inspired by a post advocating for strict tables, and it uses the existing transform mechanism to copy data into a new table with the desired mode. the features were developed with ai assistance, including manual testing prompts to find edge cases.
why it matters: these updates make it easier to script sqlite data loading and schema changes, especially for data scientists and developers who need to handle messy csv files or enforce data integrity with strict tables.
source: simon willison: sqlite-utils 4.1