How can I get the latest data from APScheduler through sqlalchemy?

Question

I try to use Advanced Python Scheduler to get data from my MySql database through flask sqlalchemy once per every five seconds.

As far as I am concerned, I want to fetch the latest modified data from APScheduler through Flask SQLAlchemy, but after I changed the data in MySql database for several times, I still get the earliest data in database.

It seems like APScheduler just run the job only for one time, and at the following times it just "remember" and "copy" what it done. The following are related setting for APScheduler.

jobstores={"default":MemoryJobStore(),}
executors={"threadpool":ThreadPoolExecutor(max_workers=20)}
job_default={
    'coalesce': True,
    'max_instances': 1,
    'replace_existing':True
}
scheduler=BackgroundScheduler(jobstores=jobstores,executors=executors,job_default=job_default)

def testJob():
    test=Test.query.filter_by(id=2).first()
    with open('test.log','a+') as f:
        s=str(datetime.now())+'  '+str(test)+'\n'
        f.writelines([s])

tmpL=[CronTrigger(minute=m,second=s) for m in range(60) for s in range(0,60,5)]
tmpL.append(DateTrigger(run_date=datetime.now()))
trigger=OrTrigger(tmpL)
job=scheduler.add_job(testJob,trigger,id="testJob",replace_existing=True)
scheduler.start()

In my test, my table contains data "cat 4" at first ,then I change it to "dog 3", "orange 7" and so on. I even delete that line of data. But I can only get get "Test 2:cat->4" every five seconds.

enter image description here

How can I get the latest data from APScheduler through sqlalchemy?

Answer 1

Your scheduler is probably using a single thread to run jobs, since they are so infrequent. From Test.query it looks like you might be using Flask-SQLAlchemy, which defaults to using a scoped session, or in other words a thread-local session. Your job also does not end the transaction implicitly started by the query. These combined with using MySQL that defaults to REPEATABLE READ transaction isolation level result in that the jobs share the same transaction, and so read from the same snapshot of the database. Simply end your transaction timely:

def testJob():
    test=Test.query.filter_by(id=2).first()
    db.session.rollback()
    ...