一個讓 Flask 能夠操控資料庫的套件,並且可以選擇使用操控物件的方法來與資料庫互動(ORM)
簡單說就是:
在 Flask 中可以用熟悉的物件方式去操控資料庫
例如:
Task.query.all() ==> SELECT * FROM member;
推薦資源:
Flask-SQLAlchemy — Flask-SQLAlchemy Documentation (3.1.x)
[Flask教學] Flask-SQLAlchemy 資料庫連線&設定入門(一) - Max行銷誌
Flask 学习-12.Flask-SQLAlchemy 连接 mysql 数据库 - 上海-悠悠 - 博客园
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
dialect + driver://username:passwor@host:port/database
class Config():
SQLALCHEMY_DATABASE_URI = "mysql://root:12345678@localhost/todo"
SQLALCHEMY_TRACK_MODIFICATIONS = False # 關閉對資料庫修改的追蹤
from .config import Config
from .extensions import db
app.config.from_object(Config)
db.init_app(app)
# MySQL
CREATE TABLE member(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(500) NOT NULL,
salt VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
# models.py
from .extensions import db # 引用 db 實例
class Member(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(255), unique=True, nullable=False)
password_hash = db.Column(db.String(500), nullable=False)
salt = db.Column(db.String(255), nullable=False)
email = db.Column(db.String(255), unique=True, nullable=False)
tasks = db.relationship("Task", back_populates="member", cascade="all, delete-orphan")
# MySQL
CREATE TABLE task(
id INT PRIMARY KEY AUTO_INCREMENT,
member_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
priority ENUM('High', 'Medium', 'Low') DEFAULT "Medium",
state ENUM('Todo', 'Doing', 'Done') DEFAULT "Todo",
start DATETIME DEFAULT CURRENT_TIMESTAMP,
deadline DATETIME DEFAULT (NOW() + INTERVAL 1 DAY),
description TEXT,
FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE
);
# models.py
from .extensions import db # 引用 db 實例
class Task(db.Model):
id = db.Column(db.Integer, primary_key=True)
member_id = db.Column(db.Integer, db.ForeignKey("member.id"), nullable=False)
title = db.Column(db.String(255), nullable=False)
priority = db.Column(db.Enum('High', 'Medium', 'Low'), default="Medium")
state = db.Column(db.Enum('Todo', 'Doing', 'Done'), default="Todo")
start = db.Column(db.DateTime, server_default=func.current_timestamp())
deadline = db.Column(db.DateTime, default=datetime.now() + timedelta(days=1))
description = db.Column(db.Text, default="")
member = db.relationship("Member", back_populates="tasks")
tasks = db.relationship("Task", back_populates="member", cascade="all, delete-orphan")
member = db.relationship("Member", back_populates="tasks")
member_id = db.Column(db.Integer, db.ForeignKey("member.id"), nullable=False)
member = Member.query.filter_by(id=id).first()
all_tasks = member.tasks
@app.route('/tasks', methods=['GET'])
def get_all_tasks():
# 查詢所有的 Task
tasks = Task.query.all()
# 組裝幾個 Task 的資料
result = [{'id': task.id, 'title': task.title, 'priority': task.priority, 'state': task.state} for task in tasks]
return jsonify(result)