Python CLI utility and library for manipulating SQLite databases
APACHE-2.0 License
Bot releases are hidden (Show)
create-table
and insert-files
commands all now accept multiple --pk
options for compound primary keys. (#620)numpy
installation, producing a module 'numpy' has no attribute 'int8'
. (#632)Published by simonw 11 months ago
create-table
, insert
and upsert
all now accept a --strict
option.table.create()
and insert/upsert/insert_all/upsert_all
all now accept an optional strict=True
parameter.transform
command and table.transform()
method preserve strict mode when transforming a table.sqlite-utils create-table
command now accepts str
, int
and bytes
as aliases for text
, integer
and blob
respectively. (#606)Published by simonw 12 months ago
--load-extension=spatialite
option and find_spatialite() utility function now both work correctly on arm64
Linux. Thanks, Mike Coats. (#599)sqlite-utils insert
could cause your terminal cursor to disappear. Thanks, Luke Plant. (#433)datetime.timedelta
values are now stored as TEXT
columns. Thanks, Harald Nezbeda. (#522)Published by simonw about 1 year ago
rowid
values for a table rather than keeping them consistent across the operation. (#592)Published by simonw about 1 year ago
Adding foreign keys to a table no longer uses PRAGMA writable_schema = 1
to directly manipulate the sqlite_master
table. This was resulting in errors in some Python installations where the SQLite library was compiled in a way that prevented this from working, in particular on macOS. Foreign keys are now added using the table transformation mechanism instead. (#577)
This new mechanism creates a full copy of the table, so it is likely to be significantly slower for large tables, but will no longer trigger table sqlite_master may not be modified
errors on platforms that do not support PRAGMA writable_schema = 1
.
A new plugin, sqlite-utils-fast-fks, is now available for developers who still want to use that faster but riskier implementation.
Other changes:
foreign_keys=
allows you to replace the foreign key constraints defined on a table, and add_foreign_keys=
lets you specify new foreign keys to add. These complement the existing drop_foreign_keys=
parameter. (#577)--add-foreign-key
option which can be called multiple times to add foreign keys to a table that is being transformed. (#585)--pdb
option for opening a debugger on the first encountered error in your conversion script. (#581)sqlite-utils install -e '.[test]'
option did not work correctly.Published by simonw about 1 year ago
This release introduces a new plugin system. (#567)
sqlite-utils
. (#569)sqlite_utils.Database(..., execute_plugins=False)
option for disabling plugin execution. (#575)sqlite-utils install -e path-to-directory
option for installing editable code. This option is useful during the development of a plugin. (#570)table.create(...)
method now accepts replace=True
to drop and replace an existing table with the same name, or ignore=True
to silently do nothing if a table already exists with the same name. (#568)sqlite-utils insert ... --stop-after 10
option for stopping the insert after a specified number of records. Works for the upsert
command as well. (#561)--csv
and --tsv
modes for insert
now accept a --empty-null
option, which cases empty strings in the CSV file to be stored as null
in the database. (#563)db.rename_table(table_name, new_name)
method for renaming tables. (#565)sqlite-utils rename-table my.db table_name new_name
command for renaming tables. (#565)table.transform(...)
method now takes an optional keep_table=new_table_name
parameter, which will cause the original table to be renamed to new_table_name
rather than being dropped at the end of the transformation. (#571)table.transform()
without any arguments will reformat the SQL schema stored by SQLite to be more aesthetically pleasing. (#564)Published by simonw over 1 year ago
sqlite-utils
will now use sqlean.py in place of sqlite3
if it is installed in the same virtual environment. This is useful for Python environments with either an outdated version of SQLite or with restrictions on SQLite such as disabled extension loading or restrictions resulting in the sqlite3.OperationalError: table sqlite_master may not be modified
error. (#559)with db.ensure_autocommit_off()
context manager, which ensures that the database is in autocommit mode for the duration of a block of code. This is used by db.enable_wal()
and db.disable_wal()
to ensure they work correctly with pysqlite3
and sqlean.py
.db.iterdump()
method, providing an iterator over SQL strings representing a dump of the database. This uses sqlite-dump
if it is available, otherwise falling back on the conn.iterdump()
method from sqlite3
. Both pysqlite3
and sqlean.py
omit support for iterdump()
- this method helps paper over that difference.Published by simonw over 1 year ago
$
. (#551)bash
and zsh
. (#552)Published by simonw over 1 year ago
sqlite-utils tui
interface for interactively building command-line invocations, powered by Trogon. This requires an optional dependency, installed using sqlite-utils install trogon
. There is a screenshot in the documentation. (#545)sqlite-utils analyze-tables
command (documentation) now has a --common-limit 20
option for changing the number of common/least-common values shown for each column. (#544)sqlite-utils analyze-tables --no-most
and --no-least
options for disabling calculation of most-common and least-common values.null
values, analyze-tables
will no longer attempt to calculate the most common and least common values for that column. (#547)sqlite-utils analyze-tables
with non-existent columns in the -c/--column
option now results in an error message. (#548)table.analyze_column()
method (documented here) now accepts most_common=False
and least_common=False
options for disabling calculation of those values.Published by simonw over 1 year ago
--raw-lines
option for the sqlite-utils query
and sqlite-utils memory
commands, which outputs just the raw value of the first column of evy row. (#539)table.upsert_all()
failed if the not_null=
option was passed. (#538)ResourceWarning
when using sqlite-utils insert
. (#534)sqlite-utils insert
is called with invalid JSON. (#532)table.convert(..., skip_false=False)
and sqlite-utils convert --no-skip-false
options, for avoiding a misfeature where the convert() mechanism skips rows in the database with a falsey value for the specified column. Fixing this by default would be a backwards-incompatible change and is under consideration for a 4.0 release in the future. (#527)sqlite-utils transform
no longer breaks if a table defines default values for columns. Thanks, Kenny Song. (#509)table.transform()
did not work correctly. Thanks, Martin Carpenter. (#525)rows_from_file()
is passed a non-binary-mode file-like object. (#520)Published by simonw almost 2 years ago
table.search_sql(include_rank=True)
option, which adds a rank
column to the generated SQL. Thanks, Jacob Chapman. (#480)--nl
option. Thanks, Mischa Untaga. (#485)db.close()
method. (#504)sqlite-utils install
and sqlite-utils uninstall
commands for installing packages into the same virtual environment as sqlite-utils
, described here. (#483)Published by simonw about 2 years ago
sqlite-utils query
, memory
and bulk
commands now all accept a new --functions
option. This can be passed a string of Python code, and any callable objects defined in that code will be made available to SQL queries as custom SQL functions. See Defining custom SQL functions for details. (#471)db[table].create(...)
method now accepts a new transform=True
parameter. If the table already exists it will be transform to match the schema configuration options passed to the function. This may result in columns being added or dropped, column types being changed, column order being updated or not null and default values for columns being set. (#467)sqlite-utils create-table
command now accepts a --transform
option.table.default_values
returns a dictionary mapping each column name with a default value to the configured default value. (#475)--load-extension
option can now be provided a path to a compiled SQLite extension module accompanied by the name of an entrypoint, separated by a colon - for example --load-extension ./lines0:sqlite3_lines0_noread_init
. This feature is modelled on code first contributed to Datasette by Alex Garcia. (#470)db.register_function(fn, name=...)
parameter. (#458)--order
option for specifying the sort order for the returned rows. (#469)global
keyword. (#472)table.extract()
would not behave correctly for columns containing null values. Thanks, Forest Gregg. (#423)sqlite-utils
to import and clean an example CSV file.sqlite-utils
now have a Discord community. Join the Discord here.Published by simonw over 2 years ago
sqlite-utils duplicate data.db table_name new_name
CLI command for Duplicating tables. (#454)sqlite_utils.utils.rows_from_file()
is now a documented API. It can be used to read a sequence of dictionaries from a file-like object containing CSV, TSV, JSON or newline-delimited JSON. It can be passed an explicit format or can attempt to detect the format automatically. (#443)sqlite_utils.utils.TypeTracker
is now a documented API for detecting the likely column types for a sequence of string rows, see Detecting column types using TypeTracker. (#445)sqlite_utils.utils.chunks()
is now a documented API for splitting an iterator into chunks. (#451)sqlite-utils enable-fts
now has a --replace
option for replacing the existing FTS configuration for a table. (#450)create-index
, add-column
and duplicate
commands all now take a --ignore
option for ignoring errors should the database not be in the right state for them to operate. (#450)Published by simonw over 2 years ago
See also the annotated release notes for this release.
sqlite_utils.utils.utils.rows_from_file()
is now a documented API, see Reading rows from a file. (#443)rows_from_file()
has two new parameters to help handle CSV files with rows that contain more values than are listed in that CSV file's headings: ignore_extras=True
and extras_key="name-of-key"
. (#440)sqlite_utils.utils.maximize_csv_field_size_limit()
helper function for increasing the field size limit for reading CSV files to its maximum, see Setting the maximum CSV field size limit. (#442)table.search(where=, where_args=)
parameters for adding additional WHERE
clauses to a search query. The where=
parameter is available on table.search_sql(...)
as well. See Searching with table.search(). (#441)table.detect_fts()
and other search-related functions could fail if two FTS-enabled tables had names that were prefixes of each other. (#434)Published by simonw over 2 years ago
Now depends on click-default-group-wheel, a pure Python wheel package. This means you can install and use this package with Pyodide, which can run Python entirely in your browser using WebAssembly. (#429)
Try that out using the Pyodide REPL:
>>> import micropip
>>> await micropip.install("sqlite-utils")
>>> import sqlite_utils
>>> db = sqlite_utils.Database(memory=True)
>>> list(db.query("select 3 * 5"))
[{'3 * 5': 15}]
Published by simonw over 2 years ago
errors=r.IGNORE/r.SET_NULL
parameter for the r.parsedatetime()
and r.parsedate()
convert recipes. (#416)--multi
could not be used in combination with --dry-run
for the convert command. (#415)deterministic=True
is supported. (#425)Published by simonw over 2 years ago
Published by simonw over 2 years ago
hash_id_columns=
parameter for creating a primary key that's a hash of the content of specific columns - see Setting an ID based on the hash of the row contents for details. (#343)(3, 38, 0)
.Published by simonw over 2 years ago
sqlite-utils
command-line tool - thanks, Chris Amico. (#398)
--init-spatialite
option for initializing SpatiaLite on a newly created database.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)sqlite-utils transform
. (#403)Published by simonw over 2 years ago
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)