您好,登錄后才能下訂單哦!
Pony是Python的一種ORM,它允許使用生成器表達(dá)式來構(gòu)造查詢,通過將生成器表達(dá)式的抽象語法樹解析成SQL語句。它也有在線ER圖編輯器可以幫助你創(chuàng)建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學(xué)習(xí)PY2\Pony學(xué)習(xí)\music.sqlite') # print conn # database = pny.Database() # database.bind("sqlite","music.sqlite",create_db=True) # 路徑建議寫絕對路徑。我這邊開始寫相對路徑報(bào)錯(cuò) unable to open database file database = pny.Database("sqlite","D:\日常python學(xué)習(xí)PY2\Pony學(xué)習(xí)\music.sqlite",create_db=True) ######################################################################## class Artist(database.Entity): """ Pony ORM model of the Artist table """ name = pny.Required(unicode) #被外鍵關(guān)聯(lián) albums = pny.Set("Album") ######################################################################## class Album(database.Entity): """ Pony ORM model of album table """ #外鍵字段artlist,外鍵關(guān)聯(lián)表Artist,Artist表必須寫Set表示被外鍵關(guān)聯(lián) #這個(gè)外鍵字段默認(rèn)就是index=True,除非自己指定index=False才不會(huì)創(chuàng)建索引,索引名默認(rèn)為[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) # 如果數(shù)據(jù)庫表沒有創(chuàng)建表
運(yùn)行之后生成sqlite如下:
上述代碼對應(yīng)的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
插入/增加數(shù)據(jù)
源碼地址: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學(xué)習(xí)PY2\Pony學(xué)習(xí)\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") ''' 您會(huì)注意到我們需要使用一個(gè)裝飾器db_session來處理數(shù)據(jù)庫。 它負(fù)責(zé)打開連接,提交數(shù)據(jù)并關(guān)閉連接。 你也可以把它作為一個(gè)上 下文管理器,with pny.db_session '''
更新數(shù)據(jù)
#!/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學(xué)習(xí)PY2\Pony學(xué)習(xí)\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() 結(jié)果: 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補(bǔ)充
可以連接的數(shù)據(jù)庫:
##postgres db.bind('postgres', user='', password='', host='', database='') ##sqlite create_db:如果數(shù)據(jù)庫不存在創(chuàng)建數(shù)據(jù)庫文件 db.bind('sqlite', 'filename', create_db=True) ##mysql db.bind('mysql', host='', user='', passwd='', db='') ##Oracle db.bind('oracle', 'user/password@dsn')
Entity(實(shí)體)類似mvc里面的model
在創(chuàng)建實(shí)體實(shí)例之前,需要將實(shí)體映射到數(shù)據(jù)庫表,生成映射后,可以通過實(shí)體查詢數(shù)據(jù)庫并創(chuàng)建新的實(shí)例。db.Entity自己定義新的實(shí)體必須從db.Entity繼承
屬性
class Customer(db.Entity): name = Required(str) picture = Optional(buffer) sql_debug(True) # 顯示debug信息(sql語句) db.generate_mapping(create_tables=True) # 如果數(shù)據(jù)庫表沒有創(chuàng)建表
屬性類型
Required and Optional
通常實(shí)體屬性分為Required(必選)和Optional(可選)
PrimaryKey(主鍵)
默認(rèn)每個(gè)實(shí)體都有一個(gè)主鍵,默認(rèn)添加了id=PrimaryKey(int,auto=True)屬性
class Product(db.Entity): name = Required(str, unique=True) price = Required(Decimal) description = Optional(str) #等價(jià)于下面 class Product(db.Entity): id = PrimaryKey(int, auto=True) name = Required(str, unique=True) price = Required(Decimal) description = Optional(str)
Set
定義了一對一,一對多,多對多等數(shù)據(jù)結(jié)構(gòu)
# 一對一 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) #聯(lián)合主鍵 pny.sql_debug(True) # 顯示debug信息(sql語句) db.generate_mapping(create_tables=True) # 如果數(shù)據(jù)庫表沒有創(chuàng)建表 #------------------------------------------------------- #一對多 class Artist(database.Entity): """ Pony ORM model of the Artist table """ name = pny.Required(unicode) #被外鍵關(guān)聯(lián) albums = pny.Set("Album") class Album(database.Entity): """ Pony ORM model of album table """ #外鍵字段artlist,外鍵關(guān)聯(lián)表Artist,Artist表必須寫Set表示被外鍵關(guān)聯(lián) #這個(gè)外鍵字段默認(rèn)就是index=True,除非自己指定index=False才不會(huì)創(chuàng)建索引,索引名默認(rèn)為[idx_表名__字段](artist) artist = pny.Required(Artist) #外鍵字段(數(shù)據(jù)庫顯示artist) title = pny.Required(unicode) release_date = pny.Required(datetime.date) publisher = pny.Required(unicode) media_type = pny.Required(unicode) # Compositeindexes(復(fù)合索引) class Example1(db.Entity): a = Required(str) b = Optional(int) composite_index(a, b) #也可以使用字符串composite_index(a, 'b')
屬性數(shù)據(jù)類型
格式為 :
屬性名 = 屬性類型(數(shù)據(jù)類型)
attr1 = Required(str) # 等價(jià) attr2 = Required(unicode) attr3 = Required(LongStr) # 等價(jià) attr4 = Required(LongUnicode) attr1 = Required(buffer) # Python 2 and 3 attr2 = Required(bytes) # Python 3 only #字符串長度,不寫默認(rèn)為255 name = Required(str,40) #VARCHAR(40) #整數(shù)的大小,默認(rèn)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 #無符號(hào)整型 attr1 = Required(int, size=8, unsigned=True) # TINYINT UNSIGNED in MySQL # 小數(shù)和精度 price = Required(Decimal, 10, 2) #DECIMAL(10,2) # 時(shí)間 dt = Required(datetime,6) # 其它參數(shù) unique 是否唯一 auto 是否自增 default 默認(rèn)值 sql_default created_at = Required(datetime, sql_default='CURRENT_TIMESTAMP') index 創(chuàng)建索引 index='index_name' 指定索引名稱 lazy 延遲加載的屬性加載對象 cascade_delete 關(guān)聯(lián)刪除對象 column 映射到數(shù)據(jù)庫的列名 columns Set(多對多列名) table 多對多中間表的表名字 nullable 允許該列為空 py_check 可以指定一個(gè)函數(shù),檢查數(shù)據(jù)是否合法和修改數(shù)據(jù) class Student(db.Entity): name = Required(str) gpa = Required(float, py_check=lambda val: val >= 0 and val <= 5)
實(shí)例操作
# 獲取實(shí)例 p = Person.get(name="Person") #返回單個(gè)實(shí)例,如同 Django ORM的get #------------------------------ # 查詢 persons = Person.select() ''' select并沒有連接數(shù)據(jù)庫查詢,只是返回一個(gè)Query object,調(diào)用persons[:]返回所有Person實(shí)例 ''' # limit persons [1:5] # show persons.show() # 生成器表達(dá)式查詢,然后解析AST樹的方式構(gòu)造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 #----------------------------- # 修改實(shí)例 @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使用還可以使用游標(biāo)操作(這樣就可以寫原生sql語句了)
result = db.execute('''select name from Artist''') print result.fetchall()
類似Django ORM的save函數(shù)
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)
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。