A similar question about another anti-pattern was asked/answered recently. 
Most of what Mike says applies in this use-case 
 https://groups.google.com/forum/#!topic/sqlalchemy/W_Rn-EwKvZo especially 
the locking and integrity issues with long-running transactions.   He's 
written about it elsewhere as well.

Personally, I prefer to use the following approach when dealing with 
repeated actions and long-running processes:

1. Long running, complicated, processes have their own table, which 
includes at-least:
    job_id, current_state, timestamp_start, timestamp_last, timestamp_finish
As the job progresses, this table is updated.  Having it in the database 
allows us to find stuck jobs, etc.

2. Long processes span multiple transactions. Some transactions are nested 
with savepoints.

3. Re-usable functions accept the session as an argument and, as a rule, 
never commit.  When they must commit (it happens) you require a kwarg set 
and raise an error if it's missing.  This way the logic is absolutely clear 
in the calling function (otherwise, maintenance and code reviews are a 

We often use secondary sessions with auto-commit to track 3rd party api 
logging/etc too. 

For example, a payment processing task on an ecormmerce project I worked on 
once did the following:

    session autocommit- log that we're about to charge $x, returning id
    api integration- charge $x
    session autocommit- log that we successfully charged $x to id (or 
    session transaction - note the charge, continue with the task

automated reports then check for charges that were not completed and not 
marked as an acceptable fail.  those items are errors that need to be 
reconciled with the payment processor's logs.

Other people here have enforce much better standards and practices than I 
do.  We have some Celery jobs that use 4-5 transactions when dealing with 
external APIs.

SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to