How to properly associate SQLAlchemy session with pyramid sessions in Pyramid?

Question

In pyramid, I'm connecting to database with credentials supplied by user: every HTTP session have its own database connection, with different credentials. It works fine, if I'm opening and closing database connection in every request, but is inefficient. I've tried to use SQLAlchemy scoped_session with scopefunc returning beaker.session.id, but randomly, pyramid session receives wrong sqlalchemy session (created by different user). Is there any working solution for such case ?

Answer 1

If you can extract user credentials from your HTTP session, you can use an event listener to update connection parameters dynamically and connect to the database:

from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker


engine = create_engine(
    "postgresql+psycopg2://test_user:test_password@localhost:5433/test?connect_timeout=10",
    pool_size=30,
)


@event.listens_for(engine, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs: list, cparams: dict):
    # extract user credentials
    username, password = get_credentials_from_incoming_connection() # TODO

    # and update them here:
    cparams['user'] = username
    cparams['password'] = password

Here is the list of keys cparams contain (you can also dynamically update any of these):

host
dbname
user
password
port

# other params
connect_timeout

When a connection is closed, it is returned to the pool for re-use.