Transaction

Transactions ensure a group of operations either all succeed (commit) or all fail (rollback).

class Conn:
  def tx(
    self,
    isolation_level: IsolationLevel | None = None,
    readonly: bool | None = None,
  ) -> Transaction: ...

class Transaction:
  def commit(self) -> None: ...
  def rollback(self) -> None: ...

The transactions should be entered as a context manager. On successful exit, the transaction commits. On exception, it rolls back.

with conn.tx():
    conn.query_drop("INSERT INTO users (name) VALUES ('Alice')")
    conn.query_drop("INSERT INTO users (name) VALUES ('Bob')")
# auto-committed here

with conn.tx():
    conn.query_drop("INSERT INTO users (name) VALUES ('Alice')")
    raise ValueError("oops")
# auto-rolled back, no data inserted

Explicit Commit / Rollback

You can also call commit() or rollback() explicitly inside the context manager. After the call, the transaction object cannot be used anymore.

with conn.tx() as tx:
    conn.query_drop("INSERT ...")
    if some_condition:
        tx.commit()
    else:
        tx.rollback()

Isolation Level

from pyro_postgres import IsolationLevel

# BEGIN ISOLATION LEVEL SERIALIZABLE
with conn.tx(isolation_level=IsolationLevel.Serializable):
    ...

You can also create isolation levels from strings:

level = IsolationLevel("READ COMMITTED")
level = IsolationLevel("repeatable_read")
level = IsolationLevel("sErIaLiZaBle")

assert level.as_str() == "SERIALIZABLE"

Read-Only Transactions

Set readonly=True for read-only transactions. This can improve performance and is required for read replicas.

with conn.tx(readonly=True):  # BEGIN READ ONLY
    rows = conn.query("SELECT * FROM users")

Async

For async connections, use async with and await:

async with conn.tx() as tx:
    await conn.query_drop("INSERT ...")

# explicit commit/rollback
async with conn.tx() as tx:
    await conn.query_drop("INSERT ...")
    await tx.commit()