Python开发---操作MySQL数据库

博客核心内容：

---

1.初试操作
2.用户初次登陆MySQL数据库的验证以及由此引发SQL注入的问题
3.如何解决MySQL的注入问题
4.pymysql模块对数据库进行增删改查操作
5.通过pymysql获取新增数据的自增ID:通过lastrowid这个方法
6.fetch数据类型的获取

---

（一）pymysql模块初步操作MySQL数据库
pymysql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。
python操作MySQL的原理：通过Socket客户端连接MySQL的服务端,连接之后发送SQL语句,最后关闭连接。
代码示例：

#!/usr/bin/python# -*- coding:utf-8 -*-import pymysql#通过Socket的客户端连接MySQL的服务端,创建相应的连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",database="Test_mine1",charset='utf8')#创建游标cursor_oper = conn.cursor()"""创建完游标cursor之后便可以对数据库MySQL进行相应的操作,注意：游标自带移动特性游标每移动一次,便获取下一条数据"""#执行SQL,并返回受影响的行数v1 = cursor_oper.execute('select * from student')print("student表中数据的行数是:%d"%v1)#通过游标中的fetch方法获取数据库中的数据result1 = cursor_oper.fetchall()print(result1)#执行SQL,并返回受影响的行数v2 = cursor_oper.execute('select * from student where name = "lidong" ')print("%d"%v2)result2 = cursor_oper.fetchone()print(result2)#执行SQL,并返回受影响的行数v3 = cursor_oper.execute('select * from student where id >= 2')print("%d"%v3)result3 = cursor_oper.fetchmany(1)print(result3)#关闭游标和链接cursor_oper.close()conn.close()

运行结果：

student表中数据的行数是:4((1, 'zhangsan', 'nan'), (2, 'lidong', 'nan'), (3, 'wangting', 'nv'), (4, '张明', 'nan'))1(2, 'lidong', 'nan')3((2, 'lidong', 'nan'),)

（二）用户初次登陆MySQL数据库的验证以及由此引发SQL注入的问题
相关知识点：

用户登录:socket链接数据库,并进行SQL语句的定制基于数据库的登录验证SQL注入问题的原因:如果通过字符串格式化去拼接SQL语句,将会带来SQL注入的问题。SQL注入问题的解决措施:pymysql已经帮我们解决这个问题了，所以我们就不要在通过字符串格式化去拼接SQL语句了,在%s两边不要加引号了，参数在execute的第二个参数传值,而不要进行格式化了cursor_oper.execute(sql,[user,pass_wd])这个时候有点让我想起了C语言中的处理方案.

对比图：

示例程序：初步登陆验证程序

#!/usr/bin/python# -*- coding:utf-8 -*-"""用户登录:socket链接数据库,并进行SQL语句的定制基于数据库的登录验证SQL注入问题的原因:如果通过字符串格式化去拼接SQL语句,将会带来SQL注入的问题。SQL注入问题的解决措施:pymysql已经帮我们解决这个问题了，所以我们就不要在通过字符串格式化去拼接SQL语句了,在%s两边不要加引号了，参数在execute的第二个参数传值,而不要进行格式化了cursor_oper.execute(sql,[user,pass_wd])这个时候有点让我想起了C语言中的处理方案."""import pymysqluser = input("请输入用户名:")pass_wd = input("请输入密码:")#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()"""客户端与数据库建立链接之后,便可以进行相应的操作,注意:SQL语句中相应的字段要加引号"""sql = """      select *      from userinfo      where username = '%s' and pass_wd = '%s'      """%(user,pass_wd)print(sql)v1 = cursor_oper.execute(sql)print(v1)result = cursor_oper.fetchall()print(result)cursor_oper.close()conn.close()

运行结果1示例：

请输入用户名:alex请输入密码:841807      select *      from userinfo      where username = 'alex' and pass_wd = '841807'1((1, 'alex', '841807'),)

运行结果2示例：（SQL注入的问题）

（三）如何解决MySQL的注入问题
原因：SQL注入问题的原因:如果通过字符串格式化去拼接SQL语句,将会带来SQL注入的问题。
方法：不通过字符串格式化去拼接SQL语句，并在cursor_oper.execute(sql,[user,pass_wd])中传入第二个参数。

cursor_oper.execute(sql,[user,pass_wd])

示例程序：MySQL注入问题的解决方法

#!/usr/bin/python# -*- coding:utf-8 -*-"""用户登录:socket链接数据库,并进行SQL语句的定制基于数据库的登录验证SQL注入问题的原因:如果通过字符串格式化去拼接SQL语句,将会带来SQL注入的问题。"""import pymysqluser = input("请输入用户名:")pass_wd = input("请输入密码:")#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()"""客户端与数据库建立链接之后,便可以进行相应的操作,注意:SQL语句中相应的字段要加引号"""sql = """      select *      from userinfo      where username = %s and pass_wd = %s      """print(sql)v1 = cursor_oper.execute(sql,[user,pass_wd])print(v1)result = cursor_oper.fetchall()print(result)cursor_oper.close()conn.close()

运行结果：

请输入用户名:alex请输入密码:841807      select *      from userinfo      where username = %s and pass_wd = %s1((1, 'alex', '841807'),)

（四）pymysql模块对数据库进行增删改查操作
核心：除了查询操作，其余三个操作都要通过conn.commit()提交，不然无法保存新建或者修改的数据。
①查询操作：略（看上）
②插入操作（代码示例）：

#!/usr/bin/python# -*- coding:utf-8 -*-"""通过pymysql对MySQL进行增、删、改操作"""import pymysql#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()"""客户端与数据库建立链接之后,便可以进行相应的操作"""sql = """      insert into userinfo(username,pass_wd) values(%s,%s)      """print(sql)v1 = cursor_oper.execute(sql,['zhangyiheng','6688'])print("表中受影响的行数是:%d"%v1)#凡是涉及到增删改查操作,都需要通过链接conn向数据库中提交操作,随后才能生效conn.commit()cursor_oper.close()conn.close()

运行结果：

③删除操作（代码示例）：

#!/usr/bin/python# -*- coding:utf-8 -*-"""通过pymysql对MySQL进行增、删、改操作"""import pymysql#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()"""客户端与数据库建立链接之后,便可以进行相应的操作"""sql = """      delete from userinfo where username = %s      """print(sql)v1 = cursor_oper.execute(sql,['zhangyiheng'])print("表中受影响的行数是:%d"%v1)conn.commit()cursor_oper.close()conn.close()

运行结果：

④更新操作：（代码示例）

#!/usr/bin/python# -*- coding:utf-8 -*-"""通过pymysql对MySQL进行增、删、改操作"""import pymysql#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()"""客户端与数据库建立链接之后,便可以进行相应的操作"""sql = """      update userinfo set pass_wd = %s  where username = %s       """print(sql)v1 = cursor_oper.execute(sql,['343434','alex'])print("表中受影响的行数是:%d"%v1)conn.commit()cursor_oper.close()conn.close()

运行结果：

（五）.通过pymysql获取新增数据的自增ID:通过lastrowid这个方法
代码示例：

#!/usr/bin/python# -*- coding:utf-8 -*-"""通过pymysql获取新增数据的自增ID:通过lastrowid这个方法"""import pymysql#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()"""客户端与数据库建立链接之后,便可以进行相应的操作"""sql = """      insert into userinfo(username,pass_wd) values(%s,%s)      """print(sql)v1 = cursor_oper.execute(sql,['wangting','5566'])print("表中受影响的行数是:%d"%v1)#凡是涉及到增删改查操作,都需要通过链接conn向数据库中提交操作,随后才能生效conn.commit()#获取刚刚增加的数据的行健print(type(cursor_oper.lastrowid))print(cursor_oper.lastrowid)cursor_oper.close()conn.close()

运行结果：

      insert into userinfo(username,pass_wd) values(%s,%s)表中受影响的行数是:1<class 'int'>10

图片展示：

（六）.fetch数据类型的获取
function:fetchall()、fetone()、fetmanay()默认获取的数据是元祖类型，如果想要或者字典类型的数据，方法：
方法：游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
前后对比：

conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor()conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor(cursor=pymysql.cursors.DictCursor)

代码示例：

#!/usr/bin/python# -*- coding:utf-8 -*-"""function:fetchall()、fetone()、fetmanay()获取的结果转化为列表,有点类似于之前dict(zip)的操作"""import pymysql#用户验证之前与数据库建立连接conn = pymysql.Connect(host="192.168.80.100",port=3306,user="root",password="admin",                       database="Test_mine1",charset='utf8')cursor_oper = conn.cursor(cursor=pymysql.cursors.DictCursor)"""客户端与数据库建立链接之后,便可以进行相应的操作"""sql = """      select * from userinfo       """print(sql)v1 = cursor_oper.execute(sql)print("表中受影响的行数是:%d"%v1)result = cursor_oper.fetchall()print(result)cursor_oper.close()conn.close()

运行结果：

      select * from userinfo 表中受影响的行数是:6[{'uid': 1, 'pass_wd': '343434', 'username': 'alex'}, {'uid': 3, 'pass_wd': '789789', 'username': 'yuanhao'}, {'uid': 5, 'pass_wd': '99999', 'username': 'eric'}, {'uid': 7, 'pass_wd': '8866', 'username': 'lihao'}, {'uid': 8, 'pass_wd': '8866', 'username': 'lihao'}, {'uid': 10, 'pass_wd': '5566', 'username': 'wangting'}]Process finished with exit code 0