查询单条语句:用来获取用户列表和单个用户
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
|
>>> fields
=
[
"id"
,
"name"
,
"name_cn"
,
"email"
,
"mobile"
]
>>> sql
=
"select %s from users where name ='admin'"
%
','
.join(fields)
>>> sql
"select id,name,name_cn,email,mobile from users where name ='admin'"
>>> cur.execute(sql)
1L
>>> res
=
cur.fetchone()
>>> res
(
16L
, u
'admin'
, u
'admin'
, u
'134556'
, u
'123455'
)
>>> user
=
{}
>>>
for
i,k
in
enumerate
(fields):
... user[k]
=
res[i]
...
>>> user
{
'mobile'
: u
'123455'
,
'email'
: u
'134556'
,
'name_cn'
: u
'admin'
,
'id'
:
16L
,
'name'
: u
'admin'
}
>>>
for
i,k
in
enumerate
(fields):
...
print
i,k i 是索引值,k是fields 里面的值
...
0
id
1
name
2
name_cn
3
email
4
mobile
>>> res[
1
]
u
'admin'
|
插入语句:添加用户
1.0版本
1
2
3
4
5
6
|
>>> fields
=
[
"id"
,
"name"
,
"name_cn"
,
"email"
,
"mobile"
,
"role"
]
>>> fields
[
'id'
,
'name'
,
'name_cn'
,
'email'
,
'mobile'
,
'role'
]
>>> sql
=
"insert into users (%s) values ('fujinzhou','fujinzhou','144@qq.com','136**','ops')"
%
','
.join(fields)
>>> sql
"insert into users (id,name,name_cn,email,mobile,role) values ('fujinzhou','fujinzhou','144@qq.com','136**','ops')"
|
2.0版本
1
2
3
4
5
6
7
8
|
>>> fields
=
[
"name"
,
"name_cn"
,
"email"
,
"mobile"
,
"role"
]
>>> data
=
{
"name"
:
"fujinzhou"
}
>>> data
{
'name'
:
'fujinzhou'
}
>>> data
=
{
"name"
:
"fujinzhou"
,
"name_cn"
:
"fujinzhou"
,
"email"
:
"144@qq.com"
,
"mobile"
:
"1122"
,
"role"
:
"ops"
}
>>> sql
=
"insert into users (%s) values %s"
%
(
','
.join(fields),
','
.join([
'"%s"'
%
data[k]
for
k
in
fields]))
>>> sql
'insert into users (name,name_cn,email,mobile,role) values "fujinzhou","fujinzhou","144@qq.com","1122","ops"'
|
3.0版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
>>> fields
=
[]
>>> values
=
[]
>>> data
=
{
"name"
:
"fujinzhou"
,
"name_cn"
:
"fujinzhou"
,
"email"
:
"144@qq.com"
,
"mobile"
:
"1122"
,
"role"
:
"ops"
}
>>>
for
k,v
in
data.items():
... fields.append(k)
... values.append(
"'%s'"
%
v)
...
>>> fields
[
'mobile'
,
'role'
,
'name_cn'
,
'name'
,
'email'
]
>>> values
[
"'1122'"
,
"'ops'"
,
"'fujinzhou'"
,
"'fujinzhou'"
,
"'144@qq.com'"
]
>>> sql
=
"insert into users (%s) values (%s)"
%
(
','
.join(fields),
','
.join(values))
>>> sql
"insert into users (mobile,role,name_cn,name,email) values ('1122','ops','fujinzhou','fujinzhou','144@qq.com')"
|
本文转自 shouhou2581314 51CTO博客,原文链接:http://blog.51cto.com/thedream/1839868,如需转载请自行联系原作者