APSW Utilities

0.1.2 · active · verified Sun Apr 12

apswutils (version 0.1.2) is a Python utility library that forks sqlite-minutils to provide a convenient API for interacting with SQLite databases using the `apsw` driver. It offers simplified methods for database and table creation, data insertion, and querying, essentially acting as a lightweight wrapper over `apsw`. The project is actively maintained with recent updates and emphasizes features like WAL mode by default.

Warnings

breaking In version 0.0.3, the `fetchone` method was renamed to `item`. Additionally, `item` now raises an exception if more than one row or field is present in the result.
Fix: Replace calls to `fetchone()` with `item()`. Ensure that `item()` is only used when exactly one row and field are expected, or wrap in a try-except block.
breaking Version 0.1.0 introduced breaking changes, though specific details beyond 'Bump version number due to breaking change' are not explicitly documented in the release notes. Subsequent releases indicate the removal of `OperationError` which might be related.
Fix: Review your code for any changes in error handling, particularly around `OperationError`, and test thoroughly when upgrading from versions prior to 0.1.0.
gotcha The library is built on `apsw`, which has specific behaviors differing from `sqlite3`. `apswutils` defaults to WAL (Write-Ahead Logging) mode. Additionally, `apsw` connections should not be used across `fork` operations (e.g., in `multiprocessing`) unless explicitly closed in the parent before forking.
Fix: Be aware of `apsw`'s unique characteristics. If using `multiprocessing`, ensure `apsw` database connections are closed in the parent process before new processes are spawned. Explicitly manage transactions if not relying on `apsw`'s default autocommit behavior for non-transactional statements.
gotcha While `apswutils` provides a simplified API, its comprehensive documentation directs users to the `sqlite-utils` project for full details, as `apswutils` is largely a fork with minor changes.
Fix: Refer to the `sqlite-utils` documentation for in-depth understanding of many underlying concepts and functionalities.

Install

pip install apswutils Install latest version

Imports

Database
```
from apswutils.db import Database
```
Table
```
from apswutils.db import Table
```
View
```
from apswutils.db import View
```
Queryable
```
from apswutils.db import Queryable
```
* (wildcard import)
```
from apswutils.db import *
```
The library's README suggests this pattern, stating it only brings in Database, Queryable, Table, View classes without namespace pollution.

Quickstart

This quickstart demonstrates how to initialize an in-memory SQLite database, create a table with defined columns and a primary key, insert single and multiple records, and query all rows. It also shows how to modify the table schema by adding a new column.

from apswutils.db import Database, Table

db = Database(':memory:') # Use ':memory:' for an in-memory database

# Create a table
users = Table(db, 'Users')
users.create(columns=dict(id=int, name=str, age=int), pk='id')

# Insert data
users.insert({'id': 1, 'name': 'Alice', 'age': 30})
users.insert_all([
    {'id': 2, 'name': 'Bob', 'age': 24},
    {'id': 3, 'name': 'Charlie', 'age': 35}
])

# Query data
for user in users.rows:
    print(f"ID: {user['id']}, Name: {user['name']}, Age: {user['age']}")

# Update table schema (add a column)
users.create(columns=dict(id=int, name=str, age=int, email=str), transform=True, pk='id')
print("\nUpdated schema:")
print(db.schema)

db.close()

view raw JSON →