sqlite-utils 3.3 ¶
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¶
- sqlite-utils command-line tool
- Running queries and returning JSON
- Running queries and returning CSV
- Running queries and outputting a table
- Returning raw data from a query, such as binary content
- Returning all rows in a table
- Listing tables
- Listing views
- Listing triggers
- Analyzing tables
- Inserting JSON data
- Inserting CSV or TSV data
- Insert-replacing data
- Upserting data
- Inserting binary data from files
- 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
- Loading SQLite extensions
- Python API
- Connecting to or creating a database
- Executing queries
- Accessing tables
- Listing tables
- Listing views
- Listing rows
- Retrieving a specific record
- 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
- 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
- Introspection
- Enabling 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
- Changelog
- 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)
Take a look at this script for an example of this library in action.