According to SQLAlchemy documentation
<https://docs.sqlalchemy.org/en/13/orm/session_basics.html#session-faq-whentocreate>,
engine and sessionmaker instances should be created in the application's
global scope:
> *When do I make a sessionmaker?*Just one time, somewhere in your
> application’s global scope. It should be looked upon as part of your
> application’s configuration. If your application has three .py files in a
> package, you could, for example, place the sessionmaker line in your
> __init__.py file; from that point on your other modules say “from mypackage
> import Session”. That way, everyone else just uses Session(), and the
> configuration of that session is controlled by that central point.
Questions:
1. What is the best practice for cleaning up SQLAlchemy engine and
sessionmaker instances? Please refer to my example below, while I could
call *engine.dispose()* in *main.py*, it does not seem like a good practice
to clean up a global object from a different module (*database.py*) in
*main.py*, is there a better way to do it?
2. Do we need to clean up sessionmaker instances? It seems there is no
method for closing the sessionmaker instance (*sessionmaker.close_all()* is
deprecated, and *session.close_all_sessions()* is a session instance method
and not sessionmaker method.)
Example:
- I created the engine and sessionmaker object in a module called
*database.py*:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from contextlib import contextmanager
DB_ENGINE = create_engine(DB_CONNECTION_STRING, pool_size=5, max_overflow=10
)
DB_SESSION = sessionmaker(bind=DB_ENGINE, autocommit=False, autoflush=True,
expire_on_commit=False)
@contextmanager
def db_session(db_session_factory):
"""Provide a transactional scope around a series of operations."""
session = db_session_factory()
try:
yield session
session.commit()
except:
session.rollback()
raise
finally:
session.close()
- In my main application *main.py*, I import the module and use the engine
and sessionmaker instances as follows. I cleaned up the engine instance at
the end of *__main__*.
from multiprocessing.pool import ThreadPool
from database import DB_ENGINE, DB_SESSION, db_session
def worker_func(data):
with db_session(DB_SESSION) as session:
[...database operations using session object...]
if __name__ == '__main__':
try:
data = [1,2,3,4,5]
with ThreadPool(processes=5) as thread_pool:
results = thread_pool.map(worker_func, data)
except:
raise
finally:
# Cleanup
DB_ENGINE.dispose()
Thank you very much!
(Also posted on stackoverflow:
https://stackoverflow.com/questions/59337676/best-practices-for-cleaning-up-sqlalchemy-engine-and-sessionmaker-instances
)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1e0a7980-5aa7-417c-85f6-92ec29273d17%40googlegroups.com.
