Skip to content

Instantly share code, notes, and snippets.

@jdavcs

jdavcs/running with autocommit

Last active September 14, 2023 13:44
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jdavcs/a0b4c15c326c079cd28b433581bf6cc1 to your computer and use it in GitHub Desktop.
Save jdavcs/a0b4c15c326c079cd28b433581bf6cc1 to your computer and use it in GitHub Desktop.
Download ZIP
savepoint transaction
Raw
gistfile1.txt
import contextlib
from sqlalchemy import Column, Integer, String
from sqlalchemy import select
from sqlalchemy import create_engine
from sqlalchemy.orm import (
declarative_base,
sessionmaker,
)
#################### setup ####################
engine = create_engine([connection string], echo=True)
Base = declarative_base()
class Job(Base):
__tablename__ = "job"
id = Column(Integer, primary_key=True)
state = Column(String)
Base.metadata.create_all(engine)
@contextlib.contextmanager
def transaction(session):
if not session.in_transaction():
with session.begin():
yield
else:
yield
def get_jobs(session):
return session.scalars(select(Job).order_by(Job.id))
#################### tests ####################
def test_autocommit():
"""
Works with both autocommit settings.
Pass job error_id to simulate error at a given step in the iteration.
Expeted result: jobs processed before error should be commited, errored
job: rolled back, subsequent jobs: not reached.
"""
#sm = sessionmaker(bind=engine, autoflush=False, autocommit=True)
sm = sessionmaker(bind=engine, autoflush=False, autocommit=False)
with sm() as session, session.begin():
jobs = get_jobs(session)
try:
for job in jobs:
with session.begin_nested():
change_state(session, job)
finally:
session.commit()
def change_state(session, job, error_id=None):
with transaction(session):
job.state = 'updated'
if job.id == error_id:
raise Exception("MY EXCEPTION") # simulate error
session.commit()
test_autocommit()
Raw
running with autocommit
(.venv_sa14) rivendell$ python savepoint_demo.py
2023-09-14 09:38:57,035 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-09-14 09:38:57,035 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-09-14 09:38:57,036 INFO sqlalchemy.engine.Engine select current_schema()
2023-09-14 09:38:57,036 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-09-14 09:38:57,036 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-09-14 09:38:57,036 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-09-14 09:38:57,037 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-09-14 09:38:57,037 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2023-09-14 09:38:57,037 INFO sqlalchemy.engine.Engine [generated in 0.00008s] {'name': 'job'}
2023-09-14 09:38:57,037 INFO sqlalchemy.engine.Engine COMMIT
/home/john/0dev/drafts/python/sqlalchemy/sa20/savepoint_demo.py:50: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to "sqlalchemy<2.0". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings. Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
with sm() as session, session.begin():
2023-09-14 09:38:57,038 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-09-14 09:38:57,039 INFO sqlalchemy.engine.Engine SELECT job.id, job.state
FROM job ORDER BY job.id
2023-09-14 09:38:57,039 INFO sqlalchemy.engine.Engine [generated in 0.00006s] {}
2023-09-14 09:38:57,040 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_1
2023-09-14 09:38:57,040 INFO sqlalchemy.engine.Engine [no key 0.00005s] {}
2023-09-14 09:38:57,040 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine [generated in 0.00006s] {'state': 'updated', 'job_id': 13}
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_1
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine [no key 0.00005s] {}
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_2
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine [cached since 0.0007861s ago] {'state': 'updated', 'job_id': 14}
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_2
2023-09-14 09:38:57,041 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_3
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine [cached since 0.001357s ago] {'state': 'updated', 'job_id': 15}
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_3
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_4
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine [no key 0.00005s] {}
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:38:57,042 INFO sqlalchemy.engine.Engine [cached since 0.001909s ago] {'state': 'updated', 'job_id': 16}
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_4
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_5
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine [cached since 0.002437s ago] {'state': 'updated', 'job_id': 17}
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_5
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:38:57,043 INFO sqlalchemy.engine.Engine COMMIT
Raw
running with autocommit = false
(.venv_sa14) rivendell$ python savepoint_demo.py
2023-09-14 09:43:45,991 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-09-14 09:43:45,991 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-09-14 09:43:45,992 INFO sqlalchemy.engine.Engine select current_schema()
2023-09-14 09:43:45,992 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-09-14 09:43:45,993 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-09-14 09:43:45,993 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-09-14 09:43:45,993 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-09-14 09:43:45,993 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2023-09-14 09:43:45,993 INFO sqlalchemy.engine.Engine [generated in 0.00008s] {'name': 'job'}
2023-09-14 09:43:45,994 INFO sqlalchemy.engine.Engine COMMIT
2023-09-14 09:43:45,994 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-09-14 09:43:45,995 INFO sqlalchemy.engine.Engine SELECT job.id, job.state
FROM job ORDER BY job.id
2023-09-14 09:43:45,995 INFO sqlalchemy.engine.Engine [generated in 0.00005s] {}
2023-09-14 09:43:45,996 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_1
2023-09-14 09:43:45,996 INFO sqlalchemy.engine.Engine [no key 0.00005s] {}
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine [generated in 0.00006s] {'state': 'updated', 'job_id': 13}
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_1
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine [no key 0.00005s] {}
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_2
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:43:45,997 INFO sqlalchemy.engine.Engine [cached since 0.000822s ago] {'state': 'updated', 'job_id': 14}
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_2
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine [no key 0.00005s] {}
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_3
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine [cached since 0.001418s ago] {'state': 'updated', 'job_id': 15}
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_3
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_4
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,998 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine [cached since 0.002s ago] {'state': 'updated', 'job_id': 16}
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_4
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine SAVEPOINT sa_savepoint_5
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine UPDATE job SET state=%(state)s WHERE job.id = %(job_id)s
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine [cached since 0.002572s ago] {'state': 'updated', 'job_id': 17}
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine RELEASE SAVEPOINT sa_savepoint_5
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine [no key 0.00004s] {}
2023-09-14 09:43:45,999 INFO sqlalchemy.engine.Engine COMMIT
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment