詳解python的ORM中Pony用法

更新時(shí)間：2018年02月09日 10:40:30 作者：ll513701

本篇文章給大家詳細(xì)介紹了python的ORM中Pony用法以及詳細(xì)代碼分享，對(duì)此有需要的朋友測(cè)試參考下。

Pony是Python的一種ORM，它允許使用生成器表達(dá)式來(lái)構(gòu)造查詢(xún)，通過(guò)將生成器表達(dá)式的抽象語(yǔ)法樹(shù)解析成SQL語(yǔ)句。它也有在線ER圖編輯器可以幫助你創(chuàng)建Model。

示例分析

Pony語(yǔ)句：

select(p for p in Person if p.age > 20)

翻譯成sql語(yǔ)句就是：

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語(yǔ)句：

select(c for c in Customer
     if sum(c.orders.price) > 1000)

翻譯成sql語(yǔ)句就是:

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)

# 路徑建議寫(xiě)絕對(duì)路徑。我這邊開(kāi)始寫(xiě)相對(duì)路徑報(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表必須寫(xiě)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語(yǔ)句)

# map the models to the database
# and create the tables, if they don't exist
database.generate_mapping(create_tables=True)    # 如果數(shù)據(jù)庫(kù)表沒(méi)有創(chuàng)建表

運(yùn)行之后生成sqlite如下:

上述代碼對(duì)應(yīng)的sqlite語(yǔ)句是：

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來(lái)處理數(shù)據(jù)庫(kù)。 
它負(fù)責(zé)打開(kāi)連接，提交數(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"
  
  #使用生成器形式查詢(xún)
  '''
  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ù)庫(kù)：

##postgres
db.bind('postgres', user='', password='', host='', database='')
##sqlite     create_db:如果數(shù)據(jù)庫(kù)不存在創(chuàng)建數(shù)據(jù)庫(kù)文件
db.bind('sqlite', 'filename', create_db=True)
##mysql
db.bind('mysql', host='', user='', passwd='', db='')
##Oracle
db.bind('oracle', 'user/password@dsn')

Entity(實(shí)體)類(lèi)似mvc里面的model

在創(chuàng)建實(shí)體實(shí)例之前，需要將實(shí)體映射到數(shù)據(jù)庫(kù)表，生成映射后，可以通過(guò)實(shí)體查詢(xún)數(shù)據(jù)庫(kù)并創(chuàng)建新的實(shí)例。db.Entity自己定義新的實(shí)體必須從db.Entity繼承

屬性

class Customer(db.Entity):
  name = Required(str)
  picture = Optional(buffer)
sql_debug(True) # 顯示debug信息(sql語(yǔ)句)
db.generate_mapping(create_tables=True) # 如果數(shù)據(jù)庫(kù)表沒(méi)有創(chuàng)建表

屬性類(lèi)型

Required
Optional
PrimaryKey
Set

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

定義了一對(duì)一，一對(duì)多，多對(duì)多等數(shù)據(jù)結(jié)構(gòu)

# 一對(duì)一
class User(db.Entity):
  name = Required(str)
  cart = Optional("Cart") #必須Optional-Required or Optional-Optional

class Cart(db.Entity):
  user = Required("User")
  
# 多對(duì)多
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語(yǔ)句)
db.generate_mapping(create_tables=True)   # 如果數(shù)據(jù)庫(kù)表沒(méi)有創(chuàng)建表
#-------------------------------------------------------
#一對(duì)多
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表必須寫(xiě)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ù)庫(kù)顯示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ù)類(lèi)型

格式為：

屬性名 = 屬性類(lèi)型(數(shù)據(jù)類(lèi)型)

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)
# 等價(jià)
attr2 = Required(unicode)

attr3 = Required(LongStr)
# 等價(jià)
attr4 = Required(LongUnicode)

attr1 = Required(buffer) # Python 2 and 3

attr2 = Required(bytes) # Python 3 only

#字符串長(zhǎng)度,不寫(xiě)默認(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

#無(wú)符號(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' 指定索引名稱(chēng)
lazy  延遲加載的屬性加載對(duì)象
cascade_delete   關(guān)聯(lián)刪除對(duì)象
column   映射到數(shù)據(jù)庫(kù)的列名
columns Set(多對(duì)多列名)
table  多對(duì)多中間表的表名字
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
#------------------------------
# 查詢(xún)
persons = Person.select()
'''
select并沒(méi)有連接數(shù)據(jù)庫(kù)查詢(xún)，只是返回一個(gè)Query object，調(diào)用persons[:]返回所有Person實(shí)例
'''

# limit
persons [1:5]

# show
persons.show()

# 生成器表達(dá)式查詢(xún)，然后解析AST樹(shù)的方式構(gòu)造SQL語(yǔ)句

select(p for p in Person) 
#和Person.select()一樣返回Query object

select((p.id, p.name) for p in Person)[:]

# 帶where條件查詢(xún)
select((p.id, p.name) for p in Person if p.age ==20)[:]

# 分組聚合查詢(xún)
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)操作(這樣就可以寫(xiě)原生sql語(yǔ)句了)

result = db.execute('''select name from Artist''')
print result.fetchall()

類(lèi)似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)

您可能感興趣的文章: