CODEWITHSUNDEEP
pythonmultithreadingflasksqlalchemyflask-sqlalchemy
Nolan Conaway
Nolan Conaway

Reputation: 2757

Flask-SQLAlchemy fails to update data in while in thread

I am building an app where users will occasionally initiate a longer-running process. While running, the process will commit updates to a database entry.

Since the process takes some time, I am using the threading module to execute it. But values updated while in the thread are never actually committed.

An example:

from flask import Flask, url_for, redirect
from flask_sqlalchemy import SQLAlchemy
import time, threading, os

if os.path.exists('test.db'): os.remove('test.db')

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.Integer)
    def __init__(self, value): self.value = value

db.create_all()

item = Item(1)
db.session.add(item)
db.session.commit()

@app.route('/go', methods=['GET'])
def go():
    def fun(item):
        time.sleep(2)
        item.value += 1
        db.session.commit()
    thr = threading.Thread(target=fun, args=(item,))
    # thr.daemon = True
    thr.start()
    return redirect(url_for('view'))

@app.route('/view', methods=['GET'])
def view(): return str(Item.query.get(1).value)

app.run(host='0.0.0.0', port=8080, debug=True)

My expectation was that the item's value would be asynchronously updated after two seconds (when the fun completes), and that additional requests to /view would reveal the updated value. But this never occurs. I am not an expert on what is going on in the threading module; am I missing something?

I have tried setting thr.daemon=True as pointed out in some posts; but that is not it. The closest SO post I have found is this one; that question does not have a minimal and verifiable example and has not been answered.

Upvotes: 3

Views: 5304

Answers (2)

MottiG
MottiG

Reputation: 41

I guess this is due to the fact that sessions are local threaded, as mentioned in the documentation. In your case, item was created in one thread and then passed to a new thread to be modified directly.

You can either use scoped sessions as suggested in the documentation, or simply change your URI config to bypass this behavior:

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db?check_same_thread=False'

Upvotes: 4

Nolan Conaway
Nolan Conaway

Reputation: 2757

After some debugging I figured out a solution; though I still do not understand the problem. It has to do with referencing a variable for the database object. If fun updates an object returned by a query, it works as expected:

def fun(item_id):
    time.sleep(2)
    Item.query.get(item_id).value += 1
    db.session.commit()

In context:

from flask import Flask, url_for, redirect
from flask_sqlalchemy import SQLAlchemy
import time, threading, os

if os.path.exists('test.db'): os.remove('test.db')

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.Integer)
    def __init__(self, value): self.value = value

db.create_all()

item = Item(1)
db.session.add(item)
db.session.commit()

@app.route('/go', methods=['GET'])
def go():
    def fun(item_id):
        time.sleep(2)
        Item.query.get(item_id).value += 1
        db.session.commit()
    thr = threading.Thread(target=fun, args=(item.id,))
    # thr.daemon = True
    thr.start()
    return redirect(url_for('view'))

@app.route('/view', methods=['GET'])
def view(): return str(Item.query.get(1).value)

app.run(host='0.0.0.0', port=8080, debug=True)

I would be very pleased to hear from anyone knows what exactly is going on here!

Upvotes: 2

Related Questions