SQLAlchemy - 模組檔案以及增刪改查(CURD操作)

顾平安發表於2024-07-06
SQL

建立 db.py

db.py 檔案是我們管理資料庫連線和模型基類的地方。它讓我們的程式碼更加模組化和可維護，實際生產中也是類似的，無論是在 FastAPI 或者 Flask 等框架中，當使用到 SqlAlchemy 時，的的確確需要一個單獨 db.py，儲存著引擎、會話以及模型基類。這個檔案請務必建好，後續所有教程都會使用到。

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, declarative_base, Session, DeclarativeBase

# 1.建立資料庫驅動引擎
engine = create_engine(
    # url = '驅動://賬戶:密碼@地址:埠/資料庫名?charset=編碼'
    url='mysql+pymysql://root:0908@localhost:3306/db_sqlalchemy_demo?charset=utf8mb4',
    echo=True,
    pool_size=8,  # 連線池的資料庫連線數量
    max_overflow=30,  # 連線池的資料庫連線最大數量
    pool_recycle=60 * 30,  # 設定秒數限制資料庫多久沒連線自動斷開
)

# 2.基於底層資料庫驅動建立資料庫連線會話
# DBSession = sessionmaker(bind=engine)
# session = DBSession()
session = Session(bind=engine)


# 3.模型物件基類，提供資料庫的基操和方法
# Model = declarative_base()
class Model(DeclarativeBase):
    pass

建立 model.py

該檔案負責模型類建立，需要基於 db.py 檔案中的各個物件來建立。方便於其他檔案或者生產開發中的引入（比如 Flask 專案可以根據業務引入相對應的模型類，進行增刪改查操作）

import datetime
import db


class Student(db.Model):
    __tablename__ = 'tb_student'
    id = db.Column(db.Integer, primary_key=True, comment='學生編號')
    name = db.Column(db.String(20), comment='學生姓名')
    sex = db.Column(db.Boolean, default=True, comment='學生性別')
    age = db.Column(db.SmallInteger, comment='學生年齡')
    class_ = db.Column('class', db.SMALLINT, comment='學生班級')
    description = db.Column(db.Text, comment='個性簽名')
    status = db.Column(db.Boolean, default=True, comment='登入狀態')
    addtime = db.Column(db.DateTime, default=datetime.datetime.now, comment='入學時間')
    orders = db.Column(db.SMALLINT, default=True, comment='學生排序')

    def __repr__(self):
        return f'<{self.__class__.__name__}: {self.name}({self.id})>'

    def to_dict(self):
        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'),
            'orders': self.orders,
        }

在此之前，確保資料庫和表已經建立。如果沒有建立，可以執行以下程式碼來建立表：

import db


db.Model.metadata.create_all(db.engine)
# 刪除所有表格如下操作：
db.Model.metadata.drop_all(db.engine)

新增資料

請注意，上方 db.py 以及 model.py 是在同一目錄下的。

import db
from model import Student

def run():
    try:
        student = Student(
            id=1,
            name='王小明',
            sex=True,  # 預設值為 True，可以省略
            age=18,
            class_=3,
            description='滾出去',
            status=True,  # 預設值為 True，可以省略
            orders=0  # 預設值為 0，可以省略
        )  # 例項化學生模型類
        db.session.add(student)  # 透過會話告知新增
        # 另外還有一個 add_all([student1, ...])
        db.session.commit()  # 我們使用的 MySQL 是支援事務操作的，上述例項化、新增無誤後，可提交，這樣才是真正的新增資料完畢
        print(student.to_dict())  # 列印瞅瞅
    except Exception as e:
        db.session.rollback()  # 出現異常就回滾（事務操作），並列印異常
        print(f"Error: {e}")
    finally:
        db.session.close()  # 最終手動關閉會話

if __name__ == '__main__':
    run()

新增多條資料如下：

import db
from model import Student
    
students = [
    Student(name='汪倫', age=19, class_=3, description='滾出去', ),
    Student(name='上官麗麗', age=18, sex=False, class_=1, description='滾進來', ),
]
db.session.add_all(students)
db.session.commit()

查詢資料

查詢一條資料

現在我們來查詢剛剛新增的資料：

import db
from model import Student


# 呼叫會話的 Query 物件，該物件有一個 get 方法，傳入主鍵值即可
# 查詢 id 為 1 的學生，也就是 王小明
student = db.session.query(Student).get(1)  # 單個主鍵 聯合主鍵則使用(1,2) 或者 {'id': 1, 'class_id': 2}
print(student.to_dict())
# 上述可能會提示警告，可使用下列方式
q1 = db.select(Student).where(Student.id == 1)  # SELECT * FROM Student WHERE id == 1
student = db.session.execute(q1).scalar()
print(student.to_dict())
# 該方式也行
q1 = db.select(Student)   # SELECT * FROM Student
student = db.session.execute(q1).scalar()  # 取出一條資料
print(student.to_dict())
# 這樣也可以查詢id為 1 的學生，方式還是很多的
student = db.session.query(Student).filter_by(id=1).first()
print(student.to_dict())
print(student.__dict__)

在後續的教程中，無非都是利用 db 中的 select、delete 等語句結合會話執行，又或者是會話中的 Query 物件進行篩選限制……

查詢多條資料

import db
from model import Student

# 獲取所有的資料，其實和 SQL 語句還是有相似之處的
q2 = db.select(Student)    # SELECT * FROM Student
students = db.session.execute(q2).scalars()  # 建立迭代器物件
# scalar 是查詢出來的首個，scalars 是所有，並不是所有資料，而是迭代器，遍歷可拿到
# for stu in students:
#     print(stu.to_dict())

# 務必注意以下這一點
print(students)  # 正常列印資料
print(students)  # 列印空列表 []
# 為什麼？請參考迭代器的原理，簡而言之就是（拿完了，沒了~）
# 怎麼解決？轉成列表（賦值或者複製？未測試過，自行試試吧！）

條件過濾資料

import db
from model import Student


# 官網示例的方式如下：
q3 = db.select(Student).where(Student.class_ == 3)
students = db.session.execute(q3).scalars()
for stu in students:
    print(stu.to_dict())
# 你也可以這樣：
students = db.session.query(Student).filter_by(class_=3).all()
print(students)
students = db.session.query(Student).filter(Student.class_ == 3).all()
print(students)

值得注意的是 filter_by 不支援 大於小於等 範圍查詢，而需要使用 filter 或者官網示例中 Select 物件 where 語句進行查詢。

邏輯查詢資料

如果想使用並且、或者、取非這些邏輯條件怎麼辦呢？SQLAlchemy 可不支援我們 Python 語法的條件，你可以試試，是否報錯或者結果有問題！如果資料沒問題，我只能說巧了，少部分條件還是可以判斷到的，但想要精確無誤，還是需要使用下方的三個函式。

SQLAlchemy 內建有這三個相關的函式：and_、not_、or_，可相互巢狀使用。

  1. and_(條件1, 條件2, ...)
  2. not_(條件)
  3. or_(條件1, 條件2, ...)
import db
from model import Student

# 查詢班級不是1的學生或者非女生，條件看似複雜，有內到外拆分
# 內部 and_ 表示 班級1的女生，外層是 not_，取反即可，班級1的女生除外，其他都能取
# 經過數學轉義並更貼切表示就是：(not (班級 == 1)) or (not (性別 == 女))
q4 = db.select(Student).where(db.not_(db.and_(Student.class_ == 1, Student.sex == 0)))
students = db.session.execute(q4).scalars()
for stu in students:
    print(stu.to_dict())
# 三個條件函式可支援 select 物件中的 where 以及 會話 Query 物件中的 filter
students = db.session.query(Student).filter(db.not_(db.and_(Student.class_ == 1, Student.sex == 0))).all()
for stu in students:
    print(stu.to_dict())

到目前為止，我認為你有必要牢記兩個物件，一個是 Select，源於 SqlAlchemy，另一個是 Qurey，源於我們的會話 session 中。Select 物件或者將來的 Delete 等，本質上可看做是構造 SQL 語句的，構造後透過會話的 execute 方法執行，有資料返回時可呼叫 scalar 或者 scalars 獲取。而 Query 是查詢過濾專用的，基於會話直接使用，可呼叫 all、first 等方法獲取資料。

成員查詢資料

除了上述的三個邏輯函式，還有一個模型物件的欄位物件所擁有的方法：in_，也就是類似於我們 Python 中的成員運算子 in。SQLAlchemy 同樣不支援 in 運算子，因此你還需要掌握欄位物件的 in_ 方法

'a' in 'abcde'
# SQLAlchemy
student.id.in_([1, 3, 5, 6])

請參考實際例子：

import db
from model import Student


# 查詢學生編號為1、2的學生
students = db.session.query(Student).filter(Student.id.in_([1, 2])).all()
for stu in students:
    print(stu.to_dict())
    
# 使用 Select 物件也可~
q5 = db.select(Student).where(Student.id.in_([1, 2]))
students = db.session.execute(q5).scalars()
for stu in students:
    print(stu.to_dict())

更新資料

查詢到物件後，直接對物件屬性進行賦值操作，可修改記錄的欄位值，再透過 commit 提交後，可儲存記錄。

import db
from model import Student

q = db.select(Student).where(Student.id == 1)
student = db.session.execute(q).scalar()
print(student.to_dict())  # 列印查詢到的資料
student.name = '張曉明'  # 將 王小明 轉成 張曉明
db.session.commit()  # 提交事務

# 再此查詢一遍
q = db.select(Student).where(Student.id == 1)
student = db.session.execute(q).scalar()
print(student.to_dict())  # 列印資料

刪除資料

刪除某條記錄，首先需要有刪除的條件，其次才能刪除。

import db
from model import Student


# 方式1
q = db.select(Student).where(Student.id == 1)
student = db.session.execute(q).scalar()
db.session.delete(student)
db.session.commit()
# 方式2
# q = db.delete(Student).where(Student.id == 1)
# db.session.execute(q)
# db.session.commit()

# 查詢一下資料
q = db.select(Student).where(Student.id == 1)
student = db.session.execute(q).scalar()
print(student)  # 應為 None

以上例子僅供參考，具體條件可以更加的靈活和簡單。

相關文章