萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

Flask-SQLAlchemy 資料庫整合:模型定義、遷移、關聯查詢實戰

為什麼選 Flask-SQLAlchemy?

封裝 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__.py

from 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.py

from 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())

Shell 快速測試

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)讓資料庫結構演進有據可查、可回滾。

copyright © 萬盛學電腦網 all rights reserved