Best way to integrate SqlAlchemy into a Django project

Question

I changed my Django application to use SQLAlchemy, and it works now.

But I'm wondering where I should put these lines:

engine = sqlalchemy.create_engine(settings.DATABASE_URL)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()

The reason I'm asking is because I want to use SQLAlchemy at many place, and I don't think its correct/powerful/well-written to call this three lines everytime I need to use the database.

The place I will require SA is :

• In my views, of course
• In some middleware I wrote
• In my models. Like in get_all_tags for a BlogPost Model.

What I think would be correct, is to get the session, by re-connecting to the database if the session is closed, or just returning the current, connected session if exists.

How can I use SQLAlchemy correctly with my Django apps?

Thanks for your help!

Note: I already followed this tutorial to implement SA into my Django application, but this one doesn't tell me exactly where to put those 3 lines (http://lethain.com/entry/2008/jul/23/replacing-django-s-orm-with-sqlalchemy/).

Answer 1

for the first two, engine and Session, you can put them in settings.py; they are, configuration, after all.

Actually creating a session requires slightly more care, since a session is essentially a 'transaction'. The simplest thing to do is to create it in each view function when needed, and commit them just before returning. If you'd like a little bit more magic than that, or if you want/need to use the session outside of the view function, you should instead define some middleware, something like

class MySQLAlchemySessionMiddleware(object):
    def process_request(self, request):
        request.db_session = settings.Session()

    def process_response(self, request, response):
        try:
            session = request.db_session
        except AttributeError:
            return response
        try:
            session.commit()
            return response
        except:
            session.rollback()
            raise

    def process_exception(self, request, exception):
        try:
            session = request.db_session
        except AttributeError:
            return
        session.rollback()

Then, every view will have a db_session attribute in their requests, which they can use as they see fit, and anything that was added will get commited when the response is finished.

Don't forget to add the middleware to MIDDLEWARE_CLASSES