封裝 SQLAlchemy ORM,提供 Flask 風格初始化、自動 Session 管理、db.Model 基類、db.create_all() 一鍵建表。搭配 **Flask-Migrate (Alembic)** 實現資料庫版本控制。
pip install flask-sqlalchemy flask-migrate
# config.py
import os
basedir = os.path.abspath(os.path.dirname(__file__))
SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'app.db')
SQLALCHEMY_MIGRATE_REPO = os.path.join(basedir, 'db_repository')
SQLALCHEMY_TRACK_MODIFICATIONS = False # 關閉事件通知,省效能
app/__init__.pyfrom flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
app = Flask(__name__)
app.config.from_object('config')
db = SQLAlchemy(app)
migrate = Migrate(app, db)
from app import views, models
models.pyfrom app import db
from werkzeug.security import generate_password_hash, check_password_hash
ROLE_USER = 0
ROLE_ADMIN = 1
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
nickname = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
role = db.Column(db.SmallInteger, default=ROLE_USER)
posts = db.relationship('Post', backref='author', lazy='dynamic')
about_me = db.Column(db.String(140))
last_seen = db.Column(db.DateTime)
def __repr__(self):
return f''
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(140))
timestamp = db.Column(db.DateTime, index=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
def __repr__(self):
return f''
# 關注關係(多對多自關聯)
followers = db.Table('followers',
db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
)
User.followed = db.relationship(
'User', secondary=followers,
primaryjoin=(followers.c.follower_id == User.id),
secondaryjoin=(followers.c.followed_id == User.id),
backref=db.backref('followers', lazy='dynamic'),
lazy='dynamic'
)
# 初始化遷移倉庫(僅首次)
flask db init
# 偵測模型變更,產生遷移腳本
flask db migrate -m "Initial migration"
# 套用遷移至資料庫
flask db upgrade
# 回滾
flask db downgrade
# 新增
user = User(nickname='john', email='john@example.com')
db.session.add(user)
db.session.commit()
# 查詢
User.query.all()
User.query.filter_by(nickname='john').first()
User.query.filter(User.nickname.like('%joh%')).all()
# 關聯查詢
user.posts.all() # 使用者的所有貼文
user.followed.all() # 使用者關注的人
user.followers.all() # 使用者的粉絲
Post.query.join(User).filter(User.nickname=='john').all() # Join 查詢
# 關注/取消關注
def follow(self, user):
if not self.is_following(user):
self.followed.append(user)
def unfollow(self, user):
if self.is_following(user):
self.followed.remove(user)
def is_following(self, user):
return self.followed.filter(
followers.c.followed_id == user.id).count() > 0
# 關注者的貼文流(高效 SQL)
def followed_posts(self):
return Post.query.join(
followers, (followers.c.followed_id == Post.user_id)
).filter(followers.c.follower_id == self.id).order_by(Post.timestamp.desc())
flask shell
>>> from app import db
>>> from models import User, Post
>>> db.create_all() # 開發環境快速建表(生產請用 migrate)
Flask-SQLAlchemy + Flask-Migrate = **ORM 易用性 + 遷移版本控制**。模型定義關聯、查詢用關係屬性、複雜查詢用 join/filter。遷移指令三步走(init/migrate/upgrade)讓資料庫結構演進有據可查、可回滾。