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
├── database (directory)
│   ├── example.db
├── my-project (directory)
│   ├── __init__.py
│   ├── views.py
│   ├── models.py
│   ├── templates (directory)
│   │   ├── base.html
│   │   ├── home.html
│   │   ├── results.html
│   └── static (directory)
│       └── custom.css

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)

Here is how you could query all of the data from a table where "users" is the name of the table in your model.

from .models import users

@views.route('/Results', methods=['GET', 'POST'])
def Results():

    data = users.query.all()

    for row in data:
        print(f"ID         = {row.id}")
        print(f"first name = {row.firstname}")
        print(f"last name  = {row.lastname}")

    return render_template('results.html', results=data)  

Something like this should be returned.

ID = 123
first name = john
last name = doe

with_entitlies can be used to query data from one or more specific columns in a table.

data = users.query.with_entities(users.firstname, users.lastname)

for row in data:
    print(f"first name = {row.firstname}")
    print(f"last name  = {row.lastname}")

distinct can be used to only return the first row that matches, no duplicates.

data = users.query.with_entities(users.firstname, users.lastname).distinct(users.lastname)

for row in data:
    print(f"first name = {row.firstname}")
    print(f"last name  = {row.lastname}")

_or can be used to chain together two or more queries.

from sqlalchemy import or_
data = users.query.filter(or_(users.firstname.ilike('%{john}%'), users.lastname.ilike('%{doe}%')))

_and can be used to chain together two or more queries.

from sqlalchemy import and_
data = users.query.filter(and_(users.firstname.ilike('%{john}%'), users.lastname.ilike('%{doe}%')))

Often, the expectation is that your query should return a single result. count can be used to count the count the results. This is often also done by chaining together multiple queries.

count = users.query.filter(users.firstname.like("%{john}%"))).filter(users.lastname.like("%{doe}%")).count()

if count == 1:
    print("all good, got 1 result")
else:
    data = users.query.filter(users.firstname.like("%{john}%"))).filter(users.lastname.like("%{doe}%")
    print(f"data = {data}")

In this example, the "data" object could contain the name of the table (users in this example) and the ID of the column with the matching record (123 in this example).

<users 123>

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>

And then views.py could have something like this, getting the SearchString value. This uses query.filter_by to query the table. The syntax is <table name>.query.filter_by(<column>=<value>)

@views.route('/Results', methods=['GET', 'POST'])
def results():
    id = request.form.get('id')

    data = users.query.filter_by(id=id)
    
    for row in data:
        print(f"first name = {row.firstname}")
        print(f"last name  = {row.lastname}")

    return render_template('results.html', results=data)

Which means you can do something like this.

if data == None:
    print("no results found")
else:
    print("result found")

one() can be used to return one and one only matching result.

from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound

result = users.query.filter_by(email='john.doe@example').one()

print(f"firstname = {result.firstname}")

first() can be used to only return the first result when the query may match multiple results, and to optionally raise an exception if multiple results are found.

from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound

result = users.query.filter_by(email='john.doe@example').first()

Or like this, using a LIKE statement.

SearchString = request.form["SearchString"]
search = "%{}%".format(SearchString)
data = users.query.filter(users.username.like(search))

Or like this, to return all emails contain the @ symbol, which should return all.

data = users.query.filter(users.email.like('%@%'))

And then your HTML could look something like this.

{% extends "base.html" %}
{% block content %}
{% if result == None %}
    no results
{% else %}
    {% for row in result %}
        First Name = {{ row['firstname'] }}
        Last Name  = {{ row['lastname'] }}
    {% endfor %}
{% endif %}
{% endblock %}

By default, the results will be returned in ascending order. order_by can be used to return results in descending order.

from sqlalchemy import desc
data = users.query.filter(users.email.like('%@%')).order_by(desc(users.id))

And here is how you could shuffle the results using func.random.

from sqlalchemy import func
data = users.query.filter(users.email.like('%@%')).order_by(func.random())