溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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 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')
屬性數據類型
格式為 ：
屬性名 = 屬性類型(數據類型)
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)
實例操作
# 獲取實例 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)
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
