How to use READ ONLY transaction mode in SQLAlchemy?
One solution is to execute a statement on every transaction:
engine = create_engine('postgresql+psycopg2://postgres:pass@127.0.0.1:5432/')@event.listens_for(engine, 'begin')def receive_begin(conn): conn.execute('SET TRANSACTION READ ONLY')But it's better to set mode in BEGIN TRANSACTION line an not a separate statement.
With SQL Alchemy 1.4, it is possible to set readonly and deferrable mode using Connection.execution_options().
with engine.connect() as conn: conn = conn.execution_options( isolation_level="SERIALIZABLE", postgresql_readonly=True, postgresql_deferrable=True ) with conn.begin(): # ... work with transactionThe above is an example taken from https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#postgresql-readonly-deferrable