Flask-Sqlalchemy: DB queries do not return new data
I am creating an application that receives webhooks from one service, stores data in a database, and then makes the data available through the API.
I can successfully add data to my application, but when I query the database, I only get the first commit from what was in the database when I last ran the application.
For example, if I had 26 orders in the Orders table when I downloaded the app, then I ran the webhook, it Order.query.all()
will return 27 orders until I reload the app, no matter how many orders are actually in the table (I can check the usage MySQL).
Here is an example of a class used to insert data into a 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 is the 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 latest data?
source to share
It looks like the order of the queries in the query might be an issue.
from my_app.models import Order
order_test = Order.query.all()
This is the structure in the tutorial ( https://pythonhosted.org/Flask-SQLAlchemy/queries.html#querying-records ), but it looks like it might only be looking at the data in the original imported model. Feel free to correct me.
In similar operations in the flash shell, I was able to get real-time data right after committing with this query structure:
db.session.query([model]).all()
So a working example for an API method could 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'
source to share
The problem from what I see is that the order_list is only populated when this view is triggered. So if you move that line of code within your route call, it will update every time the route is called.
eg.
@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 said so far it seems like you can only add one new entry to the database no matter how many times new data is sent to the webhook and that if you restart the API then you are back to squared, and the new entry no longer exists.
It seems to me that the problem with the transaction being committed in the webhook is that after the call to db.session.add (), the data is not saved in the db, and therefore the transaction remains open, and so when new data is added it potentially overrides the data from the previous one call, and then when you exit the API, the transaction is either committed or rolled back (can't remember the default fake alchemy action). you may need to check the data itself and see what data is returned on line 51 after the webcam is called and see if it changes after new data is sent to the website.
If you also compare your web host code above and below commit and return lines are different. In yours, they are in a different tab and are outside the webhook function and will not be triggered when the webhook is called, so there will 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'
source to share