Python中的ORM使用之peewee

本来要看看sqlalchemy这个老牌重量级ORM的,可以用sqlacodegen自动生成实体层。如下所示
sqlacodegen --noviews --noconstraints --noindexes --outfile d:\\models.py mysql://test:test@122.30.100.12:3388/test  
--noviews 不对视图生成model
--outfile 后面跟的是将生成的代码输出到哪个文件保存



不过因为没时间啊，还是先使用peewee这个轻量级的ORM吧。


一、安装peewee模块：
windows:cmd 下 输入：pip install peewee



二、根据数据库表生成模型
#在cmd中执行命令
python -m pwiz -e mysql -H localhost -p3306 -uroot -Pkkd93kd  web_db > db.py


#会生成如下db.py中的内容：
from peewee import *

database = MySQLDatabase('web_db', **{'host': 'localhost', 'password': 'kkd93kd  ', 'port': 3306, 'user': 'root'})

class UnknownField(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class SyShieldOrder(BaseModel):
    _id = PrimaryKeyField()
    order_up_day = IntegerField(null=True)
    order_up_hours = IntegerField(null=True)
    order_up_moon = IntegerField(null=True)
    order_up_quarter = IntegerField(null=True)
    order_up_week = IntegerField(null=True)
    order_up_year = IntegerField(null=True)
    tu_shopid = CharField(null=True)

    class Meta:
        db_table = 'sy_shield_order'
class SyShieldUser(BaseModel):
    _id = PrimaryKeyField()
    tu_account = CharField(null=True)
    tu_area = CharField(null=True)
    tu_city = CharField(null=True)
    tu_commence = DateField(null=True)
    tu_contract = DateField(null=True)
    tu_cost = IntegerField(null=True)
    tu_domain = CharField(null=True)
    tu_nick = CharField(null=True)
    tu_platform = CharField(null=True)
    tu_province = CharField(null=True)
    tu_realcost = IntegerField(null=True)
    tu_shopid = CharField(null=True)
    tu_version = CharField(null=True)

    class Meta:
        db_table = 'sy_shield_user'


三、调用peewee


1）插入

q = User.insert(username='admin', active=True, registration_expired=False)
q.execute() 


2）更新
q = User.update(active=False).where(User.registration_expired == True)
q.execute() 


3）.删除
q = User.delete().where(User.active == False)
q.execute() 


4）.查询

# 查询名字为Marry的person
grandma = Person.select().where(Person.name == 'Marry').get()

#列出Person表中所有的person
for person in Person.select():
    print person.name, person.is_relative

#查询Pet表中animal_type为cat的所有pet
query = (Pet
         .select(Pet, Person)
         .join(Person)
         .where(Pet.animal_type == 'cat'))

for pet in query:
    print pet.name, pet.owner.name

#查询Pet表中主人名为Bob的所有pet
for pet in Pet.select().join(Person).where(Person.name == 'Bob'):
    print pet.name

#查询Pet表中person为uncle_bob的所有pet
for pet in Pet.select().where(Pet.owner == uncle_bob):
    print pet.name

#查询Pet表中person为uncle_bob结果按pet名排列
for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
    print pet.name

#将Person表中的person按生日降序查询
for person in Person.select().order_by(Person.birthday.desc()):
    print person.name, person.birthday

#查询Person表中person所拥有的pet数量及名字和类型
for person in Person.select():
    print person.name, person.pets.count(), 'pets'
    for pet in person.pets:
        print '      ', pet.name, pet.animal_type

#查询Person表中生日小于1940或大于1960的person
d1940 = date(1940, 1, 1)
d1960 = date(1960, 1, 1)
query = (Person
         .select()
         .where((Person.birthday < d1940) | (Person.birthday > d1960)))

#查询Person表中生日在1940和1960之间的person
for person in query:
    print person.name, person.birthday

query = (Person
         .select()
         .where((Person.birthday > d1940) & (Person.birthday < d1960)))

for person in query:
    print person.name, person.birthday

#按照expression查询person名开头为小写或大写 G 的person
expression = (fn.Lower(fn.Substr(Person.name, 1, 1)) == 'g')
for person in Person.select().where(expression):
    print person.name


5）其他
#连接数据库db
db.connect()

#关闭数据库
db.close()

四、案例：
from datetime import datetime
from src import *

database.connect()

for i in SyShieldUser.select():
    print i.tu_account
    print i.__dict__

for i in range(10):
    data = {
            'tu_account': "user_%s" % str(i),
            'tu_area': "HuaDong",
            'tu_city': "Shanghai",
            }
    print SyShildUser.create(tu_account="user", tu_area="HuaDong", tu_city="Shanghai", tu_shopid="100000%s" % str(i))