SQLAlchemy with Flask db.session.commit() not working

Viewed 4054

I've got a similar problem as here so inserting new records to db is working but updating not.

Difference is: I'm not getting any error message and I DO db.create_all() each time I start my app.

  1. I initiate the app_context and db:

     app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://karolina@127.0.0.1/tests_results'
     app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
     db.init_app(app)
     app_context = app.app_context()
     DataBaseController.init(app_context)
  2. Then I run threads in the main app with an app_context as parameter, then I have a method that creates record and each thread that is a function that in the end - always updates one record in the database.

update_test_result method from DataBaseController:

@staticmethod
    def update_test_result(app_context):
        with app_context:
            db.session.commit()

EDIT: The hint with multiple db instances/ current session could be it - though I am really confused how this should be done. Seems to me that, maybe along with the appcontext, I should pass also the db from the app.py - in an act of extreme desperation I decided to pass the db and appcontext from the main app basically to every function - no result...As to the way I'm doing it now:

a) I have a script database.py which declares an "empty" db (without a model):

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

b) Then I have models.py that imports the db from the above and declares the model like this:

from database_management.database import db

class Result(db.Model):
    id = db.Column(db.String(150), primary_key=True)
    result = db.Column(db.Boolean)
    status = db.Column(db.String(20))

c) Finally I connect the db with the model I created in the main app and I pass the app_context to each thread that updates records in db.

Main app.py:

db.init_app(app)
app_context = app.app_context()
DataBaseController.init(app_context, db)

This is how I initiate the db in the DataBaseController:

from database_management.database import db
@staticmethod
    def init(app_context, db):
        with app_context:
            db.create_all()
            DataBaseController.create_bots()
            db.session.commit()

Loop from the main app:

test_cases = TestCasesLoader.split_test_cases(dialog_cases, db, app_context)  # here I create records
    threads_list = list()
    for test_case in test_cases[:10]:
        threads_list.append(Thread(target=test_case.run_test_case, args=(db, app_context, bot_config)))  # and here I update them

Creating the records:

@staticmethod
    def split_test_cases(dialog_cases, db, appcontext):
        db_name = dialog_cases.get("bot_name")
        for test_case in dialog_cases.get("test_case_list"):
            test_result = DataBaseController.get_test_result(db, appcontext, test_id)
            if test_result:
                DataBaseController.clean_result_data_in_db(db, appcontext, test_result)
            else:
                test_result = {
                    'id': test_id,
                    'status': 'IN_PROGRESS'
                }
                test_result = Result(test_result)
                DataBaseController.add_result_to_db(db, appcontext, test_result)

Creating in the DataBaseController:

@staticmethod 
    def add_result_to_db(db, appcontext, result):
        with appcontext:
            db.session.add(result)
            db.session.commit()

Updating in the thread:

def update_passed_result_in_db(self, appcontext):
    print("passed")
    result = DataBaseController.get_test_result(appcontext, self.test_id)
    result.result = self.test_passed
    result.status = 'DONE'
    with appcontext:
        db.session.commit()

And that's the updating in the DataBaseController:

from database_management.database import db

def update_passed_result_in_db(self, db, appcontext, human_said):
        result = DataBaseController.get_test_result(db, appcontext, self.test_id)
        result.result = self.test_passed
        result.status = 'DONE'
        with appcontext:
            db.session.commit()

Please, let me know if you have any ideas of what I could be doing wrong. What's super interesting, when I experimented like this:

def update_passed_result_in_db(self, db, appcontext):   
result = DataBaseController.get_test_result(db, appcontext, self.test_id)
        cprint.err(type(result))
        result.id = self.test_id
        result.result = self.test_passed
        result.status = 'working'
        with appcontext:
            db.session.add(result)
            db.session.commit()

The record is added properly...

1 Answers

The problem turned out to be indeed connected with the app context.

This one did not work:

result = db.session.query(Result).filter_by(id=test_id).first()
result.id = self.test_id
result.result = self.test_passed
result.status = 'working'
with appcontext:
    db.session.commit()

This one did the job:

result_data = {
    'status': 'working'
}
with appcontext:
    result = Result.query.filter_by(id=self.test_id).update(result_data)
    db.session.commit()

That's because querying the data and updating it should be done within one app context, not separately.

Related