Atomically insert / update rows in multiple tables in SQLAlchemy

Question

My use case requires that tables A, B, and C need to have rows inserted / updated atomically. In other words, my application will break if A was updated, B was updated, and there was a catastrophic failure before C was updated.

So, either all three tables should be updated at once or not at all.

In SQLAlchemy I'm currently using the following function to execute my insertions.

self.db_conn.execute(table.insert(), dicts)

But from my understanding, this "execute" commits the transaction as well. So, if I were to execute row insertions on A, B and C in three different function calls, atomicity isn't guaranteed.

Thanks for any help!

snakecharmerb · Accepted Answer · 2020-12-06 19:33:16Z

2

Assuming self.dbconn is a connection produced by engine.connect(), you can use it as a context manager, which commits when it exits:

names = ['Alice', 'Bob', 'Carol']

with engine.connect() as conn:
    with conn.begin():
        for name in names:
            conn.execute(users.insert(), name=name)

The code snippet produces this output, showing each insert takes place within a single transaction:

2020-12-06 17:16:36,683 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-12-06 17:16:36,683 INFO sqlalchemy.engine.Engine INSERT INTO users (name) VALUES (?)
2020-12-06 17:16:36,684 INFO sqlalchemy.engine.Engine [generated in 0.00019s] ('Alice',)
2020-12-06 17:16:36,684 INFO sqlalchemy.engine.Engine INSERT INTO users (name) VALUES (?)
2020-12-06 17:16:36,684 INFO sqlalchemy.engine.Engine [cached since 0.0006672s ago] ('Bob',)
2020-12-06 17:16:36,684 INFO sqlalchemy.engine.Engine INSERT INTO users (name) VALUES (?)
2020-12-06 17:16:36,684 INFO sqlalchemy.engine.Engine [cached since 0.0009919s ago] ('Carol',)
2020-12-06 17:16:36,684 INFO sqlalchemy.engine.Engine COMMIT

See the docs here.

If we change the last element in the list to something that can't be saved - for example the built-in int function - the entire transaction is rolled back

2020-12-06 17:21:50,760 INFO sqlalchemy.engine.Engine BEGIN (implicit)                                                         
2020-12-06 17:21:50,761 INFO sqlalchemy.engine.Engine INSERT INTO users (name) VALUES (?)                                      
2020-12-06 17:21:50,761 INFO sqlalchemy.engine.Engine [generated in 0.00019s] ('Alice',)                                       
2020-12-06 17:21:50,761 INFO sqlalchemy.engine.Engine INSERT INTO users (name) VALUES (?)                                      
2020-12-06 17:21:50,761 INFO sqlalchemy.engine.Engine [cached since 0.0006409s ago] ('Bob',)                                   
2020-12-06 17:21:50,761 INFO sqlalchemy.engine.Engine INSERT INTO users (name) VALUES (?)                                      
2020-12-06 17:21:50,761 INFO sqlalchemy.engine.Engine [cached since 0.0009716s ago] (<class 'int'>,)                           
2020-12-06 17:21:50,762 INFO sqlalchemy.engine.Engine ROLLBACK

edited Dec 6, 2020 at 19:33

answered Dec 6, 2020 at 17:23

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carpetfizz Over a year ago

Thank you! So my application has queries A, B, C, and D where (A, B, C) must be committed atomically but not necessarily D. I would like to use the same DB connection for all 4 queries instead of opening two different ones. Is that possible?

snakecharmerb Over a year ago

Yes. Just do another with self.db_conn.begin(): - the scope of the begin context manager is the transaction; it does not close the connection (given that self.db_conn is an instance attribute, you'll have to close it manually rather than using it as a context manager as I have in the answer).

Collectives™ on Stack Overflow

Atomically insert / update rows in multiple tables in SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related