3.24

• SpatiaLite helpers for the sqlite-utils command-line tool - thanks, Chris Amico. (#398)
  • sqlite-utils create-database --init-spatialite option for initializing SpatiaLite on a newly created database.
  • sqlite-utils add-geometry-column command for adding geometry columns.
  • sqlite-utils create-spatial-index command for adding spatial indexes.
• db[table].create(..., if_not_exists=True) option for creating a table only if it does not already exist. (#397)
• Database(memory_name="my_shared_database") parameter for creating a named in-memory database that can be shared between multiple connections. (#405)
• Documentation now describes how to add a primary key to a rowid table using sqlite-utils transform. (#403)

3.23

This release introduces four new utility methods for working with SpatiaLite. Thanks, Chris Amico. (#330)

• sqlite_utils.utils.find_spatialite() finds the location of the SpatiaLite module on disk.
• db.init_spatialite() initializes SpatiaLite for the given database.
• table.add_geometry_column(...) adds a geometry column to an existing table.
• table.create_spatial_index(...) creates a spatial index for a column.
• sqlite-utils batch now accepts a --batch-size option. (#392)

3.22.1

• All commands now include example usage in their --help - see CLI reference. (#384)
• Python library documentation has a new Getting started section. (#387)
• Documentation now uses Plausible analytics. (#389)

3.22

• New CLI reference documentation page, listing the output of --help for every one of the CLI commands. (#383)
• sqlite-utils rows now has --limit and --offset options for paginating through data. (#381)
• sqlite-utils rows now has --where and -p options for filtering the table using a WHERE query, see Returning all rows in a table. (#382)

3.21

CLI and Python library improvements to help run ANALYZE after creating indexes or inserting rows, to gain better performance from the SQLite query planner when it runs against indexes.

Three new CLI commands: create-database, analyze and bulk.

More details and examples can be found in the annotated release notes.

• New sqlite-utils create-database command for creating new empty database files. (#348)
• New Python methods for running ANALYZE against a database, table or index: db.analyze() and table.analyze(), see Optimizing index usage with ANALYZE. (#366)
• New sqlite-utils analyze command for running ANALYZE using the CLI. (#379)
• The create-index, insert and upsert commands now have a new --analyze option for running ANALYZE after the command has completed. (#379)
• New sqlite-utils bulk command which can import records in the same way as sqlite-utils insert (from JSON, CSV or TSV) and use them to bulk execute a parametrized SQL query. (#375)
• The CLI tool can now also be run using python -m sqlite_utils. (#368)
• Using --fmt now implies --table, so you don't need to pass both options. (#374)
• The --convert function applied to rows can now modify the row in place. (#371)
• The insert-files command supports two new columns: stem and suffix. (#372)
• The --nl import option now ignores blank lines in the input. (#376)
• Fixed bug where streaming input to the insert command with --batch-size 1 would appear to only commit after several rows had been ingested, due to unnecessary input buffering. (#364)

3.20

• sqlite-utils insert ... --lines to insert the lines from a file into a table with a single line column, see Inserting unstructured data with --lines and --text.
• sqlite-utils insert ... --text to insert the contents of the file into a table with a single text column and a single row.
• sqlite-utils insert ... --convert allows a Python function to be provided that will be used to convert each row that is being inserted into the database. See Applying conversions while inserting data, including details on special behavior when combined with --lines and --text. (#356)
• sqlite-utils convert now accepts a code value of - to read code from standard input. (#353)
• sqlite-utils convert also now accepts code that defines a named convert(value) function, see Converting data in columns.
• db.supports_strict property showing if the database connection supports SQLite strict tables.
• table.strict property (see .strict) indicating if the table uses strict mode. (#344)
• Fixed bug where sqlite-utils upsert ... --detect-types ignored the --detect-types option. (#362)

3.19

• The table.lookup() method now accepts keyword arguments that match those on the underlying table.insert() method: foreign_keys=, column_order=, not_null=, defaults=, extracts=, conversions= and columns=. You can also now pass pk= to specify a different column name to use for the primary key. (#342)

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)