Queries in Sqlalchemy Flask not returning rows

Question

So i'm trying to query database in heroku based on a keyword that the user searches and i got a problem, it's not giving me back the rows.

• when i use this query below it gives me the right row.

books = Book.query.filter_by(title=keyword).all()

• this row below gives an error, or is not defined but i don't see the issue ?

books = Book.query.filter(or_(Book.isbn.like("%keyword%"), Book.title.like("%keyword%")

• when i use these queries below, no rows are returned:

Book.author.like("%keyword%"))).all()

Book.query.filter(Book.title.like("%keyword%")).all()

below the code of my search() function

@app.route("/search", methods=['GET', 'POST'])
@login_required
def search():
    keyword = request.form.get("keyword")
    books = Book.query.filter(Book.title.like("%keyword%")).all() 

    #books is null, if condition is not satisfied

    if books is None:
        return render_template("home.html")

    return render_template('search.html', title='Search', books=books)

models.py

from flaskblog import db, login_manager
from flask_login import UserMixin    
    
    
@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))
    
    
class User(db.Model, UserMixin):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    
    
class Book(db.Model):
    __tablename__ = "books"
    id = db.Column(db.Integer, primary_key=True)
    isbn = db.Column(db.String, nullable=False, unique=True)
    title = db.Column(db.String, nullable=False)
    author = db.Column(db.String, nullable=False)
    year = db.Column(db.Integer, nullable=False)

search.html:

{% extends "layout.html" %}
{% block content %}
<div class="col-md-12">
    <!-- Search form -->
    <form method="post" action="search" class="form-inline d-flex justify-content-center md-form form-sm">
        <input class="form-control form-control-sm mr-3 w-75" type="text" name="keyword" placeholder="Search" aria-label="Search">
        <i class="fas fa-search" aria-hidden="true"></i>
    </form>
</div>
{% if books %}
<div class="row">
    <div class="col-sm-8 blog-main">
        {% for book in books %}
        <div class="blog-post">
            <h2 class="blog-post-title"> {{ book.title }}</h2>
            <p>
                {{ book.isbn }}
            </p>
        </div>
        {% endfor %}
    </div>
</div>
{% endif %}
{% endblock content %}

Answer 1

• the query below will return books having the word / string keyword in their title

Book.query.filter( Book.title.like("%keyword%") ).all()

to use keyword as variable and not word / string in like filter, use python interpolation or f-string:

Book.query.filter( Book.title.like('%{}%'.format(keyword)) ).all()
or
Book.query.filter( Book.title.like(f'%{keyword}%') ).all()

• to use or_ (and_, in_ ..) expression you need to import it, refer to this topic

from sqlalchemy import or_

books = Book.query.filter( or_(Book.isbn.like(f'%{keyword}%'), Book.title.like(f'%{keyword}%') )

• in search.html

search forms are usually submitted with GET method

<!-- Search form -->
<form method="GET" action="search" class="form-inline d-flex justify-content-center md-form form-sm">
  <input class="form-control form-control-sm mr-3 w-75" type="text" name="keyword" placeholder="Search" aria-label="Search">
  <i class="fas fa-search" aria-hidden="true"></i>
</form>

• in search() function

since the form action is GET you don't need POST in route methods (and you can even remove it, it defaults to GET method)

from markupsafe import escape  # to escape user input and prevent sql injection ..

@app.route("/search")
@login_required
def search():

    keyword = escape(request.form.get("keyword"))  # here
    books = Book.query.filter(Book.title.like(f'%{keyword}%')).all()  # here

    # books is null, if condition is not satisfied
    # if books is None:
    #    return render_template("home.html")

    # return "books" object even it's empty and in your temple check it and display
    # the right message according to it
    return render_template('search.html', title='Search', books=books)

• in search.html

{% if books %}
<div class="row">
    <div class="col-sm-8 blog-main">
        {% for book in books %}
        <div class="blog-post">
            <h2 class="blog-post-title"> {{ book.title }}</h2>
            <p>
                {{ book.isbn }}
            </p>
        </div>
        {% endfor %}
    </div>
</div>
{% else %}

  no books with "keyword"

{% endif %}