SQLAlchemy freezing application

Question

I am using SQLAlchemy in my python command line app. The app is basically reading a set of URLs and doing inserts into a postgreql database based on the data.

After about the same number of inserts (give or take a few), the entire app freezes.

Having seen python sqlalchemy + postgresql program freezes I am assuming I am doing something wrong with the SQLAlchemy Session (although I am not using drop_all(), which seemed to be the cause of that issue). I've tried a couple of things but thus far they have had no impact.

Any hints or help would be welcome. If my integration of SQLAlchemy into my app is incorrect, a pointer to a good example of doing it right would also be welcome.

My code is as follows:

Set up the sql alchemy base:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Create the session info and attach it to the Base

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

engine = create_engine("sqlite:///myapp.db")
db_session = scoped_session(sessionmaker(bind=engine))

Base.query = db_session.query_property()
Base.scoped_db_session = db_session

Create my model from Base and make use of the session

class Person(Base):

    def store(self):
        if self.is_new():
            self.scoped_db_session.add(self)
        self.scoped_db_session.commit()

If I create enough objects of type Person and call store(), the app eventually freezes.

Answer 1

Managed to solve the problem. Turns out that my implementation is specifically on the don't do it this way list (see http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions E.g. don't do this) and I was not managing the session correctly

To solve my problem I moved the session out of the model into a separate class, so instead of having calls like:

mymodel.store()

I now have:

db.current_session.store(mymodel)

where db is an instance of my custom DBContext below:

from contextlib import contextmanager
from sqlalchemy.orm import scoped_session, sessionmaker

class DbContext(object):
    def __init__(self, engine, session=None):
        self._engine = engine
        self._session = session or scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=self._engine))
        self.query = self._session.query_property()
        self.current_session = None

    def start_session(self):
        self.current_session = self._session()

    def end_session(self):
        if self.current_session:
            self.current_session.commit()
            self.current_session.close()
        self.current_session = None

    @contextmanager
    def new_session(self):
        try:
            self.start_session()
            yield
        finally:
            self.end_session()

When you want to store one or more model objects, call DBContext.start_session() to start a clean session. When you finally want to commit, call DBContext.end_session().