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_obj():
    app = Flask(__name__)
    uri = 'sqlite:///example.db'
    app.config['SQLALCHEMY_DATABASE_URI'] = uri 
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app)

    with app.app_context():
        try:
            engine = create_engine(uri) # or db.create_all()
        except exc.SQLAlchemyError as exception:
            print(exception)
        except Exception as exception:
            print(exception)
        else:
            print("no exceptions were raised")

    return app

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_obj():
    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

More commonly, your update logic is going to be in your view (views.py in this example).

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('/Edit', methods=['GET', 'POST'])
def edit():
    id = request.form.get('id')
    result = users.query.filter_by(id=id)    
    return render_template('edit.html', result=result)

@views.route('/update', methods=['GET', 'POST'])
def update():
    firstname      = request.form.get('firstname')
    lastname       = request.form.get('lastname')
    id             = request.form.get('id')
    data           = users.query.filter_by(id=id).first()
    data.firstname = firstname
    data.lastname  = lastname
    db.session.commit()
    return render_template('home.html')

The edit.html page could have something like this.

{% extends "base.html" %}
{% block content %}
{% for row in result %}
<form method="POST" action="/update">
  <input type="text" 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 %}

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.