python元祖，orm設置bool型 python_詳解python的ORM中Pony用法

Pony是Python的一種ORM，它允許使用生成器表達式來構造查詢，通過將生成器表達式的抽象語法樹解析成SQL語句。它也有在線ER圖編輯器可以幫助你創建Model。

示例分析

Pony語句：

python元祖？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

python list、WHERE p.classtype IN ('Student', 'Professor', 'Person')

AND p.age > 20

Pony語句：

select(c for c in Customer

python3。if sum(c.orders.price) > 1000)

翻譯成sql語句就是:

SELECT "c"."id"

FROM "Customer" "c"

python mybatis，LEFT JOIN "Order" "order-1"

ON "c"."id" = "order-1"."customer"

GROUP BY "c"."id"

HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000

python import random。安裝Pony

pip install pony

使用Pony

#!/usr/bin/env python

Python bool。#-*- coding:utf-8 -*-

import datetime

import pony.orm as pny

import sqlite3

python lambda？# conn = sqlite3.connect('D:\日常python學習PY2\Pony學習\music.sqlite')

# print conn

# database = pny.Database()

# database.bind("sqlite","music.sqlite",create_db=True)

python中bool函數，# 路徑建議寫絕對路徑。我這邊開始寫相對路徑報錯 unable to open database file

database = pny.Database("sqlite","D:\日常python學習PY2\Pony學習\music.sqlite",create_db=True)

########################################################################

class Artist(database.Entity):

python boolean。"""

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

插入/增加數據

#!/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)

#整數的大小，默認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)