CODEWITHSUNDEEP
pythonmysqlflaskflask-sqlalchemypythonanywhere
Nathan Wailes
Nathan Wailes

Reputation: 12232

Flask-SQLAlchemy session object not seeing changes to the database?

I have a website on PythonAnywhere that uses Flask and Flask-SQLAlchemy connected to a MySQL database. Users can use the website to queue a task that is saved to the database as a record in a table, and then a separate scheduled task (Python program) checks the database and handles every unhandled record.

The problem I'm encountering is that the scheduled task's db query seems to only find new records when it runs for the first time, but if I then use the website to add a new task, the still-running scheduled task's recurring db queries (every 5 seconds) don't seem to detect the new record.

Any ideas about what could be happening here?

Here's the code being run by the bash file:

def generate_any_pending_videos():
    unfinished_videos = db.session.query(Video)\
                                  .filter(~Video.status.has(VideoStatus.status.in_(['Error', 'Finished', 'Video deleted'])))\
                                  .order_by(Video.datetime_created)\
                                  .all()
    for video in unfinished_videos:
        try:
            logging.info("Attempting to create video for video %d" % video.id)
            generate_video(video)
        except Exception as e:
            logging.error(str(e))


if __name__ == '__main__':
    while True:
        generate_any_pending_videos()
        time.sleep(5)

Upvotes: 3

Views: 3792

Answers (4)

Gaurav Sarma
Gaurav Sarma

Reputation: 2297

The way I made this work was by setting the transaction isolation level in the MySQL config.

With a GLOBAL scope, the transaction isolation level is reset after restarting MySQL. You can do this by logging into the MySql DB on the terminal

sudo mysql -u root -p

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Or:

sudo mysql -u root -p

SET GLOBAL transaction_isolation = 'READ-COMMITTED';

You can set 4 transaction isolation levels with 4 scopes as shown below. You can check more details in the docs here https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

Upvotes: 0

user1941407
user1941407

Reputation: 2842

You can change isolation level in sqlalchemy engine:

engine = create_engine(db_path, execution_options={"isolation_level": "READ COMMITTED"})

That is problem of mysql default config.

Upvotes: 1

Miguel Garcia
Miguel Garcia

Reputation: 356

All SqlAlchemy queries run inside a transaction by default http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html . That's the reason you are only getting new data in the first call of your script. Because inside a transaction seen data won't change, that is the I in ACID, transactions are isolated. After the commit or rollback the next query starts a new transactions, so you will get fresh data from the database.

Upvotes: 6

Nathan Wailes
Nathan Wailes

Reputation: 12232

Found a fix: for some reason running db.session.commit() before my query made the new records show up.

if __name__ == '__main__':
    while True:
        generate_any_pending_videos()
        time.sleep(5)
        db.session.commit()  # For some reason this is needed to be able to detect newly-created videos

Upvotes: 8

Related Questions