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 determine if SQLite Database example.db exists, and to create the database if it doesn't exist.

• database_exists is used to determine if the SQLite example.db database exists
• create_all will create the SQLite example.db 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
from sqlalchemy_utils import database_exists
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)

    if database_exists(f'sqlite:///instance/{db_name}'):
        print(f"{db_name} already exists")
    else:
        print(f"{db_name} does not exist, will create {db_name}")
        # 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 the database does not exist, the following should be printed.

On the other hand, if the database exists, the following should be printed.

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'] = f'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(f"Created {db_name} SQLLite Database")   

    return app

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