Python ORM Pony 常用表连接聚合操作(sum()、count()、min()、max()、avg()等)

Pony是一个高级的对象关系映射器ORM框架。Pony它能够使用Python生成器表达式和lambdas向数据库编写查询。Pony分析表达式的抽象语法树，并将其转换为SQL查询。支持SQLite, MySQL, PostgreSQL和Oracle等数据库，本文主要介绍Python ORM Pony中常用聚合操作(sum()、count()、min()、max()、avg()和group_concat()等)。

1、sum()、count()、min()、max()、avg()和group_concat()

Pony中实现sum()、count()、min()、max()、avg()和group_concat()等聚合操作，示例如下， 

from __future__ import absolute_import, print_function
from decimal import Decimal
from pony.orm import *
#db = Database("sqlite", "demo.sqlite", create_db=True)
db = Database()#Database("sqlite", "demo.sqlite", create_db=True)
db.bind(provider='oracle', user='admin', password='admin', dsn='docker_oracle11')
class Customer(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    email = Required(str, unique=True)
    orders = Set("OrderInfo")
class OrderInfo(db.Entity):
    id = PrimaryKey(int, auto=True)
    total_price = Required(Decimal)
    customer = Required(Customer)
    items = Set("OrderItem")
class Product(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    price = Required(Decimal)
    items = Set("OrderItem")
class OrderItem(db.Entity):
    quantity = Required(int, default=1)
    order = Required(OrderInfo)
    product = Required(Product)
    PrimaryKey(order, product)
sql_debug(True)
db.generate_mapping(create_tables=True)
@db_session
def populate_database():
    c1 = Customer(name='John Smith', email='john@example.com')
    c2 = Customer(name='Matthew Reed', email='matthew@example.com')
    c3 = Customer(name='Chuan Qin', email='chuanqin@example.com')
    c4 = Customer(name='Rebecca Lawson', email='rebecca@example.com')
    c5 = Customer(name='Oliver Blakey', email='oliver@example.com')
    p1 = Product(name='Kindle Fire HD', price=Decimal('284.00'))
    p2 = Product(name='Apple iPad with Retina Display', price=Decimal('478.50'))
    p3 = Product(name='SanDisk Cruzer 16 GB USB Flash Drive', price=Decimal('9.99'))
    p4 = Product(name='Kingston DataTraveler 16GB USB 2.0', price=Decimal('9.98'))
    p5 = Product(name='Samsung 840 Series 120GB SATA III SSD', price=Decimal('98.95'))
    p6 = Product(name='Crucial m4 256GB SSD SATA 6Gb/s', price=Decimal('188.67'))
    o1 = OrderInfo(customer=c1, total_price=Decimal('292.00'))
    OrderItem(order=o1, product=p1)
    OrderItem(order=o1, product=p4, quantity=2)
    o2 = OrderInfo(customer=c1, total_price=Decimal('478.50'))
    OrderItem(order=o2, product=p2)
    o3 = OrderInfo(customer=c2, total_price=Decimal('680.50'))
    OrderItem(order=o3, product=p2)
    OrderItem(order=o3, product=p4, quantity=2)
    OrderItem(order=o3, product=p6)
    o4 = OrderInfo(customer=c3, total_price=Decimal('99.80'))
    OrderItem(order=o4, product=p4, quantity=10)
    o5 = OrderInfo(customer=c4, total_price=Decimal('722.00'))
    OrderItem(order=o5, product=p1)
    OrderItem(order=o5, product=p2)
    commit()
#populate_database()
with db_session:
    print(sum(p.price for p in Product if p.id > 2))
    print(count(p.price for p in Product if p.id > 2))
    print(min(p.price for p in Product if p.id > 2))
    print(max(p.price for p in Product if p.id > 2))
    print(avg(p.price for p in Product if p.id > 2))
    print(group_concat(p.name for p in Product if p.id > 1))

2、表连接

Pony中可以进行多表查询，也可以使用left_join()可以实现表的连接，示例如下， 

from __future__ import absolute_import, print_function
from decimal import Decimal
from pony.orm import *
#db = Database("sqlite", "demo.sqlite", create_db=True)
db = Database()#Database("sqlite", "demo.sqlite", create_db=True)
db.bind(provider='oracle', user='admin', password='admin', dsn='docker_oracle11')
class Customer(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    email = Required(str, unique=True)
    orders = Set("OrderInfo")
class OrderInfo(db.Entity):
    id = PrimaryKey(int, auto=True)
    total_price = Required(Decimal)
    customer = Required(Customer)
    items = Set("OrderItem")
class Product(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    price = Required(Decimal)
    items = Set("OrderItem")
class OrderItem(db.Entity):
    quantity = Required(int, default=1)
    order = Required(OrderInfo)
    product = Required(Product)
    PrimaryKey(order, product)
sql_debug(True)
db.generate_mapping(create_tables=True)
@db_session
def populate_database():
    c1 = Customer(name='John Smith', email='john@example.com')
    c2 = Customer(name='Matthew Reed', email='matthew@example.com')
    c3 = Customer(name='Chuan Qin', email='chuanqin@example.com')
    c4 = Customer(name='Rebecca Lawson', email='rebecca@example.com')
    c5 = Customer(name='Oliver Blakey', email='oliver@example.com')
    p1 = Product(name='Kindle Fire HD', price=Decimal('284.00'))
    p2 = Product(name='Apple iPad with Retina Display', price=Decimal('478.50'))
    p3 = Product(name='SanDisk Cruzer 16 GB USB Flash Drive', price=Decimal('9.99'))
    p4 = Product(name='Kingston DataTraveler 16GB USB 2.0', price=Decimal('9.98'))
    p5 = Product(name='Samsung 840 Series 120GB SATA III SSD', price=Decimal('98.95'))
    p6 = Product(name='Crucial m4 256GB SSD SATA 6Gb/s', price=Decimal('188.67'))
    o1 = OrderInfo(customer=c1, total_price=Decimal('292.00'))
    OrderItem(order=o1, product=p1)
    OrderItem(order=o1, product=p4, quantity=2)
    o2 = OrderInfo(customer=c1, total_price=Decimal('478.50'))
    OrderItem(order=o2, product=p2)
    o3 = OrderInfo(customer=c2, total_price=Decimal('680.50'))
    OrderItem(order=o3, product=p2)
    OrderItem(order=o3, product=p4, quantity=2)
    OrderItem(order=o3, product=p6)
    o4 = OrderInfo(customer=c3, total_price=Decimal('99.80'))
    OrderItem(order=o4, product=p4, quantity=10)
    o5 = OrderInfo(customer=c4, total_price=Decimal('722.00'))
    OrderItem(order=o5, product=p1)
    OrderItem(order=o5, product=p2)
    commit()
#populate_database()
with db_session:
    print(left_join((p.name, i.quantity) for p in Product for i in p.items if p.id>0 )[:])
    print(select((p.name, c.name) for p in Product for c in Customer if p.id==c.id )[:])
    print(select(o for o in OrderInfo if o.customer in
       select(c for c in Customer if c.name.startswith('A')))[:])

3、group by

Pony中也可以生成带有group by的SQL语句，示例如下， 

from __future__ import absolute_import, print_function
from decimal import Decimal
from pony.orm import *
#db = Database("sqlite", "demo.sqlite", create_db=True)
db = Database()#Database("sqlite", "demo.sqlite", create_db=True)
db.bind(provider='oracle', user='admin', password='admin', dsn='docker_oracle11')
class Customer(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    email = Required(str, unique=True)
    orders = Set("OrderInfo")
class OrderInfo(db.Entity):
    id = PrimaryKey(int, auto=True)
    total_price = Required(Decimal)
    customer = Required(Customer)
    items = Set("OrderItem")
class Product(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    price = Required(Decimal)
    items = Set("OrderItem")
class OrderItem(db.Entity):
    quantity = Required(int, default=1)
    order = Required(OrderInfo)
    product = Required(Product)
    PrimaryKey(order, product)
sql_debug(True)
db.generate_mapping(create_tables=True)
@db_session
def populate_database():
    c1 = Customer(name='John Smith', email='john@example.com')
    c2 = Customer(name='Matthew Reed', email='matthew@example.com')
    c3 = Customer(name='Chuan Qin', email='chuanqin@example.com')
    c4 = Customer(name='Rebecca Lawson', email='rebecca@example.com')
    c5 = Customer(name='Oliver Blakey', email='oliver@example.com')
    p1 = Product(name='Kindle Fire HD', price=Decimal('284.00'))
    p2 = Product(name='Apple iPad with Retina Display', price=Decimal('478.50'))
    p3 = Product(name='SanDisk Cruzer 16 GB USB Flash Drive', price=Decimal('9.99'))
    p4 = Product(name='Kingston DataTraveler 16GB USB 2.0', price=Decimal('9.98'))
    p5 = Product(name='Samsung 840 Series 120GB SATA III SSD', price=Decimal('98.95'))
    p6 = Product(name='Crucial m4 256GB SSD SATA 6Gb/s', price=Decimal('188.67'))
    o1 = OrderInfo(customer=c1, total_price=Decimal('292.00'))
    OrderItem(order=o1, product=p1)
    OrderItem(order=o1, product=p4, quantity=2)
    o2 = OrderInfo(customer=c1, total_price=Decimal('478.50'))
    OrderItem(order=o2, product=p2)
    o3 = OrderInfo(customer=c2, total_price=Decimal('680.50'))
    OrderItem(order=o3, product=p2)
    OrderItem(order=o3, product=p4, quantity=2)
    OrderItem(order=o3, product=p6)
    o4 = OrderInfo(customer=c3, total_price=Decimal('99.80'))
    OrderItem(order=o4, product=p4, quantity=10)
    o5 = OrderInfo(customer=c4, total_price=Decimal('722.00'))
    OrderItem(order=o5, product=p1)
    OrderItem(order=o5, product=p2)
    commit()
#populate_database()
with db_session:
    print(select((p.name, avg(p.price)) for p in Product)[:])
    print(select((p.name, count(i for i in p.items if i.quantity <= 3),
           count(i for i in p.items if i.quantity > 1 ),
           count(i for i in p.items if i.quantity > 2)) for p in Product)[:])

推荐阅读
cjavapy编程之路首页
版权所有 © 2018 cjavapy.com 保留所有权利
吉ICP备18005501号-1