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

Flask uses the MVC (Model View Controller) Framework. Let's say your Flask app has something like this.

• 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)
│   │   ├── home.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)
  state     = db.Column(db.String(10), nullable=False, unique=False)

Often, the logic for the initial setup of the database is placed in __init__.py using create_all or create_engine.

• 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

In this example, create_engine is used to connect to a Postgres database.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def app():
    app = Flask(__name__)
    uri = 'postgresql+psycopg2://johndoe:itsasecret@10.21.50.198:5432/mydb'
    app.config['SQLALCHEMY_DATABASE_URI'] = uri 
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app)

    with app.app_context():
        try:
            create_engine(uri) # or db.create_all()
        except Exception as exception:
            print(f"got the following exception when trying create_engine(uri) => {exception}")
        else:
            print("no exceptions were raised when attempting create_engine(uri)")

    return app

Let's say your /Edit route updates firstname and lastname. 

Which should display a form like this. Clicking on Update should go to the /update view, which contains the SQL to update the record in the SQLite database.

In this example, your /Edit route would fetch firstname and lastname from the database so that the current firstname and lastname are displayed. For example, perhaps the record in the database is fetched using query.filter_by.

It's important to recognize that in this example the one() method was used thus there is no need to loop over the results. Almost always, when updating a recording, the one() or first() method is used to ensure you are updating a single record in the database.

from flask import Blueprint, render_template
from . import app, db
from .models import users

views = Blueprint('views', __name__)

@views.route('/Edit', methods=['GET', 'POST'])
def edit():
  
  id = request.form.get('id')
  
  if request.method == 'GET':
    result = users.query.filter_by(id=id).one()    
    return render_template('edit.html', result=result)
    
  if request.method == 'POST':
    firstname      = request.form.get('firstname')
    lastname       = request.form.get('lastname')
    id             = request.form.get('id')
    data           = users.query.filter_by(id=id).one()
    data.firstname = firstname
    data.lastname  = lastname
    db.session.commit()
    db.session.close()
    return render_template('success.html')  

The edit.html page could have something like this.

{% extends "base.html" %}
{% block content %}
{% for row in result %}
<form method="POST" action="/Edit">
  <input type="hidden" name="id" id="id" value="{{ row['id'] }}">
  <input type="text" name="firstname" id="firstname" value="{{ row['firstname'] }}">
  <input type="text" name="lastname"  id="lastname"  value="{{ row['lastname'] }}">
  <button type="submit">Update</button>
</form>
{% endfor %}
{% endblock %}

When edit.html submits the POST request to the /Edit route, the POST second of the /Edit route updates firstname and lastname in the database.

If the update is failing, here is how you can troubleshoot.

• app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
• app.config["SQLALCHEMY_ECHO"] = True
• app.config["SQLALCHEMY_RECORD_QUERIES"] = True
• include "before" and "after" print statements

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def app():
    app = Flask(__name__)

    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
    app.config["SQLALCHEMY_ECHO"] = True
    app.config["SQLALCHEMY_RECORD_QUERIES"] = True

    db.init_app(app)

    with app.app_context():
        db.create_all()

        from .models import users

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

        print(f"before email     = {data.email}")
        print(f"before firstname = {data.firstname}")
        print(f"before lastname  = {data.lastname}")
        print(f"before state     = {data.state}")

        data.state = 'active'
        try:
            db.session.commit()
        except Exception as exception:
            print(exception)
        finally:
            db.session.close()

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

        print(f"after email     = {recheck.email}")
        print(f"after firstname = {recheck.firstname}")
        print(f"after lastname  = {recheck.lastname}")
        print(f"after state     = {recheck.state}")

    return app

The console/log should contain very useful output that can help to see what SQLAlchemy is doing, something like this.

2023-03-04 08:38:42,601 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-03-04 08:38:42,702 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2023-03-04 08:38:42,702 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-03-04 08:38:42,705 INFO sqlalchemy.engine.Engine COMMIT
2023-03-04 08:38:42,715 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-03-04 08:38:42,728 INFO sqlalchemy.engine.Engine SELECT users.email AS users_email, users.firstname AS users_firstname, users.lastname AS users_lastname, users.state AS users_state
FROM users
WHERE users.email = ?
 LIMIT ? OFFSET ?
2023-03-04 08:38:42,730 INFO sqlalchemy.engine.Engine [generated in 0.00253s] ('john.doe@example.com', 1, 0) <- these values are used in the ? in the prior command
before email = john.doe@example.com
before firstname = John
before lastname = Doe
before state = inactive
2023-03-04 08:38:42,747 INFO sqlalchemy.engine.Engine COMMIT
after email = john.doe@example.com
after firstname = John
after lastname = Doe
after state = active
2023-03-04 08:38:52,809 INFO sqlalchemy.engine.Engine ROLLBACK