sqlite-utils

3.39 · active · verified Mon Apr 13

sqlite-utils is a CLI tool and Python library for conveniently creating and manipulating SQLite databases. It simplifies common tasks like importing data, running queries, and managing schema. The current stable version is 3.39, with development on 4.0 in alpha. The library maintains a frequent release cadence, with over 128 releases since 2018.

Warnings

breaking The `db.table(table_name)` method now exclusively works with tables. To access SQL views, you must use `db.view(view_name)` instead. This change improves type hinting capabilities for tables vs views.
Fix: Update calls from `db.table()` to `db.view()` for SQL views.
breaking Upsert operations (`.upsert()` and `.upsert_all()`) now utilize SQLite's `INSERT ... ON CONFLICT SET` syntax on SQLite versions newer than 3.23.1. Previously, it used `INSERT OR IGNORE` followed by an `UPDATE`. While largely functionally equivalent, applications depending on the exact old behavior might see minor differences.
Fix: If the old behavior is critical, pass `use_old_upsert=True` to the `Database()` constructor. Otherwise, ensure your code is compatible with the new upsert mechanism.
breaking sqlite-utils now requires Python 3.10 or higher. Older Python versions are no longer supported.
Fix: Upgrade your Python environment to 3.10 or a newer compatible version.
breaking The default floating point column type has been changed from `FLOAT` to `REAL`, which is the correct SQLite type for floating-point values. This primarily affects auto-detected column types when inserting data.
Fix: No direct fix needed unless explicit type declarations are required, in which case specify column types during table creation or insertion.
gotcha Foreign key creation no longer directly manipulates the `sqlite_master` table using `PRAGMA writable_schema = 1`. Instead, it uses a table transformation mechanism. Code that relied on directly writing to `sqlite_master` for foreign key management may break or behave unexpectedly.
Fix: Use the `table.transform()` method with `foreign_keys=` or `add_foreign_keys=` parameters, or `db['table'].add_foreign_key()` instead of direct schema manipulation.

Install

pip install sqlite-utils Install stable version

Imports

Database
```
from sqlite_utils import Database
```

Quickstart

This quickstart demonstrates how to create a Database object, insert data into a table (which is automatically created if it doesn't exist), and then query and update data. It shows both in-memory and file-based database creation.

from sqlite_utils import Database

# Create an in-memory database
db = Database(memory=True)

# Or create a file-based database
# db = Database('my_data.db')

# Insert data into a table, creating it if it doesn't exist
db["dogs"].insert_all(
    [
        {"id": 1, "name": "Cleo", "age": 4},
        {"id": 2, "name": "Pancakes", "age": 2}
    ],
    pk="id"
)

# Query data
for row in db.query("select * from dogs where age > ?", [3]):
    print(row)

# Access table objects and perform operations
dogs_table = db["dogs"]
print(f"Total dogs: {dogs_table.count}")

dogs_table.update(1, {"age": 5})

for row in dogs_table.rows: # Iterate through all rows
    print(row)

view raw JSON →