Flask Get all products from table and iterate over them

Question

I'm writing a website that uses Flask as the backend, and I am trying to add all products from a table into the webpage.

I've never used SQLAlchemy or anything like it, so I'm not sure how exactly to get all the products from a database, and then categorize them as I need for the website.

Here is the code I'm using in views.py

@app.route('/products')
def products():
    product = [
        {
            'product_name': 'T-Shirts',
            'product_price': '18.00',
            'product_img': '',
            'product_description': 'A t-shirt.'
        },
        {
            'product_name': 'TShirts 2',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 3',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 4',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 5',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 6',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 7',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 8',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 9',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 10',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 11',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 12',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 13',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 14',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
        {
            'product_name': 'TShirts 15',
            'product_price': '25.00',
            'product_img': '',
            'product_description': 'A t-shirt, again.'
        },
    ]
    return render_template("products.html",
                           title="Products",
                           products = product)

Here is the code in my template (This works perfectly):

{% extends "base.html" %}
{% block content %}
         <div class="row">
            <div class="large-4 small-12 columns">
                <img src="{{ url_for('static', filename='img/Straight_Up_Performance_Logo.png') }}">

                <div class="hide-for-small panel">
                    <h3>Straight Up Racing</h3>
                    <h5 class="subheader">Straight Up Racing believes that the best way to get in touch with our customers is for
                    them to call us. Give us a call at (406) 239-4975 to order yours today!
                    </h5>
                </div>
            </div>

            <div class="large-8 columns">
               <div class="row container1">
                   {% for product in products %}
                       <div class="large-4 small-4 columns">
                           <ul class="pricing-table">
                               {% if product.product_name %}<li class="title">{{ product.product_name }}</li>{% endif %}
                               {% if product.product_price %}<li class="price">${{ product.product_price }} + S&H</li>{% endif %}
                               {% if product.product_img %}<li class="bullet-item">{{ product.product_img }}</li>{% endif %}
                               {% if product.product_description %}<li class="description">{{ product.product_description }}</li>{% endif %}
                           </ul>
                       </div>
                   {% endfor %}
               </div>
            </div>
         </div>


{% endblock %}

Here is the code that created the Product table:

class Product(db.Model):
    product_id = db.Column(db.Integer, primary_key=True)
    product_name = db.Column(db.String(64), index = True)
    product_price = db.Column(db.Float, index=True)
    product_img = db.Column(db.String(200), index=True)
    product_description = db.Column(db.String(1000), index=True)

    def __repr__(self):
        return '<Product %r>' % (self.product_name)

My question isn't why it's not currently pulling data from the database, I know it's using what I manually define. I don't know how to go about pulling it from the database using SQLAlchemy?

Answer 1

If you want to show all products, you need SQLAlchemy's all method.

@app.route('/products')
def products():
    products = Product.query.all()
    return render_template("products.html",
                           title="Products",
                           products=products)

If you wish to filter by some field, you will want to use SQLAlchemy's filter or filter_by.

products = Product.query.filter(Product.product_name == 'TShirts 11').all()

# or

products = Product.query.filter_by(product_name='TShirts 11').all()