How to initialize SQL Alchemy engine, session and table globally

Question

I'm developing a python application where most of its functions will interact (create, read, update and delete) with a specific table in a MySQL database. I know that I can query this specific table with the following code:

engine = create_engine(
            f"mysql+pymysql://{username}:{password}@{host}:{port}",
            pool_pre_ping=True
        )
meta = MetaData(engine)
my_table = Table(
    'my_table',
     meta,
     autoload=True,
     schema=db_name
)
dbsession = sessionmaker(bind=engine)
session = dbsession()

# example query to table
results = session.query(my_table).filter(my_table.columns.id >=1)
results.all()

However, I do not understand how to make these definitions (engine, meta, table, session) global to all of my functions. Should I define these things in my init.py and then pass them along as function arguments? Should I define a big class and initialize them during the class init?

My goal is to be able to query that table in any of my functions at any time without having to worry if the connection has gone away. According to the SQL Alchemy docs:

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.

Ok, but what about the engine, table and meta? Do I need to worry about those?

If you're working with single Table instance then that instance (my_table) and the engine ought to be enough to have available globally. You are only using meta to set up the table, and you aren't using the ORM so there's not much point in having a session. — snakecharmerb
Ok, but does that instance of table needs connection lifecycle? Or once defined it simply manages its own pool of connections to the database? — eduardokapp

snakecharmerb snakecharmerb · Accepted Answer · 2021-05-20T16:09:22

If you are working with a single table then the reflected table instance (my_table) and the engine should be all you need to expose globally.

the metadata object (meta) not required for querying, but is available as my_table.metadata if required
sessions are not required because you do not appear to be using the SQLAlchemy ORM.

The engine maintains a pool of connections, which you can check out to run queries (don't forget to close them though). This example code uses context managers to ensure that transactions are committed and connections are closed:

# Check out a connection
with engine.connect() as conn:
    # Start a transaction
    with conn.begin():
        q = select(my_table).where(my_table.c.id >= 1)
        result = conn.execute(q)

How to initialize SQL Alchemy engine, session and table globally

1 Answers