Python中的ORM使用之peewee_python orm连接sqlserver

本来要看看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))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172