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.

Solution 1

You can use .any():

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

Solution 2

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

Solution 3

Can also use

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

Comments

[Alex B]

where does this line of code go in the actual flask app? in the models or view code ?

[univerio]

@AlexB It goes where you want to query for stuff, so generally in a view function.

[Alex B]

more specifically where in a view below? class Table_AView(ModelView): datamodel = SQLAInterface(AuctionTransactions) label_columns = {'Field_A':'A'} list_columns = ['Field_A']

[univerio]

@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.

[Alex B]

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!