CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyautocommit
yevt
yevt

Reputation: 816

Why sqlalchemy postgres engine connection commits automatically?

For some reason engine connection commits automatically, which is not desired. I want to commit explicitly. Here's my code (it repeats official tutorial verbatim):

from sqlalchemy import Table, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from sqlalchemy.orm import registry
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey

engine = create_engine('postgresql+psycopg2://user:password@localhost:5432/test_db', echo=True)

mapper_registry = registry()
Base = mapper_registry.generate_base()

user_table = Table(
    "user_account",
    mapper_registry.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(30)),
    Column("fullname", String),
)

mapper_registry.metadata.create_all(engine)

stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")
print(stmt)
    
with engine.connect() as conn:
    result = conn.execute(stmt)
    # conn.commit()

Even without commit my logs are:

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
2023-02-09 16:12:05,033 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING user_account.id
2023-02-09 16:12:05,034 INFO sqlalchemy.engine.Engine [generated in 0.00123s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
2023-02-09 16:12:05,037 INFO sqlalchemy.engine.Engine COMMIT

And new object appears in the database. I tried to set conn.execution_options(isolation_level="SERIALIZABLE") and create_engine(...).execution_options(isolation_level="SERIALIZABLE") with no success

However when I set isolation_level to "AUTOCOMMIT", logs are slightly different (so I'm sure isolation_level option is interpreted correctly):

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
2023-02-09 16:17:35,889 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING user_account.id
2023-02-09 16:17:35,890 INFO sqlalchemy.engine.Engine [generated in 0.00132s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
2023-02-09 16:17:35,893 INFO sqlalchemy.engine.Engine COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode

sqlalchemy.__version__
'1.4.46'

What's the reason of such behavior?

What do I not understand?

What am I missing?

Thanks

IMPORTANT UPDATE

As @snakecharmerb pointed, with fututre=true it works as expected (and this is the only significant difference with the docs).

engine = create_engine('...url', echo=True, future=True**)

So this flag changes autocommit behavior.

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
2023-02-09 17:42:08,499 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-09 17:42:08,499 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING user_account.id
2023-02-09 17:42:08,500 INFO sqlalchemy.engine.Engine [generated in 0.00085s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
<sqlalchemy.engine.cursor.CursorResult object at 0x7fef30f76ac0>
2023-02-09 17:42:10,511 INFO sqlalchemy.engine.Engine ROLLBACK

It answers my question mostly, but I'm still curious if I can have similar behavior without future=True, because I cannot turn it on (for the project I work on) yet

Upvotes: 1

Views: 2270

Answers (1)

yevt
yevt

Reputation: 816

Ok. Now it's clear. autocommit and isolation_level are different things. (They should be related, but that is out of the scope of the current question)

  1. autocommit is off by default if future=True, and on by default otherwise.
  2. If you cannot set future=True but still want autocommit to be off you have 2 options (at least):

2.1 Pass autocommit=false to connection:

with engine.connect().execution_options(autocommit=False) as conn

2.2 Pass autocommit=false to engine:

engine = create_engine('...dburl', echo=True).execution_options(autocommit=False)

Upvotes: 1

Related Questions