How to populate a HTML table by querying existing SQLite table based on search using Flask?

I have a SQLite table where I store multiple columns, 3 of them are Product Name, Description and Like. I want a user to be able to search for Product Names, add the product data as rows in a HTML table and press if the user likes the Product or not. This should then update the SQLite Like column for the selected products.

As you can see in the code below, I am trying to fetch the "name" which is entered by the user, and filter the SQLite table for the data and print it in a table. But I do not know how to write the line where I filter the SQLite table, as marked below.

app.py

from flask import Flask, request, jsonify, render_template, redirect
import os
from models import db, Products

app = Flask(__name__)

# Create dummy secrey key so we can use sessions
app.config['SECRET_KEY'] = '123456790'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# Create in-memory database
app.config['DATABASE_FILE'] = 'mna.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + app.config['DATABASE_FILE']
db.init_app(app)

@app.route('/', methods = ['GET'])
def index():
   products = []
   name = request.args.get('search')
   products.append(Products.productname == name)  ##<-- I don't know how to write this line correctly
   return render_template('index.html', products=products)

# run Flask app
if __name__ == "__main__":
   app.run(debug = True)

models.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Products(db.Model):

   productid= db.Column(db.String(), primary_key=True)
   productname= db.Column(db.String(), primary_key=False)
   description = db.Column(db.String(), nullable=False)
   like = db.Column(db.Boolean(), nullable=False)

def __str__(self):
    return unicode(self).encode('utf-8')

def __unicode__(self):
    return "Name: {productname}; Description : {description}".format(productname=self.productname, description=self.description)

And my index.html

{% extends 'base.html' %}

{% block content %}
    <form method="GET">
        <input type="text" placeholder="Search for Product.." name="search">
        <button type="submit"><i class="search-container"></i></button>
    </form>
    <div class="product-container" style="overflow: auto; max-height: 80vh">
        <div class="table-responsive">
            <table class="table" id="products">
                <thead>
                    <tr>
                        <th scope="col">Product Name</th>
                        <th scope="col">Description</th>
                        <th scope="col">Like</th>
                    </tr>
                </thead>
                <tbody>
                {% for product in products %}
                    <tr ={{ product .productid}}>
                        <th scope="row">{{ product .productname}}</th>
                        <td> {{ product.description }} </td>
                        <td> {{ product.like }} </td>
                {% endfor %}
                </tbody>
            </table>
        </div>
    </div>
{% endblock %}

1 answer

  • answered 2019-10-15 15:14 Sujan Adiga

    You need to use query interface from SQLAlchemy,

    Use either,

    1. filter or
    2. filter_by

    Try,

    db.session.query(Product).filter(Product.productname == name)
    or
    db.session.query(Product).filter_by(productname = name)