https://sqlite-utils.datasette.io/en/stable/index.html

sqlite-utils
stable
[                    ] 
  * Installation
  * sqlite-utils command-line tool
  * sqlite_utils Python library
  * API reference
  * CLI reference
  * Contributing
  * Changelog

sqlite-utils

  *  >>
  * sqlite-utils 3.24
  * Edit on GitHub

---------------------------------------------------------------------

sqlite-utils 3.24 

PyPI Changelog CI License

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
      + Using Homebrew
      + Using pip
      + Using pipx
  * sqlite-utils command-line tool
      + Running SQL queries
          o Returning JSON
          o Returning CSV or TSV
          o Table-formatted output
          o Returning raw data, such as binary content
          o Using named parameters
          o UPDATE, INSERT and DELETE
          o SQLite extensions
          o Attaching additional databases
      + Querying data directly using an in-memory database
          o Running queries directly against CSV or JSON
          o Explicitly specifying the format
          o Joining in-memory data against existing databases using
            --attach
          o --schema, --analyze, --dump and --save
      + Returning all rows in a table
      + Listing tables
      + Listing views
      + Listing indexes
      + Listing triggers
      + Showing the schema
      + Analyzing tables
          o Saving the analyzed table details
      + Creating an empty database
      + Inserting JSON data
          o Inserting binary data
          o Inserting newline-delimited JSON
          o Flattening nested JSON objects
      + Inserting CSV or TSV data
          o Alternative delimiters and quote characters
          o CSV files without a header row
      + Inserting unstructured data with --lines and --text
      + Applying conversions while inserting data
          o --convert with --lines
          o --convert with --text
      + Insert-replacing data
      + Upserting data
      + Executing SQL in bulk
      + Inserting data from files
      + Converting data in columns
          o Importing additional modules
          o sqlite-utils convert recipes
          o Saving the result to a different column
          o Converting a column into multiple columns
      + Creating tables
      + Dropping tables
      + Transforming tables
          o 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
          o Adding multiple foreign keys at once
          o 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
          o Adding spatial indexes
  * sqlite_utils Python library
      + Getting started
      + Connecting to or creating a database
          o Attaching additional databases
          o Tracing queries
      + Executing queries
          o db.query(sql, params)
          o db.execute(sql, params)
          o Passing parameters
      + Accessing tables
      + Listing tables
      + Listing views
      + Listing rows
          o Counting rows
      + Listing rows with their primary keys
      + Retrieving a specific record
      + Showing the schema
      + Creating tables
          o Custom column order and column types
          o Explicitly creating a table
          o Compound primary keys
          o Specifying foreign keys
      + 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
          o Creating lookup tables explicitly
          o Populating lookup tables automatically during insert/
            upsert
      + Working with many-to-many relationships
          o Using m2m and lookup tables together
      + Analyzing a column
      + Adding columns
      + Adding columns automatically on insert/update
      + Adding foreign key constraints
          o Adding multiple foreign key constraints at once
          o Adding indexes for all foreign keys
      + Dropping a table or view
      + Transforming a table
          o Altering column types
          o Renaming columns
          o Dropping columns
          o Changing primary keys
          o Changing not null status
          o Altering column defaults
          o Changing column order
          o Dropping foreign key constraints
          o 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
      + Introspecting tables and views
          o .exists()
          o .count
          o .columns
          o .columns_dict
          o .pks
          o .use_rowid
          o .foreign_keys
          o .schema
          o .strict
          o .indexes
          o .xindexes
          o .triggers
          o .triggers_dict
          o .detect_fts()
          o .virtual_table_using
          o .has_counts_triggers
      + Full-text search
          o Enabling full-text search for a table
          o Quoting characters for use in search
          o Searching with table.search()
          o 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
      + SpatiaLite helpers
          o Initialize SpatiaLite
          o Finding SpatiaLite
          o Adding geometry columns
          o Creating a spatial index
  * API reference
      + sqlite_utils.db.Database
      + sqlite_utils.db.Queryable
      + sqlite_utils.db.Table
      + sqlite_utils.db.View
      + Other
          o sqlite_utils.db.Column
          o sqlite_utils.db.ColumnDetails
  * 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
      + optimize
      + analyze
      + vacuum
      + dump
      + add-column
      + add-foreign-key
      + add-foreign-keys
      + index-foreign-keys
      + enable-wal
      + disable-wal
      + enable-counts
      + reset-counts
      + drop-table
      + create-view
      + drop-view
      + add-geometry-column
      + create-spatial-index
  * Contributing
      + Obtaining the code
      + Running the tests
      + Building the documentation
      + Linting and formatting
  * Changelog
      + 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)
          o sqlite-utils memory
          o sqlite-utils insert --detect-types
          o 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)
          o Transform
          o Extract
          o 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)

Next
---------------------------------------------------------------------

(c) Copyright 2018-2021, Simon Willison. Revision 757f103a.

Built with Sphinx using a theme provided by Read the Docs.
Read the Docs v: stable

Versions
    latest
    stable
    3.24
    3.23
    3.22.1
    3.22
    3.21
    3.20
    3.19
    3.18
    3.17.1
    3.17
    3.16
    3.15.1
    3.15
    3.14
    3.13
    3.12
    3.11
    3.10
    3.9.1
    3.9
    3.8
    3.7
    3.6
    3.5
    3.4.1
    3.4
    3.3
    3.2.1
    3.2
    3.1.1
    3.1
    3.0
    2.23
    2.22
    2.21
    2.20
    2.19
    2.18
    2.17
    2.16.1
    2.16
    2.15.1
    2.15
    2.14.1
    2.14
    2.13
    2.12
    2.11
    2.10.1
    2.10
    2.9.1
    2.9
    2.8
    2.7.2
    2.7.1
    2.7
    2.6
    2.5
    2.4.4
    2.4.3
    2.4.2
    autodoc

Downloads

On Read the Docs
    Project Home
    Builds