SQLAlchemy session within Huey periodic task

Question

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!

score 1 · Answer 1 · answered Oct 26 '16 at 18:42

1

You can build session object with a factory in the tasks:

factory = sessionmaker()
factory.configure(bind=engine)
session = factory()

No need to use scoped session, just initialize the engine and pass it to factory.

answered Oct 26 '16 at 18:42

Ergo

1,205
9
16

score 0 · Accepted Answer · answered Oct 26 '16 at 21:44

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(...)

SQLAlchemy session within Huey periodic task

2 Answers2