Pony是Python的一种ORM，它允许使用生成器表达式来构造查询，通过将生成器表达式的抽象语法树解析成SQL语句。它也有在线ER图编辑器可以帮助你创建Model。
示例分析
Pony语句：
|
1
|
select(p for p in Person if p.age > 20) |
翻译成sql语句就是：
|
1
2
3
4
|
SELECT p.id, p.name, p.age, p.classtype, p.mentor, p.gpa, p.degreeFROM person pWHERE p.classtype IN ('Student', 'Professor', 'Person')AND p.age > 20 |
Pony语句：
|
1
2
|
select(c for c in Customer if sum(c.orders.price) > 1000) |
翻译成sql语句就是:
|
1
2
3
4
5
6
|
SELECT "c"."id"FROM "Customer" "c" LEFT JOIN "Order" "order-1" ON "c"."id" = "order-1"."customer"GROUP BY "c"."id"HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000 |
安装Pony
|
1
|
pip install pony |
使用Pony
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
#!/usr/bin/env python#-*- coding:utf-8 -*-import datetimeimport pony.orm as pnyimport sqlite3# conn = sqlite3.connect('D:\日常python学习PY2\Pony学习\music.sqlite')# print conn# database = pny.Database()# database.bind("sqlite","music.sqlite",create_db=True)# 路径建议写绝对路径。我这边开始写相对路径报错 unable to open database filedatabase = pny.Database("sqlite","D:\日常python学习PY2\Pony学习\music.sqlite",create_db=True)########################################################################class Artist(database.Entity): """ Pony ORM model of the Artist table """ name = pny.Required(unicode) #被外键关联 albums = pny.Set("Album")########################################################################class Album(database.Entity): """ Pony ORM model of album table """ #外键字段artlist,外键关联表Artist，Artist表必须写Set表示被外键关联 #这个外键字段默认就是index=True,除非自己指定index=False才不会创建索引，索引名默认为[idx_表名__字段](artist) artist = pny.Required(Artist) title = pny.Required(unicode) release_date = pny.Required(datetime.date) publisher = pny.Required(unicode) media_type = pny.Required(unicode)# turn on debug modepny.sql_debug(True) # 显示debug信息(sql语句)# map the models to the database# and create the tables, if they don't existdatabase.generate_mapping(create_tables=True) # 如果数据库表没有创建表 |
运行之后生成sqlite如下:
上述代码对应的sqlite语句是：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
GET CONNECTION FROM THE LOCAL POOLPRAGMA foreign_keys = falseBEGIN IMMEDIATE TRANSACTIONCREATE TABLE "Artist" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL) CREATE TABLE "Album" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "artist" INTEGER NOT NULL REFERENCES "Artist" ("id"), "title" TEXT NOT NULL, "release_date" DATE NOT NULL, "publisher" TEXT NOT NULL, "media_type" TEXT NOT NULL) CREATE INDEX "idx_album__artist" ON "Album" ("artist") SELECT "Album"."id", "Album"."artist", "Album"."title", "Album"."release_date", "Album"."publisher", "Album"."media_type"FROM "Album" "Album"WHERE 0 = 1 SELECT "Artist"."id", "Artist"."name"FROM "Artist" "Artist"WHERE 0 = 1 COMMITPRAGMA foreign_keys = trueCLOSE CONNECTION |
插入/增加数据
源码地址：https://github.com/flowpig/daily_demos
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
#!/usr/bin/env python#-*- coding:utf-8 -*-import datetimeimport pony.orm as pnyfrom models import Album, Artistfrom database import PonyDatabase# ----------------------------------------------------------------------@pny.db_sessiondef add_data(): """""" new_artist = Artist(name=u"Newsboys") bands = [u"MXPX", u"Kutless", u"Thousand Foot Krutch"] for band in bands: artist = Artist(name=band) album = Album(artist=new_artist, title=u"Read All About It", release_date=datetime.date(1988, 12, 01), publisher=u"Refuge", media_type=u"CD") albums = [{"artist": new_artist, "title": "Hell is for Wimps", "release_date": datetime.date(1990, 07, 31), "publisher": "Sparrow", "media_type": "CD" }, {"artist": new_artist, "title": "Love Liberty Disco", "release_date": datetime.date(1999, 11, 16), "publisher": "Sparrow", "media_type": "CD" }, {"artist": new_artist, "title": "Thrive", "release_date": datetime.date(2002, 03, 26), "publisher": "Sparrow", "media_type": "CD"} ] for album in albums: a = Album(**album)if __name__ == "__main__": db = PonyDatabase() db.bind("sqlite", "D:\日常python学习PY2\Pony学习\music.sqlite", create_db=True) db.generate_mapping(create_tables=True) add_data() # use db_session as a context manager with pny.db_session: a = Artist(name="Skillet")'''您会注意到我们需要使用一个装饰器db_session来处理数据库。 它负责打开连接，提交数据并关闭连接。 你也可以把它作为一个上下文管理器，with pny.db_session''' |
更新数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
#!/usr/bin/env python#-*- coding:utf-8 -*-import pony.orm as pnyfrom models import Artist, Albumfrom database import PonyDatabasedb = PonyDatabase()db.bind("sqlite", "D:\日常python学习PY2\Pony学习\music.sqlite", create_db=True)db.generate_mapping(create_tables=True)with pny.db_session: band = Artist.get(name="Newsboys") print band.name for record in band.albums: print record.title # update a record band_name = Artist.get(name="Kutless") band_name.name = "Beach Boys" #使用生成器形式查询 ''' result = pny.select(i.name for i in Artist) result.show() 结果： i.name -------------------- Newsboys MXPX Beach Boys Thousand Foot Krutch Skillet ''' |
删除记录
|
1
2
3
4
5
|
import pony.orm as pnyfrom models import Artistwith pny.db_session: band = Artist.get(name="MXPX") band.delete() |
Pony补充
可以连接的数据库：
|
1
2
3
4
5
6
7
8
|
##postgresdb.bind('postgres', user='', password='', host='', database='')##sqlite create_db:如果数据库不存在创建数据库文件db.bind('sqlite', 'filename', create_db=True)##mysqldb.bind('mysql', host='', user='', passwd='', db='')##Oracledb.bind('oracle', 'user/password@dsn') |
Entity(实体)类似mvc里面的model
在创建实体实例之前，需要将实体映射到数据库表，生成映射后，可以通过实体查询数据库并创建新的实例。db.Entity自己定义新的实体必须从db.Entity继承
属性
|
1
2
3
4
5
|
class Customer(db.Entity): name = Required(str) picture = Optional(buffer)sql_debug(True) # 显示debug信息(sql语句)db.generate_mapping(create_tables=True) # 如果数据库表没有创建表 |
属性类型
- Required
- Optional
- PrimaryKey
- Set
Required and Optional
通常实体属性分为Required(必选)和Optional(可选)
PrimaryKey(主键)
默认每个实体都有一个主键，默认添加了id=PrimaryKey(int,auto=True)属性
|
1
2
3
4
5
6
7
8
9
10
11
12
|
class Product(db.Entity): name = Required(str, unique=True) price = Required(Decimal) description = Optional(str) #等价于下面class Product(db.Entity): id = PrimaryKey(int, auto=True) name = Required(str, unique=True) price = Required(Decimal) description = Optional(str) |
Set
定义了一对一，一对多，多对多等数据结构
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
# 一对一class User(db.Entity): name = Required(str) cart = Optional("Cart") #必须Optional-Required or Optional-Optionalclass Cart(db.Entity): user = Required("User") # 多对多class Student(db.Entity): name = pny.Required(str) courses = pny.Set("Course")class Course(db.Entity): name = pny.Required(str) semester = pny.Required(int) students = pny.Set(Student) pny.PrimaryKey(name, semester) #联合主键pny.sql_debug(True) # 显示debug信息(sql语句)db.generate_mapping(create_tables=True) # 如果数据库表没有创建表#-------------------------------------------------------#一对多class Artist(database.Entity): """ Pony ORM model of the Artist table """ name = pny.Required(unicode) #被外键关联 albums = pny.Set("Album")class Album(database.Entity): """ Pony ORM model of album table """ #外键字段artlist,外键关联表Artist，Artist表必须写Set表示被外键关联 #这个外键字段默认就是index=True,除非自己指定index=False才不会创建索引，索引名默认为[idx_表名__字段](artist) artist = pny.Required(Artist) #外键字段(数据库显示artist) title = pny.Required(unicode) release_date = pny.Required(datetime.date) publisher = pny.Required(unicode) media_type = pny.Required(unicode)# Compositeindexes(复合索引)class Example1(db.Entity): a = Required(str) b = Optional(int) composite_index(a, b) #也可以使用字符串composite_index(a, 'b') |
属性数据类型
格式为 ：
属性名 = 属性类型(数据类型)
- str
- unicode
- int
- float
- Decimal
- datetime
- date
- time
- timedelta
- bool
- buffer ---used for binary data in Python 2 and 3
- bytes ---used for binary data in Python 3
- LongStr ---used for large strings
- LongUnicode ---used for large strings
- UUID
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
attr1 = Required(str)# 等价attr2 = Required(unicode)attr3 = Required(LongStr)# 等价attr4 = Required(LongUnicode)attr1 = Required(buffer) # Python 2 and 3attr2 = Required(bytes) # Python 3 only#字符串长度,不写默认为255name = Required(str,40) #VARCHAR(40)#整数的大小，默认2bitattr1 = Required(int, size=8) # 8 bit - TINYINT in MySQLattr2 = Required(int, size=16) # 16 bit - SMALLINT in MySQLattr3 = Required(int, size=24) # 24 bit - MEDIUMINT in MySQLattr4 = Required(int, size=32) # 32 bit - INTEGER in MySQLattr5 = Required(int, size=64) # 64 bit - BIGINT in MySQL#无符号整型attr1 = Required(int, size=8, unsigned=True) # TINYINT UNSIGNED in MySQL# 小数和精度price = Required(Decimal, 10, 2) #DECIMAL(10,2)# 时间dt = Required(datetime,6)# 其它参数unique 是否唯一auto 是否自增default 默认值sql_default created_at = Required(datetime, sql_default='CURRENT_TIMESTAMP')index 创建索引index='index_name' 指定索引名称lazy 延迟加载的属性加载对象cascade_delete 关联删除对象column 映射到数据库的列名columns Set(多对多列名)table 多对多中间表的表名字nullable 允许该列为空py_check 可以指定一个函数，检查数据是否合法和修改数据class Student(db.Entity): name = Required(str) gpa = Required(float, py_check=lambda val: val >= 0 and val <= 5) |
实例操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
# 获取实例p = Person.get(name="Person") #返回单个实例，如同Django ORM的get#------------------------------# 查询persons = Person.select()'''select并没有连接数据库查询，只是返回一个Query object，调用persons[:]返回所有Person实例'''# limitpersons [1:5]# showpersons.show()# 生成器表达式查询，然后解析AST树的方式构造SQL语句select(p for p in Person) #和Person.select()一样返回Query objectselect((p.id, p.name) for p in Person)[:]# 带where条件查询select((p.id, p.name) for p in Person if p.age ==20)[:]# 分组聚合查询select((max(p.age)) for p in Person)[:] #[25]max(p.age for p in Person) #25select(p.age for p in Person).max() #25#-----------------------------# 修改实例@db_sessiondef update_persons(): p = Person.get(id=2) p.page = 1000 commit() # 删除@db_sessiondef delete_persons(): p = Person.get(id=2) p.delete() commit() |
pony使用还可以使用游标操作(这样就可以写原生sql语句了)
|
1
2
|
result = db.execute('''select name from Artist''')print result.fetchall() |
类似Django ORM的save函数
|
1
2
3
4
5
6
7
8
9
10
11
12
|
before_insert()Is called only for newly created objects before it is inserted into the database.before_update()Is called for entity instances before updating the instance in the database.before_delete()Is called before deletion the entity instance in the database.after_insert()Is called after the row is inserted into the database.after_update()Is called after the instance updated in the database.after_delete()Is called after the entity instance is deleted in the database. |
例如：
|
1
2
3
4
5
|
class Message(db.Entity): title = Required(str) content = Required(str) def before_insert(self): print("Before insert! id="codetool">
原文链接：https://www.cnblogs.com/liao-lin/p/8433785.html 延伸 · 阅读
精彩推荐
最近更新 编辑推荐
阅读排行 |
