sqlalchemy-boltons 1.0.0

Utilities that should've been inside SQLAlchemy but aren't

sqlalchemy_boltons

SQLAlchemy is great. However, it doesn't have everything built-in. Some important things are missing, and need to be "bolted on".

(Name inspired from boltons. Not affiliated.)

sqlite

SQLAlchemy doesn't automatically fix pysqlite's broken transaction handling. Instead, it provides a recipe for doing so inside the documentation. This module implements a fix for that broken behaviour.

You can customize, on a per-engine or per-connection basis:

• The type of transaction to be started, such as BEGIN or BEGIN IMMEDIATE (or BEGIN CONCURRENT someday maybe).
• The foreign-key enforcement setting. Can be True, False, or "defer".
• The journal mode such as DELETE or WAL.

from sqlalchemy.orm import sessionmaker
from sqlalchemy_boltons.sqlite import create_engine_sqlite

engine = create_engine_sqlite("file.db", journal_mode="WAL", timeout=0.5, create_engine_args={"echo": True})

# use standard "BEGIN" and use deferred enforcement of foreign keys
engine = engine.execution_options(x_sqlite_begin_mode=None, x_sqlite_foreign_keys="defer")

# make a separate engine for write transactions using "BEGIN IMMEDIATE" for eager locking
engine_w = engine.execution_options(x_sqlite_begin_mode="IMMEDIATE")

Session = sessionmaker(engine)
SessionW = sessionmaker(engine_w)

with Session() as session:
    session.execute(select(...))

# this locks the database eagerly
with SessionW() as session:
    session.execute(update(...))