sqlite-utils 3.20
CLI tool and Python utility functions for manipulating SQLite databases
This library and command-line utility helps create SQLite databases from an existing collection of data.
Most of the functionality is available as either a Python API or through the sqlite-utils
command-line tool.
sqlite-utils is not intended to be a full ORM: the focus is utility helpers to make creating the initial database and populating it with data as productive as possible.
It is designed as a useful complement to Datasette.
Contents
- Installation
- sqlite-utils command-line tool
- Running SQL queries
- Querying data directly using an in-memory database
- Returning all rows in a table
- Listing tables
- Listing views
- Listing indexes
- Listing triggers
- Showing the schema
- Analyzing tables
- Inserting JSON data
- Inserting CSV or TSV data
- Inserting unstructured data with --lines and --text
- Applying conversions while inserting data
- Insert-replacing data
- Upserting data
- Inserting data from files
- Converting data in columns
- Creating tables
- Dropping tables
- Transforming tables
- Extracting columns into a separate table
- Creating views
- Dropping views
- Adding columns
- Adding columns automatically on insert/update
- Adding foreign key constraints
- Setting defaults and not null constraints
- Creating indexes
- Configuring full-text search
- Executing searches
- Enabling cached counts
- Vacuum
- Optimize
- WAL mode
- Dumping the database to SQL
- Loading SQLite extensions
- sqlite_utils Python library
- Connecting to or creating a database
- Executing queries
- Accessing tables
- Listing tables
- Listing views
- Listing rows
- Listing rows with their primary keys
- Retrieving a specific record
- Showing the schema
- Creating tables
- Table configuration options
- Setting defaults and not null constraints
- Bulk inserts
- Insert-replacing data
- Updating a specific record
- Deleting a specific record
- Deleting multiple records
- Upserting data
- Converting data in columns
- Working with lookup tables
- Working with many-to-many relationships
- Analyzing a column
- Adding columns
- Adding columns automatically on insert/update
- Adding foreign key constraints
- Dropping a table or view
- Transforming a table
- Extracting columns into a separate table
- Setting an ID based on the hash of the row contents
- Creating views
- Storing JSON
- Converting column values using SQL functions
- Introspecting tables and views
- Full-text search
- Rebuilding a full-text search table
- Optimizing a full-text search table
- Cached table counts using triggers
- Creating indexes
- Vacuum
- WAL mode
- Suggesting column types
- Finding SpatiaLite
- Registering custom SQL functions
- Quoting strings for use in SQL
- API reference
- Contributing
- Changelog
- 3.20 (2022-01-05)
- 3.19 (2021-11-20)
- 3.18 (2021-11-14)
- 3.17.1 (2021-09-22)
- 3.17 (2021-08-24)
- 3.16 (2021-08-18)
- 3.15.1 (2021-08-10)
- 3.15 (2021-08-09)
- 3.14 (2021-08-02)
- 3.13 (2021-07-24)
- 3.12 (2021-06-25)
- 3.11 (2021-06-20)
- 3.10 (2021-06-19)
- 3.9.1 (2021-06-12)
- 3.9 (2021-06-11)
- 3.8 (2021-06-02)
- 3.7 (2021-05-28)
- 3.6 (2021-02-18)
- 3.5 (2021-02-14)
- 3.4.1 (2021-02-05)
- 3.4 (2021-02-05)
- 3.3 (2021-01-17)
- 3.2.1 (2021-01-12)
- 3.2 (2021-01-03)
- 3.1.1 (2021-01-01)
- 3.1 (2020-12-12)
- 3.0 (2020-11-08)
- 2.23 (2020-10-28)
- 2.22 (2020-10-16)
- 2.21 (2020-09-24)
- 2.20 (2020-09-22)
- 2.19 (2020-09-20)
- 2.18 (2020-09-08)
- 2.17 (2020-09-07)
- 2.16.1 (2020-08-28)
- 2.16 (2020-08-21)
- 2.15.1 (2020-08-12)
- 2.15 (2020-08-10)
- 2.14.1 (2020-08-05)
- 2.14 (2020-08-01)
- 2.13 (2020-07-29)
- 2.12 (2020-07-27)
- 2.11 (2020-07-08)
- 2.10.1 (2020-06-23)
- 2.10 (2020-06-12)
- 2.9.1 (2020-05-11)
- 2.9 (2020-05-10)
- 2.8 (2020-05-03)
- 2.7.2 (2020-05-02)
- 2.7.1 (2020-05-01)
- 2.7 (2020-04-17)
- 2.6 (2020-04-15)
- 2.5 (2020-04-12)
- 2.4.4 (2020-03-23)
- 2.4.3 (2020-03-23)
- 2.4.2 (2020-03-14)
- 2.4.1 (2020-03-01)
- 2.4 (2020-02-26)
- 2.3.1 (2020-02-10)
- 2.3 (2020-02-08)
- 2.2.1 (2020-02-06)
- 2.2 (2020-02-01)
- 2.1 (2020-01-30)
- 2.0.1 (2020-01-05)
- 2.0 (2019-12-29)
- 1.12.1 (2019-11-06)
- 1.12 (2019-11-04)
- 1.11 (2019-09-02)
- 1.10 (2019-08-23)
- 1.9 (2019-08-04)
- 1.8 (2019-07-28)
- 1.7.1 (2019-07-28)
- 1.7 (2019-07-24)
- 1.6 (2019-07-18)
- 1.5 (2019-07-14)
- 1.4.1 (2019-07-14)
- 1.4 (2019-06-30)
- 1.3 (2019-06-28)
- 1.2.2 (2019-06-25)
- 1.2.1 (2019-06-20)
- 1.2 (2019-06-12)
- 1.1 (2019-05-28)
- 1.0.1 (2019-05-27)
- 1.0 (2019-05-24)
- 0.14 (2019-02-24)
- 0.13 (2019-02-23)
- 0.12 (2019-02-22)
- 0.11 (2019-02-07)
- 0.10 (2019-02-06)
- 0.9 (2019-01-27)
- 0.8 (2019-01-25)
- 0.7 (2019-01-24)
- 0.6 (2018-08-12)
- 0.5 (2018-08-05)
- 0.4 (2018-07-31)