Python中的ORM使用之peewee

客官°小女子只卖身不卖艺 2022-07-15 08:27 168阅读 0赞

本来要看看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))