Pony ORM - 有着优美的查询语法的Python ORM

Pony ORM - 有着优美的查询语法的Python ORM

Pony是一个先进的Python ORM，支持使用类似于python中列表推导式的语法来进行查询：

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

MySQL:
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

除此之外，你还可以使用lambda表达式来进行类似的查询:

Customer.select(lambda c: sum(c.orders.total_price) > 1000)

甚至，你也粗暴的使用原生SQL进行查询：

x = 1000
y = 500
Product.select_by_sql("SELECT * FROM Product WHERE price > $x OR price = $(y * 2)")

或者
Product.select_by_sql(
    "SELECT * FROM Product WHERE price > $x OR price = $(y * 2)",
    globals={'x': 100}, locals={'y': 200}
)

Pony会查找$符号后定义的变量或者表达式，并自动计算其值然后用作参数传入Query对象，而不是简单的字符串替换，从而使SQL注入变为不可能。

与Django和SQLAlchemy等ORM相比，它有以下更多的优势：

• 非常优美的、pythonic的语法
• 支持python2 & 3
• 支持PostgreSQL、MySQL、Oracle以及sqlite
• 自动优化查询

与Django相比，Pony还提供：

• IdentityMap模式
• 自动事务管理
• 自动缓存查询和对象
• 完全支持复合键
• 使用LEFT JOIN，HAVING和SQL的其他功能轻松编写查询的能力

除此之外，Pony的社区非常的活跃，比如官方在Stack Overflow上有非常迅速的响应速度。从版本0.7开始，Pony ORM在Apache License 2.0版下发布。

安装pony

如果只使用sqlite数据库，Pony的安装只需要1条命令：

pip install pony

如果使用其他数据库，需要安装额外的驱动：

• PostgreSQL: psycopg or psycopg2cffi
• MySQL: MySQL-python or PyMySQL
• Oracle: cx_Oracle

开始使用

• 数据库与表定义
• 记录增删改
• 查询
• 事务

数据库与表定义

首先你需要创建一个db的对象：

from pony.orm import *
db = Database()

# 或者(不推荐)
from pony import orm
db = orm.Database()

然后定义表的实体，它继承于db的Entity属性：

class User(db.Entity):
    name = Required(str)
    uid = Required(int, unique=True, nullable=True)
    face = Required(str)
    info = Required(str)
    gift_rec = Set('GiftRec')

class GiftRec(db.Entity):
    key = Required(str, unique=True)
    room_id = Required(int)
    gift_id = Required(int)
    gift_name = Required(str)
    gift_type = Required(str)
    sender_id = Required(User)
    sender_type = Required(int, nullable=True)
    created_time = Required(datetime.datetime, default=datetime.datetime.now)
    status = Required(int)

接下来绑定到数据库：

db.bind(provider='mysql', user='Jax', password='123456', host='129.204.43.2', port=44444, database='bilibili')
# 或者
db.bind(provider='sqlite', filename=':memory:')
db.bind(provider='sqlite', filename='filename', create_db=True)
db.bind(provider='oracle', user='', password='', dsn='')

然后创建映射：

db.generate_mapping(check_tables=True, create_tables=False)

设置为debug模式：

set_sql_debug(True)

除此之外，可以使用db.on_connect的装饰器，在db连接的时候执行一些操作：

db = Database()

# entities declaration

@db.on_connect(provider='sqlite')
def sqlite_case_sensitivity(db, connection):
    cursor = connection.cursor()
    cursor.execute('PRAGMA case_sensitive_like = OFF')

db.bind(**options)
db.generate_mapping(create_tables=True)

表的详细定义

字段

使用attr_name = kind(type, *options)来定义表的字段，kind一共有四种情况：

• Required
• Optional

Required和Optional是最常用的两种属性，它们的区别就是前者所定义的实体，必须存在一个值，而后者则没有要求。

• PrimaryKey

映射到数据库中就是primary key，每个实体必须包含一个。如果你没有显示的定义，那么pony就会自动指定一个，类似于：

id = PrimaryKey(int, auto=True)

• Set

Set代表1个集合，也叫做关系。这里实现to-many的关系，目前(0.7)pony不支持primitive types。

type支持多种类型：

• 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
• Json - used for mapping to native database JSON type
• IntArray - array of integers
• StrArray - array of strings
• FloatArray - array of floats

options相关的说明：
<a href="https://docs.ponyorm.org/api_reference.html#attribute-options" target="_blank">https://docs.ponyorm.org/api_reference.html#attribute-options</a>

复合键

class Example(db.Entity):
    a = Required(int)
    b = Required(str)
    c = Required(str)
    d = Required(str)
    PrimaryKey(a, b)
    composite_key(c, d)
    # composite_key(a, b) will be represented as the UNIQUE ("a", "b") constraint.

复合索引

class Example(db.Entity):
    a = Required(str)
    b = Optional(int)
    composite_index(a, b)
    # or composite_index(a, 'b')

实体关联

一对多：

class Order(db.Entity):
    items = Set("OrderItem")

class OrderItem(db.Entity):
    order = Required(Order)

在上面定义的OrderItem不能脱离Order而存在。如果你想允许OrderItem在分配给确切的Order之前就存在，可以将order 属性定义为Optional：

class Order(db.Entity):
    items = Set("OrderItem")

class OrderItem(db.Entity):
    order = Optional(Order)

多对多(Pony 会自动生成中间表)：

class Product(db.Entity):
    tags = Set("Tag")

class Tag(db.Entity):
    products = Set(Product)

一对一:

必须定义为Optional-Required 或者Optional-Optional:

class Person(db.Entity):
    passport = Optional("Passport")

class Passport(db.Entity):
    person = Required("Person")

自我关联：

实体可以使用自引用关系与自身关联。这种关系可以有两种类型：对称和非对称。非对称关系由属于同一实体的两个属性定义。

对称关系的具体特性是，实体只指定了一个关系属性，而该属性定义了关系的两边。这种关系可以是一对一，也可以是多对多。以下是自我参考关系的示例：

class Person(db.Entity):
    name = Required(str)
    spouse = Optional("Person", reverse="spouse") # symmetric one-to-one
    friends = Set("Person", reverse="friends")    # symmetric many-to-many
    manager = Optional("Person", reverse="employees") # one side of non-symmetric
    employees = Set("Person", reverse="manager") # another side of non-symmetric

两实体间的多重关系：

当两个实体之间有多个关系时，pony需要指定反向属性。这是为了让Pony知道哪一对属性相互关联。

class User(db.Entity):
    tweets = Set("Tweet", reverse="author")
    favorites = Set("Tweet", reverse="favorited")

class Tweet(db.Entity):
    author = Required(User, reverse="tweets")
    favorited = Set(User, reverse="favorites")

记录增删改

插入

customer1 = Customer(login="John", password="***", name="John", email="john@google.com")

创建对象时，所有的参数必须指定为kw参数，如果有默认值则可省略之。

所有创建的实例都属于当前的db_session()。在某些ORM中，需要调用对象的save()方法才能保存它。这是非常愚蠢的，因为程序员必须跟踪创建或更新了哪些对象，并且不能忘记对每个对象调用save()方法。

Pony自动跟踪创建或更新的对象，并在当前db_session()结束时自动将其保存到数据库中。如果需要在离开db_session()作用域之前保存新创建的对象，可以使用flush()或commit()函数来保存。

class Customer(db.Entity):
    id = PrimaryKey(int, auto=True)
    email = Required(str)

@db_session
def handler(email):
    c = Customer(email=email)

def handler(email):
    with db_session:
        john = TeamMember(name='John')

更新：

product = Product.select(...
product.quantity += 10

更新多个字段：

order = Order[123]
order.state = "Shipped"
order.date_shipped = datetime.now()

order = Order[123]
order.set(state="Shipped", date_shipped=datetime.now())

Pony总是在执行以下方法之前自动保存db_session()缓存中累积的更改：select()、get()、exists()、execute()和commit()。

* 遗憾的是，在当前（0.7.9）版本，Pony不支持bulk update。

删除：

order.delete()
# 或者：
delete(p for p in Product if p.category.name == 'SD Card')
# 或：
Product.select(lambda p: p.category.name == 'SD Card').delete(bulk=True)

查询

通过pk获取：

user = User[1024]

同样的语法也适用于具有复合键的对象，只需要按照实体类描述中定义属性的相同顺序列出复合主键的元素，用逗号分隔即可。当数据库记录不存在时，就抛出ObjectNotFound异常。
同时，也可以：

product1 = Product.get(name='Product1')

当多个值返回的时候，会抛出MultipleObjectsFoundError异常。

通过生成器表达式，获取多个对象：

good_customer = select(c for c in Customer if sum(o.total_price for o in c.orders) > 1000)
# 或者通过属性来过滤：
good_customer = select(c for c in Customer if sum(c.orders.total_price) > 1000)

# 可以继续在query对象上使用filter：
good_adult_customer = good_customer.filter(lambda c: c.age >= 18)

# 还能基于query生成一个新的query:
good_adult_chinese_customer = select(c.name for c in good_adult_customer if c.country == 'CN')

更多的查询实例：<a href="https://docs.ponyorm.org/queries.html#pony-query-examples" target="_blank">https://docs.ponyorm.org/queries.html#pony-query-examples</a>

聚合

以下五个聚合函数用于声明性查询：sum()、count()、min()、max()、avg()和group_concat()。让我们来看一些使用这些函数的简单查询示例。

# Total GPA of students from group 101:
sum(s.gpa for s in Student if s.group.number == 101)

#Number of students with a GPA above three:
count(s for s in Student if s.gpa > 3)

# First name of a student, who studies philosophy, sorted alphabetically:
min(s.name for s in Student if "Philosophy" in s.courses.name)

# Birth date of the youngest student in group 101:
max(s.dob for s in Student if s.group.number == 101)

# Average GPA in department 44:
avg(s.gpa for s in Student if s.group.dept.number == 44)

# Names of students of group 101 joined by comma:
group_concat(s.name for s in Student if s.group.number == 101)

需要注意的是，上述的sum、count等函数并非是python的标准函数，而是属于pony.orm模块中，pony实现的函数。pony使用了与python标准函数相同的名称来构造这些函数，如果在程序中直接使用它，不会影响原有的标准函数的行为。但如果忘了从pony.orm中导入这些函数，使用python标准函数sum、count……时会出现一个错误。

聚合函数也可以在查询中使用:

# 例如，不仅需要查找组中最年轻的学生的出生日期，还需要查找学生本人，则可以编写以下查询：
select(s for s in Student if s.group.number == 101 and s.dob == max(s.dob for s in Student if s.group.number == 101))

# to get all groups with an average GPA above 4.5:
select(g for g in Group if avg(s.gpa for s in g.students) > 4.5)

条件计数：

select((g, count(s for s in g.students if s.gpa <= 3),
           count(s for s in g.students if s.gpa > 3 and s.gpa <= 4),
           count(s for s in g.students if s.gpa > 4)) for g in Group)

更复杂的聚合

借助Pony可以实现复杂的分组：

# group by an attribute part:
select((s.dob.year, avg(s.gpa)) for s in Student)
# 此时birth year不会被加`distinct`条件，因为它现在是dob的一个属性

# You can have expressions inside the aggregate functions:
select((item.order, sum(item.price * item.quantity))
        for item in OrderItem if item.order.id == 123)

# Here is another way of making the same query:
select((order, sum(order.items.price * order.items.quantity))
        for order in Order if order.id == 123)

事务

数据库事务是一个逻辑工作单元，可以由一个或多个操作组成。事务是原子的，这意味着当事务对数据库进行更改时，在提交事务时所有更改都会成功，或者在回滚事务时所有更改都会撤消。

借助Pony的db session，你可以实现自动事务管理。

db_session

与数据库交互的代码必须放在数据库会话中。与数据库一起工作的每个应用程序线程都建立一个单独的数据库session，并使用一个单独的标识映射实例。当通过对象的主键或唯一键访问对象，并且该对象已存储在标识映射中时，此标识映射可作为缓存使用，有助于避免数据库查询。为了使用数据库会话处理数据库，可以使用@db_session（）修饰器或db_session（）上下文管理器。当会话结束时，它执行以下操作：

• 如果数据已更改且未发生异常，则提交事务，否则将回滚事务
• 释放db的connection到连接池
• 清除标识映射缓存

如果忘记在必要时指定db_session（），pony将在处理数据库时引发异常。

# Example of using the @db_session() decorator:
@db_session
def check_user(username):
    return User.exists(username=username)

# Example of using the db_session() context manager:
def process_request():
    ...
    with db_session:
        u = User.get(username=username)
        ...

嵌套的db_session

如果您递归地进入db_session（）作用域，例如从另一个用@db_session（）修饰的函数调用一个用@db_session（）修饰的函数，那么pony将不会创建新的会话，而是为两个函数共享同一个会话。数据库会话在离开最外层的db_session（）修饰器或上下文管理器的作用域时结束。

db_session的cache

Pony在几个阶段缓存数据以提高性能。它缓存：

• 生成器表达式转换的结果。如果在程序中多次使用同一个生成器表达式查询，则只会将其转换为SQL一次。这个缓存对于整个程序是全局的，不仅仅是对于单个数据库会话
• 从数据库创建或加载的对象。离开db_session（）作用域或事务回滚时清除此缓存。
• 查询结果。如果用相同的参数再次调用相同的查询，pony将从缓存返回查询结果。一旦任何实体实例发生更改，就会清除此缓存。离开db_session（）作用域或事务回滚时清除此缓存

多数据库混合使用

如果一个session中，使用到了多个数据库，那么在离开session时Pony会针对每个db执行commit()或rollback()。如果需要在退出函数之前提交到一个数据库，可以使用db1.commit()或db2.commit()方法。

乐观的并发控制

默认情况下，pony使用乐观并发控制概念来提高性能。Pony不会获取数据库的行锁，取而代之的是，它会验证是否有其他事务修改了它已读取或正在尝试修改的数据。如果检查到冲突的修改，提交事务时就会抛出一个异常：

OptimisticCheckError, 'Object XYZ was updated outside of current transaction'

关于乐观锁和悲观锁的分析：
<a href="https://docs.ponyorm.org/transactions.html#optimistic-concurrency-control" target="_blank">https://docs.ponyorm.org/transactions.html#optimistic-concurrency-control</a>