Flask SQL Alchemy is a library that is intended to make it easy to do something against a database, such as creating a database, creating tables, inserting data, and so on. Instead of having to write complex SQL commands, simple SQL Alchemy directives are used.

Use pip to install the Flask SQL Alchemy package.

pip install flask-sqlalchemy

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)

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
• from .models import users tells SQL Alchemy to create the "users" table 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.

Be aware that a SQLite .db file does not have built in logic to be shared across two or more different Flask systems running on different servers. One option here would be to go with some other database type, such as mySQL, MariaDB, Postgres, MongoDB, et cetera. Or, you could store the Flask .db file on a File Server (such as a Samba File Server) and then mount the path to the .db file on the Flask systems running on different servers.

If running Flask in two (or more) Docker containers (such as tiangolo/uwsgi-nginx-flask) and you want to access the .db file , you will most likely want to mount the share as a --volume mount in the container. You will probably also need to enable the SYS_ADMIN capability to be able to mount the share in the container and include the nobrl option when mounting the share.

Following is the minimal markup you could put in your controller (__init__.py).

• Create the database if it doesn't exist
• Query records in the database
• Print the records to the log/console

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'
    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")

      from .models import users

      data = users.query.filter_by(id='1').first()

      print(f"id        = {data.id}")
      print(f"firstname = {data.firstname}")
      print(f"lastname  = {data.lastname}")

    return app

The console/log should contain the print statements, something like this.

id = 1
firstname = John
lastname = Doe