SQLAlchemy 學習筆記（二）：ORM 基礎

照例先看層次圖

一、聲明映射關系

使用 ORM 時，我們首先需要定義要操作的表（通過 ​

​Table​

​​），然後再定義該表對應的 Python class，并聲明兩者之間的映射關系（通過 ​

​Mapper​

​）。

友善起見，SQLAlchemy 提供了 Declarative 系統來一次完成上述三個步驟，Declarative 系統提供 base class，這個 base class 會為繼承了它的 Python class（可稱作 model）建立 Table，并維護兩者的映射關系。

from sqlalchemy.ext.declarative import declarative_base
from SQLAlchemy import Column, Integer, String

Base = declarative_base()  # 拿到 Base 類


class User(Base):
    id = Column(Integer, primary_key=True)
    username = Column(String(32), nullable=False, index=True)  # 添加 index 提升搜尋效率
    fullname = Column(String(64))
    password = Column(String(32))  # 真實情況下一般隻存 hash

    def __repr__(self):
        return f"<User {self.username}>"      

這樣就聲明好了一個對象-關系映射，上一篇文章說過所有的 Table 都在某個 MetaData 中，可以通過 ​

​Base.metadata​

​ 擷取它。

Base.metadata.create_all(engine)  # 通過 metadata 建立表（或者說生成模式 schema）      

engine 的建立請見上篇文檔 ​​SQLAlchemy 學習筆記（一）：Engine 與 SQL 表達式語言​​

限制條件

可參考 ​​SQL 基礎筆記（三）：限制​​​ 與 ​​SQLAlchemy 學習筆記（一）：Engine 與 SQL 表達式語言 - 表定義中的限制​​

使用 ORM 來定義限制條件，與直接使用 SQL 表達式語言定義很類似，也有兩種方法：

1. 直接将限制條件作為​

   ​Column​

   ​​、​

   ​ForeignKey​

   ​ 的參數傳入。這種方式最簡潔，也最常用。
2. 使用​

   ​UniqueConstraint​

   ​​、​

   ​CheckConstraint​

   ​​ 等類構造限制，然後放入​

   ​__table_args__​

   ​ 屬性中。舉例：

class User(Base):
    id = Column(Integer, primary_key=True)
    username = Column(String(32), nullable=False, index=True)  # 添加 index 提升搜尋效率
    fullname = Column(String(64))
    password = Column(String(32))  # 真實情況下一般隻存 hash

    # 顧名思義，這是 `Table` 類的參數的序列。裡面的限制條件會被用于建構 __table__
    __table_args__ = (UniqueConstraint('username', name='c_user'),)  # username 的唯一性限制

    def __repr__(self):
        return f"<User {self.username}>"      

二、擷取 session

上一節講 engine 時，我們是通過 connection 來與資料庫互動，而在 ORM 中我們使用 Session 通路資料庫。

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)  # 擷取 session      

三、增删改查

直接使用 SQL 表達式語言時，我們使用 insert()、select()、update()、delete() 四個函數構造 SQL，使用 where() 添加條件，使用 model.join(another_model) 進行 join 操作。

而使用 ORM 時，資料庫操作不再與 SQL 直接對應。我們現在是通過操作 Python 對象來操作資料庫了。

現在，我們通過 db.session.add()、db.session.delete() 進行添加與删除，使用 db.session.query(Model) 進行查詢，通過 filter 和 filter_by 添加過濾條件。

而修改，則是先查詢出對應的 row 對象，直接修改這個對象，然後 commit 就行。

1. 增添：

ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')  # 用構造器構造對象
session.add(ed_user)  # 添加，此外還有批量添加 add_all([user1, user2...])
session.commit()  # 必須手動 commit      

1. 修改：

ed_user = session.query(User).filter_by(name='ed').first()  # 先擷取到 User 對象
ed_user.password = 'f8s7ccs'  # 改了密碼
session.commit()  # 送出

# 批量修改
session.query(User).filter(User.home=='shanghai') \
    .update({User.login_num:0})  # 将所有上海的使用者的 login_num 設為 0
session.commit()      

1. 删除：

ed_user = session.query(User).filter_by(name='ed').first()  # 先擷取到 User 對象
session.delete(ed_user)  # 直接删除（session 知道 ed_user 屬于哪個表）
session.commit()  # 送出

# 批量删除
session.query(User).filter(User.home=='shanghai') \
    .delete()  # 删除所有上海的使用者

session.commit()      

四、進階查詢

1. filter_by：使用關鍵字參數進行過濾，前面的示範中已經用過多次了。
2. filter：它對應 SQL 表達式語言中的 where，支援各種複雜的 SQL 文法。
3. group_by: 通過指定 column 分組
4. distinct(): 去重
5. join(): 關聯

query.filter(User.name == 'ed')  # 這個等同于 filter_by，但是更繁瑣
query.filter(User.name != 'ed')  # 不等于，這個就是 filter_by 無法做到的了
query.filter(User.name.like('%ed%'))  # SQL like 的 like 文法
query.filter(User.name.in_(['ed', 'wendy', 'jack']))  # 包含

# 查詢還可以嵌套
query.filter(User.name.in_(
    session.query(User.name).filter(User.name.like('%ed%'))
))

query.filter(~User.name.in_(['ed', 'wendy', 'jack']))  # 不包含
query.filter(User.name == None)  # NULL 對應 Python 的 None

from sqlalchemy import or_, and_, in_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))  # OR 文法

query.group_by(User.name)  # 分組
query.distinct()  # 去重

from sqlalchemy import func  # SQL 函數包
session.query(func.count(User.name)).filter_by(xxx=xxx)  # 使用 count 函數

# join 關聯
# 預設使用内聯（inner），即隻取兩表的交集
session.query(User, Address).filter(User.id==Address.user_id)  # 方法一

session.query(User).join(Address).\  # 方法二
    filter(Address.email_address=='[email protected]')

# 外聯 outer join，将另一表的列聯結到主表，沒有的行為 NULL
session.query(User).outerjoin(User.addresses) \
    .filter(Address.email_address=='[email protected]')      

# 構造 query 對象
query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)

# 1. all 傳回所有結果的清單
res_list = query.all()

# 2. first 先在 SQL 中加入限制 `limit 1`，然後執行。
res = query.first()

# 3. one 執行 sql 并擷取所有結果
# 如果結果不止一行，抛出 MultipleResultsFound Error！！！
# 如果結果為空，抛出 NoResultFound Error ！！！
res = query.one()

4. one_or_none 差别在于結果為空，它不抛出異常，而是傳回 None
res = query.one_or_none()      

參考

• ​​SQLAlchemy 對象關系入門​​
• ​​SQLAlchemy ORM 的關聯映射定義：一對多、多對多​​