Pony 是 Python 的一种 ORM, 它允许使用生成器表达式来构造查询, 通过将生成器表达式的抽象语法树解析成 SQL 语句它也有在线 ER 图编辑器可以帮助你创建 Model
示例分析
Pony 语句:
select(p for p in Person if p.age> 20)
翻译成 sql 语句就是:
- 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 语句:
- select(c for c in Customer
- if sum(c.orders.price) > 1000)
翻译成 sql 语句就是:
- 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
pip install pony
使用 Pony
- #!/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 语句是:
- 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
- #!/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
'''
更新数据
- #!/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
- '''
删除记录
- import pony.orm as pny
- from models import Artist
- with pny.db_session:
- band = Artist.get(name="MXPX")
- band.delete()
Pony 补充
可以连接的数据库:
- ##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 继承
属性
- 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) 属性
- 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
定义了一对一, 一对多, 多对多等数据结构
- # 一对一
- 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
- 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)
- # 整数的大小, 默认 32bit
- 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)
实例操作
- #获取实例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 语句了)
- result = db.execute('''select name from Artist''')
- print result.fetchall()
类似 Django ORM 的 save 函数
- 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.
例如:
- class Message(db.Entity):
- title = Required(str)
- content = Required(str)
- def before_insert(self):
- print("Before insert! title=%s" % self.title)
参考资料: http://www.blog.pythonlibrary.org/2014/07/21/python-101-an-intro-to-pony-orm/
来源: https://www.cnblogs.com/liao-lin/p/8433785.html