實(shí)體類的高級(jí)定義
假設(shè)我們有Student（學(xué)生）畦木，Classroom（班級(jí)）和MasterTeacher（班主任）三個(gè)類非区。他們之間的關(guān)系如下：
- 一個(gè)班級(jí)只有一個(gè)班主任
- 一個(gè)班級(jí)有多名學(xué)生
- 一個(gè)班主任只管理一個(gè)班級(jí)
- 一個(gè)班主任管理多名學(xué)生
- 一個(gè)學(xué)生只屬于一個(gè)班級(jí)并且只有一個(gè)班主任
根據(jù)上面的需求，我們進(jìn)行了如下的定義
聯(lián)合主鍵
class Student(db.Entity):
"""學(xué)生"""
_table_ = "student"
name = Required(str, max_len=40)
master_teacher = Required("MasterTeacher") # 班主任
classroom = Required("Classroom") # 班級(jí)
class Classroom(db.Entity):
"""班級(jí)"""
_table_ = "classroom"
name = Required(str, max_len=40)
master_teacher = Optional("MasterTeacher") # 班主任
students = Set(Student) # 學(xué)生
class MasterTeacher(db.Entity):
"""班主任"""
_table_ = "master_teacher"
name = Required(str, max_len=40)
classroom = Required(Classroom) # 班級(jí)
students = Set(Student) # 學(xué)生
定義以后，有了一個(gè)新的問題：
有班主任同名的現(xiàn)象，這樣單依賴名字就無法區(qū)別老師了玻靡，（比如三一班和三二班的班主任都叫張三）于是提出用老師的名字+班級(jí) 確認(rèn)老師的唯一性，這個(gè)問題就解決了（三一班的張三老師和三二班的張三老師）中贝，這時(shí)候囤捻，我們就要用到聯(lián)合主鍵了
不要在意上面的需求是否合理，我們提出上述假設(shè)的需求的目的只是為了演示一些pony的高級(jí)用法雄妥。
修改MasterTeacher類的定義
class MasterTeacher(db.Entity):
"""班主任"""
_table_ = "master_teacher"
name = Required(str, max_len=40)
classroom = Required(Classroom) # 班級(jí)
students = Set(Student) # 學(xué)生
PrimaryKey(name, classroom) # 定義一個(gè)聯(lián)合主鍵
執(zhí)行
db.generate_mapping(create_tables=True) # 生成實(shí)體最蕾，表和映射關(guān)系
屏幕截圖_1.png
我們會(huì)發(fā)現(xiàn)數(shù)據(jù)庫中的主鍵不再是id依溯，而是name和classroom生成的聯(lián)合主鍵了老厌。
聯(lián)合輔助鍵
如果你需要一個(gè)聯(lián)合輔助鍵,那么只需要把PrimaryKey換成composite_key即可。
class MasterTeacher(db.Entity):
"""班主任"""
_table_ = "master_teacher"
name = Required(str, max_len=40)
classroom = Required(Classroom) # 班級(jí)
students = Set(Student) # 學(xué)生
composite_key(name, classroom) # 聯(lián)合輔助鍵
屏幕截圖_4.png
觀察數(shù)據(jù)庫黎炉，你會(huì)發(fā)現(xiàn)master_teacher的主鍵依然是id枝秤，只是多個(gè)一個(gè)而是name和classroom生成的聯(lián)合輔助鍵
聯(lián)合索引
composite_index(name, classroom) # 聯(lián)合索引
聯(lián)合唯一索引
composite_key(name, classroom) # 聯(lián)合唯一索引
屬性定義
屬性定義有很多參數(shù)，用于對(duì)字段進(jìn)行約束或者驗(yàn)證慷嗜。下面介紹一些常用的屬性定義淀弹，注意，屬性定義往往只在特定的字段類型上生效庆械！
- max_len: (int), 字段長度薇溃，默認(rèn)值：255，適用字段類型（str）缭乘，max_len=40 ? varchar(40)
- unique： （bool）唯一沐序，默認(rèn)值：False，適用字段類型（任意）
- auto： （bool）唯一堕绩，默認(rèn)值：True策幼，僅可用于PrimaryKey屬性。
- autostrip： （bool）去掉字符串頭尾的不可見字符奴紧，默認(rèn)值：True特姐，適用字段類型（str）
- column： （str）列名/字段名，默認(rèn)值：屬性名黍氮，可用于非Set屬性唐含。
- cascade_delete： （bool）級(jí)聯(lián)刪除，默認(rèn)值：None沫浆，僅可用于外鍵屬性捷枯。
- default： （numeric | str | functio）聯(lián)默認(rèn)值，沒有默認(rèn)值件缸，注意铜靶，這個(gè)默認(rèn)值的設(shè)置不會(huì)寫入數(shù)據(jù)庫的列定義中。
- index： （bool | str）允許控制此列的索引創(chuàng)建。index=True-將使用默認(rèn)名稱創(chuàng)建索引争剿。index='index_name'-用指定名稱創(chuàng)建索引已艰。index=False–跳過索引創(chuàng)建，適用字段類型（任意）
- lazy： （bool）懶加載蚕苇，默認(rèn)值：True哩掺，適用字段類型（任意）.加載對(duì)象時(shí)推遲加載屬性值。除非您嘗試直接訪問此屬性涩笤，否則不會(huì)加載該值嚼吞。
- max： （numeric）最大值，默認(rèn)值：無蹬碧，適用字段類型（int舱禽，float，Decimal）.設(shè)置允許的最大值恩沽。
- min： （numeric）最小值誊稚，默認(rèn)值：無，適用字段類型（int罗心，float里伯，Decimal）.設(shè)置允許的最小值。
- reverse： （str）反向引用渤闷，默認(rèn)值：無疾瓮，僅適用于關(guān)系映射字段.設(shè)置關(guān)系的另一端指定應(yīng)用于關(guān)系的屬性名稱
- reverse_column： （str）反向引用的列名稱，默認(rèn)值：無飒箭，僅適用于多對(duì)多關(guān)系映射字段.設(shè)置中間表指定數(shù)據(jù)庫列的名稱狼电。
- nullable： （bool）允許空值？补憾，默認(rèn)值：False砌庄，適用字段類型（任意）.改字段是否允許為空吕座？您很可能不需要指定此選項(xiàng)井氢，因?yàn)镻ony默認(rèn)將其設(shè)置為最合適的值虹脯。
- unsigned： （bool）無符號(hào)？削饵，默認(rèn)值：False岩瘦，適用字段類型（int，float窿撬，Decimal）.字段是否有符號(hào)启昧？是否區(qū)分正負(fù)，這影響字段的大小范圍
- sql_type： （str）數(shù)據(jù)庫類型劈伴，默認(rèn)值：無密末，適用字段類型（任意）。設(shè)置列的特定SQL類型。
- table： （str）中間表的名稱严里，默認(rèn)值：pony默認(rèn)新啼，僅適用于多對(duì)多關(guān)系。指定中間表的名稱刹碾。
- size： （int）反向引用燥撞，默認(rèn)值：32，僅適用于int類型迷帜，指定應(yīng)在數(shù)據(jù)庫中使用的整數(shù)類型的大小物舒。此參數(shù)接收應(yīng)用于表示數(shù)據(jù)庫中整數(shù)的位數(shù)。允許值為8戏锹、16冠胯、24、32和64景用，對(duì)于mysql：
- size=8 ? TINYINT(4)
- size=16 ? SMALLINT(6)
- size=24 ? MEDIUMINT(9)
- size=32 ? INT(11)
- size=64 ? BIGINT(20)
連接查詢
我們按照連接方式分為自然連接涵叮，左連接和右連接，我們分別就這3種連接進(jìn)行示范和說明伞插。
定義2個(gè)實(shí)體類，Boy和Girl盾碗，分別代表男孩和女孩媚污。
- Tom和John是男孩
- Abby和Anna是女孩
- Tom和Abby是情侶
- John和Anna是單身??
實(shí)體類代碼如下：
class Girl(db.Entity):
name = Required(str, max_len=40) # 名字
lover = Optional("Boy", column="boy_id", nullable=True, default=None, reverse="lover") # 愛人
class Boy(db.Entity):
name = Required(str, max_len=40) # 名字
lover = Optional(Girl, nullable=True, default=None, reverse="lover") # 愛人
自然連接
把不是單身??的小兩口的名字打印出來。
with db_session(sql_debug=True):
query = select((x.name, x.lover.name) for x in Boy)
for x in query:
print(x)
sql_debug 你很容易發(fā)現(xiàn)多了一個(gè)參數(shù)廷雅，這個(gè)參數(shù)的目的就是為了在操作數(shù)據(jù)庫時(shí)耗美，把生成的語句打印出來，這樣做的目的是： 一旦發(fā)現(xiàn)查詢結(jié)果不對(duì)航缀，我們可以參照打印出來的sql語句來判斷自己的表達(dá)式是否拼寫錯(cuò)誤商架。
執(zhí)行的結(jié)果是這樣的
SELECT DISTINCT `x`.`name`, `girl`.`name`
FROM `boy` `x`, `girl` `girl`
WHERE `x`.`id` = `girl`.`boy_id`
('Tom', 'Abby')
COMMIT
RELEASE CONNECTION
Process finished with exit code 0
-
SELECT DISTINCT
x.name,girl.nameFROMboyx,girlgirlWHEREx.id=girl.boy_id就是打印出來的sql語句。注意DISTINCT這個(gè)關(guān)鍵字芥玉，表示結(jié)果去重了蛇摸。 - ('Tom', 'Abby') 查詢結(jié)果，這個(gè)沒什么好說的灿巧。
左連接
把所有的男孩子包括他們的情侶的名字打印出來赶袄。
with db_session(sql_debug=True):
query = left_join((x.name, x.lover.name) for x in Boy)
for x in query:
print(x)
執(zhí)行的結(jié)果是這樣的
GET NEW CONNECTION
SELECT DISTINCT `x`.`name`, `girl`.`name`
FROM `boy` `x`
LEFT JOIN `girl` `girl`
ON `x`.`id` = `girl`.`boy_id`
('Tom', 'Abby')
('John', None)
COMMIT
RELEASE CONNECTION
Process finished with exit code 0
- 注意語句中的左連接動(dòng)詞**LEFT JOIN **
- 查詢結(jié)果中，沒有女朋友的人的名字也打印出來了抠藕。
右連接
pony中沒有提供右連接方法饿肺，如果需要類似的功能，你需要把查詢的對(duì)象反過來即可盾似。
把所有的女孩子包括他們的情侶的名字打印出來敬辣。
with db_session(sql_debug=True):
query = left_join((x.name, x.lover.name) for x in Girl)
for x in query:
print(x)
查詢結(jié)果
GET NEW CONNECTION
SELECT DISTINCT `x`.`name`, `boy`.`name`
FROM `girl` `x`
LEFT JOIN `boy` `boy`
ON `x`.`boy_id` = `boy`.`id`
('Abby', 'Tom')
('Anna', None)
COMMIT
RELEASE CONNECTION
Process finished with exit code 0
查詢實(shí)例
下面我們使用例子來說明一些常用的查詢函數(shù)的使用方法
實(shí)體類Worker的定義如下：
from decimal import Decimal
class Worker(db.Entity):
"""工人"""
name = Required(str, max_len=40) # 名字
sex = Required(str, max_len=16) # 性別
age = Required(int, size=8) # 年齡
salary = Required(Decimal) # 月薪
表中有如下數(shù)據(jù)
屏幕截圖_8.png
條件查詢
- 查詢年齡大于等于22小于等于35的男性工人。
with db_session:
query = select(x for x in Worker if between(x.age, 22, 35) and x.sex == "男")
for x in query:
print(x.to_dict())
- 查詢年齡大于30歲的工人的平均工資。
with db_session:
query = avg(x.salary for x in Worker if x.age > 30)
print(query)
- 查詢年齡大于30歲的工人的最高/最低工資溉跃。
max(x.salary for x in Worker if x.age > 30)
min(x.salary for x in Worker if x.age > 30)
- 統(tǒng)計(jì)年齡大于30歲的工人的人數(shù)
count(x for x in Worker if x.age > 30)
或者
select(x.salary for x in Worker if x.age > 30).count()
- 以年齡大小正/倒序查詢工人信息
select(x for x in Worker).order_by(lambda x: x.age)
select(x for x in Worker).order_by(lambda x: desc(x.age))
或者
select(x for x in Worker).order_by(Worker.age)
select(x for x in Worker).order_by(desc(Worker.age))
或者
select(x for x in Worker).order_by("x.age")
select(x for x in Worker).order_by(desc("x.age"))
- 先以年齡倒序排列汰聋，如果年齡相同，就以月薪倒序排列,也就是我們平時(shí)說的多重排序
select(x for x in Worker).order_by(desc(Worker.age)).order_by(desc(Worker.salary))
- 分頁查詢
select(x for x in Worker).page(pagenum=1, pagesize=2)
- 根據(jù)年齡正序排列喊积，限制輸出前3個(gè)烹困。
select(x for x in Worker).order_by(desc(Worker.age)).limit(3)
或者
select(x for x in Worker).order_by(desc(Worker.age))[:3]
不要以為第二種方法是先查詢出來全部數(shù)據(jù)再對(duì)結(jié)果進(jìn)行切片的，pony明白操作者的意圖乾吻，會(huì)對(duì)sql語句進(jìn)行優(yōu)化髓梅。實(shí)際上兩種方式生成的sql語句是一模一樣的??
SELECTx.id,x.name,x.sex,x.age,x.salary
FROMworkerx
ORDER BYx.ageDESC LIMIT 3
- 統(tǒng)計(jì)所有工人的工資之和
sum(x.salary for x in Worker)
- 計(jì)算平均工資
avg(x.salary for x in Worker)
或者
select(x.salary for x in Worker).avg()
- 查詢名字以T開頭的工人
select(x for x in Worker).where(lambda x: x.name.startswith("T"))
- 查詢名字以a結(jié)束的工人
select(x for x in Worker).where(lambda x: x.name.endswith("a"))
- 查詢名字中有m這個(gè)字母的工人
select(x for x in Worker).where(lambda x: "m" in x.name)
- 使用原生語句查詢
select(x for x in Worker).where(lambda x: raw_sql("x.name = 'Tom'"))
