服务器之家

服务器之家 > 正文

详解python的ORM中Pony用法

时间：2021-01-13 00:56     来源/作者：ll513701

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.degree
FROM person p
WHERE 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 datetime
import pony.orm as pny
import 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 file
database = 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 mode
pny.sql_debug(True)   # 显示debug信息(sql语句)
 
# map the models to the database
# and create the tables, if they don't exist
database.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 POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE 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
 
COMMIT
PRAGMA foreign_keys = true
CLOSE 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 datetime
import pony.orm as pny
from models import Album, Artist
from database import PonyDatabase
 
 
# ----------------------------------------------------------------------
@pny.db_session
def 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 pny
 
from models import Artist, Album
from database import PonyDatabase
 
db = 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 pny
from models import Artist
with pny.db_session:
  band = Artist.get(name="MXPX")
  band.delete()

Pony补充

可以连接的数据库：

?
1
2
3
4
5
6
7
8
##postgres
db.bind('postgres', user='', password='', host='', database='')
##sqlite     create_db:如果数据库不存在创建数据库文件
db.bind('sqlite', 'filename', create_db=True)
##mysql
db.bind('mysql', host='', user='', passwd='', db='')
##Oracle
db.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-Optional
 
class 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 3
 
attr2 = Required(bytes) # Python 3 only
 
#字符串长度,不写默认为255
name = Required(str,40)   #VARCHAR(40)
 
#整数的大小，默认2bit
attr1 = Required(int, size=8)  # 8 bit - TINYINT in MySQL
attr2 = Required(int, size=16) # 16 bit - SMALLINT in MySQL
attr3 = Required(int, size=24) # 24 bit - MEDIUMINT in MySQL
attr4 = Required(int, size=32) # 32 bit - INTEGER in MySQL
attr5 = 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实例
'''
 
# limit
persons [1:5]
 
# show
persons.show()
 
# 生成器表达式查询，然后解析AST树的方式构造SQL语句
 
select(p for p in Person)
#和Person.select()一样返回Query object
 
select((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) #25
 
select(p.age for p in Person).max() #25
#-----------------------------
# 修改实例
@db_session
def update_persons():
 p = Person.get(id=2)
 p.page = 1000
 commit()
 
# 删除
@db_session
def 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

标签：
Python ORM Pony 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
Intellij idea2020永久破解，亲测可用！！！
Intellij idea2020永久破解，亲测可用！！！ 2020-07-29
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部