SQLAlchemy session within Huey periodic task

I'm integrating Huey with a simple pyramid app. I'm not using a global SQLAlchemy session in the app (I'm making use of the latest alchemy scaffold). However, there seems to be no other straightforward way to provide a session to periodic tasks.

from huey import RedisHuey

huey = RedisHuey(password=os.environ.get('REDIS_PASSWORD', ''))
DBSession = scoped_session(sessionmaker())


@huey.periodic_task(crontab(minute='*/1'))
def notify_not_confirmed_assignments():
    # TODO: Use a non-global DB session
    assignments = DBSession.query(Assignment).filter_by(date=next_date).all()

Does Huey offer hooks to close the DB connection on task completion? What is the best way to provide a thread-safe connection to these tasks?

Thanks in advance!

Solution

scoped_session provides you with a contextual/thread-local session (i.e. it corresponds to an individual DB connection in each thread, and it's also possible to configure a custom scope when you need a separate session per something which is not a thread.

So, basically, all you need to do is to have a properly-configured pseudo-global variable (similar to what you have now) and make sure you call DBSession.begin() at the start of the task and DBSession.commit() at the end - doing that manually is probably a chore but it can easily be abstracted into a context manager

def my_task():
    with magically_start_session() as session:
        session.query(...)

or into a decorator:

@huey.periodic_task(crontab(minute='*/1'))
@start_session
def notify_not_confirmed_assignments(session):
    # TODO: Use a non-global DB session
    assignments = session.query(...)