sqlalchemy-boltons 2.1.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. This module implements the usual fix for that well-known broken behaviour, and also adds extra features on top of that.

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

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

Here's a minimal example:

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},
)

# Configure the engine to use a plain "BEGIN" to start transactions and
# and to use deferred enforcement of foreign keys (recommended!)
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")

# Construct a sessionmaker for each engine.
Session = sessionmaker(engine)
SessionW = sessionmaker(engine_w)

# read-only transaction
with Session() as session:
    session.execute(select(...))

# lock the database eagerly for writing
with SessionW() as session:
    session.execute(update(...))

orm

Somedays you really wish you could write something like:

# Find every parent that has at least one child.
Parent1 = aliased(Parent)
select(Parent1).where(
    exists().select_from(Child).where(Child.parent == Parent1)  # this doesn't work
)

But it doesn't work. You get an exception. You try various other things but it just won't produce the right subquery. You find tons of people online saying you must resign yourself to writing the explicit filtering condition, writing out .where(Child.parent_id == Parent1.id). Like a caveman. Why even bother using an ORM at this point? If you're lucky, you find yourself reading mail archives from a decade ago with somewhat of a solution, but it's not obvious how to make it work with table aliases. Despair is setting in.

But lucky you, you can now use this library:

from sqlalchemy_boltons.orm import RelationshipComparator as Rel

# Find every parent that has at least one child.
Parent1 = aliased(Parent)
select(Parent1).where(
    exists().select_from(Child).where(Rel(Child.parent) == Parent1)
)  #                                  ^^^^            ^

Hope is restored. This even works on self-referential tables!