What is the best way to manage sqlalchemy sessions in a flask app in the given project structure?

Question

Hi Sorry this is a very open ended question and more like a coding advice. Looking for what are the best practises for using sqlalchemy session for flask projects. Couldn't find a blog that could answer my question, hence asking it here.

I have project structure for a flask app similar to following:

---services
     --- service_1.py
     --- service_2.py
---models.py
---adapter.py
---app.py

All the business logic resides in services. Methods in services call methods in adapter for any interaction with DB.

Adapter is data access layer all the sql queries are happening inside it. Adaper code is like this:

from .models import *
class DBAdapter(object):
    def __init__(self, session):
        self._db_session = get_session() # get a sql alchemy session

    def create_customer(**kwargs):
        c = Customer(**kwargs)
        self._db_session.add(c)
        return c
    .
    .
    .
    def commit(self):
        self.session.commit()

    def close_session(self):
        self.session.close()

The service looks something like this:

from ..adapter import DBAdapter

class Service1(object):
    def __init__(self, a):
       self.a = a
       self._db_adapter = DBAdapter()

    def do_something(self, x, y):
       if x != 10:
           self.create_something(x)
           self._db_adapter.commit()
           self._db_adapter.close_session()
           return

       self._db_adapter.create_customer(y)
       self._db_adapter.create_something_else(x)
       self._db_adapter.commit()
       self._db_adapter.close_session()
       return

Now the problem is how to close the session without repetition of self._db_adapter.close_session() before each return statement. It needs to be closed at two places once before return inside if condition then at the end.

Should I create my data access layer DBAdapter in a different way?

I want to keep my session clean and create it only when a service is initialized, so I can't put it in g object in flask like this:

Flask and sqlalchemy: handling sessions

Answer 1

Python has context managers for things like that. Consider something like this:

import contextlib

@contextlib.contextmanager
def db_session():
  db_adapter = None
  try:
    db_adapter = DBAdapter();
    yield db_adapter
  finally:
    if db_adapter:
      db_adapter.close_session()

Now you can have sessions automatically closed:

with db_session() as session:
  customer = session.create_customer(...)
  if something is wrong:
    session.rollback()
    return  # Exits the `with` scope, so closes session.
  customer.set_name('Joe')
  session.commit()
# Here session is closed.

Or you can consider an even more convenient pattern that does commit / rollback for you.