Flask is a lightweight Python web framework that offers high flexibility and minimal built-in structure. Unlike Django, it does not include an ORM by default, so developers often integrate SQLAlchemy—a powerful SQL toolkit and ORM—for database interactions.

This guide demonstrates two approaches to using plain SQLAlchemy in Flask: declarative (class-based) mapping and classical (manual table-mapper) mapping.

Installation

Install SQLAlchemy via pip:

pip install sqlalchemy

Verify the installation:

import sqlalchemy
print(sqlalchemy.__version__)

Declarative Mapping Approach

Project Structure

.
├── app.py
├── database.py
├── models.py
├── static/
└── templates/

Database Setup (database.py)

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///./test.db', convert_unicode=True)
db_session = scoped_session(
    sessionmaker(autocommit=False, autoflush=False, bind=engine)
)
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    import models  # Ensure models are imported before creating tables
    Base.metadata.create_all(bind=engine)

Model Definition (models.py)

from sqlalchemy import Column, Integer, String
from database import Base

class Account(Base):
    __tablename__ = 'accounts'

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    contact = Column(String(120), unique=True)

    def __init__(self, username=None, contact=None):
        self.username = username
        self.contact = contact

    def __repr__(self):
        return f'<Account {self.username}>'

Application Entry (app.py)

from flask import Flask
from database import init_db, db_session
from models import Account

app = Flask(__name__)

@app.teardown_appcontext
def close_db(error):
    db_session.remove()

@app.route('/')
def index():
    return 'Flask + SQLAlchemy'

@app.route('/create/<username>/<contact>')
def create_user(username, contact):
    new_account = Account(username=username, contact=contact)
    try:
        db_session.add(new_account)
        db_session.commit()
        return f'Created account for {username}'
    except Exception:
        db_session.rollback()
        return 'Creation failed'

@app.route('/find/<username>')
def find_user(username):
    account = Account.query.filter_by(username=username).first()
    if account:
        return f'Found: {account.username}'
    return 'Not found'

if __name__ == '__main__':
    init_db()
    app.run(debug=True)

The @app.teardown_appcontext ensures sessions are cleaned up after each request.

Classical Mapping Approach

In this method, table definitions and class mappings are handled explicitly.

Updated database.py

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine('sqlite:///./test.db', convert_unicode=True)
metadata = MetaData()
db_session = scoped_session(
    sessionmaker(autocommit=False, autoflush=False, bind=engine)
)

def init_db():
    metadata.create_all(bind=engine)

Updated models.py

from sqlalchemy import Table, Column, Integer, String
from sqlalchemy.orm import mapper
from database import metadata, db_session

class Account:
    query = db_session.query_property()

    def __init__(self, username=None, contact=None):
        self.username = username
        self.contact = contact

    def __repr__(self):
        return f'<Account {self.username}>'

accounts_table = Table(
    'accounts', metadata,
    Column('id', Integer, primary_key=True),
    Column('username', String(50), unique=True),
    Column('contact', String(120), unique=True)
)

mapper(Account, accounts_table)

Application Logic (app.py)

The app.py remains largely unchanged—only the model name and routes may differ slightly. The ORM usage (.add(), .query, etc.) works identically.

Both approaches allow full control over SQLAlchemy without relying on the Flask-SQLAlchemy extension.