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

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

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".

• date_created defaults to func.now() which will set the datetime to the current date and time.
• date_updated uses onupdate to always update date_updated when the record is updated

from . import db
from sqlalchemy import func, select

class users(db.Model):
  date_created = db.Column(db.DateTime(timezone=True), default=func.now())
  date_updated = db.Column(db.DateTime(timezone=True), onupdate=func.now())
  id           = db.Column(db.Integer,     nullable=False, unique=True, primary_key=True)
  firstname    = db.Column(db.String(100), nullable=False, unique=True)
  lastname     = db.Column(db.String(100), nullable=False, unique=False)

​

Here is an example blueprint that demonstrates how to add (append) a record to the "users" table.

from flask import Blueprint, render_template, request, redirect, url_for
from datetime import datetime
from sqlalchemy import func, exc
from . import db
from .models import users

blueprint = Blueprint('example', __name__)

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

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

  firstname = request.form.get('firstname')
  lastname  = request.form.get('lastname')

  data = users(
     firstname=firstname.strip(),
     lastname=lastname.strip(),
     date_created=func.now(),
     date_updated=func.now()
  )

  try:
      db.session.add(data)
  except exc.SQLAlchemyError as exception:
      return(f"got SQLAlchemyError - {exception}")
  except Exception as exception:
      return(f"got exception - {exception}")
  else:
      print("all good, data appended to table)
 
  try:
      db.session.commit()
  except exc.SQLAlchemyError as exception:
      db.session.rollback()
      return(f"got SQLAlchemyError - {exception}")
  except Exception as exception:
      db.session.rollback()
      return(f"got exception - {exception}")
  else:
      id = data.id
      print("all good, commit was successful)
      db.session.close()

  return redirect(url_for('view.example', id=id))