Bootstrap FreeKB - Flask - Update records in a table using SQLAlchemy
Flask - Update records in a table using SQLAlchemy

Updated:   |  Flask articles

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.

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("before email     = " + str(data.email))
        print("before firstname = " + str(data.firstname))
        print("before lastname  = " + str(data.lastname))
        print("before state     = " + str(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("after email     = " + str(recheck.email))
        print("after firstname = " + str(recheck.firstname))
        print("after lastname  = " + str(recheck.lastname))
        print("after state     = " + str(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.

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


Please enter 8d3db5 in the box below so that we can be sure you are a human.