Python - SQLAlchemy multiple sessions

Question

I am working on a system which uses SQLAlchemy to read/write to a MySQL database. I have a Factory which makes multiple repositories each one with his own session. I read the documentation os SQLAlchemy and it states that one session should not be used by different processes.

I cannot use same session as it the code will run on different machines.

My question is, is it a good practice to make different sessions? Will it have concurrency problems or races?

Example: If i have 2 sessions writing multiple records to the db, and have a collision of a record. Will the session.commit() abort everything?

bad_coder · Accepted Answer · 2020-09-04 16:22:08Z

7

SQLAlchemy sessions are light-weight objects so no trouble to create many sessions, and you have to use multiple sessions when you use multiple processes or multiple threads because a session cannot be shared between processes nor threads.

If there is a collision between 2 sessions, then the collision will be raised on session.commit or session autocommit depending on session configuration.

It's better to use sessions with transactions to ensure atomicity property:

session = get_session()
with session.begin():
    session.add(db_obj0)
    session.add(db_obj1)

both db_obj0 and db_obj1 will be created or none.

edited Sep 4, 2020 at 16:22

bad_coder

13.2k20 gold badges59 silver badges95 bronze badges

answered Apr 10, 2018 at 18:51

ZZ ll

1963 bronze badges

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

2 Comments

AMNevess Over a year ago

Thank you! I'm not using transactions as i need to make sure in the best way possible that everything is written on the db, if it fails i want the least number of objects to not be created. Example, i'm sniffing packets and storing them.

Paymahn Moghadasian Over a year ago

FYI that modern implementations don't need session.begin(): docs.sqlalchemy.org/en/13/orm/…

Collectives™ on Stack Overflow

Python - SQLAlchemy multiple sessions

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