SQLAlchemy query tables joined with foreign key

Question

I am trying to display data from MySQL via Flask-SQLAlchemy query and change foreign key (category_id) into name assign to the category_id. To be more precise -

With the query I want to display item with the name from category Table, not the category_id.

Here is my code:

class MyEnum(enum.Enum):
    piece = "piece"
    kg = "kg"

class Category(db.Model):
    __tablename__ = 'category'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(25), nullable=False)

class Product(db.Model):
    __tablename__ = 'product'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)
    product_type = db.Column(db.Enum(MyEnum), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    description = db.Column(db.String(255))

    category = db.relationship("Categ{{user.id}}ory",
                            backref=('products'))


    def __init__(self,name, quantity, product_type, category_id, description):
        self.name = name
        self.quantity = quantity
        self.product_type = product_type
        self.category_id = category_id
        self.description = description


db.create_all()
db.session.commit()

@app.route('/grocery-list', methods=['GET'])
def display_data():
    data = Product.query.all()
    category_name = db.session.query(Product).join(Category, Product.category_id == Category.name)
    return render_template('query_database.html', data=data, category_name = category_name)

#query_database.html
<body>
    {% for user in data %}
        <li>{{user.id}}.   {{user.name}}  {{user.quantity}}  {{user.product_type}} Category {{user.category_id}}  {{user.description}}</li>
    {% endfor %}
    {{ category_name }}
</body>

Result of query_Database.html:

3. Ziemniaczki 2 MyEnum.kg Category 1 Na obiad

SELECT product.id AS product_id, product.name AS 
product_name,product.quantity AS product_quantity, product.product_type AS product_product_type, product.category_id AS product_category_id, 
product.description AS product_description FROM product INNER JOIN category ON product.category_id = category.name

Questions:

1) How to create such query? I got overview how should this look like in pure SQL but I can't find equivalent in documentation of SqlAlchemy :

select p.name, c.name
from product as p
join category as c
on c.id = p.category_id

2) What MyEnum.kg is doing out there? How to delete the My.Enum from the this view?

EDIT - Success

Just leaving the working code, if someone would ever need so.

@app.route('/grocery-list', methods=['GET'])
def display_data():
    data = db.session.query(Product, Category).join(Category).all()
    return render_template('query_database.html', data=data)

    {% for user, category in data  %}
    <li>{{user.id}}. {{user.name}}  {{user.quantity}}  {{user.product_type}} Category {{user.category.name}}  {{user.description}}</li>
    {% endfor %}

Solution

After joining tables, in template file it's required to unpack the value of the category.name with

{{user.category.name}}

Could you explain what you're trying to achieve with `Product.category_id == Category.name` (comparing an integer column to a text column)? — Ilja Everilä, Jan 09 '18 at 19:12
That part is just my testing(failed as you can see). With the docs I tried to find the solution to replace `Product.category_id` with `Category.name`. I don't code to display number but assign `Category.name` to this number in `category` table — michael93pl, Jan 09 '18 at 19:17

Ilja Everilä · Accepted Answer · 2018-01-09T20:35:28.483

7

1) How to create such query? I got overview how should this look like in pure SQL but I can't find equivalent in documentation of SqlAlchemy

Here are some links that you might find useful:

Since you've defined the ORM relationship between Product and Category, you can eager load the related categories along with the products:

data = Product.query.options(db.joinedload(Product.category)).all()

and then you can access user.category.name in your template without it emitting new queries. Another, more SQL-esque solution, would be to fetch Product, Category tuples, which seems like what you were after:

# No need to explicitly define the ON clause of the join, unless you
# really want to. SQLAlchemy examines the foreign key(s) and does what
# needs to be done.
data = db.session.query(Product, Category).join(Category).all()

and then in your template you'd unpack the result tuples:

<!-- I don't understand why it's called "user" -->
{% for user, category in data %}
    ...
{% endfor %}

2) What MyEnum.kg is doing out there? How to delete the My.Enum from the this view?

That's just the string representation of an enum:

In [4]: str(MyEnum.kg)
Out[4]: 'MyEnum.kg'

You'll want to modify {{user.product_type}} to suit your needs, if you're unhappy with it. You've used the SQLAlchemy Enum type and a PEP-435-compliant enumerated class for that column:

When using an enumerated class, the enumerated objects are used both for input and output, rather than strings as is the case with a plain-string enumerated type...

edited Jan 09 '18 at 20:35

answered Jan 09 '18 at 20:18

Ilja Everilä

50,538
7
126
127

At first, great thanks for your reply! I will read carefully links you mentioned to expand my knowledge immediately. ATM - if i understand that correctly, with `def display_date()` we are returning only one argument `data = db.session.query(Product, Category).join(Category).all()`and the view should look like `{% for user, category in data %}` ? Why do we iterate with the category in there? With current "settings" the output stays unchanged – michael93pl Jan 09 '18 at 20:54
If you want to use the more explicit and SQL like style, then yes. You "iterate with category" since the query results in a list of `product, related_category` tuples. That's what you ask for with `query(Product, Category)`. – Ilja Everilä Jan 09 '18 at 21:03
So the current changes have no affects to the output. I made an edit to the question with the desired output. – michael93pl Jan 09 '18 at 21:28
Did you fix your template, e.g. change the `{{user.category_id}}`...? – Ilja Everilä Jan 10 '18 at 04:35
Probably exactly there is my issue, no I haven't changed. I have tried to find the solution for the template but nothing seems to work(even though that's probably easy fix) – michael93pl Jan 10 '18 at 06:00
`{{category.name}}`, given you've unpacked it using that variable name. – Ilja Everilä Jan 10 '18 at 06:29
That was that simple -_- I can't tell how thankful I am, it's my first touch with the flask, sqlalchemy and sql, trying to learn all of these while coding. Sorry for the late reply, couldn't check this at work. Cheers! – michael93pl Jan 10 '18 at 16:10

SQLAlchemy query tables joined with foreign key

1 Answers1