程序人生►Python

Python-pymysql的使用

一、操作数据库模板

pymysql是Python中操作mysql的模块，（使用方法几乎和MySQLdb相同，但是在Python3中，mysqldb这个库已经不能继续使用了）

下载安装方法：

方法一、

在cmd中输入下面的命令即可下载：

1
pip3 install pymysql

方法二、

在pycharm集成开发环境中进行安装模块：

File–>Settings–>Project:xxx–>Project Interpreter–>右侧有个小 ‘+’ 号，点击—>在输入框里输入要安装的模块（pymysql）–>左下角Install Package

img

img

1 连接数据库—>创建游标—>执行SQL—>fetch获得数据，进行业务处理—>关闭游标—>commit—>关闭数据库连接

二、基本使用

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
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接
conn=pymysql.connect(host='localhost',user='root',password='',database='db2',charset='utf8')       #与数据库的服务端建立连接，databases是我们要查询的表所在的数据库
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#配置结果集为字典形式
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

#执行sql语句
sql='select * from user where username="%s" and password="%s"' %(user,pwd) #注意%s需要加引号，就是一条基本的sql语句,事先要在user表中插入数据，这样查询才会有结果
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目,我只在表中插入一条记录，查询成功最多所以也就一条记录数
print(res)

cursor.close()
conn.close()
if res:
    print('登录成功')
else:
    print('登录失败')

三、sql注入问题

首先明确一点：–是sql语句的注释，他会注释掉–sql后面的语句，语法为：–sql语句

根本原理：就根据程序的字符串的拼接，user=’%s’，我们输入一个xxx’ – haha,用我们输入的xxx加’在程序中拼接成一个判断条件name=’xxx’ – haha‘

1
2
3
4
5
#1、sql注入之：用户名存在，绕过密码
xingxing' -- 任意字符

#2、sql注入之：用户不存在，绕过用户名与密码
xxx' or 1=1 -- 任意字符

准备条件：

首先自己先创建一个库，在库下面创建一个表，在表中插入一些记录，准备好条件我们就可以来测试一下如何跳过用户名或密码直接登陆吧

img sql注入问题

那么我们如何避免用户通过输入特殊字符来跳过用户名或密码直接登陆呢

解决办法：

1
2
3
4
5
6
7
8
# 原来是我们对sql进行字符串拼接
# sql="select * from user where user='%s' and pwd='%s'" %(user,pwd)
# print(sql)
# res=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from user where user=%s and pwd=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

代码实现：

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
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接
#localhost也可换成'127.0.0.1',port=3306
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='',database='db7',charset='utf8')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)       #返回的结果是列表内套着一个一个字典的形式


#执行sql语句
#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql='select * from user where user=%s and pwd=%s' #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
print(sql)
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。
print(res)

cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('用户名或密码错误')

四、增、删、改

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
import pymysql
'''链接（连接数据库并切换到指定的数据库下）'''
conn=pymysql.connect(host='localhost',user='root',password='',database='db2')
'''游标'''
cursor=conn.cursor()

'''
执行sql语句
part1
'''
sql='insert into user(username,password) values("liuqingzheng","123456");'
res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
print(res)

'''part2'''
# sql='insert into user (username,oassword) values (%s,%s)'
# res=cursor.execute(sql,("xingxing","123"))                  #执行sql语句，返回sql成功插入的记录数
# print(res)

'''part3 插入'''
# sql='insert into user(username,password) values(%s,%s);'
# res=cursor.executemany(sql,[("xingxing","123"),("yueliang","123"),("taiyang","123")]) #可以同时插入多行记录，执行sql语句，返回sql影响成功的行数
# print(res)

'''part4 修改'''
# sql='update user set username=%s , password = %s where id=%s'
# res=cursor.execute(sql,['love','1314',5]) #执行sql语句，返回sql影响成功的行数,修改记录中id=5，将用户名该为love,密码改为1314
# print(res)               #成功修改一条记录内容

'''part5 删除'''
# sql= 'delete from user where id = %s'
# res=cursor.execute(sql,2)               #删除指定的某一条记录，删除第二掉记录
# print(res)
conn.commit() #提交后才发现表中插入记录成功，强调如果没有提交记录是不能插入成功的（增、删、改必须要commit）
cursor.close()    #关闭游标
conn.close()      #关闭连接（套接字）

五、查询之fetchone、fetchmany、fetchall

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
import pymysql
#建立与数据库的连接
conn=pymysql.connect(host='localhost',user='root',password='',database='db2')

#创建游标
cursor=conn.cursor()

#执行sql语句
sql='select * from user;'
rows=cursor.execute(sql)            #返回的是表中所有记录的条数
cursor.scroll(3,mode='absolute') # 相对绝对位置移动，第一个参数是相对绝对位置移动的记录条个数
# cursor.scroll(1,mode='relative') # 相对当前位置移动，第一个参数是相对当前位置移动的记录条个数

#通过fetchone、fetchmany、fetchall拿到查询结果
res1=cursor.fetchone()              #以元组的形式，返回查询记录的结果，默认是从第一条记录开始查询
# res2=cursor.fetchone()            #会接着上一次的查询记录结果继续往下查询
# res3=cursor.fetchone()
# res4=cursor.fetchmany(2)           #查询两条记录会以元组套小元组的形式进行展示
# res5=cursor.fetchall()

#打印查询的最终结果到终端
print(res1)
# print(res2)
# print(res3)
# print(res4)
# print(res5)                         #会元组套小元组的形式将表中的左右记录头查询出来展示在终端
print('%s行数据'%rows)

六、获取插入最新自增id

1
2
3
4
5
6
7
8
9
10
11
12
13
import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',database='db2')
cursor=conn.cursor()

sql='insert into user(username,password) values(%s,%s);'
rows=cursor.execute(sql,['hxx','123'])               #查看的就是当前插入这条记录的id，传入当前查询的sql语句和要插入的记录内容
# rows= cursor.executemany(sql,[('hxx','111'),('hxx1','222')])        #虽然插入2条记录，但是任然会以第一台哦记录的id为准
print(cursor.lastrowid) #在插入语句后查看,查看的id是当前插入的记录的第一条记录的id

conn.commit()
cursor.close()
conn.close()
print(cursor.lastrowid) #放在这也可以

七、注意事项

fetch获取的数据默认是元组，如果想要字典类型：

1
2
3
4
5
6
7
8
9
10
11
import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='',database='db2')
#游标
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)#在此处设置
#执行sql语句
sql='select * from user;'
rows=cursor.execute(sql)
res1=cursor.fetchone()          #查询的结果就是一个字典的形式，字典的key就是对应的字段名，value就是字段名对应的记录内容
# res1=cursor.fetchall()        #查询多条或所有结果是一个列表中套着一个一个字典
print(res1)