3.19a0

• Extra keyword arguments for table.lookup() which are passed through to .insert(). #342

3.18

• The table.lookup() method now has an optional second argument which can be used to populate columns only the first time the record is created, see Working with lookup tables. (#339)
• sqlite-utils memory now has a --flatten option for flattening nested JSON objects into separate columns, consistent with sqlite-utils insert. (#332)
• table.create_index(..., find_unique_name=True) parameter, which finds an available name for the created index even if the default name has already been taken. This means that index-foreign-keys will work even if one of the indexes it tries to create clashes with an existing index name. (#335)
• Added py.typed to the module, so mypy should now correctly pick up the type annotations. Thanks, Andreas Longo. (#331)
• Now depends on python-dateutil instead of depending on dateutils. Thanks, Denys Pavlov. (#324)
• table.create() (see Explicitly creating a table) now handles dict, list and tuple types, mapping them to TEXT columns in SQLite so that they can be stored encoded as JSON. (#338)
• Inserted data with square braces in the column names (for example a CSV file containing a item[price]) column now have the braces converted to underscores: item_price_. Previously such columns would be rejected with an error. (#329)
• Now also tested against Python 3.10. (#330)

3.17.1

• sqlite-utils memory now works if files passed to it share the same file name. (#325)
• sqlite-utils query now returns [] in JSON mode if no rows are returned. (#328)

3.17

• The sqlite-utils memory command has a new --analyze option, which runs the equivalent of the analyze-tables command directly against the in-memory database created from the incoming CSV or JSON data. (#320)
• sqlite-utils insert-files now has the ability to insert file contents in to TEXT columns in addition to the default BLOB. Pass the --text option or use content_text as a column specifier. (#319)

3.16

• Type signatures added to more methods, including table.resolve_foreign_keys(), db.create_table_sql(), db.create_table() and table.create(). (#314)
• New db.quote_fts(value) method, see Quoting characters for use in search - thanks, Mark Neumann. (#246)
• table.search() now accepts an optional quote=True parameter. (#296)
• CLI command sqlite-utils search now accepts a --quote option. (#296)
• Fixed bug where --no-headers and --tsv options to sqlite-utils insert could not be used together. (#295)
• Various small improvements to API reference documentation.

3.15.1

• Python library now includes type annotations on almost all of the methods, plus detailed docstrings describing each one. (#311)
• New API Reference documentation page, powered by those docstrings.
• Fixed bug where .add_foreign_keys() failed to raise an error if called against a View. (#313)
• Fixed bug where .delete_where() returned a [] instead of returning self if called against a non-existant table. (#315)

3.15

• sqlite-utils insert --flatten option for flattening nested JSON objects to create tables with column names like topkey_nestedkey. (#310)
• Fixed several spelling mistakes in the documentation, spotted using codespell.
• Errors that occur while using the sqlite-utils CLI tool now show the responsible SQL and query parameters, if possible. (#309)

3.14

This release introduces the new sqlite-utils convert command (#251) and corresponding table.convert(...) Python method (#302). These tools can be used to apply a Python conversion function to one or more columns of a table, either updating the column in place or using transformed data from that column to populate one or more other columns.

This command-line example uses the Python standard library textwrap module to wrap the content of the content column in the articles table to 100 characters:

$ sqlite-utils convert content.db articles content\
    '"\n".join(textwrap.wrap(value, 100))'\
    --import=textwrap

The same operation in Python code looks like this:

import sqlite_utils, textwrap

db = sqlite_utils.Database("content.db")
db["articles"].convert("content", lambda v: "\n".join(textwrap.wrap(v, 100)))

See the full documentation for the sqlite-utils convert command and the table.convert(...) Python method for more details.

Also in this release:

• The new table.count_where(...) method, for counting rows in a table that match a specific SQL WHERE clause. (#305)
• New --silent option for the sqlite-utils insert-files command to hide the terminal progress bar, consistent with the --silent option for sqlite-utils convert. (#301)

3.13

• sqlite-utils schema my.db table1 table2 command now accepts optional table names. (#299)
• sqlite-utils memory --help now describes the --schema option.

3.12

• New db.query(sql, params) method, which executes a SQL query and returns the results as an iterator over Python dictionaries. (#290)
• This project now uses flake8 and has started to use mypy. (#291)
• New documentation on contributing to this project. (#292)