sqlite-utils

https://sqlite-utils.datasette.io/en/stable/
Admin
sqlite-utils is a CLI tool and Python library for manipulating, querying, and transforming SQLite...
Tokens:301,169
Snippets:2,386
Trust Score:9.5
Update:1 week ago
Show doc for...
Tokens:
Raw
### sqlite-utils create-table examples

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Examples of creating a table with sqlite-utils create-table, including options for primary key, NOT NULL, and defaults. The last example shows the schema output using sqlite-utils tables --schema -t.

```bash
sqlite-utils create-table mydb.db mytable id integer name text --pk=id
```

```bash
sqlite-utils create-table mydb.db mytable \
    id integer \
    name text \
    age integer \
    is_good integer \
    --not-null name \
    --not-null age \
    --default is_good 1 \
    --pk=id
```

```bash
sqlite-utils tables mydb.db --schema -t
```

```output
table    schema
-------  --------------------------------
mytable  CREATE TABLE "mytable" (
            "id" INTEGER PRIMARY KEY,
            "name" TEXT NOT NULL,
            "age" INTEGER NOT NULL,
            "is_good" INTEGER DEFAULT '1'
        )
```

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

### Example output of sqlite-utils migrate --list

Source: https://sqlite-utils.datasette.io/en/stable/_sources/migrations.rst.txt

Example output showing the applied and pending migrations for a database.

```output
Migrations for: creatures

  Applied:
    create_table - 2026-06-09 17:23:12.048092+00:00
    add_weight - 2026-06-09 17:23:12.051249+00:00

  Pending:
    add_age
```

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

### Install a plugin with sqlite-utils install

Source: https://sqlite-utils.datasette.io/en/stable/_sources/plugins.rst.txt

Installs a plugin by name using the sqlite-utils install command.

```bash
sqlite-utils install sqlite-utils-name-of-plugin
```

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

### sqlite-utils install CLI help

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli-reference.rst.txt

CLI help output for the sqlite-utils install command. Shows the usage and the available options: -U/--upgrade, -e/--editable, and -h/--help.

```text
Usage: sqlite-utils install [OPTIONS] [PACKAGES]...

  Install packages from PyPI into the same environment as sqlite-utils

Options:
  -U, --upgrade        Upgrade packages to latest version
  -e, --editable TEXT  Install a project in editable mode from this path
  -h, --help           Show this message and exit.
```

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

### Build documentation with live-reload

Source: https://sqlite-utils.datasette.io/en/stable/_sources/contributing.rst.txt

Build the documentation and start a live-reload server on port 8000.

```bash
uv run make livehtml --directory docs
```

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

### Example output of --list

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

Example output showing applied and pending migrations for the creatures database.

```text
Migrations for: creatures

  Applied:
    create_table - 2026-06-09 17:23:12.048092+00:00
    add_weight - 2026-06-09 17:23:12.051249+00:00

  Pending:
    add_age

```

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

### CSV output example

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Example CSV output from the previous query.

```output
TreeID,qAddress,Latitude,Longitude
141565,501X Baker St,37.7759676911831,-122.441396661871
232565,940 Elizabeth St,37.7517102172731,-122.441498017841
119263,495X Lakeshore Dr,,
207368,920 Kirkham St,37.760210314285,-122.47073935813
188702,1501 Evans Ave,37.7422086702947,-122.387293152263
```

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

### sqlite-utils insert-files CLI help

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli-reference.rst.txt

Command-line help for sqlite-utils insert-files, showing usage and an example invocation for inserting files as BLOBs. The example is truncated in the source.

```text
Usage: sqlite-utils insert-files [OPTIONS] PATH TABLE FILE_OR_DIR...

  Insert one or more files using BLOB columns in the specified table

  Example:


```

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

### sqlite-utils query --help output

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Shows the full help text for the 'sqlite-utils query' command, including usage, options, and an example of executing a SQL query with parameters. The example demonstrates reading SQL from standard input by passing '-' as the SQL argument.

```text
Usage: sqlite-utils query [OPTIONS] PATH SQL

  Execute SQL query and return the results as JSON

  Example:

      sqlite-utils data.db \
          "select * from chickens where age > :age" \
          -p age 1

  Pass "-" as the SQL to read the query from standard input:

      echo "select * from chickens" | sqlite-utils data.db -

Options:
  --attach <TEXT FILE>...     Additional databases to attach - specify alias and
                              filepath
  --nl                        Output newline-delimited JSON
  --arrays                    Output rows as arrays instead of objects
  --csv                       Output CSV
  --tsv                       Output TSV
  --no-headers                Omit headers from CSV/TSV and table/--fmt output
  -t, --table                 Output as a formatted table
  --fmt TEXT                  Table format - one of asciidoc, colon_grid,
                              double_grid, double_outline, fancy_grid,
                              fancy_outline, github, grid, heavy_grid,
                              heavy_outline, html, jira, latex, latex_booktabs,
                              latex_longtable, latex_raw, mediawiki, mixed_grid,
                              mixed_outline, moinmoin, orgtbl, outline, pipe,
                              plain, presto, pretty, psql, rounded_grid,
                              rounded_outline, rst, simple, simple_grid,
                              simple_outline, textile, tsv, unsafehtml, youtrack
  --json-cols                 Detect JSON cols and output them as JSON, not
                              escaped strings
  --ascii                     Escape non-ASCII characters in JSON output as
                              \uXXXX
  -r, --raw                   Raw output, first column of first row
  --raw-lines                 Raw output, first column of each row
  -p, --param <TEXT TEXT>...  Named :parameters for SQL query
  --functions TEXT            Python code or a file path defining custom SQL
                              functions; can be used multiple times
  --load-extension TEXT       Path to SQLite extension, with optional
                              :entrypoint
  -h, --help                  Show this message and exit.
```

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

### Install plugin from path in editable mode

Source: https://sqlite-utils.datasette.io/en/stable/_sources/plugins.rst.txt

Installs the plugin in editable mode by passing the path to the plugin directory.

```bash
sqlite-utils install -e /dev/sqlite-utils-hello-world
```

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

### Pass named parameters as a dictionary

Source: https://sqlite-utils.datasette.io/en/stable/_sources/python-api.rst.txt

Shows how to pass named parameters to db.query() using a dictionary for ':name' placeholders. The example uses next() to get the first result.

```python
dog = next(db.query(
    "select rowid, name from dogs where name = :name",
    {"name": "Cleopaws"}
))
# dog is now {'rowid': 1, 'name': 'Cleopaws'}
```

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

### sqlite-utils create-database CLI help

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Shows the full CLI help for sqlite-utils create-database, including the example command and available options.

```text
Usage: sqlite-utils create-database [OPTIONS] PATH

  Create a new empty database file

  Example:

      sqlite-utils create-database trees.db

Options:
  --enable-wal           Enable WAL mode on the created database
  --init-spatialite      Enable SpatiaLite on the created database
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.

```

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

### Read CSV data into rows for TypeTracker

Source: https://sqlite-utils.datasette.io/en/stable/python-api.html

Prepare sample data as a list of dictionaries from a CSV string. This is the starting point for the TypeTracker example, where rows are initially represented as strings.

```python
import csv, io

csv_file = io.StringIO("id,name\n1,Cleo\n2,Cardi")
rows = list(csv.DictReader(csv_file))

# rows is now this:
# [{'id': '1', 'name': 'Cleo'}, {'id': '2', 'name': 'Cardi'}]

```

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

### sqlite-utils create-table CLI help

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Shows the full CLI help for sqlite-utils create-table, including the example command and available options.

```text
Usage: sqlite-utils create-table [OPTIONS] PATH TABLE COLUMNS...

  Add a table with the specified columns. Columns should be specified using
  name, type pairs, for example:

      sqlite-utils create-table my.db people \
          id integer \
          name text \
          height real \
          photo blob --pk id

  Valid column types are text, integer, real, float, blob and any.

Options:
  --pk TEXT                 Column to use as primary key
  --not-null TEXT           Columns that should be created as NOT NULL
  --default <TEXT TEXT>...  Default value that should be set for a column
  --fk <TEXT TEXT TEXT>...  Column, other table, other column to set as a
                            foreign key
  --ignore                  If table already exists, do nothing
  --replace                 If table already exists, replace it
  --transform               If table already exists, try to transform the schema
  --load-extension TEXT     Path to SQLite extension, with optional :entrypoint
  --strict                  Apply STRICT mode to created table
  -h, --help                Show this message and exit.

```

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

### Execute UPDATE, INSERT, or DELETE and get affected rows

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

When executing an UPDATE, INSERT, or DELETE query, the command returns the number of affected rows. The example shows an UPDATE and its output.

```bash
sqlite-utils dogs.db "update dogs set age = 5 where name = 'Cleo'"

```

```json
[{"rows_affected": 1}]

```

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

### Run live documentation server with Just

Source: https://sqlite-utils.datasette.io/en/stable/_sources/contributing.rst.txt

Run the live documentation server, which runs Cog first.

```bash
just docs
```

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

### Example access.log for --convert with --lines

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

Sample log file used to demonstrate converting lines into structured data.

```text
INFO:     127.0.0.1:60581 - GET / HTTP/1.1 200 OK
INFO:     127.0.0.1:60581 - GET /foo/-/static/app.css?cead5a HTTP/1.1 200 OK
```

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

### extract command usage and example

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Shows the sqlite-utils extract command usage, an example invocation, and its options. The example extracts the species column from the Street_Trees table in trees.db into a separate table.

```text
Usage: sqlite-utils extract [OPTIONS] PATH TABLE COLUMNS...

  Extract one or more columns into a separate table

  Example:

      sqlite-utils extract trees.db Street_Trees species

Options:
  --table TEXT             Name of the other table to extract columns to
  --fk-column TEXT         Name of the foreign key column to add to the table
  --rename <TEXT TEXT>...  Rename this column in extracted table
  --load-extension TEXT    Path to SQLite extension, with optional :entrypoint
  -h, --help               Show this message and exit.

```

```text
Usage: sqlite-utils schema [OPTIONS] PATH [TABLES]...

  Show full schema for this database or for specified tables

  Example:

      sqlite-utils schema trees.db

Options:
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.

```

```text
Usage: sqlite-utils insert-files [OPTIONS] PATH TABLE FILE_OR_DIR...

  Insert one or more files using BLOB columns in the specified table

  Example:

      sqlite-utils insert-files pics.db images *.gif \
          -c name:name \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content:content \
          -c content
```

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

### List installed plugins with sqlite-utils plugins

Source: https://sqlite-utils.datasette.io/en/stable/_sources/plugins.rst.txt

Lists installed plugins as JSON.

```bash
sqlite-utils plugins
```

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

### Install package with sqlite-utils install

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Installs a Python package from PyPI into the sqlite-utils environment, enabling its use in convert and insert --convert scripts. Use -U to upgrade an existing package.

```bash
sqlite-utils install beautifulsoup4
```

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

### Create database with SpatiaLite initialization

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Creates a new database and initializes SpatiaLite metadata using --init-spatialite.

```bash
sqlite-utils create-database empty.db --init-spatialite
```

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

### Install plugin in editable mode

Source: https://sqlite-utils.datasette.io/en/stable/_sources/plugins.rst.txt

Installs the plugin in editable mode from the current directory.

```bash
sqlite-utils install -e .
```

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

### sqlite-utils vacuum command help

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Shows the command-line help for sqlite-utils vacuum, which runs VACUUM against the database. The example runs it on chickens.db.

```text
Usage: sqlite-utils vacuum [OPTIONS] PATH

  Run VACUUM against the database

  Example:

      sqlite-utils vacuum chickens.db

Options:
  -h, --help  Show this message and exit.

```

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

### Install sqlite-utils with Homebrew

Source: https://sqlite-utils.datasette.io/en/stable/_sources/installation.rst.txt

Installs the sqlite-utils command-line tool on macOS using Homebrew.

```bash
brew install sqlite-utils
```

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

### Create a Database instance

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

Initialize a connection to a file-based SQLite database or create an in-memory database.

```python
# create data.db file, or open existing:
db = Database("data.db")
# Create an in-memory database:
dB = Database(memory=True)
```

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

### Nested JSON example for --flatten

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Example document with nested objects to be flattened.

```json
{
    "httpRequest": {
        "latency": "0.112114537s",
        "requestMethod": "GET",
        "requestSize": "534",
        "status": 200
    },
    "insertId": "6111722f000b5b4c4d4071e2",
    "labels": {
        "service": "datasette-io"
    }
}
```

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

### Create plugin directory

Source: https://sqlite-utils.datasette.io/en/stable/_sources/plugins.rst.txt

Creates and enters the plugin directory for a hello-world plugin.

```bash
mkdir hello-world
cd hello-world
```

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

### Install sqlite-utils with pipx

Source: https://sqlite-utils.datasette.io/en/stable/_sources/installation.rst.txt

Installs the sqlite-utils command-line tool in its own isolated environment using pipx.

```bash
pipx install sqlite-utils
```

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

### JSON list example for sqlite-utils insert

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Example JSON file with multiple records to be inserted.

```json
[
    {
        "id": 1,
        "name": "Cleo",
        "age": 4
    },
    {
        "id": 2,
        "name": "Pancakes",
        "age": 2
    },
    {
        "id": 3,
        "name": "Toby",
        "age": 6
    }
]
```

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

### sqlite-utils optimize command help

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Shows the command-line help for sqlite-utils optimize, which optimizes full-text search tables and runs VACUUM. The example runs it on chickens.db. Use --no-vacuum to skip the VACUUM step.

```text
Usage: sqlite-utils optimize [OPTIONS] PATH [TABLES]...

  Optimize all full-text search tables and then run VACUUM - should shrink the
  database file

  Example:

      sqlite-utils optimize chickens.db

Options:
  --no-vacuum            Don't run VACUUM
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.

```

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

### Install sqlite-utils with pip

Source: https://sqlite-utils.datasette.io/en/stable/_sources/installation.rst.txt

Installs the sqlite-utils package from PyPI, which includes both the Python library and the command-line tool.

```bash
pip install sqlite-utils
```

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

### sqlite-utils create-view CLI help

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli-reference.rst.txt

CLI help output for the sqlite-utils create-view command. Shows the usage, an example invocation creating a view named heavy_chickens from a SELECT query, and the available options: --ignore, --replace, --load-extension, and -h/--help.

```text
Usage: sqlite-utils create-view [OPTIONS] PATH VIEW SELECT

  Create a view for the provided SELECT query

  Example:

      sqlite-utils create-view chickens.db heavy_chickens \
        'select * from chickens where weight > 3'

Options:
  --ignore               If view already exists, do nothing
  --replace              If view already exists, replace it
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.
```

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

### sqlite-utils drop-table CLI help

Source: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

Displays the CLI help for the drop-table command, including the example invocation `sqlite-utils drop-table chickens.db chickens`. The --ignore flag makes the command do nothing if the table does not exist.

```text
Usage: sqlite-utils drop-table [OPTIONS] PATH TABLE

  Drop the specified table

  Example:

      sqlite-utils drop-table chickens.db chickens

Options:
  --ignore               If table does not exist, do nothing
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.
```

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

### Apply Black with Just

Source: https://sqlite-utils.datasette.io/en/stable/_sources/contributing.rst.txt

Apply Black formatting using Just.

```bash
just black
```

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

### Example JSON output for most_common column

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Example of the nested JSON array stored in the most_common and least_common columns.

```json
[
    ["Del Libertador, Av", 5068],
    ["Alberdi Juan Bautista Av.", 4612],
    ["Directorio Av.", 4552],
    ["Rivadavia, Av", 4532],
    ["Yerbal", 4512],
    ["Cosquín", 4472],
    ["Estado Plurinacional de Bolivia", 4440],
    ["Gordillo Timoteo", 4424],
    ["Montiel", 4360],
    ["Condarco", 4288]
]
```

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

### Example JSON data for --convert

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

Sample JSON file used to demonstrate the --convert option.

```json
[
    {"id": 1, "name": "Cleo"},
    {"id": 2, "name": "Pancakes"}
]
```

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

### Install sqlite-utils-tui plugin

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

The optional terminal interface (sqlite-utils tui) has moved to a plugin. Install it with this command to continue using the TUI.

```bash
sqlite-utils install sqlite-utils-tui

```

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

### sqlite-utils schema CLI help

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli-reference.rst.txt

Command-line help for sqlite-utils schema, showing usage, an example invocation, and options for displaying the database schema.

```text
Usage: sqlite-utils schema [OPTIONS] PATH [TABLES]...

  Show full schema for this database or for specified tables

  Example:

      sqlite-utils schema trees.db

Options:
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.
```

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

### Uninstall package with sqlite-utils uninstall

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Uninstalls a package that was previously installed with sqlite-utils install. Use -y to skip the confirmation prompt.

```bash
sqlite-utils uninstall beautifulsoup4
```

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

### Install and use sqlite-utils in Pyodide

Source: https://sqlite-utils.datasette.io/en/stable/_sources/changelog.rst.txt

Use the Pyodide REPL to install sqlite-utils with micropip and run a simple query against an in-memory database.

```python
>>> import micropip
>>> await micropip.install("sqlite-utils")
>>> import sqlite_utils
>>> db = sqlite_utils.Database(memory=True)
>>> list(db.query("select 3 * 5"))
[{'3 * 5': 15}]
```

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

### Create database with WAL enabled

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Creates a new database and enables WAL mode using --enable-wal.

```bash
sqlite-utils create-database empty.db --enable-wal
```

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

### Install sqlite-dump with sqlite-utils

Source: https://sqlite-utils.datasette.io/en/stable/_sources/installation.rst.txt

Installs the sqlite-dump package, required to use the .iterdump() method or the 'sqlite-utils dump' command, since pysqlite3 does not provide an implementation of .iterdump().

```bash
sqlite-utils install sqlite-dump
```

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

### Database.__init__()

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

Creates a new Database instance with the specified table schema configuration.

```APIDOC
## Database.__init__

### Description
Initializes a Database object with table schema configuration.

### Parameters
- **name** (str) - Required - Name of table
- **columns** (dict[str, Any]) - Optional - Dictionary mapping column names to their types, for example `{
```

```APIDOC
{"name": str, "age": int}
```

```APIDOC
{"name": str, "age": int}
```

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

### CSV file: chickens.csv

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

Example CSV file containing chicken records with id and name columns. Used as input for the sqlite-utils bulk command example.

```csv
id,name
1,Blue
2,Snowy
3,Azi
4,Lila
5,Suna
6,Ccardi

```

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

### Create database with custom SpatiaLite extension

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Creates a new database, initializes SpatiaLite, and loads the extension from a custom path using --load-extension.

```bash
sqlite-utils create-database empty.db --init-spatialite --load-extension /path/to/spatialite.so
```

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

### sqlite-utils create-spatial-index CLI help

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli-reference.rst.txt

CLI help output for the sqlite-utils create-spatial-index command. Requires an existing table and geometry column. By default it tries to load the SpatiaLite extension from usual paths; use --load-extension to specify a path. Options: --load-extension and -h/--help.

```text
Usage: sqlite-utils create-spatial-index [OPTIONS] DB_PATH TABLE COLUMN_NAME

  Create a spatial index on a SpatiaLite geometry column. The table and geometry
  column must already exist before trying to add a spatial index.

  By default, this command will try to load the SpatiaLite extension from usual
  paths. To load it from a specific path, use --load-extension.

Options:
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.
```

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

### Python convert function example

Source: https://sqlite-utils.datasette.io/en/stable/_sources/cli.rst.txt

Defines a convert(value) function that uppercases the input value. This is used as an example of a custom conversion function for the sqlite-utils convert command.

```python
def convert(value):
    return value.upper()
```

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

### Run plugin command

Source: https://sqlite-utils.datasette.io/en/stable/_sources/plugins.rst.txt

Runs the newly registered hello-world command.

```bash
sqlite-utils hello-world
```
Tokens:
Raw