2

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://[email protected]/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...

4
  • To narrow down the possibilities, you don't happen to have multiple db instances of the SQLAlchemy class from Flask-SQLA? I.e. the db used in db.session.commit() is the same from which models inherit db.Model? Commented Jul 9, 2020 at 14:04
  • 1
    Are you sure the object from get_rest_result is part of the current session? Because I'm far from sure of that. Commented Jul 9, 2020 at 16:34
  • @IljaEverilä I think you are right - could you please have a look at the section in my question that I added which starts with "EDIT"? Commented Jul 10, 2020 at 8:22
  • @KenKinder I think you are right - could you please have a look at the section in my question that I added which starts with "EDIT"? Commented Jul 10, 2020 at 8:23

1 Answer 1

2

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.

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

2 Comments

Running .update() more or less skips the ORM. It's the equivalent of running raw SQL. There's still something wrong with your sessions, I believe. If I were you, I would try putting the entire update in one app context, though I'm not sure that's the issue. Alternatively, if you have a short example of an app one could test, that might work.
@KenKinder you're right. When I put query'ing the result, then changing it and comitting within ONE appcontext and not separately - it's working

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.