SQLAlchemy and Falcon - session initialization

Question

I'm wondering where the best place would be to create a scoped session for use in falcon.

From reading the flask-sqlalchemy code, it, in a round about way, does something like this:

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

try:
    from greenlet import get_current as get_ident
except ImportError:
    try:
        from thread import get_ident
    except ImportError:
        from _thread import get_ident

connection_uri = 'postgresql://postgres:@localhost:5432/db'
engine = create_engine(connection_uri)
session_factory = sessionmaker(bind=engine)
session_cls = scoped_session(session_factory, scopefunc=get_ident)
session = session_cls()

Would this work for falcon? Will the get_ident func "do the right thing" when using gunicorn?

eshlox · Accepted Answer · 2017-10-26T21:38:20.590

21

You can use middleware

Example.

Create engine, session_factory and scoped_session object.

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker

import settings


engine = create_engine(
    '{engine}://{username}:{password}@{host}:{port}/{db_name}'.format(
    **settings.POSTGRESQL
    )
)

session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)

Create middleware.

class SQLAlchemySessionManager:
    """
    Create a scoped session for every request and close it when the request
    ends.
    """

    def __init__(self, Session):
        self.Session = Session

    def process_resource(self, req, resp, resource, params):
        resource.session = self.Session()

    def process_response(self, req, resp, resource, req_succeeded):
        if hasattr(resource, 'session'):
            Session.remove()

Register middleware.

import falcon


app = falcon.API(middleware=[
    SQLAlchemySessionManager(Session),
])

Session is accessible in every request.

import falcon


class MyAPI:

    def on_get(self, req, resp):
        # You can access self.session here
        # self.session.add(foo)
        # self.session.commit()

edited Oct 26 '17 at 21:38

answered Nov 02 '16 at 19:22

eshlox

766
2
11
22

This was really helpful. One quick question, should that be `resource.session.close()` or `resource.session.remove()`? – misakm Feb 14 '17 at 07:09
3

Neither, it should be `Session.remove()` according to http://docs.sqlalchemy.org/en/rel_1_1/orm/contextual.html#using-thread-local-scope-with-web-applications. – Ronald Jul 17 '17 at 20:00
6

Then should it not be `self.Session.remove()`? – bmelton May 03 '18 at 20:25
How do you compare to https://eshlox.net/2019/05/28/integrate-sqlalchemy-with-falcon-framework-second-version/ ? Is there any rollback support integrated into remove method ? Edit: the close method seem to be using rollback under the hood https://docs.sqlalchemy.org/en/13/orm/contextual.html – Ambroise Rabier Nov 07 '19 at 15:18

score 1 · Answer 2 · edited Jul 15 '20 at 07:44

1

There is a package on pypi, falcon-sqla, that provides a middleware to manage SQLAlchemy sessions with Falcon.

It uses the request context object to add a different session to each http request, avoiding the need to use a scoped session.

edited Jul 15 '20 at 07:44

Peter Csala

17,736
16
35
75

answered Jul 15 '20 at 07:29

Federico Caselli

21
4

SQLAlchemy and Falcon - session initialization

2 Answers2

Linked