How to use READ ONLY transaction mode in SQLAlchemy?

Question

In PostgreSQL transaction access mode can be changed to READ ONLY (docs). In SQLAlchemy, isolation level can be changed for an engine, but no parameter is provided for read-only access mode (docs).

How to make an engine with READ ONLY access mode on connections?

Hyponitrite

Answer 1

One solution is to execute a statement on every transaction:

engine = create_engine('postgresql+psycopg2://postgres:[email protected]: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.

Hyponitrite

Answer 2

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 transaction

The above is an example taken from https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#postgresql-readonly-deferrable

Dafna

Answer 3

It is convenient to create an engine by using create_engine() together with execution_options(), because it allows to create an engine with readonly access mode (meaning that readonly is applied to all connections of that engine):

from sqlalchemy import create_engine

engine = (create_engine("postgresql://scott:tiger@localhost:5432/mydatabase")
          .execution_options(postgresql_readonly=True))

Also possible is the following (found it in the docs):

from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost:5432/mydatabase",
                       execution_options={"postgresql_readonly": True})

Fecund

You may want to tell scott that his password has been compromised. – Holdup