目錄

1、SQLAlchemy

1.1、ORM概述

1.2、SQLAlchemy概述

1.3、SQLAlchemy的組成部分

1.4、SQLAlchemy的使用

1.4.1、安裝

1.4.2、創(chuàng)建數(shù)據(jù)庫(kù)連接

1.4.3、執(zhí)行原生SQL語(yǔ)句

1.4.4、映射已存在的表

1.4.5、創(chuàng)建表

1.4.5.1、創(chuàng)建表的兩種方式

1、使用 Table 類直接創(chuàng)建表

2、使用聲明式方式創(chuàng)建模型類

1.4.5.2、約束

1、創(chuàng)建約束示列

2、外鍵約束

3、刪除/更新行為

1.4.5.3、多表關(guān)系

2、一對(duì)一關(guān)系

3、一對(duì)多/多對(duì)一關(guān)系

4、多對(duì)多關(guān)系

1.4.5.4、scoped_session實(shí)現(xiàn)線程安全

1.4.5.5、新增數(shù)據(jù)

1.4.5.6、修改數(shù)據(jù)

1.4.5.7、刪除數(shù)據(jù)

1.4.5.8、查詢數(shù)據(jù)

1、測(cè)試數(shù)據(jù)準(zhǔn)備

2、基礎(chǔ)查詢

2.1、查詢多個(gè)字段（查詢指定字段）

2.2、去除重復(fù)記錄

2.3、調(diào)試小技巧

3、條件查詢

3.1、常用的比較運(yùn)算符

3.2、常見的邏輯運(yùn)算符

3.3、綜合示例

4、聚合函數(shù)

4.1、常見的聚合函數(shù)

4.2、綜合示列

5、分組查詢

5.1、綜合示例

6、排序查詢

6.1、排序方式

7、 分頁(yè)查詢

7.1、綜合示例

1.4.5.9、多表查詢

1、多表查詢概述

1.1、測(cè)試數(shù)據(jù)準(zhǔn)備

1.2、概述

1.3、多表查詢的分類

2、內(nèi)連接

3、外連接

4、自連接查詢

5、聯(lián)合查詢

6、子查詢

6.1、概述

6.2、標(biāo)量子查詢

6.3、列子查詢

6.4、行子查詢

6.5、表子查詢

1、SQLAlchemy

1.1、ORM概述

定義：ORM（Object-Relational Mapping）模型，即對(duì)象關(guān)系映射，是一種程序設(shè)計(jì)技術(shù)，用于實(shí)現(xiàn)面向?qū)ο缶幊陶Z(yǔ)言里不同類型系統(tǒng)的數(shù)據(jù)之間的轉(zhuǎn)換。在面向?qū)ο蟮木幊陶Z(yǔ)言中，如Java、Python等，數(shù)據(jù)通常被組織成復(fù)雜的對(duì)象關(guān)系。簡(jiǎn)單來(lái)說(shuō)就是ORM模型把面向?qū)ο缶幊膛c操作數(shù)據(jù)庫(kù)之間建立了映射。設(shè)置開發(fā)者操作數(shù)據(jù)庫(kù)無(wú)需維護(hù)和編寫SQL語(yǔ)句，而是基于面對(duì)對(duì)象的方式操作數(shù)據(jù)庫(kù)。

映射關(guān)系：數(shù)據(jù)庫(kù)中的表>編程語(yǔ)言中的類，表中的字段>類中的屬性，表之間的關(guān)系>類之間的關(guān)系。

使用ORM模型的優(yōu)勢(shì)在于：

• 提高開發(fā)效率：開發(fā)者可以使用面向?qū)ο蟮姆绞絹?lái)操作數(shù)據(jù)庫(kù)，無(wú)需編寫大量的SQL語(yǔ)句。
• 易于維護(hù)：由于ORM模型提供了清晰的映射規(guī)則，使得代碼更加清晰、易于理解和維護(hù)。
• 跨數(shù)據(jù)庫(kù)平臺(tái)：ORM框架通常支持多種數(shù)據(jù)庫(kù)平臺(tái)，使得應(yīng)用程序可以輕松地在不同的數(shù)據(jù)庫(kù)之間遷移。

當(dāng)然ORM也存在一下劣勢(shì)：

• 性能開銷：ORM框架在將對(duì)象的操作轉(zhuǎn)換為SQL語(yǔ)句并執(zhí)行這些語(yǔ)句時(shí)，可能會(huì)產(chǎn)生一定的性能開銷。
• 復(fù)雜查詢的復(fù)雜性：在處理復(fù)雜的查詢時(shí)，ORM的語(yǔ)法可能會(huì)變得復(fù)雜且難以維護(hù)。

1.2、SQLAlchemy概述

在Python語(yǔ)言中實(shí)現(xiàn)ORM系統(tǒng)的就是SQLAlchemy，它具備以下特點(diǎn)：

• 對(duì)象關(guān)系映射（ORM）：SQLAlchemy 允許開發(fā)者將數(shù)據(jù)庫(kù)表映射為 Python 類，將表的行映射為 Python 對(duì)象，從而簡(jiǎn)化了數(shù)據(jù)庫(kù)操作。開發(fā)者可以像操作 Python 對(duì)象一樣來(lái)操作數(shù)據(jù)庫(kù)記錄，無(wú)需編寫大量的 SQL 語(yǔ)句。
• 動(dòng)態(tài) SQL 生成：SQLAlchemy 提供了表達(dá)式語(yǔ)言，允許開發(fā)者在運(yùn)行時(shí)動(dòng)態(tài)地構(gòu)建 SQL 語(yǔ)句。這使得開發(fā)者能夠靈活地根據(jù)應(yīng)用程序的需求來(lái)生成和執(zhí)行 SQL 語(yǔ)句。
• 支持多種數(shù)據(jù)庫(kù)：SQLAlchemy 支持多種關(guān)系型數(shù)據(jù)庫(kù)，如 MySQL、PostgreSQL、SQLite、Oracle 等。開發(fā)者可以輕松地切換數(shù)據(jù)庫(kù)后端，而無(wú)需修改太多代碼。
• 連接池和事務(wù)管理：SQLAlchemy 提供了連接池和事務(wù)管理的功能，以確保數(shù)據(jù)庫(kù)連接的穩(wěn)定性和事務(wù)的原子性。這有助于開發(fā)者編寫高效、可靠的數(shù)據(jù)庫(kù)應(yīng)用程序。
• 關(guān)系映射：SQLAlchemy 支持各種關(guān)系數(shù)據(jù)庫(kù)中的關(guān)系類型，如一對(duì)一、一對(duì)多、多對(duì)多等，并提供了相應(yīng)的 API 來(lái)處理這些關(guān)系。

然而，ORM模型也存在一些缺點(diǎn)：

• 性能開銷：ORM框架在將對(duì)象的操作轉(zhuǎn)換為SQL語(yǔ)句并執(zhí)行這些語(yǔ)句時(shí)，可能會(huì)產(chǎn)生一定的性能開銷。
• 復(fù)雜查詢的復(fù)雜性：在處理復(fù)雜的查詢時(shí)，ORM的語(yǔ)法可能會(huì)變得復(fù)雜且難以維護(hù)。

注意：在處理復(fù)雜的SQL查詢時(shí)，由于ORM框架效率低下，所以這個(gè)時(shí)候可以編寫SQL語(yǔ)句執(zhí)行原生SQL語(yǔ)句。

1.3、SQLAlchemy的組成部分

1、核心架構(gòu)（Core）：

• 引擎（Engine）：負(fù)責(zé)與數(shù)據(jù)庫(kù)的通信，管理連接池和事務(wù)。它是SQLAlchemy與數(shù)據(jù)庫(kù)交互的入口點(diǎn)。
• 連接（Connection）：代表與數(shù)據(jù)庫(kù)的單個(gè)連接會(huì)話。它是執(zhí)行SQL語(yǔ)句的直接通道。
• 會(huì)話（Session）：在ORM中使用，代表與數(shù)據(jù)庫(kù)的持久化會(huì)話。它用于管理對(duì)象的持久化，包括添加、修改、刪除和查詢對(duì)象。
• 元數(shù)據(jù)（Metadata）：用于定義和存儲(chǔ)關(guān)于數(shù)據(jù)庫(kù)結(jié)構(gòu)的信息，如表和列的定義。
• 表（Table）：表示數(shù)據(jù)庫(kù)中的一個(gè)表。
• 列（Column）：表示表中的一個(gè)列。
• 類型（Types）：用于定義列的數(shù)據(jù)類型。
• 表達(dá)式構(gòu)造器（Expression Language）：用于構(gòu)建SQL表達(dá)式，如select(), insert(), update(), delete()等。

2、ORM架構(gòu)：

• 聲明基類（Declarative Base）：用于定義ORM模型。
• 模型（Model）：表示數(shù)據(jù)庫(kù)中的一個(gè)表，由Python類定義。
• 屬性（Attributes）：表示模型的屬性，與數(shù)據(jù)庫(kù)表的列相對(duì)應(yīng)。
• 關(guān)系（Relationships）：表示模型之間的關(guān)系，如一對(duì)多、多對(duì)多等。

4、數(shù)據(jù)庫(kù)連接池（Connection Pooling）：管理數(shù)據(jù)庫(kù)連接的池化，確保高效的數(shù)據(jù)庫(kù)連接復(fù)用。

5、Dialect：選擇連接數(shù)據(jù)庫(kù)的DB API種類，根據(jù)配置文件的不同調(diào)用不同的數(shù)據(jù)庫(kù)API，從而實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的操作。

6、架構(gòu)和類型（Schema/Types）：定義數(shù)據(jù)庫(kù)的架構(gòu)和數(shù)據(jù)類型。

7、SQL表達(dá)式語(yǔ)言（SQL Expression Language）：

• 選擇（SELECT）：使用select()構(gòu)造器來(lái)構(gòu)建查詢語(yǔ)句。
• 該語(yǔ)言允許用戶以非常靈活和直觀的方式構(gòu)造SQL語(yǔ)句，而無(wú)需直接編寫SQL字符串。

1.4、SQLAlchemy的使用

SQLAlchemy官方文檔：Dialects — SQLAlchemy 2.0 Documentation

1.4.1、安裝

pip install sqlalchemy

1.4.2、創(chuàng)建數(shù)據(jù)庫(kù)連接

注意：sqlalchemy沒(méi)有提供直接連接數(shù)據(jù)庫(kù)的操作，所以需要借助第三方庫(kù)來(lái)連接數(shù)據(jù)庫(kù)，操作數(shù)據(jù)庫(kù)。以 MySQL 為例，sqlalchemy就是借助pymsql庫(kù)來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)的連接和操作。

連接不同/相同的數(shù)據(jù)庫(kù)借助不同的第三方庫(kù)如下：

MySQL-Python
? ? mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql
? ? mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
MySQL-Connector
? ? mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle
? ? oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

創(chuàng)建連接：

from sqlalchemy import create_engine
from urllib import parseuser = "root" # 用戶名
password = "" # 密碼
pwd = parse.quote_plus(password)   # 解決密碼中含@符導(dǎo)致報(bào)錯(cuò)
host = "172.22.70.174" # 數(shù)據(jù)庫(kù)主機(jī)地址
# 第一步: 創(chuàng)建engine
engine = create_engine(url=f"mysql+pymysql://{user}:{pwd}@{host}:3306/test?charset=utf8",max_overflow=10,  # 超過(guò)連接池大小外最多創(chuàng)建的連接pool_size=10,  # 連接池大小pool_timeout=30,  # 池中沒(méi)有線程最多等待的時(shí)間，否則報(bào)錯(cuò)pool_recycle=-1  # 對(duì)線程池中的線程進(jìn)行一次連接的回收的時(shí)間，如果是3600，表示1個(gè)小時(shí)后對(duì)連接進(jìn)行回收
)

1.4.3、執(zhí)行原生SQL語(yǔ)句

需求：查詢表t_student的全部數(shù)據(jù)，執(zhí)行的SQL語(yǔ)句是：select * from t_student

from sqlalchemy import create_engine
from urllib import parse
import threadinguser = "root" # 用戶名
password = "" # 密碼
pwd = parse.quote_plus(password)   # 解決密碼中含@符導(dǎo)致報(bào)錯(cuò)
host = "172.22.70.174" # 數(shù)據(jù)庫(kù)主機(jī)地址
# 第一步: 創(chuàng)建engine
engine = create_engine(url=f"mysql+pymysql://{user}:{pwd}@{host}:3306/test?charset=utf8",max_overflow=2,  # 超過(guò)連接池大小外最多創(chuàng)建的連接pool_size=3,  # 連接池大小pool_timeout=30,  # 池中沒(méi)有線程最多等待的時(shí)間，否則報(bào)錯(cuò)pool_recycle=-1  # 對(duì)線程池中的線程進(jìn)行一次連接的回收的時(shí)間，如果是3600，表示1個(gè)小時(shí)后對(duì)連接進(jìn)行回收
)# 第二步：使用
def test_execute():# conn = engine.connect() # 創(chuàng)建一個(gè)新的連接conn = engine.raw_connection()  # 從連接池中取一個(gè)連接cursor = conn.cursor() # 創(chuàng)建游標(biāo)sql = "select * from t_student" # 定義執(zhí)行的SQL語(yǔ)句cursor.execute(sql) # 執(zhí)行SQL語(yǔ)句print(cursor.fetchall()) # 獲取執(zhí)行的結(jié)果并打印置控制臺(tái)# 測(cè)試配置是否生效
if __name__ == '__main__':for i in range(20):t = threading.Thread(target=test_execute)t.start()

1.4.4、映射已存在的表

說(shuō)明：使用ORM映射已存在的表時(shí)，只能映射其對(duì)應(yīng)的字段，對(duì)于每個(gè)字段的約束最好和原表保持一致，映射已存在的表時(shí)不能新增字段，新增外鍵約束，新增索引操作。如果需要進(jìn)行這些操作可以數(shù)據(jù)庫(kù)中執(zhí)行相關(guān)sql語(yǔ)句或者使用SQLAlchemy的遷移工具（如Alembic）

注意：

• 在某些情況下，即使SQLAlchemy模型中的約束與原始數(shù)據(jù)庫(kù)表不完全一致，應(yīng)用程序可能仍然能夠正常運(yùn)行。但是，這可能會(huì)導(dǎo)致數(shù)據(jù)完整性問(wèn)題或難以預(yù)測(cè)的行為。
• 因此，最佳實(shí)踐是盡可能保持SQLAlchemy模型中的約束與原始數(shù)據(jù)庫(kù)表一致。

示例：

需求：創(chuàng)建Student表模型映射數(shù)據(jù)庫(kù)中的t_student表

import datetime
from sqlalchemy.orm import declarative_base , sessionmaker
from sqlalchemyBaseUse import engine # 這里的engine就是上面創(chuàng)建連接中創(chuàng)建的engine
from sqlalchemy import Column, Integer, String, Text,  DateTime, Index# 聲明ORM基類
Base = declarative_base()class Student(Base):__tablename__ = 't_student'id = Column(Integer, primary_key=True)stuno = Column(String(10), comment="學(xué)號(hào)")name = Column(String(10), comment="姓名")gender = Column(String(1), comment="性別")age = Column(Integer, comment="年齡")idcard = Column(String(18), comment="身份證")entrydate = Column(DateTime, default=datetime.datetime.now, comment="入學(xué)時(shí)間")addr = Column(String(50), comment="家庭地址")def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)def drop_db():# 刪除繼承base類的表，注意：除了刪除表的映射關(guān)系，數(shù)據(jù)庫(kù)中的表和數(shù)據(jù)都會(huì)被刪減，生產(chǎn)中謹(jǐn)慎操作Base.metadata.drop_all(engine)if __name__ == '__main__':init_db()

1.4.5、創(chuàng)建表

1.4.5.1、創(chuàng)建表的兩種方式

1、使用 Table 類直接創(chuàng)建表

• 編程風(fēng)格：這是一種更接近于 SQL 風(fēng)格的方法，因?yàn)樗苯佣x了表的列和約束，沒(méi)有額外的類定義。
• 用法：你需要顯式地創(chuàng)建 Table 對(duì)象，并指定列和約束。這種方法在不需要 ORM 功能，只需要直接操作數(shù)據(jù)庫(kù)表的場(chǎng)景中特別有用。
• 靈活性：由于這種方法沒(méi)有與 ORM 類綁定，因此它更加靈活，可以更容易地用于更復(fù)雜的數(shù)據(jù)庫(kù)操作或與其他數(shù)據(jù)庫(kù)工具集成。

示例：

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table, CheckConstraint  # 假設(shè)已經(jīng)有了一個(gè)引擎和元數(shù)據(jù)  
engine = create_engine('sqlite:///:memory:')  
metadata = MetaData()  # 創(chuàng)建一個(gè)表，并添加一個(gè)檢查約束  
my_table = Table('my_table', metadata,  Column('id', Integer, primary_key=True),  Column('name', String(50)),  Column('age', Integer),  CheckConstraint("age >= 0 AND age <= 150", name='age_check')  
)  # 創(chuàng)建表（包括檢查約束）  
metadata.create_all(engine)

2、使用聲明式方式創(chuàng)建模型類

• 編程風(fēng)格：這是一種面向?qū)ο蟮姆椒?#xff0c;通過(guò)定義類來(lái)定義表結(jié)構(gòu)。這種方式更加符合 Python 的編程習(xí)慣，并且與 ORM 緊密集成。
• 用法：你需要繼承一個(gè)由 declarative_base() 創(chuàng)建的基類，并在子類中定義字段和關(guān)系。字段通常使用 Column 類定義，而關(guān)系則使用 relationship() 函數(shù)定義。SQLAlchemy 的 ORM 層會(huì)將這些類定義轉(zhuǎn)換為實(shí)際的數(shù)據(jù)庫(kù)表。
• 靈活性：雖然這種方法不如直接使用 Table 類那么靈活，但它提供了更高級(jí)別的抽象和更強(qiáng)大的 ORM 功能。通過(guò) ORM，你可以使用 Python 對(duì)象來(lái)操作數(shù)據(jù)庫(kù)記錄，而無(wú)需編寫繁瑣的 SQL 語(yǔ)句。

示例：

• 在 SQLAlchemy 的聲明式（Declarative）映射中，__table_args__ 是一個(gè)類變量，用于指定表級(jí)別的參數(shù)和選項(xiàng)，這些參數(shù)和選項(xiàng)在創(chuàng)建表時(shí)會(huì)被應(yīng)用到數(shù)據(jù)庫(kù)表上。__table_args__ 通常是一個(gè)元組，其中包含多個(gè) SQLAlchemy 提供的表級(jí)構(gòu)造器，如 UniqueConstraint、ForeignKeyConstraint、Index、CheckConstraint 等。

from sqlalchemy import Column, Integer, String, CheckConstraint  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy.orm import sessionmaker  Base = declarative_base()  class MyModel(Base):  __tablename__ = 'my_table'  id = Column(Integer, primary_key=True)  name = Column(String(50))  age = Column(Integer)  __table_args__ = (  CheckConstraint("age >= 0 AND age <= 150", name='age_check'),  )  

1.4.5.2、約束

概念：約束是作用于表中字段上的規(guī)則，用于限制存儲(chǔ)在表中的數(shù)據(jù)。

目的：保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的正確、有效性和完整性。

分類：

約束	描述	關(guān)鍵字
非空約束	限制該字段的數(shù)據(jù)不能為null	NOT NULL
唯一約束	保證該字段的所有數(shù)據(jù)都是唯一、不重復(fù)的	UNIQUE
主鍵約束	主鍵是一行數(shù)據(jù)的唯一標(biāo)識(shí)，要求非空且唯一	PRIMARY KEY
默認(rèn)約束	保存數(shù)據(jù)時(shí)，如果未指定該字段的值，則采用默認(rèn)值	DEFAULT
檢查約束(8.0.16版本之后)	保證字段值滿足某一個(gè)條件	CHECK
外鍵約束	用來(lái)讓兩張表的數(shù)據(jù)之間建立連接，保證數(shù)據(jù)的一致性和完整性	FOREIGN KEY

注意：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時(shí)候添加約束。

1、創(chuàng)建約束示列

需求如下：

要求創(chuàng)建一張名為t_user的數(shù)據(jù)表，各個(gè)字段的規(guī)則如下：

字段名	字段含義	字段類型	約束條件	約束關(guān)鍵字
id	ID唯一標(biāo)識(shí)	int	主鍵，并且自動(dòng)增長(zhǎng)	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(10)	不為空，并且唯一	NOT NULL , UNIQUE
age	年齡	int	大于0，并且小于等于150	CHECK
status	狀態(tài)	char(1)	如果沒(méi)有指定該值，默認(rèn)為1	DEFAULT
gender	性別	char(1)	無(wú)

from sqlalchemy.orm import declarative_base , sessionmaker
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, CheckConstraint# 聲明ORM基類
Base = declarative_base()class UserModel(Base):__tablename__ = "t_user"id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=True, unique=True, comment="姓名")age = Column(Integer, comment="年齡")status = Column(String(1), default=1, comment="狀態(tài)")gender = Column(String(1), comment="性別")# 添加age的檢查約束# 注意__table_args__是元祖數(shù)據(jù)類型，如果只有一個(gè)數(shù)據(jù)的時(shí)候，注意后面的逗號(hào)不能少__table_args__ = (CheckConstraint("age >0 AND age <= 150", name="age_check"),)def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)if __name__ == '__main__':init_db()

2、外鍵約束

作用：外鍵用來(lái)讓兩張表的數(shù)據(jù)之間建立連接，從而保證數(shù)據(jù)的一致性和完整性。

mysql 語(yǔ)法：

1、添加外鍵
CREATE TABLE 表名(
? ? 字段名 數(shù)據(jù)類型,
? ? ...
? ? [CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名)
);
或
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名)REFERENCES 主表 (主表列名) ;

?2、刪除外鍵
?ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

在 SQLAlchemy 中，創(chuàng)建外鍵約束通常不需要直接使用 __table_args__，因?yàn)?SQLAlchemy 提供了 ForeignKey 和 relationship 這兩個(gè)工具來(lái)定義關(guān)系和外鍵約束。

示例：

需求：創(chuàng)建一張部門表和員工表，建立外鍵約束關(guān)系一個(gè)員工對(duì)應(yīng)一個(gè)部門。

部門表 t_departments

字段名	字段含義	字段類型	約束條件	約束關(guān)鍵字
id	ID唯一標(biāo)識(shí)	int	主鍵，并且自動(dòng)增長(zhǎng)	PRIMARY KEY,AUTO_INCREMENT
name	部門名稱	varchar(50)	非空約束	NOT NULL

員工表 t_employees

字段名	字段含義	字段類型	約束條件	約束關(guān)鍵字
id	ID唯一標(biāo)識(shí)	int	主鍵，并且自動(dòng)增長(zhǎng)	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(50)	非空約束	NOT NULL
age	年齡	int
dept_id	部門id	int	外鍵約束	FOREIGN KEY

1、創(chuàng)建ORM表模型如下：

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index, CheckConstraint# 聲明ORM基類
Base = declarative_base()
# 創(chuàng)建部門表
class Department(Base):__tablename__ = 't_departments'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="部門名稱")# 定義與 Employee 的一對(duì)多關(guān)系employees = relationship("Employee", back_populates="department")# 創(chuàng)建員工表
class Employee(Base):__tablename__ = 't_employees'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="姓名")age = Column(Integer, comment="年齡")department_id = Column(Integer, ForeignKey('t_departments.id', ondelete="CASCADE"), nullable=False)# 定義與 Department 的多對(duì)一關(guān)系department = relationship("Department", back_populates="employees")def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)if __name__ == '__main__':init_db()

2、往數(shù)據(jù)庫(kù)中插入測(cè)試數(shù)據(jù)如下：

# 部門表測(cè)試數(shù)據(jù)
INSERT INTO t_departments (id, name) VALUES (1, '研發(fā)部'), (2, '市場(chǎng)部'),(3, '財(cái)務(wù)部');

# 員工表測(cè)試數(shù)據(jù)
INSERT INTO t_employees (id, name, age, department_id)
VALUES (1, '張三', 22, 1), (2, '李四', 33, 1), (3, 'TOM', 30, ?2), (4, '小藝', 25, 2), (5, '小李', 36, 3);

3、展示通過(guò)關(guān)聯(lián)關(guān)系查詢，如：查詢員工姓名為張三對(duì)應(yīng)的部門信息

def query():Session = sessionmaker(bind=engine)session = Session()# 查詢姓名為張三的員工employee = session.query(Employee).filter_by(name="張三").first()# 如果找到了員工，則獲取其部門信息if employee:department = employee.departmentprint(f"員工姓名: {employee.name}, 部門名稱: {department.name}")else:print("沒(méi)有找到姓名為'張三'的員工")if __name__ == '__main__':# init_db()query()

4、展示通過(guò)關(guān)聯(lián)關(guān)系查詢，如：查詢研發(fā)部所有員工信息

def query2():Session = sessionmaker(bind=engine)session = Session()# 直接通過(guò)部門對(duì)象獲取其員工department = session.query(Department).filter_by(name='研發(fā)部').first()if department:for employee in department.employees:  # 使用了Department 模型中有一個(gè)名為 'employees' 的反向關(guān)聯(lián)print(f"員工姓名: {employee.name}, 員工ID: {employee.id}")if __name__ == '__main__':# init_db()query2()

還是上面4的示例，如果沒(méi)有使用反向關(guān)聯(lián)，在正常情況下我們應(yīng)該如何查詢呢？

思考：如果正常在數(shù)據(jù)庫(kù)中查詢時(shí)，利用的外鍵關(guān)系進(jìn)行查詢，可以使用內(nèi)連接查詢，對(duì)應(yīng)的SQL語(yǔ)句如下：

select e.*, d.name from t_departments d inner join t_employees e on e.department_id=d.id where d.name='研發(fā)部';

那么把SQL語(yǔ)句對(duì)應(yīng)到ORM中就是（使用子查詢）：

• 1、先在t_departments表中把研發(fā)部對(duì)應(yīng)的部門id查詢出來(lái)
• 2、然后在t_employees表中把上面查詢出來(lái)的部門id對(duì)應(yīng)的員工信息查詢出來(lái) 實(shí)現(xiàn)代碼如下：

def query3():Session = sessionmaker(bind=engine)session = Session()# 1、先查詢研發(fā)部信息department = session.query(Department).filter_by(name='研發(fā)部').first()# 如果沒(méi)有找到研發(fā)部，則退出查詢if not department:print("沒(méi)有找到研發(fā)部")session.close()exit()# 2、根據(jù)研發(fā)部的部門id查詢所有員工employees = session.query(Employee).filter_by(department_id=department.id).all()# 打印研發(fā)部所有員工的信息for employee in employees:print(f"員工姓名: {employee.name}, 員工ID: {employee.id}")if __name__ == '__main__':# init_db()query3()

綜上：使用反向關(guān)聯(lián)可以直接查詢所需要的數(shù)據(jù)，效率更高，代碼更加簡(jiǎn)潔。

5、relationship的使用說(shuō)明

可以看到上面兩張表中分別創(chuàng)建了一個(gè)反向關(guān)聯(lián)如下：

t_departments： employees = relationship("Employee", back_populates="department")

t_employees ：department = relationship("Department", back_populates="employees")

參數(shù)說(shuō)明：

• relationship的第一個(gè)參數(shù)：對(duì)應(yīng)關(guān)聯(lián)的模型類的類名（字符串形式），如在t_departments表中建立一個(gè)與t_employees的關(guān)聯(lián)，而t_employees表對(duì)應(yīng)的數(shù)據(jù)模型類為：Employee，所以第一個(gè)參數(shù)就是Employee
• relationship的第二個(gè)參數(shù)：是一個(gè)可選參數(shù)，反向引用另一個(gè)數(shù)據(jù)模型類里的屬性，引用的屬性必須在關(guān)聯(lián)的數(shù)據(jù)模型類中顯示存在。如： employees = relationship("Employee", back_populates="department") 這意味著在 Employee 類中，必須要有一個(gè)名為 department 的屬性，該屬性可以自動(dòng)訪問(wèn)到關(guān)聯(lián)的 Department 對(duì)象。

3、刪除/更新行為

添加了外鍵之后，再刪除父表數(shù)據(jù)時(shí)產(chǎn)生的約束行為，就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種：

行為	說(shuō)明
NO ACTION	當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則不允許刪除/更新。 (與 RESTRICT 一致) 默認(rèn)行為
RESTRICT	當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則不允許刪除/更新。 (與 NO ACTION 一致) 默認(rèn)行為
CASCADE	當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有，則同時(shí)刪除/更新外鍵在子表中的記錄。
SET NULL	當(dāng)在父表中刪除對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則設(shè)置子表中該外鍵值為null（這就要求該外鍵允許取null）。
SET DEFAULT	父表有變更時(shí)，子表將外鍵列設(shè)置成一個(gè)默認(rèn)的值 (Innodb不支持)

對(duì)應(yīng)SQL語(yǔ)法：

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

示例：

在上面的t_employees表中我們創(chuàng)建了一個(gè)外鍵，并指定了外鍵的刪除行為為CASCADE，也就是如果父表t_departments發(fā)生了刪除，那么對(duì)應(yīng)子表t_employees中外鍵關(guān)聯(lián)的數(shù)據(jù)也會(huì)被刪除。

department_id = Column(Integer, ForeignKey('t_departments.id', ondelete="CASCADE"), nullable=False)

注意事項(xiàng)：

• 在本例中，如果ondelete="SET NULL"或onupdate="SET NULL" 會(huì)出現(xiàn)建立外鍵失敗的錯(cuò)誤，因?yàn)樵趖_employees表中的外鍵字段department_id設(shè)置了非空約束（nullable=False），這就和刪除更新行為“SET NULL”互相沖突，所以會(huì)出現(xiàn)創(chuàng)建外鍵失敗。

1.4.5.3、多表關(guān)系

1、relationship?的作用與使用

relationship?是 SQLAlchemy 庫(kù)中的一個(gè)重要功能，它用于在模型（或稱為“表”）之間建立關(guān)聯(lián)關(guān)系。以下是對(duì)?relationship?的詳細(xì)說(shuō)明，包括其作用和使用方式：

作用：

• 定義模型之間的關(guān)系：relationship 允許你定義模型之間的一對(duì)一、一對(duì)多、多對(duì)一和多對(duì)多關(guān)系。這種關(guān)系映射了數(shù)據(jù)庫(kù)中表之間的關(guān)系，使得你可以以面向?qū)ο蟮姆绞教幚頂?shù)據(jù)。
• 自動(dòng)關(guān)聯(lián)查詢：通過(guò) relationship 定義的關(guān)聯(lián)關(guān)系，SQLAlchemy 可以在查詢時(shí)自動(dòng)進(jìn)行關(guān)聯(lián)查詢，從而簡(jiǎn)化了開發(fā)過(guò)程。
• 反向引用：使用 backref 參數(shù)，你可以在一個(gè)關(guān)系上定義反向引用，這使得從一個(gè)對(duì)象可以輕松地訪問(wèn)與之相關(guān)的另一個(gè)對(duì)象。

使用方式：

• 1、引入必要的模塊：

from sqlalchemy.orm import relationship

• 2、定義模型：
  • 在模型中，使用 Column 定義字段，使用 ForeignKey 定義外鍵，外鍵也可以不用定義
  • 使用 relationship 定義與其他模型的關(guān)系。
• 3、建立關(guān)聯(lián)關(guān)系：
  • 一對(duì)一關(guān)系：通過(guò)在從表模型中增加字段和 relationship 對(duì)象，并使用 uselist=False 參數(shù)指定。
  • 一對(duì)多關(guān)系：在“多”的表中設(shè)置外鍵，并使用 relationship 函數(shù)指向“一”的表。
  • 多對(duì)多關(guān)系：創(chuàng)建一個(gè)包含兩個(gè)外鍵的“中間表”，并在兩個(gè)需要做多對(duì)多關(guān)系的模型中分別使用 relationship 函數(shù)，并通過(guò) secondary 參數(shù)指定中間表。
• 4、參數(shù)設(shè)置：
  • backref：定義反向引用，使得從另一個(gè)模型可以方便地訪問(wèn)當(dāng)前模型。
  • uselist：對(duì)于一對(duì)一關(guān)系，設(shè)置為 False 表示關(guān)聯(lián)的對(duì)象不是列表，而是單個(gè)對(duì)象。
  • cascade：定義級(jí)聯(lián)操作，如 "all, delete-orphan" 表示當(dāng)父對(duì)象被刪除時(shí)，所有相關(guān)的子對(duì)象也將被刪除。
  • lazy：是用于控制關(guān)系加載方式的。這個(gè)參數(shù)決定了當(dāng)從數(shù)據(jù)庫(kù)中查詢一個(gè)對(duì)象時(shí)，相關(guān)的對(duì)象是如何被加載的。lazy 參數(shù)可以接受幾個(gè)不同的值，每個(gè)值都對(duì)應(yīng)著不同的加載策略：
  • select (默認(rèn)值): 使用單獨(dú)的 SELECT 語(yǔ)句來(lái)加載相關(guān)對(duì)象。這是默認(rèn)的加載策略，因?yàn)樗?jiǎn)單且直觀。但是，如果你知道你會(huì)經(jīng)常訪問(wèn)相關(guān)的對(duì)象，并且想要減少數(shù)據(jù)庫(kù)的查詢次數(shù)，那么使用其他的加載策略可能會(huì)更有效。
  • order_by：指定關(guān)聯(lián)對(duì)象的排序方式。
  • joined: 使用 JOIN 來(lái)加載主對(duì)象和相關(guān)對(duì)象。這通常會(huì)導(dǎo)致更大的查詢，但可以減少查詢的數(shù)量，特別是當(dāng)你需要訪問(wèn)相關(guān)的對(duì)象時(shí)。
  • dynamic: 這會(huì)返回一個(gè)可以發(fā)出額外查詢的查詢對(duì)象。這意味著你可以根據(jù)需要來(lái)動(dòng)態(tài)地加載相關(guān)的對(duì)象，而不是立即加載它們。這允許你執(zhí)行更復(fù)雜的查詢或操作相關(guān)的對(duì)象集合。
  • noload: 不加載相關(guān)的對(duì)象。這可以用于在你知道不需要相關(guān)的對(duì)象時(shí)節(jié)省數(shù)據(jù)庫(kù)查詢。
  • immediate: 這會(huì)立即加載關(guān)系，而不是在首次訪問(wèn)相關(guān)對(duì)象時(shí)。然而，這種策略在 SQLAlchemy 的當(dāng)前版本中并不常用，因?yàn)樗赡軙?huì)導(dǎo)致意外的行為。
  • subquery: 使用子查詢來(lái)加載相關(guān)對(duì)象。這種策略在某些情況下可能比默認(rèn)的 select 策略更有效，因?yàn)樗梢栽谝粋€(gè)查詢中加載多個(gè)相關(guān)的對(duì)象。
• 5、查詢：
  • 通過(guò) relationship 定義的關(guān)聯(lián)關(guān)系，你可以使用 SQLAlchemy 的查詢 API 進(jìn)行復(fù)雜的關(guān)聯(lián)查詢。

2、一對(duì)一關(guān)系

實(shí)現(xiàn)：在任意一方添加外鍵，關(guān)聯(lián)另一方的主鍵，并設(shè)置外鍵為唯一的（UNIQUE），一般用作單表的拆分【注：這里的實(shí)現(xiàn)是指在MySQL中的實(shí)現(xiàn)方式，但是在ORM中實(shí)現(xiàn)思路是一樣的】

示列：用戶 與 用戶詳情的關(guān)系

關(guān)系：一對(duì)一關(guān)系，用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他詳情字段放在另一張表中，用來(lái)提升操作效率。

1、方式一：使用back_populates參數(shù)實(shí)現(xiàn)反向關(guān)聯(lián)

# 聲明ORM基類
Base = declarative_base()class User(Base):__tablename__ = 't_user'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="姓名")# 定義一個(gè)關(guān)系來(lái)訪問(wèn)UserDetail對(duì)象detail = relationship("UserDetail", uselist=False, back_populates="user")class UserDetail(Base):__tablename__ = 't_user_details'id = Column(Integer, primary_key=True, autoincrement=True)address = Column(String(100), comment="地址")phone = Column(String(20), comment="電話")# 外鍵列，引用t_user表的iduser_id = Column(Integer, ForeignKey('t_user.id'), nullable=False, unique=True)# 定義一個(gè)關(guān)系來(lái)回引用User對(duì)象user = relationship("User", back_populates="detail")

2、方式二：使用backref參數(shù)實(shí)現(xiàn)反向關(guān)聯(lián)

說(shuō)明：backref 參數(shù)是 relationship() 函數(shù)的一個(gè)非常有用的功能，它允許我們自動(dòng)創(chuàng)建反向關(guān)系，而無(wú)需在另一個(gè)模型類中顯式定義它。使用 backref 可以簡(jiǎn)化代碼并減少冗余。

• 在這個(gè)例子中，User 類中的 detail 關(guān)系使用了 backref="user" 參數(shù)。這會(huì)在 UserDetail 類的實(shí)例上自動(dòng)創(chuàng)建一個(gè)名為 user 的屬性，該屬性是一個(gè)指向與之關(guān)聯(lián)的 User 對(duì)象的引用。因此，我們不再需要在 UserDetail 類中顯式定義 user 關(guān)系。

• 現(xiàn)在，如果你有一個(gè) UserDetail 對(duì)象 ud，你可以通過(guò) ud.user 訪問(wèn)與之關(guān)聯(lián)的 User 對(duì)象。同樣地，如果你有一個(gè) User 對(duì)象 u，你可以通過(guò) u.detail 訪問(wèn)與之關(guān)聯(lián)的 UserDetail 對(duì)象（假設(shè)它存在）。

class User(Base):__tablename__ = 't_user'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="姓名")# 使用 backref 自動(dòng)創(chuàng)建反向關(guān)系  detail = relationship("UserDetail", uselist=False, backref="user")class UserDetail(Base):__tablename__ = 't_user_details'id = Column(Integer, primary_key=True, autoincrement=True)address = Column(String(100), comment="地址")phone = Column(String(20), comment="電話")# 外鍵列，引用t_user表的iduser_id = Column(Integer, ForeignKey('t_user.id'), nullable=False, unique=True)# 注意：這里我們不再需要顯式定義 user 關(guān)系，因?yàn)?backref 已經(jīng)為我們做了# 定義一個(gè)關(guān)系來(lái)回引用User對(duì)象# user = relationship("User", back_populates="detail")

3、backref與back_populates的區(qū)別

backref

• backref 是一個(gè)方便的工具，用于在 relationship() 中自動(dòng)創(chuàng)建反向的 relationship()。它通常用于一對(duì)多或多對(duì)一關(guān)系，其中反向關(guān)系是一個(gè)簡(jiǎn)單的屬性。
• 當(dāng)你使用 backref 時(shí)，SQLAlchemy 會(huì)為你自動(dòng)創(chuàng)建一個(gè)反向的 relationship()，并附加到目標(biāo)模型類上。你不需要在目標(biāo)模型類中顯式定義這個(gè)關(guān)系。

back_populates

• back_populates 是一個(gè)更底層的參數(shù)，用于在定義雙向關(guān)系時(shí)手動(dòng)指定反向的 relationship() 屬性的名稱。當(dāng)你需要在兩個(gè)模型類中都明確控制關(guān)系的各個(gè)方面時(shí)，或者當(dāng)你正在處理更復(fù)雜的關(guān)系（如多對(duì)多）時(shí)，back_populates 會(huì)很有用。
• 使用 back_populates 時(shí)，你需要在兩個(gè)模型類中都顯式定義 relationship()，并使用 back_populates 參數(shù)來(lái)指示哪個(gè)屬性是反向關(guān)系。

3、一對(duì)多/多對(duì)一關(guān)系

實(shí)現(xiàn)：在多的一方建立外鍵，指向一的一方的主鍵

示例：部門表和員工表關(guān)系

關(guān)系：一個(gè)員工對(duì)應(yīng)一個(gè)部門，一個(gè)部門對(duì)應(yīng)多個(gè)員工

class Department(Base):__tablename__ = 't_departments'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="部門名稱")# 定義與 Employee 的一對(duì)多關(guān)系employees = relationship("Employee", back_populates="department")class Employee(Base):__tablename__ = 't_employees'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="姓名")age = Column(Integer, comment="年齡")department_id = Column(Integer, ForeignKey('t_departments.id', ondelete="CASCADE"), nullable=False)# 定義與 Department 的多對(duì)一關(guān)系department = relationship("Department", back_populates="employees")

4、多對(duì)多關(guān)系

實(shí)現(xiàn)：建立第三張中間表，中間表至少包含兩個(gè)外鍵，分別關(guān)聯(lián)兩方的主鍵

示列：學(xué)生 與 課程的關(guān)系

關(guān)系：一個(gè)學(xué)生可以選修多門課程，一門課程也可讓多個(gè)學(xué)生選擇

學(xué)生表 t_student

字段名	字段含義	字段類型	約束條件	約束關(guān)鍵字
id	ID唯一標(biāo)識(shí)	int	主鍵，并且自動(dòng)增長(zhǎng)	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(10)	非空約束	NOT NULL
stuno	學(xué)號(hào)	varchar(10)	非空約束	NOT NULL

課程表 t_course

字段名	字段含義	字段類型	約束條件	約束關(guān)鍵字
id	ID唯一標(biāo)識(shí)	int	主鍵，并且自動(dòng)增長(zhǎng)	PRIMARY KEY,AUTO_INCREMENT
name	課程名稱	varchar(10)	非空約束	NOT NULL

學(xué)生課程關(guān)系表 t_student_course

字段名	字段含義	字段類型	約束條件	約束關(guān)鍵字
id	ID唯一標(biāo)識(shí)	int	主鍵，并且自動(dòng)增長(zhǎng)	PRIMARY KEY,AUTO_INCREMENT
studentid	學(xué)生ID	int	非空約束，外鍵約束	NOT NULL, FOREIGN KEY
courseid	課程ID	int	非空約束，外鍵約束	NOT NULL, FOREIGN KEY

方式一：關(guān)聯(lián)表不直接映射到ORM類，使用back_populates參數(shù)實(shí)現(xiàn)反向關(guān)聯(lián)

• 在多對(duì)多關(guān)聯(lián)中，通常不需要直接對(duì)關(guān)聯(lián)表進(jìn)行ORM映射（注意：在ORM映射中實(shí)體表不一定需要?jiǎng)?chuàng)建外鍵約束），因?yàn)閞elationship()函數(shù)已經(jīng)足夠處理這種關(guān)系。

from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, Table# 聲明ORM基類
Base = declarative_base()# 關(guān)聯(lián)表（不直接映射到ORM類）
student_course = Table('t_student_course', Base.metadata,Column("id", Integer, primary_key=True, autoincrement=True),Column('studentid', Integer, ForeignKey('t_student.id'), nullable=False),Column('courseid', Integer, ForeignKey('t_course.id'), nullable=False),)class Student(Base):__tablename__ = 't_student'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=False, comment="姓名")stuno = Column(String(10), nullable=False, comment="學(xué)號(hào)")# 使用relationship()定義多對(duì)多關(guān)系courses = relationship("Course",# 指定中間關(guān)聯(lián)表 secondary="t_student_course",back_populates="students",lazy='dynamic')  # 使用lazy='dynamic'可以返回查詢對(duì)象class Course(Base):__tablename__ = 't_course'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=False, comment="課程名稱")# 使用relationship()定義反向多對(duì)多關(guān)系students = relationship("Student",# 指定中間關(guān)聯(lián)表 secondary="t_student_course",back_populates="courses")def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)if __name__ == '__main__':init_db()

方式二：關(guān)聯(lián)表映射到ORM類 使用backref參數(shù)實(shí)現(xiàn)反向關(guān)聯(lián)

• 因?yàn)閎ackref會(huì)自動(dòng)創(chuàng)建反向關(guān)聯(lián)，所以只需要在除了關(guān)聯(lián)表外的其中一個(gè)表里面建立反向關(guān)聯(lián)即可，需要借助參數(shù)secondary。

class Student(Base):__tablename__ = 't_student'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=False, comment="姓名")stuno = Column(String(10), nullable=False, comment="學(xué)號(hào)")# 與StudentCourse定義多對(duì)多關(guān)系courses = relationship("Course",secondary="t_student_course",  # 關(guān)聯(lián)表的名稱backref="students")  # 為Course模型創(chuàng)建反向引用class Course(Base):__tablename__ = 't_course'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=False, comment="課程名稱")class StudentCourse(Base):__tablename__ = 't_student_course'id = Column(Integer, primary_key=True, autoincrement=True)studentid = Column(Integer, ForeignKey('t_student.id'), nullable=False)courseid = Column(Integer, ForeignKey('t_course.id'), nullable=False)__table_args__ = (# 確保student_id和course_id的組合是唯一的UniqueConstraint('studentid', 'courseid', name='_student_course_uc'),)

示例：查詢姓名為張三選擇的所有課程名稱

1、插入測(cè)試數(shù)據(jù)

insert into t_student values (null, '張三', '2000100101'),(null, '李四',
'2000100102'),(null, '小五', '2000100103'),(null, '小七', '2000100104');

insert into t_course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');

insert into t_student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2), (null,2,3),(null,3,4);

2、代碼實(shí)現(xiàn)

def query():Session = sessionmaker(bind=engine)session = Session()stu = session.query(Student).filter_by(name="張三").first()if stu:for course in stu.courses:print(course.name)if __name__ == '__main__':query()

1.4.5.4、scoped_session實(shí)現(xiàn)線程安全

在SQLAlchemy中，scoped_session是一個(gè)工廠，它產(chǎn)生線程局部（thread-local）的Session對(duì)象。也就是在一個(gè)線程中，多次調(diào)用scoped_session工廠將返回同一個(gè)Session實(shí)例，而在另一個(gè)線程中，你會(huì)得到一個(gè)不同的實(shí)例。這有助于實(shí)現(xiàn)線程安全的數(shù)據(jù)庫(kù)會(huì)話管理，因?yàn)槊總€(gè)線程都有自己的會(huì)話，從而避免了并發(fā)問(wèn)題。

使用scoped_session來(lái)實(shí)現(xiàn)線程安全的步驟：

• 首先，你需要一個(gè)Session類，這通常是通過(guò)sessionmaker創(chuàng)建的。
• 然后，你使用scoped_session來(lái)包裝這個(gè)Session類。
• 其次你可以通過(guò)scoped_session工廠來(lái)獲取會(huì)話，并在需要時(shí)使用它。
• 最后在不需要時(shí)，關(guān)閉它?！咀⒁?#xff1a;當(dāng)線程結(jié)束時(shí)，scoped_session會(huì)自動(dòng)關(guān)閉并清理其內(nèi)部的Session實(shí)例。 所以，通常不需要在代碼中顯式地關(guān)閉或清理會(huì)話?！?/li>

from sqlalchemy import create_engine  
from sqlalchemy.orm import sessionmaker, scoped_session，declarative_base# 假設(shè)你有一個(gè)Base類和你的模型定義...  
Base = declarative_base()  # 創(chuàng)建一個(gè)引擎  
engine = create_engine('sqlite:///example.db')  # 創(chuàng)建一個(gè)Session類  
Session = sessionmaker(bind=engine)  # 使用scoped_session來(lái)包裝Session類  
# 這將確保每次在同一個(gè)線程中調(diào)用scoped_session()時(shí)，都會(huì)返回相同的Session實(shí)例  
scoped_session = scoped_session(Session)  # 在你的代碼中...  
def some_function():  # 獲取一個(gè)會(huì)話  session = scoped_session()  # 使用會(huì)話進(jìn)行查詢、添加、更新或刪除操作...  # 例如: result = session.query(MyModel).filter_by(some_column='value').first()  # 提交事務(wù)（如果需要）  session.commit()  # 關(guān)閉會(huì)話（通常不需要，因?yàn)閟coped_session會(huì)在線程結(jié)束時(shí)自動(dòng)關(guān)閉會(huì)話）  # 但如果你想在函數(shù)結(jié)束時(shí)立即關(guān)閉它，可以調(diào)用remove()方法  # scoped_session.remove()  

1.4.5.5、新增數(shù)據(jù)

需求：在表t_student中新增數(shù)據(jù)

數(shù)據(jù)模型：

class Student(Base):__tablename__ = 't_student'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(10), nullable=False, comment="姓名")stuno = Column(String(10), nullable=False, comment="學(xué)號(hào)")# 使用relationship()定義多對(duì)多關(guān)系courses = relationship("Course",secondary="t_student_course",back_populates="students",lazy='dynamic')  # 使用lazy='dynamic'可以返回查詢對(duì)象

新增數(shù)據(jù)示例：

from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from many_to_many_relationship import Student# 創(chuàng)建ORM基類
Base = declarative_base()# 創(chuàng)建會(huì)話類
Session = sessionmaker(bind=engine)
# 使用scoped_session創(chuàng)建線程安全會(huì)話
session = scoped_session(Session)# # 新增一條數(shù)據(jù)
# # 創(chuàng)建一個(gè)新的Student實(shí)例
# stu1 = Student(name="Tom", stuno="2000100105")
# # 將新實(shí)例添加到會(huì)話中
# session.add(stu1)
# # 提交會(huì)話，將數(shù)據(jù)保存到數(shù)據(jù)庫(kù)
# session.commit()# 批量添加數(shù)據(jù)
session.add_all([Student(name="Jack", stuno="2000100106"),Student(name="小愛(ài)", stuno="2000100107"),Student(name="大胖", stuno="2000100108"),
])
session.commit()

1.4.5.6、修改數(shù)據(jù)

修改數(shù)據(jù)的流程：先查詢出需要修改的數(shù)據(jù)，然后修改數(shù)據(jù)，最后提交修改。

需求：修改表t_student的數(shù)據(jù)

• 1、修改id=5的數(shù)據(jù)，修改為name="Nicky", stuno="2000100109"
• 2、修改name="大胖"的數(shù)據(jù)，修改為name="小胖"
• 3、修改id>6的stuno,每個(gè)stuno前面都加123

注意：如果stuno的長(zhǎng)度是10，需要增加一下長(zhǎng)度，不然新增超過(guò)長(zhǎng)度，會(huì)出現(xiàn)添加失敗的錯(cuò)誤。修改字段數(shù)據(jù)類型的SQL語(yǔ)句如下（需要執(zhí)行SQL語(yǔ)句）：

ALTER TABLE t_student MODIFY stuno varchar(30);

from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from many_to_many_relationship import Student# 創(chuàng)建ORM基類
Base = declarative_base()# 創(chuàng)建會(huì)話類
Session = sessionmaker(bind=engine)
# 使用scoped_session創(chuàng)建線程安全會(huì)話
session = scoped_session(Session)# 1、修改id=5的數(shù)據(jù)，修改為name="Nicky", stuno="2000100109"
# # 查詢出id=5的數(shù)據(jù)
# stu = session.get(Student, 5)
# # 修改數(shù)據(jù)
# if stu:
#     stu.name = "Nicky"
#     stu.stuno = "2000100109"
# # 提交修改的數(shù)據(jù)
# session.commit()# # 2、修改name="大胖"的數(shù)據(jù)，修改為name="小胖"
# session.query(Student).filter_by(name="大胖").update({"name":"小胖"})
# # 提交修改的數(shù)據(jù)
# session.commit()# 3、修改id>6的stuno,每個(gè)stuno前面都加123
session.query(Student).filter(Student.id > 6).update({"stuno": "123" + Student.stuno})
session.commit()

1.4.5.7、刪除數(shù)據(jù)

刪除數(shù)據(jù)流程：先查詢出數(shù)據(jù)，然后再刪除數(shù)據(jù)

需求：刪除表t_student的數(shù)據(jù)

• 1、刪除id=5的數(shù)據(jù)
• 2、刪除name="小胖"的數(shù)據(jù)

from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from many_to_many_relationship import Student# 創(chuàng)建ORM基類
Base = declarative_base()# 創(chuàng)建會(huì)話類
Session = sessionmaker(bind=engine)
# 使用scoped_session創(chuàng)建線程安全會(huì)話
session = scoped_session(Session)# 1、刪除id=5的數(shù)據(jù)
# stu_info = session.get(Student, 5)
# if stu_info:
#     session.delete(stu_info)
#     session.commit()# 2、刪除name="小胖"的數(shù)據(jù)
session.query(Student).filter_by(name="小胖").delete()
session.commit()

1.4.5.8、查詢數(shù)據(jù)

1、測(cè)試數(shù)據(jù)準(zhǔn)備

• 創(chuàng)建一張t_student2表，表的數(shù)據(jù)模型如下：
• 注意：如果數(shù)據(jù)庫(kù)的編碼類型不是utf8mb4，表生成后，需要執(zhí)行以下SQL語(yǔ)句修改表的編碼為utf8mb4，不然插入中文會(huì)出現(xiàn)報(bào)錯(cuò)。

ALTER TABLE t_student2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

from sqlalchemy.orm import declarative_base , sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, DateTime# 聲明ORM基類
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = scoped_session(Session)class Student(Base):__tablename__ = 't_student2'id = Column(Integer, primary_key=True)stuno = Column(String(10), comment="學(xué)號(hào)")name = Column(String(10), comment="姓名")gender = Column(String(1), comment="性別")age = Column(Integer, comment="年齡")idcard = Column(String(18), comment="身份證")entrydate = Column(DateTime, comment="入學(xué)時(shí)間")addr = Column(String(50), comment="家庭地址")def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)def basic_query():passif __name__ == '__main__':init_db()

• 往表中插入測(cè)試數(shù)據(jù)，如下：

# 向表中插入數(shù)據(jù)
insert into t_student2 values('1','1','小洋洋','女',18,'12345678901234957','2023-02-03',"北京"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('2','2','小芳','女',18,'123456789012345789','2023-02-03',"北京"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('3','3','小楓','男',22,'123456789012345123','2023-01-03',"上海"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('4','4','小敏','女',20,'123456789012345345','2022-01-03',"北京"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('5','5','小李','男',20,'12345678901234534X','2022-01-03',"上海"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('6','6','王小敏','女',16,'123456789012345345','2022-01-03',"成都"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('7','7','大劉','男',25,'123456789012345102','2022-01-03',"深圳"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('8','8','林逸','男',17,'12345678901234534X','2022-01-03',"北京"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('9','9','莫小迪','女',21,'123456789012345302','2022-01-03',"成都"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('10','10','林仙仙','女',16,'123456789012345330','2022-01-03',"深圳"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('11','11','葉小辰','男',18,'123456789012345352','2022-01-03',"成都"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ('12','12','韓跑跑','男',24,'12345678901234554X','2022-01-03',"北京");

2、基礎(chǔ)查詢

2.1、查詢多個(gè)字段（查詢指定字段）

# 指定字段查詢
stus = session.query(Student.name, Student.age).all()
for name, age in stus:print(name, age)# 全表查詢
stus = session.query(Student).all()
for stu in stus:print(stu.name)

2.2、去除重復(fù)記錄

# 查詢單個(gè)字段的不重復(fù)值
# 查詢User表中所有不重復(fù)的name字段值  
unique_names = session.query(distinct(User.name)).all()  # 遍歷結(jié)果  
for name in unique_names:  print(name)# 查詢多個(gè)字段的不重復(fù)組合
# 查詢User表中所有不重復(fù)的name和age組合  
unique_name_age_combinations = session.query(distinct(User.name, User.age)).all()  # 遍歷結(jié)果  
for name, age in unique_name_age_combinations:  print(name, age)

2.3、調(diào)試小技巧

在編寫完查詢代碼后，可以先打印出代碼對(duì)應(yīng)轉(zhuǎn)化的SQL語(yǔ)句，檢查SQL語(yǔ)句是否正確，然后再執(zhí)行。

示例：

# 指定字段查詢
# 此時(shí)語(yǔ)句結(jié)尾不加.all(),輸出的就是SQL語(yǔ)句
stus = session.query(Student.name, Student.age)
print(stus)

3、條件查詢

3.1、常用的比較運(yùn)算符

比較運(yùn)算符	作用
>	大于
>=	大于等于
	小于
	小于等于
=	等于
<> 或 !=	不等于
IN(...)	在in之后的列表中的值，多選一
LIKE 占位符	模糊匹配(_匹配單個(gè)字符, %匹配任意個(gè)字符)
IS NULL	是NULL

3.2、常見的邏輯運(yùn)算符

邏輯運(yùn)算符	作用
AND	并且 (多個(gè)條件同時(shí)成立)
OR	或者 (多個(gè)條件任意一個(gè)成立)
NOT	非 , 不是

3.3、綜合示例

def conditional_query():# 1、查詢年齡大于20的學(xué)生stus = session.query(Student).filter(Student.age > 20).all()# 2、查詢年齡不等于20的學(xué)生stus2 = session.query(Student).filter(Student.age != 20).all()# 或者使用<>, 注意python3.X版本不支持使用<>，官方推薦使用!=作為不等于運(yùn)算符。# stus2_1 = session.query(Student).filter(Student.age <> 20).all()# 3、查詢年齡為18或20或25的學(xué)生信息stus3 = session.query(Student).filter(Student.age.in_([18, 20, 25])).all()# 4、查詢年齡不為18或20或25的學(xué)生信息stus4 = session.query(Student).filter(~Student.age.in_([18, 20, 25])).all()# 5、查詢家庭住址為空的學(xué)生信息stus5 = session.query(Student).filter(Student.addr == None).all()# 或者使用is_()方法stus5_1 = session.query(Student).filter(Student.addr.is_(None)).all()# 6、查詢家庭住址不為空的學(xué)生信息stus6 = session.query(Student).filter(Student.addr != None).all()# 或者使用isnot_()方法stus6_1 = session.query(Student).filter(Student.addr.isnot(None)).all()# 7、查詢姓林，名字是兩個(gè)字的學(xué)生信息stus7 = session.query(Student).filter(Student.name.like("林_")).all()# 8、查詢身份證號(hào)最后一位是X的學(xué)生信息stus8 = session.query(Student).filter(Student.idcard.like("%X")).all()# 9、查詢年齡在18歲（包含）到25歲（包含）之間的學(xué)生信息stus9 = session.query(Student).filter(Student.age >=18, Student.age <= 25).all()# 或者使用and_()方法stus9_1 = session.query(Student).filter(and_(Student.age >= 18, Student.age <= 25)).all()# 10、查詢年齡為18或20或25的學(xué)生信息stus10 = session.query(Student).filter(or_(Student.age==18, Student.age==20, Student.age==25)).all()

4、聚合函數(shù)

說(shuō)明：將一列數(shù)據(jù)作為一個(gè)整體，進(jìn)行縱向計(jì)算 。

語(yǔ)法：

• SELECT 聚合函數(shù)(字段列表) FROM 表名 ;

注意 : NULL值是不參與所有聚合函數(shù)運(yùn)算的。

4.1、常見的聚合函數(shù)

函數(shù)	作用
count	統(tǒng)計(jì)數(shù)量
max	最大值
min	最小值
avg	平均值
sum	求和

說(shuō)明：SQLAlchemy 提供了一組內(nèi)置的函數(shù)，這些函數(shù)可以在 func 命名空間中直接使用，類似于 SQL 中的聚合函數(shù)，如 COUNT(), SUM(), AVG(), MAX(), MIN() 等。

4.2、綜合示列

def aggregate_query():# 1、統(tǒng)計(jì)學(xué)生總?cè)藬?shù)count = session.query(func.count(Student.id)).scalar()# 2、統(tǒng)計(jì)學(xué)生的平均年齡avg = session.query(func.avg(Student.age)).scalar()# 3、統(tǒng)計(jì)學(xué)生的最大年齡max = session.query(func.max(Student.age)).scalar()# 4、統(tǒng)計(jì)學(xué)生的最小年齡min = session.query(func.min(Student.age)).scalar()# 統(tǒng)計(jì)男生的總年齡count_man = session.query(func.sum(Student.age)).filter_by(gender="男").scalar()

5、分組查詢

1、where與having區(qū)別

• 執(zhí)行時(shí)機(jī)不同：where是分組之前進(jìn)行過(guò)濾，不滿足where條件，不參與分組；而having是分組之后對(duì)結(jié)果進(jìn)行過(guò)濾。
• 判斷條件不同：where不能對(duì)聚合函數(shù)進(jìn)行判斷，而having可以。

注意事項(xiàng)：

• 分組之后，查詢的字段一般為聚合函數(shù)和分組字段，查詢其他字段無(wú)任何意義。
• 執(zhí)行順序: where > 聚合函數(shù) > having 。
• 支持多字段分組, 具體語(yǔ)法為 : group by columnA,columnB

在 SQLAlchemy 中，使用 group_by() 方法來(lái)執(zhí)行分組查詢。分組查詢通常與聚合函數(shù)（如 COUNT(), SUM(), AVG(), MAX(), MIN() 等）一起使用，以便對(duì)每個(gè)分組進(jìn)行計(jì)算。

5.1、綜合示例

def group_by_query():# 1、根據(jù)性別分組，統(tǒng)計(jì)男學(xué)生和女學(xué)生的數(shù)量# select sex, count(*) from t_student2 group by sex;results = session.query(Student.gender, func.count(Student.id).label("stu_nums")).group_by(Student.gender).all()# for gender, stu_nums in results:#     print(gender, stu_nums)# 2、根據(jù)性別分組 , 統(tǒng)計(jì)男學(xué)生和女學(xué)生的平均年齡# select sex,avg(age) from t_student2 group by sex;results2 = session.query(Student.gender, func.avg(Student.age).label("avg_age")).group_by(Student.gender).all()# for gender, avg_age in results2:#     print(gender, avg_age)# 3、查詢年齡小于25的學(xué)生 , 并根據(jù)家庭地址分組 , 獲取學(xué)生數(shù)量大于等于3的家庭地址# select addr,count(*) addr_num from t_student2 where age<25 group by addr having addr_num>=3;# 方式一: 通過(guò)having實(shí)現(xiàn)results3 = session.query(Student.addr, func.count(Student.id)).filter(Student.age < 25).group_by(Student.addr)\.having(func.count(Student.id) >= 3).all()# 方式二: 通過(guò)子查詢實(shí)現(xiàn)，在子句中使用 filter()和比較運(yùn)算符來(lái)實(shí)現(xiàn)它。# 3.1、 先查詢年齡小于 25 的學(xué)生，并根據(jù)家庭地址分組，獲取學(xué)生數(shù)量大于等于 3 的家庭地址subquery = session.query(Student.addr, func.count(Student.id).label("stu_nums")).filter(Student.age < 25).\group_by(Student.addr).subquery()# 3.2、使用子查詢和 filter 子句來(lái)過(guò)濾出學(xué)生數(shù)量大于等于 3 的家庭地址results3_1 = session.query(subquery.c.addr, subquery.c.stu_nums).filter(subquery.c.stu_nums >= 3).all()# for addr, stu_nums in results3:#     print(addr, stu_nums)# 統(tǒng)計(jì)不同家庭地址男女生的數(shù)量# select addr,gender,count(*) from t_student2 group by addr,gender;results4 = session.query(Student.addr, Student.gender, func.count(Student.id).label("stu_nums")).group_by(Student.addr, Student.gender).all()print(results4)

6、排序查詢

6.1、排序方式

• ASC : 升序(默認(rèn)值)
• DESC: 降序

注意：

• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序，當(dāng)?shù)谝粋€(gè)字段值相同時(shí)，才會(huì)根據(jù)第二個(gè)字段進(jìn)行排序 ;

def order_by_query():# 1、根據(jù)年齡對(duì)學(xué)生進(jìn)行升序排序# select *from t_student order by age asc;res = session.query(Student).order_by(Student.age.asc()).all()# 或者# select *from t_student order by age;res_1 = session.query(Student).order_by(Student.age).all()# for res in res:#     print(res.age)# 2、根據(jù)年齡對(duì)學(xué)生進(jìn)行升序排序 , 年齡相同 , 再按照入學(xué)時(shí)間進(jìn)行降序排序# select *from t_student order by age,entrydate desc;res2 = session.query(Student).order_by(Student.age, Student.entrydate.desc()).all()# for res in res2:#     print(res.age,res.entrydate)

7、 分頁(yè)查詢

注意：

• 起始索引從0開始，起始索引 = （查詢頁(yè)碼 - 1）* 每頁(yè)顯示記錄數(shù)。
• 分頁(yè)查詢是數(shù)據(jù)庫(kù)的方言，不同的數(shù)據(jù)庫(kù)有不同的實(shí)現(xiàn)，MySQL中是LIMIT。
• 如果查詢的是第一頁(yè)數(shù)據(jù)，起始索引可以省略，直接簡(jiǎn)寫為 limit 10。

說(shuō)明：在 SQLAlchemy 中，分頁(yè)查詢通常通過(guò)使用 offset() 和 limit() 方法來(lái)實(shí)現(xiàn)。這兩個(gè)方法分別用于設(shè)置索引偏移量和限制查詢結(jié)果的條數(shù)。

7.1、綜合示例

def limit_query():# 1、查詢第1頁(yè)學(xué)生數(shù)據(jù), 每頁(yè)展示10條記錄# select *from t_student limit 0,10;res = session.query(Student).offset(0).limit(10).all()# 或者# select *from t_student limit 10;res_1 = session.query(Student).limit(10).all()# for stu in res:#     print(stu.id)# 2、查詢第2頁(yè)學(xué)生數(shù)據(jù), 每頁(yè)展示10條記錄# 說(shuō)明:起始索引=(頁(yè)碼-1)*頁(yè)展示記錄數(shù)# select *from t_student limit 10,10;res2 = session.query(Student).offset(10).limit(10).all()# for stu in res2:#     print(stu.id)

1.4.5.9、多表查詢

1、多表查詢概述

1.1、測(cè)試數(shù)據(jù)準(zhǔn)備

說(shuō)明：先建立t_emp員工表和t_dept部門表兩張表，并插入對(duì)應(yīng)數(shù)據(jù)

1、創(chuàng)建對(duì)應(yīng)的ORM模型如下：

from sqlalchemy.orm import declarative_base , sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, DateTime, distinct, or_, and_, func, ForeignKey# 聲明ORM基類
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = scoped_session(Session)# 部門表
class Departments(Base):__tablename__ = 't_dept'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="部門名稱")# 員工表
class Employes(Base):__tablename__ = "t_emp"id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(50), nullable=False, comment="姓名")age = Column(Integer, comment="年齡")job = Column(String(20), comment="職位")salary = Column(Integer, comment="薪資")entrydate = Column(DateTime, comment="入職時(shí)間")managerid = Column(Integer, comment="直屬領(lǐng)導(dǎo)ID")dept_id = Column(Integer, ForeignKey("t_dept.id"))def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)if __name__ == '__main__':init_db()

注意：如果創(chuàng)建的表的編碼不是utf8mb4，需要修改為utf8mb4，不然插入中文的數(shù)據(jù)會(huì)出現(xiàn)編碼錯(cuò)誤，執(zhí)行以下SQL語(yǔ)句：

ALTER TABLE t_dept CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE t_emp CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2、插入測(cè)試數(shù)據(jù)

INSERT INTO t_dept (name) VALUES ('研發(fā)部'), ('市場(chǎng)部'),('財(cái)務(wù)部'), ('銷售部'), ('總經(jīng)辦'), ('人事部');

INSERT INTO t_emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1, '大劉', 28, '總裁',40000, '2000-01-01', null,5),
(2, '夏析', 20, '項(xiàng)目經(jīng)理',20000, '2005-12-05', 1,1),
(3, '李興', 33, '開發(fā)', 8000,'2000-11-03', 2,1),
(4, '張敏', 30, '開發(fā)',11000, '2002-02-05', 2,1),
(5, '林夕', 43, '開發(fā)',10500, '2004-09-07', 3,1),
(6, '小美', 19, '程序員鼓勵(lì)師',6600, '2004-10-12', 2,1),
(7, '林逸', 60, '財(cái)務(wù)總監(jiān)',8500, '2002-09-12', 1,3),
(8, '李媛', 19, '會(huì)計(jì)',48000, '2006-06-02', 7,3),
(9, '林妙妙', 23, '出納',5250, '2009-05-13', 7,3),
(10, '趙芳', 20, '市場(chǎng)部總監(jiān)',12500, '2004-10-12', 1,2),
(11, '張三', 56, '職員',3750, '2006-10-03', 10,2),
(12, '李四', 19, '職員',3750, '2007-05-09', 10,2),
(13, '王二', 19, '職員',5500, '2009-02-12', 10,2),
(14, '周鑫', 88, '銷售總監(jiān)',14000, '2004-10-12', 1,4),
(15, '劉達(dá)', 38, '銷售',4600, '2004-10-12', 14,4),
(16, '老錢', 40, '銷售',4600, '2004-10-12', 14,4),
(17, '小六', 42, null,2000, '2011-10-12', 1,null);

1.2、概述

說(shuō)明：多表查詢就是指從多張表中查詢數(shù)據(jù)。

操作：要執(zhí)行多表查詢，就只需要使用逗號(hào)分隔多張表，如： select * from t_emp , t_dept;

具體的執(zhí)行結(jié)果如下:

解釋：可見查詢結(jié)果中包含了大量的結(jié)果集，總共102條記錄，而這其實(shí)就是員工表emp所有的記錄 (17) 與 部門表dept所有記錄(6) 的所有組合情況，這種現(xiàn)象稱之為笛卡爾積。

笛卡爾積: 笛卡爾乘積是指在數(shù)學(xué)中，兩個(gè)集合A集合 和 B集合的所有組合情況。

而在多表查詢中，需要消除無(wú)效的笛卡爾積，只保留兩張表關(guān)聯(lián)部分的數(shù)據(jù)；使用SQL語(yǔ)句，消除多表查詢的笛卡爾積：

select *from t_emp,t_dept where t_emp.dept_id = t_dept.id;

1.3、多表查詢的分類

• 連接查詢
  • 內(nèi)連接：相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)
  • 外連接：
    • 右外連接：查詢右表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
    • 左外連接：查詢左表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
  • 自連接：當(dāng)前表與自身的連接查詢，自連接必須使用表別名
• 子查詢

2、內(nèi)連接

說(shuō)明：內(nèi)連接查詢的是兩張表交集部分的數(shù)據(jù)

內(nèi)連接的語(yǔ)法分為兩種:

• 隱式內(nèi)連接
• 顯式內(nèi)連接

語(yǔ)法：

1、隱式內(nèi)連接

SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ;

2、顯示內(nèi)連接（inner join）

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;

注意：SQL語(yǔ)句中的內(nèi)連接的inner關(guān)鍵可以省略

說(shuō)明：在 SQLAlchemy 中，內(nèi)連接（INNER JOIN）是默認(rèn)的連接類型，當(dāng)你使用 join() 方法而不指定 isouter=True 時(shí)，你就是在執(zhí)行內(nèi)連接。內(nèi)連接只返回滿足連接條件的行，即兩個(gè)表中都存在匹配項(xiàng)的行。

示例：

1、查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (使用隱式內(nèi)連接實(shí)現(xiàn))

• 使用filter()函數(shù)實(shí)現(xiàn)，在filter()函數(shù)中聲明關(guān)聯(lián)關(guān)系

res = session.query(Employes, Departments).filter(Employes.dept_id == Departments.id).all()

2、查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (使用顯式內(nèi)連接實(shí)現(xiàn))

• 使用join()函數(shù)實(shí)現(xiàn)，當(dāng)你想要連接兩個(gè)模型時(shí)，應(yīng)該首先指定主模型（在 session.query() 中列出的第一個(gè)模型），然后使用 join() 方法連接第二個(gè)模型，并指定連接條件。

res2 = session.query(Employes, Departments).join(Departments, Employes.dept_id == Departments.id).all()

3、查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱（使用relationship實(shí)現(xiàn)內(nèi)連接查詢）

• 首先在Departments類中新建relationship反向關(guān)聯(lián)關(guān)系

employes = relationship("Employes", back_populates="departments")

• 在Employes類中新建relationship反向關(guān)聯(lián)關(guān)系

departments = relationship("Departments", back_populates="employes")

• 重新執(zhí)行ORM映射

def init_db():# 創(chuàng)建繼承base類的表的映射關(guān)系Base.metadata.create_all(engine)if __name__ == '__main__':init_db()

• 代碼實(shí)現(xiàn)如下：

res3 = session.query(Employes).join(Employes.departments)

3、外連接

外連接分為兩種，分別是：

• 左外連接
• 右外連接

1、左外連接

說(shuō)明：左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

語(yǔ)法：

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;

2、右外連接

說(shuō)明：右外連接相當(dāng)于查詢表2(右表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

語(yǔ)法：

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

說(shuō)明：SQLAlchemy 支持左外連接（LEFT OUTER JOIN）和右外連接（RIGHT OUTER JOIN）。不過(guò)，在 SQLAlchemy 的 ORM 層，通常更常見的是使用左外連接，因?yàn)?SQLAlchemy 更多地是按照關(guān)系型數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)來(lái)設(shè)計(jì)的，而標(biāo)準(zhǔn) SQL 中右外連接并不如左外連接那樣常見。

但是，你可以使用 SQLAlchemy 的 Core 表達(dá)式語(yǔ)言來(lái)執(zhí)行右外連接。以下是如何在 SQLAlchemy 中使用左外連接和右外連接的示例：

注意事項(xiàng)：左外連接和右外連接是可以相互替換，只需要調(diào)整在連接查詢時(shí)SQL中，表結(jié)構(gòu)的先后順序就可以。在實(shí)際開發(fā)使用時(shí)，左外連接常用。

示例：

1、查詢t_emp表的所有數(shù)據(jù), 和對(duì)應(yīng)的部門信息 (左外連接)

# 1、查詢t_emp表的所有數(shù)據(jù), 和對(duì)應(yīng)的部門信息(左外連接)
# select e.*,d.name from t_emp e left outer join t_dept d on e.dept_id = d.id;
# 注意：outerjoin()函數(shù)中的連接條件可以省略，Employes.dept_id == Departments.id
res = session.query(Employes, Departments.name).outerjoin(Departments).all()
# print(res)

2、查詢t_dept表的所有數(shù)據(jù), 和對(duì)應(yīng)的員工信息（右外連接）

# 查詢t_dept表的所有數(shù)據(jù), 和對(duì)應(yīng)的員工信息（右外連接）
# select d.*,e.* from t_emp e right outer join  t_dept d on d.id = e.dept_id;
# 使用join()函數(shù)實(shí)現(xiàn)，isouter=True使用左外連接，左外連接和右外連接可以相互轉(zhuǎn)換，也就是表的位置不同
# join()函數(shù)中的第一個(gè)參數(shù)Employes表示左外連接關(guān)聯(lián)的表是t_emp，主表是t_dept
res2 = session.query(Employes, Departments).join(Employes, isouter=True).all()
# print(res2)

4、自連接查詢

說(shuō)明：自連接查詢，顧名思義，就是自己連接自己，也就是把一張表連接查詢多次。

連接方式：對(duì)于自連接查詢，可以是內(nèi)連接查詢，也可以是外連接查詢。

注意事項(xiàng)：在自連接查詢中，必須要為表起別名，不然不清楚所指定的條件、返回的字段，到底是哪一張表的字段。

語(yǔ)法：

SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

說(shuō)明：同樣的在SQlAlchemy中實(shí)現(xiàn)自連接查詢，也需要為表起別名的方式，起別名使用aliased()函數(shù)實(shí)現(xiàn)。

示列：

1、查詢員工 及其 所屬領(lǐng)導(dǎo)的名字

• 分析：可將t_emp表看作兩個(gè)表，要求查詢每個(gè)員工及其對(duì)應(yīng)的領(lǐng)導(dǎo)姓名，可見這是一個(gè)交集關(guān)系，需要使用內(nèi)連接。

# select a.name '員工姓名', b.name '領(lǐng)導(dǎo)姓名' from t_emp a join t_emp b on a.managerid = b.id;
# 或
# select a.name '員工姓名', b.name '領(lǐng)導(dǎo)姓名' from t_emp a, t_emp b where a.managerid = b.id;
# 為表Employes創(chuàng)建別名
user = aliased(Employes)
manager = aliased(Employes)# 使用Inner join 的連接方式
res = session.query(user.name.label("employee_name"), manager.name.label("manager_name")).join(manager, user.managerid == manager.id).all()
print(res)

2、查詢所有員工 t_emp a 及其領(lǐng)導(dǎo)的名字 t_emp b, 如果員工沒(méi)有領(lǐng)導(dǎo), 也需要查詢出來(lái) 。

• 分析：如果員工沒(méi)有領(lǐng)導(dǎo), 也需要查詢出來(lái)，由此可見需要使用外連接，一般使用左外連接

# 為表Employes創(chuàng)建別名
user = aliased(Employes)
manager = aliased(Employes)# select a.name '員工姓名', b.name '領(lǐng)導(dǎo)姓名' from t_emp a left join t_emp b on a.managerid = b.id;
res2 = session.query(user.name.label("employee_name"), manager.name.label("manager_name")).join(manager, user.managerid == manager.id, isouter=True).all()
print(res2)

5、聯(lián)合查詢

關(guān)鍵字：union

說(shuō)明：

• 對(duì)于union查詢，就是把多次查詢的結(jié)果合并起來(lái)，形成一個(gè)新的查詢結(jié)果集。
• 對(duì)于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。
• union all 會(huì)將全部的數(shù)據(jù)直接合并在一起。
• union 會(huì)對(duì)合并之后的數(shù)據(jù)去重。

語(yǔ)法：

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

說(shuō)明：在 SQLAlchemy 中，可以使用 union() 或者 union_all()方法來(lái)執(zhí)行聯(lián)合查詢。

示例：

1、將薪資低于 5000 的員工 , 和 年齡大于 45 歲的員工全部查詢出來(lái).

• 分析：當(dāng)前對(duì)于上述需求，可以直接使用多條件查詢，使用邏輯運(yùn)算符 and 連接即可。也可以通過(guò)union/union all來(lái)聯(lián)合查詢.

SQL實(shí)現(xiàn)如下：

select * from t_emp where salary < 5000
union all
select * from t_emp where age > 45;

ORM實(shí)現(xiàn)如下：

query1 = session.query(Employes).filter(Employes.salary < 5000)
query2 = session.query(Employes).filter(Employes.age > 45)
union_query = query1.union(query2).all()
for un in union_query:print(un.name)

6、子查詢

6.1、概述

1、概念

• SQL語(yǔ)句中嵌套SELECT語(yǔ)句，稱為嵌套查詢，又稱子查詢
• 子查詢外部的語(yǔ)句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個(gè)。

如：

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

2、分類

分類依據(jù)：根據(jù)子查詢結(jié)果

分為：

• 標(biāo)量子查詢（子查詢結(jié)果為單個(gè)值）
• 列子查詢(子查詢結(jié)果為一列)
• 行子查詢(子查詢結(jié)果為一行)
• 表子查詢(子查詢結(jié)果為多行多列)

分類依據(jù)2：根據(jù)子查詢位置

分為：

• WHERE之后
• FROM之后
• SELECT之后

6.2、標(biāo)量子查詢

說(shuō)明：子查詢返回的結(jié)果是單個(gè)值（數(shù)字、字符串、日期等）

常用操作符：= <> > >= <

示列：

1、查詢“銷售部”所有員工的信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢出銷售部的部門id
select id from t_dept where name = '銷售部';

2、然后根據(jù)查出的部門id再查詢銷售部的所有員工信息
select e.* from t_emp e where dept_id = (select id from t_dept where name = '銷售部');

或者使用關(guān)聯(lián)查詢
select e.* from t_emp e join t_dept d on e.dept_id = d.id where d.name='銷售部';

ORM實(shí)現(xiàn)如下：

• 使用 .scalar_subquery() 方法明確地產(chǎn)生一個(gè)標(biāo)量子查詢
• 注意：代碼可能會(huì)出現(xiàn)警告但是可以正常執(zhí)行，因?yàn)镾QLAlchemy 建議在 in_() 方法中明確傳遞一個(gè) select() 構(gòu)造，而不是直接將子查詢對(duì)象傳遞給 in_() 方法。

# 1.1、先查詢出銷售部的部門id
subquery = session.query(Departments.id).filter(Departments.name=="銷售部").scalar_subquery()
# 可能會(huì)出現(xiàn)警告，可以將子查詢轉(zhuǎn)換成 select 語(yǔ)句
# subquery = select([Departments.id]).where(Departments.name == "銷售部")
# 1.2、然后根據(jù)查出的部門id再查詢銷售部的所有員工信息
res = session.query(Employes).filter(Employes.dept_id.in_(subquery)).all()
print(res)

2、查詢?cè)趩T工“林逸”之后入職的所有員工信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢出林逸的入職時(shí)間
select entrydate from t_emp where name = '林逸';

2、然后根據(jù)入職時(shí)間再查找出在林逸入職時(shí)間之后的員工信息
select e.* from t_emp e where entrydate > (select entrydate from t_emp where name = '林逸');

ORM實(shí)現(xiàn)如下：

subquery2 = session.query(Employes.entrydate).filter_by(name="林逸").scalar_subquery()
res2 = session.query(Employes).filter(Employes.entrydate > subquery2)
print(res2)

6.3、列子查詢

說(shuō)明：子查詢返回的結(jié)果是一列（可以是多行）

常用操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范圍之內(nèi)，多選一
NOT IN	不在指定的集合范圍之內(nèi)
ANY	子查詢返回列表中，有任意一個(gè)滿足即可
SOME	與ANY等同，使用SOME的地方都可以使用ANY
ALL	子查詢返回列表的所有值都必須滿足

示列：

1、查詢 "銷售部" 和 "市場(chǎng)部" 的所有員工信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先在部門表中查詢出銷售部和市場(chǎng)部的部門id
select id from t_dept where name = '銷售部' or name = '市場(chǎng)部';

2、然后根據(jù)1查出的結(jié)果，使用關(guān)鍵字in查詢出對(duì)應(yīng)銷售部和市場(chǎng)部的員工信息1
select * from t_emp where dept_id in (select id from t_dept where name = '銷售部' or name = '市場(chǎng)部');

ORM實(shí)現(xiàn)如下：

subquery = session.query(Departments.id).filter(or_(Departments.name=="銷售部", Departments.name=="市場(chǎng)部")).subquery()
res = session.query(Employes).filter(Employes.id.in_(subquery))

2、 查詢比“財(cái)務(wù)部”所有人工資都高的員工信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢出財(cái)務(wù)部所有人員的工資信息
select salary from t_emp e join t_dept d on e.dept_id = d.id where d.name = '財(cái)務(wù)部';?

2、然后根據(jù)1查出的結(jié)果，使用關(guān)鍵字all查詢出比財(cái)務(wù)部所有人工資都高的員工信息
select * from t_emp ?where salary > all (select salary from t_emp e join t_dept d on e.dept_id = d.id where d.name = '財(cái)務(wù)部');
或
select * from t_emp where salary > all ( select salary from t_emp where dept_id =(select id from t_dept where name = '財(cái)務(wù)部') );

ORM實(shí)現(xiàn)如下：

• 使用 filter() 方法結(jié)合 all_() 函數(shù)來(lái)實(shí)現(xiàn)

subquery2 = session.query(Employes.salary).join(Departments).filter(Departments.name=="財(cái)務(wù)部").scalar_subquery()
res2 = session.query(Employes).filter(Employes.salary > all_(subquery2)).all()

3、 查詢比“研發(fā)部”其中任意一人工資都高的員工信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢出研發(fā)部所有人員的工資信息
select salary from t_emp where dept_id = (select id from t_dept where name = '研發(fā)部');

2、然后根據(jù)1查出的結(jié)果，使用關(guān)鍵字any查詢出比研發(fā)部任意一人工資都高的員工信息
select * from t_emp where salary > any (select salary from t_emp where dept_id = (select id from t_dept where name = '研發(fā)部'));

ORM實(shí)現(xiàn)如下：

• 使用 filter() 方法結(jié)合 any_() 函數(shù)來(lái)實(shí)現(xiàn)

subquery3 = session.query(Employes.salary).join(Departments).filter(Departments.name == "研發(fā)部").scalar_subquery()
res3 = session.query(Employes).filter(Employes.salary > any_(subquery3)).all()

6.4、行子查詢

說(shuō)明：子查詢返回的結(jié)果是一行（可以是多列）

常用的操作符：= 、<> 、IN 、NOT IN

示列：

1、 查詢與 "張敏" 的薪資及直屬領(lǐng)導(dǎo)相同的員工信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢出張敏的薪資和直屬領(lǐng)導(dǎo)的id
select salary,managerid from t_emp where name = '張敏';

2、然后根據(jù)1查出的結(jié)果，使用 = 查詢與"張敏"的薪資及直屬領(lǐng)導(dǎo)相同的員工信息
select * from t_emp where (salary,managerid) = (select salary,managerid from t_emp where name = '張敏');

ORM實(shí)現(xiàn)如下：

• 使用 filter() 方法結(jié)合 tuple_() 函數(shù)來(lái)實(shí)現(xiàn)

subquery = session.query(Employes.salary, Employes.managerid).filter_by(name="張敏").subquery()
res = session.query(Employes).filter(tuple_(Employes.salary,Employes.managerid).in_(subquery)).all()

6.5、表子查詢

說(shuō)明：子查詢返回的結(jié)果是多行多列

常用的操作符：IN

示列：

1、查詢與 "林夕" , "林妙妙" 的職位和薪資相同的員工信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢林夕和林妙妙的職位與薪資信息
select job, salary from t_emp where name in ('林夕','林妙妙');

2、然后根據(jù)1查出的結(jié)果，使用 in 查詢與"林夕","林妙妙"的職位和薪資相同的員工信息
select * from t_emp where (job,salary) in (select job, salary from t_emp where name in ('林夕','林妙妙'));

ORM實(shí)現(xiàn)如下：

• 使用 filter() 方法結(jié)合 tuple_() 函數(shù)來(lái)實(shí)現(xiàn)

subquery = session.query(Employes.job, Employes.salary).filter(Employes.name.in_(["林夕", "林妙妙"])).subquery()
res = session.query(Employes).filter(tuple_(Employes.job, Employes.salary).in_(subquery)).all()

2、查詢?nèi)肼毴掌谑?"2002-09-12" 之后的員工信息 , 及其部門信息

SQL實(shí)現(xiàn)如下：

分析：可先將查詢分為兩步
1、先查詢出入職日期是 "2002-09-12" 之后的員工信息?
select * from t_emp where entrydate > "2002-09-12";

2、根據(jù)1查詢出的表信息，在查詢對(duì)應(yīng)的部門信息
select e.*, d.* from (select * from t_emp where entrydate > "2002-09-12") e join t_dept d on e.dept_id = d.id;

ORM實(shí)現(xiàn)如下：

• 使用join()實(shí)現(xiàn)

subquery2 = session.query(Employes).filter(Employes.entrydate > "2002-09-12").subquery()
res2 = session.query(subquery2, Departments).join(Departments, subquery2.c.dept_id == Departments.id).all()