CODEWITHSUNDEEP
pythonmysqlflaskflask-sqlalchemy
bradleyc
bradleyc

Reputation: 43

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 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?

Upvotes: 4

Views: 4061

Answers (2)

Gabe I.
Gabe I.

Reputation: 46

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'

Upvotes: 3

Michael Moura
Michael Moura

Reputation: 229

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'

Upvotes: 0

Related Questions