Pony query examples

For demonstrating Pony queries let’s use the example from the Pony ORM distribution. You can try these queries yourself in the interactive mode and see the generated SQL. For this purpose import the example module this way:

>>> from pony.orm.examples.estore import *

This module offers a simplified data model of a eCommerce online store. Here is the ER Diagram of the data model:

Here are the entity definitions:

from decimal import Decimal
from datetime import datetime
from pony.converting import str2datetime
from pony.orm import *
db = Database()
class Customer(db.Entity):
    email = Required(str, unique=True)
    password = Required(str)
    name = Required(str)
    country = Required(str)
    address = Required(str)
    cart_items = Set('CartItem')
    orders = Set('Order')
class Product(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    categories = Set('Category')
    description = Optional(str)
    picture = Optional(buffer)
    price = Required(Decimal)
    quantity = Required(int)
    cart_items = Set('CartItem')
    order_items = Set('OrderItem')
class CartItem(db.Entity):
    quantity = Required(int)
    customer = Required(Customer)
    product = Required(Product)
class OrderItem(db.Entity):
    quantity = Required(int)
    price = Required(Decimal)
    order = Required('Order')
    product = Required(Product)
    PrimaryKey(order, product)
class Order(db.Entity):
    id = PrimaryKey(int, auto=True)
    state = Required(str)
    date_created = Required(datetime)
    date_shipped = Optional(datetime)
    date_delivered = Optional(datetime)
    total_price = Required(Decimal)
    customer = Required(Customer)
    items = Set(OrderItem)
class Category(db.Entity):
    name = Required(str, unique=True)
    products = Set(Product)
set_sql_debug(True)
db.bind('sqlite', 'estore.sqlite', create_db=True)
db.generate_mapping(create_tables=True)

When you import this example, it will create the SQLite database in the file ‘estore.sqlite’ and fill it with some test data. Below you can see some query examples:

# All USA customers
Customer.select(lambda c: c.country == 'USA')
# The number of customers for each country
select((c.country, count(c)) for c in Customer)
# Max product price
max(p.price for p in Product)
# Max SSD price
max(p.price for p in Product
            for cat in p.categories if cat.name == 'Solid State Drives')
# Three most expensive products
Product.select().order_by(desc(Product.price))[:3]
# Out of stock products
Product.select(lambda p: p.quantity == 0)
# Most popular product
Product.select().order_by(lambda p: desc(sum(p.order_items.quantity))).first()
# Products that have never been ordered
Product.select(lambda p: not p.order_items)
# Customers who made several orders
Customer.select(lambda c: count(c.orders) > 1)
# Three most valuable customers
Customer.select().order_by(lambda c: desc(sum(c.orders.total_price)))[:3]
# Customers whose orders were shipped
Customer.select(lambda c: SHIPPED in c.orders.state)
# Customers with no orders
Customer.select(lambda c: not c.orders)
# The same query with the LEFT JOIN instead of NOT EXISTS
left_join(c for c in Customer for o in c.orders if o is None)
# Customers which ordered several different tablets
select(c for c in Customer
         for p in c.orders.items.product
         if 'Tablets' in p.categories.name and count(p) > 1)

You can find more queries in the pony.orm.examples.estore module.