前言
pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同。但目前pymysql支持python3.x而后者不支持3.x版本。
本文测试python版本:2.7.11。mysql版本:5.6.24
一、安装
二、使用操作
1、执行SQL
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
|
import
pymysql
? ?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,port
=
3306
,user
=
‘root‘
,passwd
=
‘
‘,db=‘
tkq1
‘,charset=‘
utf8‘)
cursor
=
conn.cursor()
? ?
effect_row
=
cursor.execute(
"select * from tb7"
)
? ?
? ?
? ?
? ?
conn.commit()
? ?
cursor.close()
conn.close()
|
注意:存在中文的时候,连接需要添加charset=‘utf8‘,否则中文显示乱码。
2、获取查询数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
cursor.execute(
"select * from tb7"
)
?
row_1
=
cursor.fetchone()
print
row_1
?
?
conn.commit()
cursor.close()
conn.close()
|
3、获取新创建数据自增ID
可以获取到最新自增的ID,也就是最后插入的一条数据ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
effect_row
=
cursor.executemany(
"insert into tb7(user,%s)"
,[(
"u3"
,
"u3pass"
,
"11113"
),(
"u4"
,
"u4pass"
,
"22224"
)])
conn.commit()
cursor.close()
conn.close()
new_id
=
cursor.lastrowid?????
print
new_id
|
4、移动游标
操作都是靠游标,那对游标的控制也是必须的
1
2
3
4
|
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
?
cursor.scroll(
1
,mode
=
‘relative‘
)
cursor.scroll(
2
,mode
=
‘absolute‘
)
|
?
5、fetch数据类型
关于默认获取的数据是元祖类型,如果想要或者字典类型的数据,即:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor(cursor
=
pymysql.cursors.DictCursor)
cursor.execute(
"select * from tb7"
)
?
row_1
=
cursor.fetchone()
print
row_1
?
conn.commit()
cursor.close()
conn.close()
|
6、调用存储过程
a、调用无参存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
?
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor(cursor
=
pymysql.cursors.DictCursor)
cursor.callproc(
‘p2‘
)?
?
row_1
=
cursor.fetchone()
print
row_1
?
?
conn.commit()
cursor.close()
conn.close()
|
b、调用有参存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
?
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor(cursor
=
pymysql.cursors.DictCursor)
?
cursor.callproc(
‘p1‘
,args
=
(
1
,
22
,
3
,
4
))
cursor.execute(
"select @p1,@_p1_1,@_p1_2,@_p1_3"
)?
row_1
=
cursor.fetchone()
print
row_1
?
?
conn.commit()
cursor.close()
conn.close()
|
三、关于pymysql防注入
?1、字符串拼接查询,造成注入
正常查询语句:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
user
=
"u1"
passwd
=
"u1pass"
sql
=
"select user,pass from tb7 where user=‘%s‘ and pass=‘%s‘"
%
(user,passwd)
row_count
=
cursor.execute(sql) row_1
=
cursor.fetchone()
print
row_count,row_1
?
conn.commit()
cursor.close()
conn.close()
|
构造注入语句:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
?
user
=
"u1‘ or ‘1‘-- "
passwd
=
"u1pass"
sql
=
"select user,passwd)
?
?
row_count
=
cursor.execute(sql)
row_1
=
cursor.fetchone()
print
row_count,row_1
?
?
conn.commit()
cursor.close()
conn.close()
|
?
?2、避免注入,使用pymysql提供的参数化语句
正常参数化查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
?
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
user
=
"u1"
passwd
=
"u1pass"
row_count
=
cursor.execute(
"select user,pass from tb7 where user=%s and pass=%s"
,(user,passwd))
row_1
=
cursor.fetchone()
print
row_count,row_1
?
conn.commit()
cursor.close()
conn.close()
|
构造注入,参数化查询注入失败。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
?
user
=
"u1‘ or ‘1‘-- "
passwd
=
"u1pass"
row_count
=
cursor.execute(
"select user,passwd))
?
row_1
=
cursor.fetchone()
print
row_count,row_1
?
conn.commit()
cursor.close()
conn.close()
|
结论:excute执行SQL语句的时候,必须使用参数化的方式,否则必然产生SQL注入漏洞。
3、使用存mysql储过程动态执行SQL防注入
使用MYSQL存储过程自动提供防注入,动态传入SQL到存储过程执行语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
delimiter
DROP PROCEDURE IF EXISTS proc_sql
CREATE PROCEDURE proc_sql (
??
in
nid1
INT
,
??
in
nid2
INT
,
??
in
callsql VARCHAR(
255
)
??
)
BEGIN
??
set
@nid1
=
nid1;
??
set
@nid2
=
nid2;
??
set
@callsql
=
callsql;
????
PREPARE myprod FROM @callsql;
-
- ??
PREPARE prod FROM
‘select * from tb2 where nid>? and nid<?‘
;? 传入的值为字符串,?为占位符
-
- ??
用@p1,和@p2填充占位符
????
EXECUTE myprod USING @nid1,@nid2;
??
DEALLOCATE prepare myprod;
?
END
delimiter ;
|
1
2
3
4
|
set
@nid1
=
12
;
set
@nid2
=
15
;
set
@callsql
=
‘select * from tb7 where nid>? and nid<?‘
;
CALL proc_sql(@nid1,@nid2,@callsql)
|
pymsql中调用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
import
pymysql
?
conn
=
pymysql.connect(host
=
‘127.0.0.1‘
,db=‘
tkq1‘)
cursor
=
conn.cursor()
mysql
=
"select * from tb7 where nid>? and nid<?"
cursor.callproc(
‘proc_sql‘
,args
=
(
11
,
15
,mysql))
?
rows
=
cursor.fetchall()
print
rows
conn.commit()
cursor.close()
conn.close()
|
四、使用with简化连接过程
每次都连接关闭很麻烦,使用上下文管理,简化连接过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
?
import
pymysql
import
contextlib
@contextlib
.contextmanager
def
mysql(host
=
‘127.0.0.1‘
,charset=‘
utf8‘):
??
conn
=
pymysql.connect(host
=
host,port
=
port,user
=
user,passwd
=
passwd,db
=
db,charset
=
charset)
??
cursor
=
conn.cursor(cursor
=
pymysql.cursors.DictCursor)
??
try
:
????
yield
cursor
??
finally
:
????
conn.commit()
????
cursor.close()
????
conn.close()
?
with mysql() as cursor:
??
print
(cursor)
??
row_count
=
cursor.execute(
"select * from tb7"
)
??
row_1
=
cursor.fetchone()
??
print
row_count,row_1
|
总结
以上就是关于Python中pymysql模块的全部内容,希望对大家学习或使用python能有一定的帮助,如果有疑问大家可以留言交流。
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|