Flask-Sqlalchemy: DB queries don't return new data

Question

I'm building an app that receives webhooks from one service, stores the data in a database, then makes the data available via API.

I'm able to successfully add data to my app, but when I query the database I only receive the first commit from the that was in the database the last time I started the app.

For example, if I had 26 orders in the Orders table when I booted the app, then trigger the webhook, Order.query.all() will return 27 orders until I reboot the app, regardless of how many orders are actually in the table (I can validate using MySQL).

Here's an example of the class used to insert data into the table:

@webhook.route('/order/insert', methods=['POST'])
def insert_orders():
 soda_json = request.json
 db.session.add(Order(
 order_id=soda_json['id'], 
 created_at=datetime.datetime.now(),
 currency=soda_json['currency'],
 total_line_items_price=soda_json['total_line_items_price'],
 refunds=sum(float(i) for i in soda_json['refunds'] if soda_json['refunds']),
 shipping_lines_price=sum([float(line['price']) for line in soda_json['shipping_lines']]),
 note=soda_json['note']
 ))
db.session.commit()
return '200'

And here's a basic API method I'm using for testing:

order_test = Order.query.all()
@api.route('/display', methods=['POST', 'GET'])
def display_test():
 return jsonify(json_list=[i.serialize for i in order_test]), '200'

What am I missing to always get the most recent data?

• Michael Moura: how is order_test being populated? can you provide the code that populates it from the DB
• bradleyc: Just added order_test
• doru: Try return jsonify(json_list = order_test). And if this doesn't work try return jsonify(json_list = order_test.all()).

Answer 1

It looks like the order of methods in the query could be an issue.

from my_app.models import Order
order_test = Order.query.all()

That is the structure in the tutorial ( https://pythonhosted.org/Flask-SQLAlchemy/queries.html#querying-records ), but it seems like that might only be looking at data in the original imported model. Feel free to correct me on that.

In similar operations in the flask shell, I've had success getting live data right after commits with this query structure:

db.session.query([model]).all()

So a working example for API method might be:

@api.route('/display', methods=['POST', 'GET'])
def display_test():
 order_test = db.session.query(Order).all()
 return jsonify(json_list=[i.serialize for i in order_test]), '200'

• bradleyc: That did it! Now my app is getting the most recent data. Thank you!

Answer 2

The issue from what I can see is that order_list is only being populated when that view is initiated. So if you move that line of code to be within your route call it will then be refreshed every time that route is called.

e.g.

@api.route('/display', methods=['POST', 'GET'])
def display_test():
 order_test = Order.query.all()
 return jsonify(json_list=[i.serialize for i in order_test]), '200'

well from what you have said so far it seems that you are only able to add one new record to the DB no matter how many time new data is sent to the web hook and that if you restart the the API then you are back to square one and the new record is no longer there.

To me that seems to be a issue with committing the transaction in the webhook in that after the db.session.add() is called the data is not save to the db and so the transaction is left open and so when new data is added it is potentially overriding the data from the previous call and then when you end the API the transaction is either committed or rollbacked (can't remember the default action of flask-alchemy). you may need to check the data itself and see what data is being returned in the 51st row after the webhook is called and see if it changes after new data is sent to the webhook.

If you also compare your above webhook code and the below the commit and return lines are different. In yours they on a different tab line and are outside the webhook function and would not get run when the webhook is called so there would be an open transaction.

@webhook.route('/order/insert', methods=['POST'])
def insert_orders():
 soda_json = request.json
 db.session.add(Order(
 order_id=soda_json['id'], 
 created_at=datetime.datetime.now(),
 currency=soda_json['currency'],
 total_line_items_price=soda_json['total_line_items_price'],
 refunds=sum(float(i) for i in soda_json['refunds'] if soda_json['refunds']),
 shipping_lines_price=sum([float(line['price']) for line in soda_json['shipping_lines']]),
 note=soda_json['note']
 ))
 db.session.commit()
 return '200'

• bradleyc: Unfortunately, that doesn't seem to make a difference - still getting the same number of orders from the query as when I boot the app.
• Michael Moura: hmm are you sure that you can see the new records in the DB after adding them? can you add multiple new records and see them there? and can you still see the records in the DB once you once you close the API down?
• bradleyc: On closer inspection, the behavior seems even stranger: 1. Boot App 2. Check Query, 50 Rows 3. Check MySQL, 50 Rows 4. Trigger Webhook, 200 OK from my app 5. Check MySQL, 51 Rows 6. Check Query, 51 Rows 7. Trigger Webhook, 200 OK from my app 8. Check MySQL, 52 Rows 9. Check Query, 51 Rows So it looks like it's actually displaying the first insert in my session only.
• Michael Moura: and what about when you close up the API is there 51 rows or 50? what about the data is it only displaying the most recent value being entered? as I'm wondering if the commit is happening properly. as when i copy the webhook code I see that the commit is outside of the webhook function
• bradleyc: "... I'm wondering if the commit is happening properly. as when i copy the webhook code I see that the commit is outside of the webhook function"