How to ensure unique SQLAlchemy database connections with uWSGI and Pyramid

Question

I have this Pyramid application:

from pyramid.config import Configurator
from pyramid.response import Response

from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
from sqlalchemy.sql import text

POOL_SIZE = 10
try:
    import uwsgi
    POOL_SIZE = int(uwsgi.opt['threads'])

    def postfork():
        engine.dispose()
    uwsgi.post_fork_hook = postfork
except ImportError:
    pass

DBURL = 'postgres://postgres:[email protected]:5455/postgres'
engine = create_engine(DBURL, poolclass=QueuePool, pool_size=POOL_SIZE)

def db_conn(request):
    conn = engine.contextual_connect()
    def cleanup(request):
        conn.close()
    request.add_finished_callback(cleanup)
    return conn

def some_view(request):
    conn = request.db_conn
    with conn.begin() as trans:
        s = text('SELECT 1')
        cur = conn.execute(s)
        result = cur.first()
    return Response('<h1>{}</h1>'.format(result))

def main():
    config = Configurator()
    config.add_request_method(db_conn, reify=True)
    config.add_route('some_view', '/')
    config.add_view(some_view, route_name='some_view')
    app = config.make_wsgi_app()
    return app

application = main()

Which I'm running with uWSGI:

uwsgi --wsgi-file webapp.py  --http :9090 --master --processes 2 --threads 2

My main question is if that code is correct. Can I be sure that different processes/threads will use different connections?

I have 2 processes, with 2 threads each, and my assumptions are:

• Calling engine.dispose() in the uWSGI post-fork hook ensures that every process has it own connections

• Calling config.add_request_method(db_conn, reify=True) will add a SQLAlchemy connection object to the request. Under the hood, uses thread-local to ensure different connections between threads

• I'm getting the connection calling contextual_connect() instead of just connect(), but I think it doesn't matter which method I use.

But I'm not sure if they are correct, specially the second.

One last remark , I know about SQLAlchemy's scoped_session and sessionmaker, but I want to use the connection object directly to better understand how it works.

Answer 1

I don't see anything wrong with your example code. I also agree, I think you should just use connect() instead of contextual_connect().