Fastlite: Enhanced SQLite Usability

0.2.4 · active · verified Sun Apr 12

Fastlite provides quality-of-life improvements for interactive use of the `sqlite-utils` library, particularly in Jupyter environments. It enhances database and table access with auto-completion, simplifies dataclass creation from schemas, and streamlines data manipulation. The library is currently active, with version 0.2.4 addressing recent bug fixes and continuing to evolve with new usability features.

Warnings

breaking In version 0.2.0, the `table.fetchone` method was renamed to `table.selectone`. Additionally, `selectone` now strictly raises an exception (`fastlite.core.RowCountError`) if the query returns more than one row, ensuring uniqueness.
Fix: Migrate calls from `fetchone` to `selectone`. If your code expects multiple rows or handles non-unique results, adjust logic to catch `RowCountError` or use alternative querying methods like `table()` for a list of results.
breaking Starting with versions 0.1.0/0.1.1, Fastlite was rewritten to use `apsw` instead of Python's standard `sqlite3` module. This change was primarily driven by concurrency and performance issues found in `sqlite3` (especially with Python 3.12).
Fix: Ensure `apsw` is installed (`pip install apsw`). Code that directly interacted with `sqlite3` specifics or relied on its exact concurrency model might need review, although Fastlite aims to abstract most of these differences.
gotcha Fastlite maintains a strict distinction between `None` (representing SQL `NULL`) and an empty string (`''`) when interacting with SQLite. This differs from some ORMs that might treat them interchangeably.
Fix: When querying or inserting, be mindful of this distinction. Use `WHERE column IS NULL` for `None` values and `WHERE column = ''` for empty strings in SQL queries, and ensure your Python data correctly reflects this semantic difference.
gotcha When using `insert` with `ignore=True` and a duplicate primary key exists, older versions (<0.2.4) could raise a `KeyError` instead of ignoring the insertion.
Fix: Upgrade to version 0.2.4 or newer to ensure `insert(..., ignore=True)` behaves as expected and gracefully handles duplicate keys without raising a `KeyError`.

Install

pip install fastlite Install stable version

Imports

*
```
from fastlite import *
```
Commonly used to import all Fastlite utilities, including the `database` function.
database
```
from fastlite import database
```
Specifically imports the function to create or connect to a SQLite database.

Quickstart

This quickstart demonstrates how to initialize an in-memory SQLite database, create a table with a primary key, insert new records, and retrieve data. It covers basic table creation, insertion, and querying using Fastlite's simplified `db.t` table access.

from fastlite import database
import os

# Create an in-memory database for quick testing
db = database(':memory:')

# Or, for a persistent file-based database
# db_file = 'example.db'
# if os.path.exists(db_file): os.remove(db_file) # Clean up for repeatable example
# db = database(db_file)

# Create a table named 'users'
users_table = db.t.users
users_table.create(
    id=int, 
    name=str, 
    email=str,
    pk='id' # Set 'id' as the primary key
)

# Insert data into the 'users' table
users_table.insert({'id': 1, 'name': 'Alice', 'email': 'alice@example.com'})
users_table.insert({'id': 2, 'name': 'Bob', 'email': 'bob@example.com'})

# Query all users
print('All users:')
for user in users_table():
    print(user)

# Query a specific user by primary key
alice = users_table.get(1)
print(f'\nUser with ID 1: {alice}')

# Update a user
users_table.update({'id': 1, 'name': 'Alicia'})
print('\nUpdated user with ID 1:')
for user in users_table(id=1):
    print(user)

view raw JSON →