Python CLI utility and library for manipulating SQLite databases
APACHE-2.0 License
Bot releases are visible (Hide)
Published by simonw over 3 years ago
table.pks_and_rows_where()
method returning (primary_key, row_dictionary)
tuples - see Listing rows with their primary keys. (#240)table_or_view.drop(ignore=True)
option for avoiding errors if the table or view does not exist. (#237)sqlite-utils drop-view --ignore
and sqlite-utils drop-table --ignore
options. (#237)--alter
if an error occurs caused by a missing column. (#259)Published by simonw over 3 years ago
This release adds the ability to execute queries joining data from more than one database file - similar to the cross database querying feature introduced in Datasette 0.55.
db.attach(alias, filepath)
Python method can be used to attach extra databases to the same connection, see db.attach() in the Python API documentation. (#113)--attach
option attaches extra aliased databases to run SQL queries against directly on the command-line, see attaching additional databases in the CLI documentation. (#236)Published by simonw over 3 years ago
sqlite-utils insert --sniff
option for detecting the delimiter and quote character used by a CSV file, see Alternative delimiters and quote characters. (#230)table.rows_where()
, table.search()
and table.search_sql()
methods all now take optional offset=
and limit=
arguments. (#231)--no-headers
option for sqlite-utils insert --csv
to handle CSV files that are missing the header row, see CSV files without a header row. (#228)Published by simonw over 3 years ago
sqlite-utils insert --csv
now accepts optional --delimiter
and --quotechar
options. See Alternative delimiters and quote characters. (#223)Published by simonw almost 4 years ago
table.m2m()
method now accepts an optional alter=True
argument to specify that any missing columns should be added to the referenced table. See Working with many-to-many relationships. (#222)Published by simonw almost 4 years ago
This release introduces a new mechanism for speeding up count(*)
queries using cached table counts, stored in a _counts
table and updated by triggers. This mechanism is described in Cached table counts using triggers, and can be enabled using Python API methods or the new enable-counts
CLI command. (#212)
table.enable_counts()
method for enabling these triggers on a specific table.db.enable_counts()
method for enabling triggers on every table in the database. (#213)sqlite-utils enable-counts my.db
command for enabling counts on all or specific tables, see Enabling cached counts. (#214)sqlite-utils triggers
command for listing the triggers defined for a database or specific tables, see Listing triggers. (#218)db.use_counts_table
property which, if True
, causes table.count
to read from the _counts
table. (#215)table.has_counts_triggers
property revealing if a table has been configured with the new _counts
database triggers.db.reset_counts()
method and sqlite-utils reset-counts
command for resetting the values in the _counts
table. (#219)db.escape()
method has been renamed to db.quote()
and is now covered by the documentation: Quoting strings for use in SQL. (#217)table.triggers_dict
and db.triggers_dict
introspection properties. (#211, #216)sqlite-utils insert
now shows a more useful error message for invalid JSON. (#206)Published by simonw almost 4 years ago
optimize
sometimes creating larger database files. (#209)brew install sqlite-utils
installation method.Published by simonw almost 4 years ago
sqlite-utils analyze-tables my.db
outputs useful information about the table columns in the database, such as the number of distinct values and how many rows are null. See Analyzing tables for documentation. (#207)table.analyze_column(column)
Python method used by the analyze-tables
command - see Analyzing a column.table.update()
method now correctly handles values that should be stored as JSON. Thanks, Andreas Madsack. (#204)Published by simonw almost 4 years ago
This release introduces a new sqlite-utils search
command for searching tables, see Executing searches. (#192)
The table.search()
method has been redesigned, see Searching with table.search(). (#197)
The release includes minor backwards-incompatible changes, hence the version bump to 3.0. Those changes, which should not affect most users, are:
-c
shortcut option for outputting CSV is no longer available. The full --csv
option is required instead.-f
shortcut for --fmt
has also been removed - use --fmt
.table.search()
method now defaults to sorting by relevance, not sorting by rowid
. (#198)table.search()
method now returns a generator over a list of Python dictionaries. It previously returned a list of tuples.Also in this release:
query
, tables
, rows
and search
CLI commands now accept a new --tsv
option which outputs the results in TSV. (#193)table.virtual_table_using
property reveals if a table is a virtual table, and returns the upper case type of virtual table (e.g. FTS4
or FTS5
) if it is. It returns None
if the table is not a virtual table. (#196)table.search_sql()
method returns the SQL for searching a table, see Building SQL queries with table.search_sql().sqlite-utils rows
now accepts multiple optional -c
parameters specifying the columns to return. (#200)Changes since the 3.0a0 alpha release:
sqlite-utils search
command now defaults to returning every result, unless you add a --limit 20
option.sqlite-utils search -c
and table.search(columns=[])
options are now fully respected. (#201)Published by simonw almost 4 years ago
This release introduces a new sqlite-utils search
command for searching tables, see Executing searches. (#192)
The table.search()
method has been redesigned, see Searching with table.search(). (#197)
The release includes minor backwards-incompatible changes, hence the version bump to 3.0. Those changes, which should not affect most users, are:
-c
shortcut option for outputting CSV is no longer available. The full --csv
option is required instead.-f
shortcut for --fmt
has also been removed - use --fmt
.table.search()
method now defaults to sorting by relevance, not sorting by rowid
. (#198)table.search()
method now returns a generator over a list of Python dictionaries. It previously returned a list of tuples.Also in this release:
query
, tables
, rows
and search
CLI commands now accept a new --tsv
option which outputs the results in TSV. (#193)table.virtual_table_using
property reveals if a table is a virtual table, and returns the upper case type of virtual table (e.g. FTS4
or FTS5
) if it is. It returns None
if the table is not a virtual table. (#196)table.search_sql()
method returns the SQL for searching a table, see Building SQL queries with table.search_sql().sqlite-utils rows
now accepts multiple optional -c
parameters specifying the columns to return. (#200)Published by simonw almost 4 years ago
table.m2m(other_table, records)
method now takes any iterable, not just a list or tuple. Thanks, Adam Wolf. (#189)sqlite-utils insert
now displays a progress bar for CSV or TSV imports. (#173)@db.register_function(deterministic=True)
option for registering deterministic SQLite functions in Python 3.8 or higher. (#191)Published by simonw about 4 years ago
--encoding
option for processing CSV and TSV files that use a non-utf-8 encoding, for both the insert
and update
commands. (#182)--load-extension
option is now available to many more commands. (#137)--load-extension=spatialite
can be used to load SpatiaLite from common installation locations, if it is available. (#136)pk=["id"]
now has the same effect as passing pk="id"
. (#181)Published by simonw about 4 years ago
table.extract()
and sqlite-utils extract
now apply much, much faster - one example operation reduced from twelve minutes to just four seconds! (#172)sqlite-utils extract
no longer shows a progress bar, because it's fast enough not to need one.column_order=
option for table.transform()
which can be used to alter the order of columns in a table. (#175)sqlite-utils transform --column-order=
option (with a -o
shortcut) for changing column order. (#176)table.transform(drop_foreign_keys=)
parameter and the sqlite-utils transform --drop-foreign-key
option have changed. They now accept just the name of the column rather than requiring all three of the column, other table and other column. This is technically a backwards-incompatible change but I chose not to bump the major version number because the transform feature is so new. (#177).disable_fts()
, .rebuild_fts()
, .delete()
, .delete_where()
and .add_missing_columns()
methods all now return self
, which means they can be chained together with other table operations.Published by simonw about 4 years ago
This release introduces two key new capabilities: transform (#114) and extract (#42).
SQLite's ALTER TABLE has several documented limitations. The table.transform()
Python method and sqlite-utils transform
CLI command work around these limitations using a pattern where a new table with the desired structure is created, data is copied over to it and the old table is then dropped and replaced by the new one.
You can use these tools to drop columns, change column types, rename columns, add and remove NOT NULL
and defaults, remove foreign key constraints and more. See the transforming tables (CLI) and transforming tables (Python library) documentation for full details of how to use them.
Sometimes a database table - especially one imported from a CSV file - will contain duplicate data. A Trees
table may include a Species
column with only a few dozen unique values, when the table itself contains thousands of rows.
The table.extract()
method and sqlite-utils extract
commands can extract a column - or multiple columns - out into a separate lookup table, and set up a foreign key relationship from the original table.
The Python library extract() documentation describes how extraction works in detail, and Extracting columns into a separate table in the CLI documentation includes a detailed example.
@db.register_function
decorator can be used to quickly register Python functions as custom SQL functions, see Registering custom SQL functions. (#162)table.rows_where()
method now accepts an optional select=
argument for specifying which columns should be selected, see Listing rows.Published by simonw about 4 years ago
sqlite-utils add-foreign-keys
command for Adding multiple foreign keys at once. (#157)table.enable_fts(..., replace=True)
argument for replacing an existing FTS table with a new configuration. (#160)table.add_foreign_key(..., ignore=True)
argument for ignoring a foreign key if it already exists. (#112)Published by simonw about 4 years ago
table.rebuild_fts()
method for rebuilding a FTS index, see Rebuilding a full-text search table. (#155)sqlite-utils rebuild-fts data.db
command for rebuilding FTS indexes across all tables, or just specific tables. (#155)table.optimize()
method no longer deletes junk rows from the *_fts_docsize
table. This was added in 2.17 but it turns out running table.rebuild_fts()
is a better solution to this problem.Published by simonw about 4 years ago
This release handles a bug where replacing rows in FTS tables could result in growing numbers of unneccessary rows in the associated *_fts_docsize
table. (#149)
PRAGMA recursive_triggers=on
by default for all connections. You can turn it off with Database(recursive_triggers=False)
. (#152)table.optimize()
method now deletes unnecessary rows from the *_fts_docsize
table. (#153)sqlite_utils.AlterError
exception thrown by in add_foreign_keys()
.