SQLAlchemy - 資料庫的連線、建立會話與模型

顾平安發表於2024-07-03
SQL資料庫會話模型

SQLAlchemy 是一個強大的 Python 庫，它讓你可以用一種物件導向的方式來運算元據庫（ORM 技術）。

在學習 SQLAlchemy 的過程中，需要一些基礎知識的沉澱：Python基礎、Python物件導向、MySQL資料庫的諸多知識點……

在此之前，你可能需要了解傳統執行SQL語句和使用ORM的一些區別以及他們的概念。

什麼是傳統執行 SQL？

傳統執行 SQL 是指直接使用 SQL 語句與資料庫進行互動。這通常包括連線資料庫、編寫 SQL 查詢、執行查詢以及處理結果。

特點

  1. 直接編寫 SQL 語句：你需要手動編寫 SQL 查詢來運算元據庫。
  2. 靈活性高：可以使用所有的 SQL 功能，精確控制查詢和操作。
  3. 低階別控制：你需要管理資料庫連線、事務處理等。

示例

import pymysql

# 連線資料庫
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='0908',
    database='db_flask_demo_school',
    charset='utf8mb4'
)

try:
    with connection.cursor() as cursor:
        # 執行 SQL 查詢
        sql = "SELECT * FROM tb_student"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()

什麼是 ORM（物件關係對映）？

ORM 是一種透過物件導向的方式來運算元據庫的技術。ORM 將資料庫表對映為類，將表中的記錄對映為類的例項，使得你可以用物件導向的方式來進行資料庫操作。

特點

  1. 物件導向：使用類和物件來表示資料庫表和記錄。
  2. 自動生成 SQL：ORM 框架會根據你的操作自動生成相應的 SQL 語句。
  3. 簡化程式碼：簡化了資料庫操作的程式碼，使得程式碼更易讀、更易維護。

示例

from sqlalchemy import create_engine, Column, Integer, String, Boolean
from sqlalchemy.orm import declarative_base, sessionmaker

# 建立資料庫引擎
engine = create_engine('mysql+pymysql://root:0908@localhost:3306/db_flask_demo_school?charset=utf8mb4')
Base = declarative_base()

# 定義模型
class Student(Base):
    __tablename__ = 'tb_student'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    sex = Column(Boolean, default=True)
    age = Column(Integer)

# 建立會話
SessionLocal = sessionmaker(bind=engine)
session = SessionLocal()

# 查詢資料
students = session.query(Student).all()
for student in students:
    print(student.name)

比較

特性 傳統執行 SQL ORM
編寫 SQL 語句 手動編寫 自動生成
操作方式 直接運算元據庫 物件導向操作
靈活性 高，完全控制 SQL 語句 中等，受限於 ORM 框架
程式碼簡潔性 程式碼較冗長 程式碼簡潔易讀
學習曲線 需要掌握 SQL 語法 需要學習 ORM 框架的用法
效能 可能更高效，具體取決於 SQL 語句 可能稍慢，但差異通常不明顯
維護性 低，SQL 語句分散在程式碼中 高，結構清晰，易於維護

適用場景

  • 傳統執行 SQL：適用於需要精細控制 SQL 查詢的場景，或者需要使用複雜的 SQL 特性的場景。
  • ORM：適用於需要快速開發、程式碼可讀性和維護性要求高的場景。ORM 可以大大簡化常見的資料庫操作。

接下來進入 SQLAlchemy 的快速學習

1. 建立資料庫驅動引擎

首先，我們需要建立一個資料庫驅動引擎。當你連線資料庫時，需要提前建立這個資料庫。

CREATE DATABASE db_flask_demo_school CHARSET=utf8mb4

這個引擎就是你和資料庫之間的橋樑。

from sqlalchemy import create_engine

engine = create_engine(
    # url = '驅動://賬戶:密碼@地址:埠/資料庫名?charset=編碼'
    url='mysql+pymysql://root:0908@localhost:3306/db_flask_demo_school?charset=utf8mb4',
    echo=True,  # 在控制檯輸出SQL語句，方便除錯
    pool_size=8,  # 連線池的資料庫連線數量
    max_overflow=30,  # 連線池的資料庫連線最大數量
    pool_recycle=60 * 30,  # 設定秒數限制資料庫多久沒連線自動斷開
)

2. 建立資料庫會話

有了引擎之後，我們需要建立一個會話，這樣才能和資料庫進行互動。會話就像是你和資料庫之間的對話視窗。

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine)  # 工廠函式，建立新的會話類
session = SessionLocal()  # 例項化。用於與資料庫進行互動

另一種建立會話的方式

from sqlalchemy.orm import Session

# 目前官方文件上的案例使用的這種
# 大多數機構教學或者舊的學習資料中可能是上述方式
session = Session(bind=engine)

3. 定義模型基類

在 SQLAlchemy 中，模型是與資料庫表對應的類。我們需要定義一個基類，所有的模型都將繼承這個基類。

from sqlalchemy.orm import declarative_base

Model = declarative_base()

另一種建立基類的方式

from sqlalchemy.orm import DeclarativeBase

class Model(DeclarativeBase):
    pass

該方式也是來源於目前官方文件的示例。

4. 建立模型

現在我們基於上方的模型基類，建立一個學生模型，這個模型對應資料庫中的 tb_student 表。

每個模型類對應的其實就是資料庫中的表，其中表名、欄位都對應了類屬性的設定，而每個類的例項物件也就是一條記錄。

import db
import datetime

class Student(db.Model):
    __tablename__ = 'tb_student'  #  資料表的名字
    # db.Column 是一個欄位物件，對應表欄位，接受很多很多的引數（沒啥好記的，浪費大腦為數不多的空間，參見末尾的表），常用欄位屬性是欄位型別、是否為主鍵、備註描述、預設值指定等等
    # 整型 + 主鍵 + 告知這是學生編號（預設自增）
    id = db.Column(db.Integer, primary_key=True, comment='學生編號')
    # 20位字串 + 告知這是學生姓名
    name = db.Column(db.String(20), comment='學生姓名')
    # 布林型（實際上儲存是0或1），預設是 True（儲存是1）
    sex = db.Column(db.Boolean, default=True, comment='學生性別')
    # 精度更小的整型
    age = db.Column(db.SmallInteger, comment='學生年齡')
    # 'class' 這裡不是型別，而是別名，因為 class = xxx，在python中是不行的（關鍵字）
    class_ = db.Column('class', db.SMALLINT, comment='學生班級')
    # 文字型別
    description = db.Column(db.Text, comment='個性簽名')
    # 布林型，預設值也可以寫成 1
    status = db.Column(db.Boolean, default=1, comment='登入狀態')
    # 日期時間型別，預設值需要特別注意，引用了 datetime 庫，使用的是 now函式的地址，而不是它的呼叫結果。如果使用呼叫結果，將以專案執行啟動的時間為準
    addtime = db.Column(db.DateTime, default=datetime.datetime.now, comment='入學時間')
    orders = db.Column(db.SMALLINT, default=1, comment='學生排序')

    def __repr__(self):
        # 便於列印列表時，顯示 [ <Student: 老王(12)>,...]
        return f'<{self.__class__.__name__}: {self.name}({self.id})>'

    def to_dict(self):
        # 便於物件直接轉成字串，實現方式很多，比如 __dict__ 複製一份，然後篩選非'_'字元開頭的鍵也行。
        return {
            'id': self.id,
            'name': self.name,
            'sex': self.sex,
            'age': self.age,
            'class': self.class_,
            'description': self.description,
            'status': self.status,
            'addtime': self.addtime.strftime('%Y-%m-%d %H:%M:%S'),  # 需要注意獲取到的是 DateTime 物件，參見 datetime 庫
            'orders': self.orders,
        }

if __name__ == '__main__':
    # 建表操作，如果如果你所在的公司或者團隊或者你自身負責資料庫的設計（DBA），那麼建表無需執行 create_all
    # 當然，也有一些坑13，如果沒有為你建表甚至沒有設計，emmmm……，那就自己來吧！
    db.Model.metadata.create_all(db.engine)

附表：常用 Column 欄位

下面是一個詳細的 SQLAlchemy Column 型別及其引數的表格，包括欄位的用途、儲存到資料庫的型別、Python 中表示的資料型別等資訊。

欄位型別 引數示例 用途 資料庫型別 Python 型別
Integer Column(Integer, primary_key=True) 儲存整數 INTEGER int
String Column(String(50), nullable=False) 儲存字串 VARCHAR str
Text Column(Text) 儲存大文字資料 TEXT str
Boolean Column(Boolean, default=True) 儲存布林值 BOOLEAN bool
DateTime Column(DateTime, default=datetime.datetime.now) 儲存日期和時間 DATETIME datetime.datetime
Float Column(Float) 儲存浮點數 FLOAT float
SmallInteger Column(SmallInteger) 儲存小範圍整數 SMALLINT int
LargeBinary Column(LargeBinary) 儲存二進位制資料 BLOB bytes
Numeric Column(Numeric(10, 2)) 儲存精確的小數 NUMERIC decimal.Decimal
Date Column(Date) 儲存日期 DATE datetime.date
Time Column(Time) 儲存時間 TIME datetime.time
Enum Column(Enum('value1', 'value2')) 儲存列舉值 ENUM enum.Enum
Interval Column(Interval) 儲存時間間隔 INTERVAL datetime.timedelta
JSON Column(JSON) 儲存 JSON 資料 JSON dict
UUID Column(UUID(as_uuid=True)) 儲存 UUID UUID uuid.UUID
ARRAY Column(ARRAY(String)) 儲存陣列 ARRAY list
JSONB Column(JSONB) 儲存 JSONB 資料（PostgreSQL） JSONB dict
HSTORE Column(HSTORE) 儲存鍵值對（PostgreSQL） HSTORE dict
INET Column(INET) 儲存 IP 地址（PostgreSQL） INET str
CIDR Column(CIDR) 儲存 IP 地址範圍（PostgreSQL） CIDR str
MACADDR Column(MACADDR) 儲存 MAC 地址（PostgreSQL） MACADDR str

常用 Column 引數

  • primary_key：是否為主鍵。
    • 型別：bool
    • 預設值：False
    • 示例：Column(Integer, primary_key=True)
  • nullable：是否允許為空。
    • 型別：bool
    • 預設值：True
    • 示例：Column(String, nullable=False)
  • default：預設值。
    • 型別：any
    • 預設值：None
    • 示例：Column(Boolean, default=True)
  • unique：是否唯一。
    • 型別：bool
    • 預設值：False
    • 示例：Column(String, unique=True)
  • index：是否建立索引。
    • 型別：bool
    • 預設值：False
    • 示例：Column(String, index=True)
  • comment：欄位註釋。
    • 型別：str
    • 預設值：None
    • 示例：Column(String, comment='使用者名稱')
  • autoincrement：是否自動遞增（通常用於主鍵）。
    • 型別：bool 或 str（好像是auto表示自增吧，忘記了，可查文件）
    • 預設值：True（在主鍵列上）
    • 示例：Column(Integer, primary_key=True, autoincrement=True)
  • server_default：資料庫伺服器端的預設值。
    • 型別：DefaultClause 或 str
    • 預設值：None
    • 示例：Column(String, server_default='default_value')
  • server_onupdate：資料庫伺服器端的更新值。
    • 型別：DefaultClause 或 str
    • 預設值：None
    • 示例：Column(DateTime, server_onupdate=func.now())
  • onupdate：更新時的預設值。
    • 型別：any
    • 預設值：None
    • 示例：Column(DateTime, onupdate=datetime.datetime.now)
  • foreign_key：外來鍵約束。
    • 型別：ForeignKey
    • 預設值：None
    • 示例：Column(Integer, ForeignKey('other_table.id'))

相關文章