### 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.
```

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

### 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.

```

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

### 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
```

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

### 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
```

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

### 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.

```

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

### 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
```

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

### 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 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
```

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

### 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
```

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

### 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"}
]
```

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

### 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
```

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

### 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
```

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

### 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}]
```

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

### 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

```

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

### 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 bulk usage example

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

Shows how to pipe JSON documents into sqlite-utils bulk to execute parameterized SQL. The example updates the 'chickens' table using named parameters :name and :id from the input documents.

```bash
echo '[
    {"id": 1, "name": "Lila2"},
    {"id": 2, "name": "Suna2"}
]' | sqlite-utils bulk data.db '
    update chickens set name = :name where id = :id
' -
```

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

### 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.
```

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

### prepare_connection hook example

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

Registers a custom SQL function named hello using the prepare_connection hook.

```python
import sqlite_utils

@sqlite_utils.hookimpl
def prepare_connection(conn):
    conn.create_function(
        "hello", 1, lambda name: f"Hello, {name}!"
    )
```

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

### sqlite-utils convert with recipe functions

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

Examples of using recipe functions with the `sqlite-utils convert` command.

```APIDOC
## sqlite-utils convert my.db mytable mycolumn 'r.jsonsplit(value)'

### Description
Uses a recipe function in the code passed to `sqlite-utils convert`. The recipe function can be passed with the `(value)` part or directly without it, as sqlite-utils will detect that it is a callable and use it automatically.

### Usage
```
sqlite-utils convert my.db mytable mycolumn \
  'r.jsonsplit(value)'
```

### Alternative Syntax
```
sqlite-utils convert my.db mytable mycolumn r.parsedate
```

### Using Imported Functions
```
sqlite-utils convert my.db mytable mycolumn json.loads --import json
```

### Using Parameters
```
sqlite-utils convert my.db mytable mycolumn \
  'r.jsonsplit(value, delimiter=":")'
```
```

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

### 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
```

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

### sqlite-utils memory command examples

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

Examples of using the sqlite-utils memory command to execute SQL queries against an in-memory database populated from imported data. The first shows importing from multiple files, the second shows piping data from standard input, the third shows explicit format specification, and the fourth shows viewing the schema of imported files.

```bash
sqlite-utils memory one.csv two.json \
    "select * from one join two on one.two_id = two.id"
```

```bash
cat animals.csv | sqlite-utils memory - \
    "select * from stdin where species = 'dog'"
```

```bash
cat animals.csv | sqlite-utils memory stdin:csv places.dat:nl \
    "select * from stdin where place_id in (select id from places)"
```

```bash
sqlite-utils memory animals.csv --schema
```

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

### sqlite-utils dump command help

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

Shows the command-line help for sqlite-utils dump, which outputs a SQL dump of the schema and full contents of the database. The example runs it on chickens.db.

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

  Output a SQL dump of the schema and full contents of the database

  Example:

      sqlite-utils dump chickens.db

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

```

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

### sqlite-utils transform CLI help

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

Command-line help for sqlite-utils transform, showing usage, an example invocation, and all options for transforming a table beyond ALTER TABLE capabilities.

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

  Transform a table beyond the capabilities of ALTER TABLE

  Example:

      sqlite-utils transform mydb.db mytable \
          --drop column1 \
          --rename column2 column_renamed

Options:
  --type <TEXT CHOICE>...         Change column type to INTEGER, TEXT, FLOAT,
                                  REAL, BLOB or ANY
  --drop TEXT                     Drop this column
  --rename <TEXT TEXT>...         Rename this column to X
  -o, --column-order TEXT         Reorder columns
  --not-null TEXT                 Set this column to NOT NULL
  --not-null-false TEXT           Remove NOT NULL from this column
  --pk TEXT                       Make this column the primary key
  --pk-none                       Remove primary key (convert to rowid table)
  --default <TEXT TEXT>...        Set default value for this column
  --default-none TEXT             Remove default from this column
  --add-foreign-key <TEXT TEXT TEXT>...
                                  Add a foreign key constraint from a column to
                                  another table with another column
  --drop-foreign-key TEXT         Drop foreign key constraint for this column
  --strict / --no-strict          Enable or disable STRICT mode (default:
                                  preserve current mode)
  --sql                           Output SQL without executing it
  --load-extension TEXT           Path to SQLite extension, with optional
                                  :entrypoint
  -h, --help                      Show this message and exit.
```

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

### Plugin register_commands example

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

Plugin module that registers a hello_world command using the register_commands hook.

```python
import click
import sqlite_utils

@sqlite_utils.hookimpl
def register_commands(cli):
    @cli.command()
    def hello_world():
        "Say hello world"
        click.echo("Hello world!")
```

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

### sqlite-utils index-foreign-keys command help

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

Creates an index on every foreign key column in the database. The example runs the command on chickens.db.

```text
Usage: sqlite-utils index-foreign-keys [OPTIONS] PATH

  Ensure every foreign key column has an index on it

  Example:

      sqlite-utils index-foreign-keys chickens.db

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

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

### 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'}
```

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

### 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 analyze command help

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

Shows the command-line help for sqlite-utils analyze, which runs ANALYZE on the whole database or specific named indexes and tables. The example runs it on chickens.db.

```text
Usage: sqlite-utils analyze [OPTIONS] PATH [NAMES]...

  Run ANALYZE against the whole database, or against specific named indexes and
  tables

  Example:

      sqlite-utils analyze chickens.db

Options:
  -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
```

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

### sqlite-utils extract CLI help

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

Command-line help for sqlite-utils extract, showing usage, an example invocation, and options for extracting columns 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.
```

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

### sqlite-utils search usage example

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

Executes a full-text search against the 'chickens' table in data.db for the query 'lila'.

```bash
sqlite-utils search data.db chickens lila
```

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

### Example CSV file with header and data

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

Sample CSV file used to illustrate automatic type detection. Contains name, age, and weight columns.

```csv
name,age,weight
Cleo,6,45.5
Dori,1,3.5

```

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

### sqlite-utils enable-wal command help

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

Enables WAL (Write-Ahead Logging) mode for one or more database files. The example enables WAL on chickens.db.

```text
Usage: sqlite-utils enable-wal [OPTIONS] PATH...

  Enable WAL for database files

  Example:

      sqlite-utils enable-wal chickens.db

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

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

### 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
```

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

### 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
```

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

### 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 tables command usage and options

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

Shows the full command-line interface for sqlite-utils tables, including usage, an example invocation, and all options for filtering, formatting, and output control.

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

  List the tables in the database

  Example:

      sqlite-utils tables trees.db

Options:
  --fts4                 Just show FTS4 enabled tables
  --fts5                 Just show FTS5 enabled tables
  --counts               Include row counts per table
  --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
  --columns              Include list of columns for each table
  --schema               Include schema for each table
  --load-extension TEXT  Path to SQLite extension, with optional :entrypoint
  -h, --help             Show this message and exit.
```

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

### 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.

```

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

### 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 rows command help

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

Command-line help output for the sqlite-utils rows command. Shows the usage and an example invocation: sqlite-utils rows trees.db Trees.

```text
Usage: sqlite-utils rows [OPTIONS] PATH DBTABLE

  Output all rows in the specified table

  Example:

      sqlite-utils rows trees.db Trees
```

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

### 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.
```

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

### Rowid table: insert, schema, and select examples

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

Creates a rowid table and shows that the rowid column is not included in select * output, but is available when explicitly selected.

```bash
echo '[{"name": "Azi"}, {"name": "Suna"}]' | \
    sqlite-utils insert chickens.db chickens -
sqlite-utils schema chickens.db

```

```sql
CREATE TABLE "chickens" (
   "name" TEXT
);

```

```bash
sqlite-utils chickens.db 'select * from chickens'

```

```json
[{"name": "Azi"},
 {"name": "Suna"}]

```

```bash
sqlite-utils chickens.db 'select rowid, * from chickens'

```

```json
[{"rowid": 1, "name": "Azi"},
 {"rowid": 2, "name": "Suna"}]

```

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

### 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]
]
```

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

### 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
```

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

### 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
```

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

### 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
```

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

### 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()
```

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

### 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.
```

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

### 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
```

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

### Example release commit

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

Example commit for a release that updates the changelog and references the issues included in the release. The commit message includes the version number and issue references.

```bash
# Update changelog
git commit -m " Release 3.29

Refs #423, #458, #467, #469, #470, #471, #472, #475" -a
git push

```

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

### Run tests with uv

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

Run the test suite using uv.

```bash
uv run pytest
```

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

### sqlite-utils convert --multi example

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

Example of using --multi to convert a column into multiple columns. The Python code returns a dictionary, and the resulting schema shows the new columns with types inferred from the returned values.

```bash
sqlite-utils convert demo.db places location \
'bits = value.split(",")
return {
  "latitude": float(bits[0]),
  "longitude": float(bits[1]),
}' --multi
```

```sql
CREATE TABLE "places" (
    "location" TEXT,
    "latitude" REAL,
    "longitude" REAL
);
```

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

### sqlite-utils create-database

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

Creates a new empty SQLite database file. Use the --enable-wal option to enable WAL mode, --init-spatialite to enable SpatiaLite, and --load-extension to load a SQLite extension.

```APIDOC
## sqlite-utils create-database

### Description
Creates a new empty SQLite database file at the specified path.

### Usage
```
sqlite-utils create-database [OPTIONS] PATH
```

### 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.

### Example
```
sqlite-utils create-database trees.db
```
```

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

### Install pysqlite3 with sqlite-utils

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

Installs the pysqlite3 package, a drop-in replacement for the standard library sqlite3 module with a recent SQLite version and full extension loading support. Use this to work around limitations like the 'sqlite3.OperationalError: table sqlite_master may not be modified' error.

```bash
sqlite-utils install pysqlite3
```

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

### sqlite-utils drop-view CLI help

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

CLI help output for the sqlite-utils drop-view command. Shows the usage, an example invocation dropping the heavy_chickens view, and the available options: --ignore, --load-extension, and -h/--help.

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

  Drop the specified view

  Example:

      sqlite-utils drop-view chickens.db heavy_chickens

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

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

### .virtual_table_using

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

Property to check if a table is a virtual table and get its type.

```APIDOC
## .virtual_table_using

### Description
Property that reveals if a table is a virtual table. Returns None for regular tables and the upper case version of the type of virtual table otherwise.

### Method
Property access on a Table object.

### Endpoint
Table.virtual_table_using

### Parameters
None

### Response
#### Success Response
- **result** (string or None) - The upper case virtual table type (e.g., "FTS5") or None.

#### Response Example
```
"FTS5"
```
```

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

### Run migrations from a directory

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

Pass a directory to consider every migrations.py file in that directory tree.

```bash
sqlite-utils migrate creatures.db path/to/project/

```

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

### List Just commands

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

List all available Just commands.

```bash
just -l
```

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

### Upgrade sqlite-utils with Homebrew

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

Upgrades an existing Homebrew installation of sqlite-utils to the most recent release.

```bash
brew upgrade sqlite-utils
```

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

### Run linters with Just

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

Run just the linters using Just.

```bash
just lint
```

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

### sqlite-utils Table Methods

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

Documentation for the pks_and_rows_where() and get() methods of the sqlite-utils Table class.

```APIDOC
## pks_and_rows_where()

### Description
Retrieves rows from a table along with their primary keys. Returns a generator that yields pairs of (primary key, row dictionary). The primary key can be a single value or a tuple for compound primary keys. For rowid tables, the rowid is returned as the primary key.

### Method
Method call on a Table object

### Endpoint
`table.pks_and_rows_where()`

### Parameters
#### Method Parameters
- **select** (string) - Optional - The `select=` parameter is not supported by this method (unlike `rows_where()`).
- Other parameters follow the same signature as `rows_where()`.

### Request Example
```python
>>> db = sqlite_utils.Database(memory=True)
>>> db.table("dogs").insert({"name": "Cleo"})
>>> for pk, row in db.table("dogs").pks_and_rows_where():
...     print(pk, row)
```

### Response
#### Success Response
- **primary key** (any) - The primary key value (single value or tuple for compound primary keys).
- **row** (dict) - The row dictionary.

#### Response Example
```python
1 {'rowid': 1, 'name': 'Cleo'}
5 {'id': 5, 'name': 'Cleo'}
('dog', 3) {'species': 'dog', 'id': 3, 'name': 'Cleo'}
```
```

```APIDOC
## get()

### Description
Retrieves a single record by its primary key. For tables with a compound primary key, pass the primary key values as a tuple. Raises a `NotFoundError` if the record does not exist.

### Method
Method call on a Table object

### Endpoint
`table.get(primary_key)`

### Parameters
#### Method Parameters
- **primary_key** (any) - Required - The primary key value. For compound primary keys, pass a tuple of values.

### Request Example
```python
>>> db = sqlite_utils.Database("dogs.db")
>>> print(db.table("dogs").get(1))
```

### Response
#### Success Response
- **row** (dict) - The row dictionary.

#### Response Example
```python
{'id': 1, 'age': 4, 'name': 'Cleo'}
```

### Error Handling
- **NotFoundError** - Raised if the record does not exist.

#### Error Example
```python
from sqlite_utils.db import NotFoundError

try:
    row = db.table("dogs").get(5)
except NotFoundError:
    print("Dog not found")
```
```

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

### Run all tests and linters with Just

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

Run all tests and linters using Just.

```bash
just
```

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

### Get default values with .default_values

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

Returns a dictionary of default values for columns that have a default.

```python
>>> db.table("table_with_defaults").default_values
{'score': 5}
```

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

### sqlite-utils search

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

Documentation for the sqlite-utils search CLI command, including its options and usage examples.

```APIDOC
## [CLI] sqlite-utils search

### Description
Executes a full-text search against a table that has been configured for full-text search. It supports various output formats and options for sorting, limiting, and selecting columns.

### Command
`sqlite-utils search mydb.db documents searchterm`

### Parameters
- **database** (string) - Required - Path to the SQLite database file.
- **table** (string) - Required - Name of the table to search.
- **searchterm** (string) - Required - The search term or query to execute.

### Options
- `--table` - Output results as a table.
- `--csv` - Output results as CSV.
- `--tsv` - Output results as TSV.
- `--nl` - Output results as newline-delimited JSON.
- `-o, --order` (string) - Sort order. Can be a column name or a column followed by `desc` (e.g., `rowid` or `created desc`).
- `--quote` - Apply automatic quoting to search terms to avoid interpretation as advanced search syntax.
- `-c, --column` (string) - Specify a subset of columns to return. Can be used multiple times.
- `--limit` (integer) - Limit the number of results returned (e.g., `--limit 20`).
- `--sql` - Output the SQL that would be executed instead of running the query.

### Examples
```
# Basic search
sqlite-utils search mydb.db documents searchterm

# Sort by rowid
sqlite-utils search mydb.db documents searchterm -o rowid

# Sort by created in descending order
sqlite-utils search mydb.db documents searchterm -o 'created desc'

# Return only title and created columns
sqlite-utils search mydb.db documents searchterm -c title -c created

# Limit to 20 results
sqlite-utils search mydb.db documents searchterm --limit 20

# Output the SQL that would be executed
sqlite-utils search mydb.db documents searchterm --sql
```

### SQL Output Example
```sql
with original as (
    select
        rowid,
        *
    from "documents"
)
select
    "original".*
from
    "original"
    join "documents_fts" on "original".rowid = "documents_fts".rowid
where
    "documents_fts" match :query
order by
    "documents_fts".rank
```

### Notes
- By default, results are ordered by relevance (most relevant first).
- SQLite advanced search syntax is enabled by default.
- In Python, the equivalent method is `table.search()`.
```

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

### Get table-level checks with .table_checks

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

Returns only the table-level CHECK constraints as a list of Check objects.

```python
>>> db["scores"].table_checks
[Check(check='score <= maximum', name='within_maximum', column='', options=None)]
```

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

### Recreate database with recreate=True

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

Recreates the database from scratch, first removing the existing file from disk if it exists.

```python
db = Database("my_database.db", recreate=True)
```