If you are not familiar with SQLAlchemy, check out my article Getting Started with 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 will be "users".

from . import db
from sqlalchemy import func, select

class users(db.Model):
    id           = db.Column(db.Integer,     nullable=False, unique=True, primary_key=True)
    date_created = db.Column(db.DateTime(timezone=True), default=func.now())
    email        = db.Column(db.String(100), nullable=False, unique=True)
    password     = db.Column(db.String(200), nullable=False, unique=False)

• Since "db" is being imported, "db" must be used in the class

​

Often, the logic for the initial setup of the database is placed in __init__.py. The create_all function will:

• Create the example.db file if it doesn't exist
• Create the tables in models.py if they don't exist
• sqlite followed by 3 forward slashes means relative path or present working directory (e.g. sqlite:///example.db). In this scenario, the example.db file would need to be in the same directory as the __init__.py file.
• sqlite followed by 4 forward slashes means full or absolute path (e.g. sqlite:////usr/local/flask/database/example.db)
• Let's say you have example.db on a network share and on your Windows PC you map a network drive resulting in something like Z:/example.db. In this scenario, you would use 3 forward slashes followed by the mapped network drive (e.g. sqlite:///Z:/example.db). This would probably only be done on your PC for local testing and development purposes.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func, exc

db = SQLAlchemy()

def app_obj():
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app)

    # this is needed in order for database session calls (e.g. db.session.commit)
    with app.app_context():
        try:
            db.create_all()
        except exc.SQLAlchemyError as sqlalchemyerror:
            print(f"got the following SQLAlchemyError: {sqlalchemyerror}")
        except Exception as exception:
            print(f"got the following Exception: {exception}")
        finally:
            print(f"db.create_all() in __init__.py was successfull - no exceptions were raised")

    return app

• Since "db" is used to instantiate SQLAlchemy in this example, "db" must be used with the SQL Alchemy built in functions.
• Since "app" is used to instantiate Flask in this example, "app" is used.

If using VSCode, the SQLite extension can be used to see that example.db exists and contains the columns.