Querying with Flask-SQLAlchemy

Question

I'm using Flask to build a RESTful api and use SQLAlchemy to connect my app to a MySQL database.

I have two models in databse : Order and Order_line. An order is made of several order lines. Each order lines has a status associated.

I'm having trouble translating my SQL request into a Flask-SQLAlchemy statement. I'm especially bugged by the join.

Here are my models:

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_created = db.Column(db.DateTime)
    lines = db.relationship('Order_line',
                            backref=db.backref('order',
                                               lazy='join'))
    def __init__(self, po):
        self.date_created = datetime.datetime.now()

class Order_line(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'))
    status_id = db.Column(db.Integer, db.ForeignKey('status.id'))

    def __init__(self, order_id):
        self.order_id = order_id
        self.status_id = 1

class Status(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    short_name = db.Column(db.String(60))
    description = db.Column(db.String(400))
    lines = db.relationship('Order_line',
                            backref=db.backref('status',
                                               lazy='join'))

    def __init__(self, short_name, description):
        self.short_name = short_name
        self.description = description

Basically, I want to retrieve all the orders (so retrieve the Order.id) which have one or more order_line's status_id different from 1.

The SQL query would be

SELECT id FROM `order`
INNER JOIN order_line
    ON order.id=order_line.order_id
WHERE
    order_line.status_id NOT LIKE 1
GROUP BY
    order.id

I didn't find a way to translate that SQL statement into a SQLAlchemy command. I'm especially confused by the difference between Flask-SQLAlchemy wrapper and 'vanilla' SQLAlchemy.

score 3 · Accepted Answer · edited May 07 '20 at 06:44

3

You can use .any():

Order.query.filter(Order.lines.any(Order_line.status_id != 1))

edited May 07 '20 at 06:44

K DawG

13,287
9
35
66

answered Aug 15 '14 at 22:38

univerio

19,548
3
66
68

where does this line of code go in the actual flask app? in the `models` or `view` code ? – Alex B Sep 05 '18 at 17:21
@AlexB It goes where you want to query for stuff, so generally in a view function. – univerio Sep 05 '18 at 17:56
more specifically where in a `view` below? `class Table_AView(ModelView): datamodel = SQLAInterface(AuctionTransactions) label_columns = {'Field_A':'A'} list_columns = ['Field_A']` – Alex B Sep 05 '18 at 18:10
@AlexB `ModelView` is something very specific to flask-admin, it looks like, and I know nothing about it. You'll have to read the docs yourself or ask a new question. – univerio Sep 05 '18 at 18:29
thx for your response. I'm shocked at how something very simple seems so difficult to implement. I have been reading the flask-appbuilder docs as well as sqlalchemy object relation tutorial and none seem to explain how to actually apply a query to a view which is implemented with a template. My quest continues! – Alex B Sep 05 '18 at 18:32

score 2 · Answer 2 · edited May 23 '17 at 12:16

I'm also new in Flask-SQLAlchemy but I've been learning a lot lately for my app. Then, some point that could help you:

The main difference between 'vanilla' and Flask-SQLAlchemy at the moment to do a query, it is the way Flas-SQLAlchemy handle the session variables. In the Flask version you have a db object that handle your session as in this case:

  db = SQLAlchemy()

With that object, you handle the query. In your case, your query could be performed in this way:

db.session.query(Order).filter(Order.id==Order_line.order_id).filter(Order_line.status_id!=1).group_by(Order.id).all()

This is not exactly the same query but it quite similar. It will return you all the fields from the Order table but if you want only the "id", you can change "Order" for "Query.id" in the query statement. The "like" filter that you have I'm not totally sure how to implement it in Flask-SQLAlchemy but I found this question that perform an answers for the "vanilla" SQLalchemy: how to pass a not like operator in a sqlalchemy ORM query

score 1 · Answer 3 · answered Dec 30 '16 at 17:38

1

Can also use

db.session.query(Order.id).filter(Order.lines.status_id != 1 ).group_by(Order.id).all()

answered Dec 30 '16 at 17:38

jackotonye

3,537
23
31

Querying with Flask-SQLAlchemy

3 Answers3