sqlite-utils 9d7da06

CLI tool and Python library 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.

Cleaning data with sqlite-utils and Datasette provides a tutorial introduction (and accompanying ten minute video) about using this tool.

Contents

• Installation
  • Using Homebrew
  • Using pip
  • Using pipx
  • Alternatives to sqlite3
  • Setting up shell completion
• sqlite-utils command-line tool
  • Running SQL queries
    • Returning JSON
    • Returning CSV or TSV
    • Table-formatted output
    • Returning raw data, such as binary content
    • Using named parameters
    • UPDATE, INSERT and DELETE
    • Defining custom SQL functions
    • SQLite extensions
    • Attaching additional databases
  • Querying data directly using an in-memory database
    • Running queries directly against CSV or JSON
    • Explicitly specifying the format
    • Joining in-memory data against existing databases using --attach
    • --schema, --analyze, --dump and --save
  • Returning all rows in a table
  • Listing tables
  • Listing views
  • Listing indexes
  • Listing triggers
  • Showing the schema
  • Analyzing tables
    • Saving the analyzed table details
  • Creating an empty database
  • Inserting JSON data
    • Inserting binary data
    • Inserting newline-delimited JSON
    • Flattening nested JSON objects
  • Inserting CSV or TSV data
    • Alternative delimiters and quote characters
    • CSV files without a header row
  • Inserting unstructured data with --lines and --text
  • Applying conversions while inserting data
    • --convert with --lines
    • --convert with --text
  • Insert-replacing data
  • Upserting data
  • Executing SQL in bulk
  • Inserting data from files
  • Converting data in columns
    • Importing additional modules
    • Using the debugger
    • Defining a convert() function
    • sqlite-utils convert recipes
    • Saving the result to a different column
    • Converting a column into multiple columns
  • Creating tables
  • Renaming a table
  • Duplicating tables
  • Dropping tables
  • Transforming tables
    • Adding a primary key to a rowid table
  • Extracting columns into a separate table
  • Creating views
  • Dropping views
  • Adding columns
  • Adding columns automatically on insert/update
  • Adding foreign key constraints
    • Adding multiple foreign keys at once
    • Adding indexes for all foreign keys
  • Setting defaults and not null constraints
  • Creating indexes
  • Configuring full-text search
  • Executing searches
  • Enabling cached counts
  • Optimizing index usage with ANALYZE
  • Vacuum
  • Optimize
  • WAL mode
  • Dumping the database to SQL
  • Loading SQLite extensions
  • SpatiaLite helpers
    • Adding spatial indexes
  • Installing packages
  • Uninstalling packages
  • Experimental TUI
• sqlite_utils Python library
  • Getting started
  • Connecting to or creating a database
    • Attaching additional databases
    • Tracing queries
  • Executing queries
    • db.query(sql, params)
    • db.execute(sql, params)
    • Passing parameters
  • Accessing tables
  • Listing tables
  • Listing views
  • Listing rows
    • Counting rows
  • Listing rows with their primary keys
  • Retrieving a specific record
  • Showing the schema
  • Creating tables
    • Custom column order and column types
    • Explicitly creating a table
    • Compound primary keys
    • Specifying foreign keys
    • Table configuration options
    • Setting defaults and not null constraints
  • Renaming a table
  • Duplicating tables
  • 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
    • Creating lookup tables explicitly
    • Populating lookup tables automatically during insert/upsert
  • Working with many-to-many relationships
    • Using m2m and lookup tables together
  • Analyzing a column
  • Adding columns
  • Adding columns automatically on insert/update
  • Adding foreign key constraints
    • Adding multiple foreign key constraints at once
    • Adding indexes for all foreign keys
  • Dropping a table or view
  • Transforming a table
    • Altering column types
    • Renaming columns
    • Dropping columns
    • Changing primary keys
    • Changing not null status
    • Altering column defaults
    • Changing column order
    • Adding foreign key constraints
    • Replacing foreign key constraints
    • Dropping foreign key constraints
    • Custom transformations with .transform_sql()
  • 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
  • Checking the SQLite version
  • Dumping the database to SQL
  • Introspecting tables and views
    • .exists()
    • .count
    • .columns
    • .columns_dict
    • .default_values
    • .pks
    • .use_rowid
    • .foreign_keys
    • .schema
    • .strict
    • .indexes
    • .xindexes
    • .triggers
    • .triggers_dict
    • .detect_fts()
    • .virtual_table_using
    • .has_counts_triggers
    • db.supports_strict
  • Full-text search
    • Enabling full-text search for a table
    • Quoting characters for use in search
    • Searching with table.search()
    • Building SQL queries with table.search_sql()
  • Rebuilding a full-text search table
  • Optimizing a full-text search table
  • Cached table counts using triggers
  • Creating indexes
  • Optimizing index usage with ANALYZE
  • Vacuum
  • WAL mode
  • Suggesting column types
  • Registering custom SQL functions
  • Quoting strings for use in SQL
  • Reading rows from a file
  • Setting the maximum CSV field size limit
  • Detecting column types using TypeTracker
  • SpatiaLite helpers
    • Initialize SpatiaLite
    • Finding SpatiaLite
    • Adding geometry columns
    • Creating a spatial index
• Plugins
  • Building a plugin
  • Plugin hooks
    • register_commands(cli)
    • prepare_connection(conn)
• API reference
  • sqlite_utils.db.Database
    • Database
  • sqlite_utils.db.Queryable
    • Queryable
  • sqlite_utils.db.Table
    • Table
  • sqlite_utils.db.View
    • View
  • Other
    • sqlite_utils.db.Column
    • sqlite_utils.db.ColumnDetails
  • sqlite_utils.utils
    • sqlite_utils.utils.hash_record
    • sqlite_utils.utils.rows_from_file
    • sqlite_utils.utils.TypeTracker
    • sqlite_utils.utils.chunks
    • sqlite_utils.utils.flatten
• CLI reference
  • query
  • memory
  • insert
  • upsert
  • bulk
  • search
  • transform
  • extract
  • schema
  • insert-files
  • analyze-tables
  • convert
  • tables
  • views
  • rows
  • triggers
  • indexes
  • create-database
  • create-table
  • create-index
  • enable-fts
  • populate-fts
  • rebuild-fts
  • disable-fts
  • tui
  • optimize
  • analyze
  • vacuum
  • dump
  • add-column
  • add-foreign-key
  • add-foreign-keys
  • index-foreign-keys
  • enable-wal
  • disable-wal
  • enable-counts
  • reset-counts
  • duplicate
  • rename-table
  • drop-table
  • create-view
  • drop-view
  • install
  • uninstall
  • add-geometry-column
  • create-spatial-index
  • plugins
• Contributing
  • Obtaining the code
  • Running the tests
  • Building the documentation
  • Linting and formatting
  • Using Just and pipenv
  • Release process
• Changelog
  • 3.38 (2024-11-23)
  • 3.37 (2024-07-18)
  • 3.36 (2023-12-07)
  • 3.35.2 (2023-11-03)
  • 3.35.1 (2023-09-08)
  • 3.35 (2023-08-17)
  • 3.34 (2023-07-22)
  • 3.33 (2023-06-25)
  • 3.32.1 (2023-05-21)
  • 3.32 (2023-05-21)
  • 3.31 (2023-05-08)
  • 3.30 (2022-10-25)
  • 3.29 (2022-08-27)
  • 3.28 (2022-07-15)
  • 3.27 (2022-06-14)
  • 3.26.1 (2022-05-02)
  • 3.26 (2022-04-13)
  • 3.25.1 (2022-03-11)
  • 3.25 (2022-03-01)
  • 3.24 (2022-02-15)
  • 3.23 (2022-02-03)
  • 3.22.1 (2022-01-25)
  • 3.22 (2022-01-11)
  • 3.21 (2022-01-10)
  • 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)
    • sqlite-utils memory
    • sqlite-utils insert --detect-types
    • Other changes
  • 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)
    • Transform
    • Extract
    • Other changes
  • 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)
  • 0.3.1 (2018-07-31)
  • 0.3 (2018-07-31)
  • 0.2 (2018-07-28)