If you are not familiar with SQLAlchemy, check out my article Getting Started with SQLAlchemy.

This assumes you have already setup Flask to connect to a database.

• Connect to MariaDB using Flask-SQLAlchemy create_all
• Connect to MariaDB using SQLAlchemy create_engine
• Connect to mySQL using SQLAlchemy create_engine
• Connect to Postgres using SQLAlchemy create_all
• Connect to Postgres using SQLAlchemy create_engine
• Connect to SQLite using SQLAlchemy

Flask uses the MVC (Model View Controller) Framework. Just to make this as obvious as possible, I like my Flask apps to have the following.

• Model -> models.py
• View -> views.py
• Controller -> __init__.py

And let's say your Flask app has the following structure.

├── main.py
├── my-project (directory)
│   ├── __init__.py
│   ├── views.py
│   ├── models.py
│   ├── templates (directory)
│   │   ├── base.html
│   │   ├── home.html
│   │   ├── results.html

Let's say models.py contains the following. In this example, the name of the table is "users".

from . import db

class users(db.Model):
  id        = db.Column(db.Integer, nullable=False, unique=True, primary_key=True)
  firstname = db.Column(db.String(20), nullable=False, unique=True)
  lastname  = db.Column(db.String(40), nullable=False, unique=False)

Often, the logic for the initial setup of the database is placed in __init__.py.

• sqlite followed by 3 forward slashes means relative path or present working directory (e.g. sqlite:///example.db). In this scenario, the example.db file would need to be in the same directory as the __init__.py file.
• sqlite followed by 4 forward slashes means full or absolute path (e.g. sqlite:////usr/local/flask/database/example.db)
• Let's say you have example.db on a network share and on your Windows PC you map a network drive resulting in something like Z:/example.db. In this scenario, you would use 3 forward slashes followed by the mapped network drive (e.g. sqlite:///Z:/example.db). This would probably only be done on your PC for local testing and development purposes.

Your HTML page would have an input form like this. Notice in this example that the name of the input is SearchString.

<form method="post" action="/Results">
  <input type="text" placeholder="search . . ." name="SearchString">
</form>

Here is how you could count the results using an equals query.

from flask import Blueprint, render_template, request
from datetime import datetime
from . import app_obj, db, get_db_connection
from .models import users

views = Blueprint('views', __name__)

@views.context_processor
def inject_now():
    return {'now': datetime.utcnow()}

@views.route('/')
def home():
    return render_template('home.html')

@views.route('/Results', methods=['GET', 'POST'])
def results():
    id = request.form.get('id')
    count = users.query.filter_by(id=id)
    print(f"count = {count}")

    return render_template('results.html')  

Here is how you could count the results using a Case SenSiTiVe LIKE query.

@views.route('/Results')
def results():
    SearchString = request.form["SearchString"]
    search = "%{}%".format(SearchString)
    count = users.query.filter(users.username.like(search)).count()
    print(f"count = {count}")

    return render_template('results.html')  

Here is how you could count the results using ilike (not Case InSenSiTiVe) query.

@views.route('/Results')
def results():
    SearchString = request.form["SearchString"]
    search = "%{}%".format(SearchString)
    count = users.query.filter(users.username.ilike(search)).count()
    print(f"count = {count}")

    return render_template('results.html')  

Here is how you could count two (or more) queries.

@views.route('/Results')
def results():
    count = users.query.filter(users.firstname.like(request.form.get('firstname'))).filter(users.firstname.like(request.form.get('lastname'))).count()
    print(f"count = {count}")

    return render_template('results.html')