抛弃丑陋，拥抱优雅--Pythonic的Pony ORM

pip install pony

from pony.orm import *

from pony import orm

db = Database()

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)

show(Person)

class Person(Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    age = Required(int)
    cars = Set(Car)

db.bind(provider='sqlite', filename=':memory:')

##### SQLite
db.bind(provider='sqlite', filename=':memory:')
db.bind(provider='sqlite', filename='database.sqlite', create_db=True)

#####  PostgreSQL
db.bind(provider='postgres', user='', password='', host='', database='')

#####  MySQL
db.bind(provider='mysql', host='', user='', passwd='', db='')

#####  Oracle
db.bind(provider='oracle', user='', password='', dsn='')

db.generate_mapping(create_tables=True)

set_sql_debug(True)

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)
commit()

GET CONNECTION FROM THE LOCAL POOL
BEGIN IMMEDIATE TRANSACTION
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]

COMMIT

@db_session
def print_person_name(person_id):
    p = Person[person_id]
    print(p.name)
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

@db_session
def add_car(person_id, make, model):
    Car(make=make, model=model, owner=Person[person_id])
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

with db_session:
    p = Person(name='Kate', age=33)
    Car(make='Audi', model='R8', owner=p)
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

BEGIN IMMEDIATE TRANSACTION
INSERT INTO "Person" ("name", "age") VALUES (?, ?)
['Kate', 33]

INSERT INTO "Car" ("make", "model", "owner") VALUES (?, ?, ?)
['Audi', 'R8', 4]

COMMIT
RELEASE CONNECTION

select(p for p in Person if p.age > 20)

<pony.orm.core.Query at 0x1f7ffb2bb38>

select(p for p in Person if p.age > 20)[:]

GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
WHERE "p"."age" > 20






[Person[2], Person[3], Person[4]]

dir(select(p for p in Person if p.age > 20))

[...
 'avg',
 'count',
 'delete',
 'distinct',
 'exists',
 'filter',
 'first',
 'for_update',
 'get',
 'get_sql',
 'limit',
 'max',
 'min',
 'order_by',
 'page',
 'prefetch',
 'random',
 'show',
 'sort_by',
 'sum',
 'to_json',
 'where',
 'without_distinct']

select(p for p in Person).order_by(Person.name)[:2]

SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
ORDER BY "p"."name"
LIMIT 2






[Person[3], Person[1]]

select(p for p in Person).order_by(Person.name)[:2].show()

SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
ORDER BY "p"."name"
LIMIT 2

id|name|age
--+----+---
3 |Bob |30 
1 |John|20 

Car.select().show()

SELECT "c"."id", "c"."make", "c"."model", "c"."owner"
FROM "Car" "c"

id|make  |model   |owner    
--+------+--------+---------
1 |Toyota|Prius   |Person[2]
2 |Ford  |Explorer|Person[3]
3 |Audi  |R8      |Person[4]

persons = select(p for p in Person if 'o' in p.name)
for p in persons:
    print(p.name, p.age)

SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
WHERE "p"."name" LIKE '%o%'

John 20
Bob 30

select(p.name for p in Person if p.age != 30)[:]

SELECT DISTINCT "p"."name"
FROM "Person" "p"
WHERE "p"."age" <> 30






['John', 'Mary', 'Kate']

select((p, count(p.cars)) for p in Person)[:]

SELECT "p"."id", COUNT(DISTINCT "car"."id")
FROM "Person" "p"
  LEFT JOIN "Car" "car"
    ON "p"."id" = "car"."owner"
GROUP BY "p"."id"






[(Person[1], 0), (Person[2], 1), (Person[3], 1), (Person[4], 1)]

max(p.age for p in Person)

SELECT MAX("p"."age")
FROM "Person" "p"






33

p1 = Person[1]

p1.name

'John'

mary = Person.get(name='Mary')

SELECT "id", "name", "age"
FROM "Person"
WHERE "name" = ?
LIMIT 2
['Mary']

show(mary)

instance of Person
id|name|age
--+----+---
2 |Mary|22 

mary.age += 1
commit()

BEGIN IMMEDIATE TRANSACTION
UPDATE "Person"
SET "age" = ?
WHERE "id" = ?
  AND "name" = ?
  AND "age" = ?
[24, 2, 'Mary', 23]

COMMIT

x = 25
Person.select_by_sql('SELECT * FROM Person p WHERE p.age < $x')

BEGIN IMMEDIATE TRANSACTION
SELECT * FROM Person p WHERE p.age < ?
[25]






[Person[1], Person[2]]

x = 20
db.select('name FROM Person WHERE age > $x')

select name FROM Person WHERE age > ?
[20]






['Mary', 'Bob', 'Kate']

import this

The Zen of Python, by Tim Peters

Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren't special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one-- and preferably only one --obvious way to do it.
Although that way may not be obvious at first unless you're Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it's a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea -- let's do more of those!