Flask-sqlalchemy: Db Queries Don't Return New Data

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

Solution 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'])defdisplay_test():
    order_test = db.session.query(Order).all()
    return jsonify(json_list=[i.serialize for i in order_test]), '200'

Solution 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'])defdisplay_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'])definsert_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'