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

SELECT p.id, p.name, p.age, p.classtype, p.mentor, p.gpa, p.degreeFROM person pWHERE p.classtype IN ('Student', 'Professor', 'Person')AND p.age > 20

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

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

pip install pony

#!/usr/bin/env python#-*- coding:utf-8 -*-import datetimeimport pony.orm as pnyimport 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 filedatabase = 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)  release_date = pny.Required(datetime.date)  publisher = pny.Required(unicode)  media_type = pny.Required(unicode)# turn on debug modepny.sql_debug(True)   # 顯示debug信息(sql語句)# map the models to the database# and create the tables, if they don't existdatabase.generate_mapping(create_tables=True)    # 如果數據庫表沒有創建表

GET CONNECTION FROM THE LOCAL POOLPRAGMA foreign_keys = falseBEGIN IMMEDIATE TRANSACTIONCREATE 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 COMMITPRAGMA foreign_keys = trueCLOSE CONNECTION

#!/usr/bin/env python#-*- coding:utf-8 -*-import datetimeimport pony.orm as pnyfrom models import Album, Artistfrom database import PonyDatabase# ----------------------------------------------------------------------@pny.db_sessiondef 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,         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 pnyfrom models import Artist, Albumfrom database import PonyDatabasedb = 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 pnyfrom models import Artistwith pny.db_session:  band = Artist.get(name="MXPX")  band.delete()

##postgresdb.bind('postgres', user='', password='', host='', database='')##sqlite     create_db:如果數據庫不存在創建數據庫文件db.bind('sqlite', 'filename', create_db=True)##mysqldb.bind('mysql', host='', user='', passwd='', db='')##Oracledb.bind('oracle', 'user/password@dsn')

class Customer(db.Entity):  name = Required(str)  picture = Optional(buffer)sql_debug(True) # 顯示debug信息(sql語句)db.generate_mapping(create_tables=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)

# 一對一class User(db.Entity):  name = Required(str)  cart = Optional("Cart") #必須Optional-Required or Optional-Optionalclass 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)  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 3attr2 = Required(bytes) # Python 3 only#字符串長度,不寫默認為255name = Required(str,40)   #VARCHAR(40)#整數的大小，默認2bitattr1 = Required(int, size=8)  # 8 bit - TINYINT in MySQLattr2 = Required(int, size=16) # 16 bit - SMALLINT in MySQLattr3 = Required(int, size=24) # 24 bit - MEDIUMINT in MySQLattr4 = Required(int, size=32) # 32 bit - INTEGER in MySQLattr5 = 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實例'''# limitpersons [1:5]# showpersons.show()# 生成器表達式查詢，然后解析AST樹的方式構造SQL語句select(p for p in Person) #和Person.select()一樣返回Query objectselect((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) #25select(p.age for p in Person).max() #25#-----------------------------# 修改實例@db_sessiondef update_persons(): p = Person.get(id=2) p.page = 1000 commit() # 刪除@db_sessiondef delete_persons():  p = Person.get(id=2)  p.delete()  commit()

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

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):  content = Required(str)  def before_insert(self):    print("Before insert! title=%s" % self.title)