Python: PonyORM设计精巧的ORM框架

pip install pony

# -*- coding: utf-8 -*-
from pony.orm import Database, db_session, Required, Set, select, set_sql_debug
# 创建数据库对象
db = Database()
# 建立数据库连接
db.bind(provider='sqlite', filename=':memory:')
# 开启debug,打印sql
set_sql_debug(True)
# 建立实体类
class Person(db.Entity):
    name = Required(str)
    age = Required(int)
    cars = Set('Car')
class Car(db.Entity):
    make = Required(str)
    model = Required(str)
    owner = Required(Person)
# 建表
db.generate_mapping(create_tables=True)
"""
CREATE TABLE "Person" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "age" INTEGER NOT NULL
)
CREATE TABLE "Car" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "make" TEXT NOT NULL,
  "model" TEXT NOT NULL,
  "owner" INTEGER NOT NULL REFERENCES "Person" ("id") ON DELETE CASCADE
)
SELECT "Car"."id", "Car"."make", "Car"."model", "Car"."owner"
FROM "Car" "Car"
WHERE 0 = 1
SELECT "Person"."id", "Person"."name", "Person"."age"
FROM "Person" "Person"
WHERE 0 = 1
"""
# 插入数据
with db_session:
    p1 = Person(name='John', age=20)
    p2 = Person(name='Mary', age=22)
    p3 = Person(name='Bob', age=30)
    c1 = Car(make='Toyota', model='Prius', owner=p2)
    c2 = Car(make='Ford', model='Explorer', owner=p3)
"""
INSERT INTO "Person" ("name", "age") VALUES (?, ?)
['John', 20]
INSERT INTO "Person" ("name", "age") VALUES (?, ?)
['Mary', 22]
INSERT INTO "Person" ("name", "age") VALUES (?, ?)
['Bob', 30]
INSERT INTO "Car" ("make", "model", "owner") VALUES (?, ?, ?)
['Toyota', 'Prius', 2]
INSERT INTO "Car" ("make", "model", "owner") VALUES (?, ?, ?)
['Ford', 'Explorer', 3]
"""
# 更新数据
with db_session:
    p = Person.get(id=2)  # 使用get来获取一条数据，如果没有查到会返回None
    p.name = "new name"
"""
SELECT "id", "name", "age"
FROM "Person"
WHERE "id" = ?
[2]
UPDATE "Person"
SET "name" = ?
WHERE "id" = ?
['new name', 2]
"""
# 查询数据
with db_session:
    for person in select(p for p in Person if p.age > 20)[:]:
        print(person.name, person.age, person.cars)
"""
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
WHERE "p"."age" > 20
SELECT "id", "make", "model", "owner"
FROM "Car"
WHERE "owner" = ?
[2]
SELECT "id", "make", "model", "owner"
FROM "Car"
WHERE "owner" = ?
[3]
"""