Flask-SQLAlchemy using global DB instance

Question

Here is what I did:

    from flask import Flask
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker

    dbhost = 'localhost'
    dbuser = 'user'
    dbpass = 'password'
    dbname = 'db'
    DBUri = 'mysql://%s:%s@%s/%s?charset=utf8'%(dbuser,dbpass,dbhost,dbname)

    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = (DBUri)
    app.secret_key = 'A0Zr98j/3yX R~XHH!jmN]LWX/,?RT'


    # an Engine, which the Session will use for connection
    # resources
    engine = create_engine(DBUri)

    # create a configured "Session" class
    Session = sessionmaker(bind=engine)

    # create a Session
    DBsession = Session()

and

@sched.cron_schedule(second='*/5')
def some_decorated_task():
    date_now = datetime.datetime.now().date()
    for item in DBsession.query(user_groups_n_hosts).filter(user_groups_n_hosts.end_time < str(date_now)): 
        print item.id, item.server_users.native_linux_user

there is a table user_groups_n_hosts The above code is a function that runs every 30 seconds.

My problem is that Once I start my Flask application at that time if the user_groups_n_hosts has 10 records then the above code will keep printing 10 records no matter whatever number of records get inserted in the table user_groups_n_hosts from outside. In other words what I think is that My function has some instance of the table which is only initialized in the beginning of the initialization of the app. I know I am doing something stupid. please someone point it out.

in place of DBsession.query(user_groups_n_hosts).filter(user_groups_n_hosts.end_time < str(date_now)): I tried user_groups_n_hosts.query.filter(user_groups_n_hosts.end_time < str(date_now)): But the result is same.

Tasos Vogiatzoglou · Accepted Answer · 2015-03-10 11:55:57Z

1

Your filter user_groups_n_hosts.end_time < str(date_now) compares a date with a string.

Also, you should consider the session as a per-request thing, not a persistent entity across your app.

With this in mind, try changing

@sched.cron_schedule(second='*/5')
def some_decorated_task():
    date_now = datetime.datetime.now().date()
    for item in DBsession.query(user_groups_n_hosts).filter(user_groups_n_hosts.end_time < str(date_now)): 
        print item.id, item.server_users.native_linux_user

to

@sched.cron_schedule(second='*/5')
def some_decorated_task():
    date_now = datetime.datetime.now().date()
    sess = Session()
    for item in sess.query(user_groups_n_hosts).filter(user_groups_n_hosts.end_time < str(date_now)): 
        print item.id, item.server_users.native_linux_user
    sess.close()

answered Mar 10, 2015 at 11:55

Tasos Vogiatzoglou

2,45315 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

balu Over a year ago

"Also, you should consider the session as a per-request thing, not a persistent entity across your app." -> I would recommend using SQLAlchemy's scoped_session here which takes care of thread safety automatically, i.e. it sets up a separate session for every thread/request, see docs.sqlalchemy.org/en/latest/orm/contextual.html .

Thomas B. · Accepted Answer · 2015-03-10 12:24:47Z

You need to establish a fresh transaction every time. And you can either create an entirely new session object each run, our reuse the session, but make sure that you commit(), rollback() or close() it after your query.

The effect is caused by some default policy of MySQL.

If you're using InnoDB as the default Storage Engine for MySQL, then it is using REPEATABLE READ as its isolation level. This is the default, because in most cases it makes sense to avoid Non-repeatable Reads inside transactions. So, if you don't end your transaction you'll get the same results from the database each time.

From the the SQLAlchemy docs:

The Session, whenever it is used to talk to the database, begins a database transaction as soon as it starts communicating. Assuming the autocommit flag is left at its recommended default of False, this transaction remains in progress until the Session is rolled back, committed, or closed. The Session will begin a new transaction if it is used again, subsequent to the previous transaction ending; from this it follows that the Session is capable of having a lifespan across many transactions, though only one at a time. We refer to these two concepts as transaction scope and session scope.

Another option, which is not recommended, is creating the Session with autocommit=True, which will automatically commit the transaction after each and every query statement.

Collectives™ on Stack Overflow

Flask-SQLAlchemy using global DB instance

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related