CODEWITHSUNDEEP
pythonpostgresqlsqlalchemypg8000
Leonel
Leonel

Reputation: 29227

SQLAlchemy, Postgres: Run SQL without transaction

I am using SQLAlchemy and pg8000 to connect to a Postgres database.

I have checked table pg_stat_activity, which shows me a few select queries in 'idle in transaction' state, many of those. But the application much more reads than writes, that is, inserts are few and far between.

I suspect that a new transaction is created for each query, even for simple select statements.

Is it possible to run a read-only query without the need for a transaction? So that it does not need to be committed/rolled back?

Currently, the app runs its queries with method sqlalchemy.engine.Engine.execute for CRUD operations and cursors for calling stored procedures. How should I update these method calls to indicate I want some of them not to start transactions?

Upvotes: 3

Views: 1040

Answers (1)

Luke
Luke

Reputation: 41

The short story, add isolation_level="AUTOCOMMIT" in creating of engine

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine(
    'postgresql+pg8000://user:pass@localhost:5432/testdb',
    isolation_level="AUTOCOMMIT"
)
df = pd.read_sql_query("select * from ...", con=engine)
print(df)

The longer explanations: DBPAI 2.0 default transaction is false for autocommit is, thus drivers like pg8000 would run "BEGIN TRANSACTION" before any query! While it improves consistency, these hidden transaction impacts performance and could break some databases supporting postgres wire protocol but doesn't expect such behavior. The solution is passing "isolation_level='AUTOCOMMIT'" to create_engine() with pg8000 to turn on support of autocommit by default. When autocommit (actually a misnomer) is true, drivers like pg8000 will omit the "BEGIN TRANSACTION" and assume a transaction is automatically created, performing commit/rollback when instructed. Obviously, it is more flexible and performance without such hidden work, luckily this could be mitigated with "isolation_level='AUTOCOMMIT'".

SQLAlchemy also attempt to probe server version and capabilities. You may have seen them and it is probably better for the developer team to minimize / test impact of all such features.

Upvotes: 2

Related Questions