APSW Utilities

0.1.2 verified Sun Apr 12 auth: no python

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.

pip install apswutils

Common errors

error ModuleNotFoundError: No module named 'apsw' ↓

cause The 'apsw' module is not installed in the Python environment.

fix

Install the 'apsw' module using pip: 'pip install apsw'.

error AttributeError: module 'apsw' has no attribute 'Connection' ↓

cause The 'apsw' module is either not installed correctly or the script is shadowing the 'apsw' module by naming a local file 'apsw.py'.

fix

Ensure 'apsw' is installed correctly and rename any local files named 'apsw.py' to avoid conflicts.

error AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension' ↓

cause The 'sqlite3' module in Python does not support the 'enable_load_extension' method in some versions.

fix

Use the 'apsw' module instead, which provides this functionality: 'import apsw; connection = apsw.Connection("database.db"); connection.enable_load_extension(True)'.

error ModuleNotFoundError: No module named 'apswutils' ↓

cause The 'apswutils' package is not installed in the Python environment, or the environment where it is installed is not the one being used.

fix

Run pip install apswutils to install the library.

error AttributeError: module 'apswutils' has no attribute 'Database' ↓

cause The core classes like `Database`, `Table`, `Queryable`, and `View` are located within the `apswutils.db` submodule, and are not directly accessible from the top-level `apswutils` package.

fix

Import the necessary classes directly from the apswutils.db submodule, for example: from apswutils.db import Database, Table or from apswutils.db import *.

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.

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