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

Often, the logic for the initial setup of the database is placed in __init__.py. Here is how you can create an empty SQLite Database file named example.db. The create_all function is what is used to create the example.db file if it doesn'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
sqlalchemyerror

db = SQLAlchemy() <- global 'db' variable
def app_obj():
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app) <- initial the app to use SQLAlchemy

    # 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 when attempting db.create_all() in __init__.py: {sqlalchemyerror}")
        except Exception as exception:
            print(f"got the following exception when attempting db.create_all() in __init__.py: {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 was created.

Taking this a step further, let's say models.py contains the following.

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())
    date_updated            = db.Column(db.DateTime(timezone=True), default=func.now())
    email_address           = db.Column(db.String(100), nullable=False, unique=True)
    password                = db.Column(db.String(200), nullable=False, unique=False)
    first_name              = db.Column(db.String(150), nullable=False, unique=False)
    last_name               = db.Column(db.String(150), nullable=False, unique=False)
    role                    = db.Column(db.String(6),   nullable=False,  unique=False)
    status                  = db.Column(db.String(10),  nullable=True,  unique=False)
    token                   = db.Column(db.String(64),  nullable=True,  unique=False)
    token_created_date_time = db.Column(db.DateTime(timezone=True), default=func.now())    
    session_id              = db.Column(db.String(30),  nullable=True,  unique=False)

Now all you need to do is to add from .models import users in __init__.py.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

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

    from .models import users

    with app.app_context():
        db.create_all()
        print("Created " + db_name + " SQLLite Database")   

    return app

And now VSCode should show that example.db contains the columns.