- The :ref:`insert-files command <cli_insert_files>` can now read from standard input:
cat dog.jpg | sqlite-utils insert-files dogs.db pics - --name=dog.jpg
. (#127) - You can now specify a full-text search tokenizer using the new
tokenize=
parameter to :ref:`enable_fts() <python_api_fts>`. This means you can enable Porter stemming on a table by runningdb["articles"].enable_fts(["headline", "body"], tokenize="porter")
. (#130) - You can also set a custom tokenizer using the :ref:`sqlite-utils enable-fts <cli_fts>` CLI command, via the new
--tokenize
option.
memoryview
anduuid.UUID
objects are now supported.memoryview
objects will be stored usingBLOB
anduuid.UUID
objects will be stored usingTEXT
. (#128)
The theme of this release is better tools for working with binary data. The new insert-files
command can be used to insert binary files directly into a database table, and other commands have been improved with better support for BLOB columns.
sqlite-utils insert-files my.db gifs *.gif
can now insert the contents of files into a specified table. The columns in the table can be customized to include different pieces of metadata derived from the files. See :ref:`cli_insert_files`. (#122)--raw
option tosqlite-utils query
- for outputting just a single raw column value - see :ref:`cli_query_raw`. (#123)- JSON output now encodes BLOB values as special base64 obects - see :ref:`cli_query_json`. (#125)
- The same format of JSON base64 objects can now be used to insert binary data - see :ref:`cli_inserting_data`. (#126)
- The
sqlite-utils query
command can now accept named parameters, e.g.sqlite-utils :memory: "select :num * :num2" -p num 5 -p num2 6
- see :ref:`cli_query_json`. (#124)
- New
--truncate
option tosqlite-utils insert
, andtruncate=True
argument to.insert_all()
. Thanks, Thomas Sibley. (#118) - The
sqlite-utils query
command now runs updates in a transaction. Thanks, Thomas Sibley. (#120)
- Added documentation for the
table.pks
introspection property. (#116)
- The
sqlite-utils
command now supports UPDATE/INSERT/DELETE in addition to SELECT. (#115)
- Added custom project links to the PyPI listing.
- New
sqlite-utils drop-table
command, see :ref:`cli_drop_table`. (#111) - New
sqlite-utils drop-view
command, see :ref:`cli_drop_view`. - Python
decimal.Decimal
objects are now stored asFLOAT
. (#110)
- New
sqlite-utils create-table
command, see :ref:`cli_create_table`. (#27) - New
sqlite-utils create-view
command, see :ref:`cli_create_view`. (#107)
db.create_view(...)
now has additional parametersignore=True
orreplace=True
, see :ref:`python_api_create_view`. (#106)
- New
sqlite-utils views my.db
command for listing views in a database, see :ref:`cli_views`. (#105) sqlite-utils tables
(andviews
) has a new--schema
option which outputs the table/view schema, see :ref:`cli_tables`. (#104)- Nested structures containing invalid JSON values (e.g. Python bytestrings) are now serialized using
repr()
instead of throwing an error. (#102)
- New
columns=
argument for the.insert()
,.insert_all()
,.upsert()
and.upsert_all()
methods, for over-riding the auto-detected types for columns and specifying additional columns that should be added when the table is created. See :ref:`python_api_custom_columns`. (#100)
- New
table.rows_where(..., order_by="age desc")
argument, see :ref:`python_api_rows`. (#76)
- Panda's Timestamp is now stored as a SQLite TEXT column. Thanks, b0b5h4rp13! (#96)
table.last_pk
is now only available for inserts or upserts of a single record. (#98)- New
Database(filepath, recreate=True)
parameter for deleting and recreating the database. (#97)
- Fixed bug where columns with only null values were not correctly created. (#95)
- Column type suggestion code is no longer confused by null values. (#94)
table.column_dicts
now works with all column types - previously it would throw errors on types other thanTEXT
,BLOB
,INTEGER
orFLOAT
. (#92)- Documentation for
NotFoundError
thrown bytable.get(pk)
- see :ref:`python_api_get`.
table.enable_fts()
now works with columns that contain spaces. (#90)
table.disable_fts()
can now be used to remove FTS tables and triggers that were created usingtable.enable_fts(...)
. (#88)- The
sqlite-utils disable-fts
command can be used to remove FTS tables and triggers from the command-line. (#88) - Trying to create table columns with square braces ([ or ]) in the name now raises an error. (#86)
- Subclasses of
dict
,list
andtuple
are now detected as needing a JSON column. (#87)
table.create_index()
now works for columns that contain spaces. (#85)
table.exists()
is now a method, not a property. This was not a documented part of the API before so I'm considering this a non-breaking change. (#83)
Fixed a bug where .upsert(..., hash_id="pk")
threw an error (#84).
New feature: sqlite_utils.suggest_column_types([records])
returns the suggested column types for a list of records. See :ref:`python_api_suggest_column_types`. (#81).
This replaces the undocumented table.detect_column_types()
method.
New feature: conversions={...}
can be passed to the .insert()
family of functions to specify SQL conversions that should be applied to values that are being inserted or updated. See :ref:`python_api_conversions` . (#77).
The .upsert()
and .upsert_all()
methods now raise a sqlite_utils.db.PrimaryKeyRequired
exception if you call them without specifying the primary key column using pk=
(#73).
This release changes the behaviour of upsert
. It's a breaking change, hence 2.0
.
The upsert
command-line utility and the .upsert()
and .upsert_all()
Python API methods have had their behaviour altered. They used to completely replace the affected records: now, they update the specified values on existing records but leave other columns unaffected.
See :ref:`Upserting data using the Python API <python_api_upsert>` and :ref:`Upserting data using the CLI <cli_upsert>` for full details.
If you want the old behaviour - where records were completely replaced - you can use $ sqlite-utils insert ... --replace
on the command-line and .insert(..., replace=True)
and .insert_all(..., replace=True)
in the Python API. See :ref:`Insert-replacing data using the Python API <python_api_insert_replace>` and :ref:`Insert-replacing data using the CLI <cli_insert_replace>` for more.
For full background on this change, see issue #66.
- Fixed error thrown when
.insert_all()
and.upsert_all()
were called with empty lists (#52)
Python library utilities for deleting records (#62)
db["tablename"].delete(4)
to delete by primary key, see :ref:`python_api_delete`db["tablename"].delete_where("id > ?", [3])
to delete by a where clause, see :ref:`python_api_delete_where`
Option to create triggers to automatically keep FTS tables up-to-date with newly inserted, updated and deleted records. Thanks, Amjith Ramanujam! (#57)
sqlite-utils enable-fts ... --create-triggers
- see :ref:`Configuring full-text search using the CLI <cli_fts>`db["tablename"].enable_fts(..., create_triggers=True)
- see :ref:`Configuring full-text search using the Python library <python_api_fts>`- Support for introspecting triggers for a database or table - see :ref:`python_api_introspection` (#59)
Ability to introspect and run queries against views (#54)
db.view_names()
method and anddb.views
property- Separate
View
andTable
classes, both subclassing newQueryable
class view.drop()
method
table.m2m(...)
method for creating many-to-many relationships: :ref:`python_api_m2m` (#23)
table.update(pk, values)
method: :ref:`python_api_update` (#35)
- Fixed bug where inserting records with 11 columns in a batch of 100 triggered a "too many SQL variables" error (#50)
- Documentation and tests for
table.drop()
method: :ref:`python_api_drop`
Support for lookup tables.
- New
table.lookup({...})
utility method for building and querying lookup tables - see :ref:`python_api_lookup_tables` (#44) - New
extracts=
table configuration option, see :ref:`python_api_extracts` (#46) - Use pysqlite3 if it is available, otherwise use
sqlite3
from the standard library - Table options can now be passed to the new
db.table(name, **options)
factory function in addition to being passed toinsert_all(records, **options)
and friends - see :ref:`python_api_table_configuration` - In-memory databases can now be created using
db = Database(memory=True)
sqlite-utils insert
can now accept TSV data via the new--tsv
option (#41)
- Support for compound primary keys (#36)
- Configure these using the CLI tool by passing
--pk
multiple times - In Python, pass a tuple of columns to the
pk=(..., ...)
argument: :ref:`python_api_compound_primary_keys`
- Configure these using the CLI tool by passing
- New
table.get()
method for retrieving a record by its primary key: :ref:`python_api_get` (#39)
- Assorted minor documentation fixes: changes since 1.4
- Added
sqlite-utils index-foreign-keys
command (:ref:`docs <cli_index_foreign_keys>`) anddb.index_foreign_keys()
method (:ref:`docs <python_api_index_foreign_keys>`) (#33)
- New mechanism for adding multiple foreign key constraints at once: :ref:`db.add_foreign_keys() documentation <python_api_add_foreign_keys>` (#31)
- Fixed bug where
datetime.time
was not being handled correctly
- Check the column exists before attempting to add a foreign key (#29)
- Improved foreign key definitions: you no longer need to specify the
column
,other_table
ANDother_column
to define a foreign key - if you omit theother_table
orother_column
the script will attempt to guess the correct values by instrospecting the database. See :ref:`python_api_add_foreign_key` for details. (#25) - Ability to set
NOT NULL
constraints andDEFAULT
values when creating tables (#24). Documentation: :ref:`Setting defaults and not null constraints (Python API) <python_api_defaults_not_null>`, :ref:`Setting defaults and not null constraints (CLI) <cli_defaults_not_null>` - Support for
not_null_default=X
/--not-null-default
for setting aNOT NULL DEFAULT 'x'
when adding a new column. Documentation: :ref:`Adding columns (Python API) <python_api_add_column>`, :ref:`Adding columns (CLI) <cli_add_column>`
- Support for
ignore=True
/--ignore
for ignoring inserted records if the primary key alread exists (#21) - documentation: :ref:`Inserting data (Python API) <python_api_bulk_inserts>`, :ref:`Inserting data (CLI) <cli_inserting_data>` - Ability to add a column that is a foreign key reference using
fk=...
/--fk
(#16) - documentation: :ref:`Adding columns (Python API) <python_api_add_column>`, :ref:`Adding columns (CLI) <cli_add_column>`
sqlite-utils rows data.db table --json-cols
- fixed bug where--json-cols
was not obeyed
- Option to automatically add new columns if you attempt to insert or upsert data with extra fields:
sqlite-utils insert ... --alter
- see :ref:`Adding columns automatically with the sqlite-utils CLI <cli_add_column_alter>`db["tablename"].insert(record, alter=True)
- see :ref:`Adding columns automatically using the Python API <python_api_add_column_alter>`
New
--json-cols
option for outputting nested JSON, see :ref:`cli_json_values`
- Ability to create unique indexes:
db["mytable"].create_index(["name"], unique=True)
db["mytable"].create_index(["name"], if_not_exists=True)
$ sqlite-utils create-index mydb.db mytable col1 [col2...]
, see :ref:`cli_create_index`table.add_column(name, type)
method, see :ref:`python_api_add_column`$ sqlite-utils add-column mydb.db mytable nameofcolumn
, see :ref:`cli_add_column` (CLI)db["books"].add_foreign_key("author_id", "authors", "id")
, see :ref:`python_api_add_foreign_key`$ sqlite-utils add-foreign-key books.db books author_id authors id
, see :ref:`cli_add_foreign_key` (CLI)- Improved (but backwards-incompatible)
foreign_keys=
argument to various methods, see :ref:`python_api_foreign_keys`
- New
--table
and--fmt
options can be used to output query results in a variety of visual table formats, see :ref:`cli_query_table` - New
hash_id=
argument can now be used for :ref:`python_api_hash` - Can now derive correct column types for numpy int, uint and float values
table.last_id
has been renamed totable.last_rowid
table.last_pk
now contains the last inserted primary key, ifpk=
was specified- Prettier indentation in the
CREATE TABLE
generated schemas
- Added
db[table].rows
iterator - see :ref:`python_api_rows` - Replaced
sqlite-utils json
andsqlite-utils csv
with a new default subcommand calledsqlite-utils query
which defaults to JSON and takes formatting options--nl
,--csv
and--no-headers
- see :ref:`cli_query_json` and :ref:`cli_query_csv` - New
sqlite-utils rows data.db name-of-table
command, see :ref:`cli_rows` sqlite-utils table
command now takes options--counts
and--columns
plus the standard output format options, see :ref:`cli_tables`
New commands for enabling FTS against a table and columns:
sqlite-utils enable-fts db.db mytable col1 col2
See :ref:`cli_fts`.
Handle datetime.date
and datetime.time
values.
New option for efficiently inserting rows from a CSV:
sqlite-utils insert db.db foo - --csv
Improved support for newline-delimited JSON.
sqlite-utils insert
has two new command-line options:
--nl
means "expect newline-delimited JSON". This is an extremely efficient way of loading in large amounts of data, especially if you pipe it into standard input.--batch-size=1000
lets you increase the batch size (default is 100). A commit will be issued every X records. This also control how many initial records are considered when detecting the desired SQL table schema for the data.
In the Python API, the table.insert_all(...)
method can now accept a generator as well as a list of objects. This will be efficiently used to populate the table no matter how many records are produced by the generator.
The Database()
constructor can now accept a pathlib.Path
object in addition to a string or an existing SQLite connection object.
Two new commands: sqlite-utils csv
and sqlite-utils json
These commands execute a SQL query and return the results as CSV or JSON. See :ref:`cli_query_csv` and :ref:`cli_query_json` for more details.
$ sqlite-utils json --help Usage: sqlite-utils json [OPTIONS] PATH SQL Execute SQL query and return the results as JSON Options: --nl Output newline-delimited JSON --arrays Output rows as arrays instead of objects --help Show this message and exit. $ sqlite-utils csv --help Usage: sqlite-utils csv [OPTIONS] PATH SQL Execute SQL query and return the results as CSV Options: --no-headers Exclude headers from CSV output --help Show this message and exit.
This release implements the sqlite-utils
command-line tool with a number of useful subcommands.
sqlite-utils tables demo.db
lists the tables in the databasesqlite-utils tables demo.db --fts4
shows just the FTS4 tablessqlite-utils tables demo.db --fts5
shows just the FTS5 tablessqlite-utils vacuum demo.db
runs VACUUM against the databasesqlite-utils optimize demo.db
runs OPTIMIZE against all FTS tables, then VACUUMsqlite-utils optimize demo.db --no-vacuum
runs OPTIMIZE but skips VACUUM
The two most useful subcommands are upsert
and insert
, which allow you to ingest JSON files with one or more records in them, creating the corresponding table with the correct columns if it does not already exist. See :ref:`cli_inserting_data` for more details.
sqlite-utils insert demo.db dogs dogs.json --pk=id
inserts new records fromdogs.json
into thedogs
tablesqlite-utils upsert demo.db dogs dogs.json --pk=id
upserts records, replacing any records with duplicate primary keys
One backwards incompatible change: the db["table"].table_names
property is now a method:
db["table"].table_names()
returns a list of table namesdb["table"].table_names(fts4=True)
returns a list of just the FTS4 tablesdb["table"].table_names(fts5=True)
returns a list of just the FTS5 tables
A few other changes:
- Plenty of updated documentation, including full coverage of the new command-line tool
- Allow column names to be reserved words (use correct SQL escaping)
- Added automatic column support for bytes and datetime.datetime
.enable_fts()
now takes optional argumentfts_version
, defaults toFTS5
. UseFTS4
if the version of SQLite bundled with your Python does not support FTS5- New optional
column_order=
argument to.insert()
and friends for providing a partial or full desired order of the columns when a database table is created - :ref:`New documentation <python_api>` for
.insert_all()
and.upsert()
and.upsert_all()
db.tables
anddb.table_names
introspection propertiesdb.indexes
property for introspecting indexestable.create_index(columns, index_name)
methoddb.create_view(name, sql)
method- Table methods can now be chained, plus added
table.last_id
for accessing the last inserted row ID
enable_fts()
,populate_fts()
andsearch()
table methods