Pony is an advanced object-relational mapper
文档：
- PyPI: https://pypi.org/project/pony/
- Github: https://github.com/ponyorm/pony
- doc: https://docs.ponyorm.org/
安装
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]
"""
