前言

        我在另一篇文章中全面详细地讲解了MySQL数据库的基础知识和语法，对MySQL数据库感兴趣和想复习MySQL的小伙伴，强烈推荐大家参考本人的另一篇文章：

一文学会MySQL数据库——小白零基础保姆级教程，从安装-配置环境开始——不能再细了！（持续更新，求赞求收藏！）

        本文主要详细讲述，如何使用python操作MySQL数据，是通过pymysql模块实现的。

1 概述

 pymysql是Python中的一个类库, 主要是通过python代码来操作SQL语句的。

1.1 pymysql模块的作用

问题: 我们如何将10万行数据插入到数据库?

回答: 我们发现如果用之前客户端几乎是不可能完全这个任务的, 因为我们不可能去构造出那个插入10万行数据的SQL. 可是，如果我们有一个功能能够插入一行数据，借助程序强大的特点：重复，就可以轻而易举的就将10w行数据收入麾下. 我们可以使用pymysql模块完成在Python中实现这样的操作。

 1.2 Python中操作MySQL步骤

操作步骤:

1. 导入模块             # import pymysql
2. 创建连接对象      # connect,即python连接mysql数据库的对象
3. 创建游标             # curdor，可以执行SQL语句的对象
4. 执行sql语句，获取结果集
5. 操作结果集
6. 释放资源

大白话解释上述步骤：

        1. 你考上了清华大学
        2. 你来到了清华, 找到了导员和清华大学建立了连接
        3. 导员帮你联系授课老师
        4. 授课老师给你授课, 传授知识
        5. 你听课，写作业，作报告，参加考试等
        6. 你从清华毕业了，跟授课老师和导员说再见

流程图：

 2 pymysql查询数据

# 1.导包 import pymysql # 若没有安装这个库，安装此库的命令：pip install pymysql # 2.创建连接对象 conn = pymysql.connect( host='数据库所在机器的IP地址', # 本机就写：localhost port=3306, # 要连接到的数据库端口号，MySQL是3306 user='账号', # 数据库的用户名 password='密码', # 数据库的密码 database='数据库名称', # 要操作的数据库 charset='utf8' # 码表 ) # 3.创建游标--可执行SQL语句的对象 cursor = conn.cursor() # 4.执行SQL语句，获取结果集---假如需求是：查询order表中所有的订单信息 sql = 'select * from order;' #order是要操作的数据库中的表名 row_count = cursor.execute(sql) # 执行SQL语句，返回 受影响的行数 print(f'首影响的行数为：{row_count}') # 5.操作结果集 data1 = cursor.fetchone() # 获取一条数据，组封装 data2 = cursor.fetchmany(6) # 获取6条数据，组嵌套的方式 ((第一条),(第二条)...) data3 = cursor.fetchall() # 获取所有数据，组嵌套的方式 ((第一条),(第二条)...) #查看data3 for line in data3: print(line) # 6.释放资源 cursor.close() conn.close()

 3. 数据提交和数据回滚

 3.1 数据提交：commit()

 数据库中的基本操作: 增删改查. 上一个章节中我们知道如何完成对数据库中数据的获取其实也就是查, 比如获取一条数据或者获取所有数据等. 接下来我们学习下如何增删改。

查数据, 并不会对原有数据库中的数据造成影响. 而增删改这三个操作都会对原有数据库中的数据造成影响，也就说查数据不会修改原有数据空中的数据, 而增删改会修改原有数据库中的数据。

当对原有数据库中数据有修改时需要使用commit()提交数据到数据库：conn.commit()
这里可以理解为：数据库询问是否确定修改数据，然后commit()就是确定修改的意思。

3.2  数据回滚：rollback() 

 当我们在使用pymysql对数据进行相应的操作时, 会有可能会有一些错误操作, 这时如果想要数据返回到最原始的状态可以使用数据回滚操作。

数据回滚是需要在 commit() 之前才有效, 也就是说数据还没有确定修改这时候使用数据回滚才是有效的。

回滚数据到什么都没做的原始状态 即撤销刚刚所有的修改操作：conn.rollback()
 

4 pymysql的增删改查操作

 案例: 演示pymysql的curd操作, 增删改操作(更新语句), 记得要提交事务, 否则结果不会被写到数据表中。

首先在要操作的数据库中创建一个数据表

# 新建poets表 包括：诗人-出生年份-代表作品 create table poets( id int primary key auto_increment, name varchar(10), # 姓名 birthyear int, # 出生年份 masterpiece varchar(10) # 代表作 ); # 查看数据表 select * from poets;

4.1增加数据的操作

import pymysql # 增 def add_data(): # 1 获取连接对象 conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() # 3 执行SQL语句，获取结果集---在poets表中添加：王维，693年出生，代表作品《山居秋暝》 sql = 'insert into poets values(null,"李白",701,"将进酒"),(null,"王维",693,"山居秋暝"),(null,"白居易",772,"钱塘湖春行");' row_count = cursor.execute(sql) ptint(f'受影响的行数：{row_count}') # 4 操作结果集---提交事务 conn.commit() # 5 释放资源 cursor.close() conn.close() # main函数, 作为程序的主入口. if __name__ == '__main__': # 测试: 添加数据. add_data()

 4.2 修改数据的操作

# 改 def update_data(): # 1 获取连接对象. conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() # 3 执行SQL语句，获取结果集 sql = 'update poets set name="杜甫",birthyear=712,masterpiece="望岳" where id = 2;' row_count=cursor.execute(sql) ptint(f'受影响的行数：{row_count}') # 4 操作结果集---提交事务 conn.commit() # 5 释放资源 cursor.close() conn.close() # main函数, 作为程序的主入口. if __name__ == '__main__': # 测试: 修改数据. update_data() 

4.3 删除数据的操作 

# 删 def delete_data(): # 1. 获取连接对象. conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() # 3 执行SQL语句，获取结果集 sql = 'delete from poets where id = 3;' row_count = cursor.execute(sql) print(f'受影响的行数为: {row_count}') # 4 操作结果集. conn.commit() # 5 释放资源. cursor.close() conn.close() # main函数, 作为程序的主入口. if __name__ == '__main__': # 测试: 删除数据. delete_data()

4.4 查询数据的操作 

# 查 def query_data(): # 1 获取连接对象. conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() # 3 执行SQL语句，获取结果集 sql = 'select * from poets;' row_count = cursor.execute(sql) print(f'受影响的行数为: {row_count}') # 4 操作结果集. datas = cursor.fetchall() for line in datas: print(line) # 5 释放资源. cursor.close() conn.close() # main函数, 作为程序的主入口. if __name__ == '__main__': # 测试: 查询数据. query_data()

5 pymysql演示注入攻击及解决方法 

5.1 注入攻击

import pymysql # 0. 提示用户录入他/她的账号 或 密码. uname = input('请录入您的账号: ') pwd = input('请录入您的密码: ') # 1. 获取连接对象. conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() # 3 执行SQL语句，获取结果集 # SQL写法1: 插值表达式. # sql = f"select * from users where username='{uname}' and password='{pwd}';" # SQL写法2：占位符 sql = "select * from user where username='%s' and password='%s';" %(uname, pwd) row_count = cursor.execute(sql) # 4. 操作结果集. print("登陆成功" if row_count > 0 else "登陆失败") # 5. 释放资源. cursor.close() conn.close()

5.2 解决注入攻击

 解决SQL注入攻击问题的思路: 预编译 + 占位符思想,  提前先对SQL语句进行预编译, 这一步已经确定SQL语句的格式了, 之后无论传入什么内容, 都只会当做普通字符来处理。

import pymysql # 0. 提示用户录入他/她的账号 或 密码. uname = input('请录入您的账号: ') pwd = input('请录入您的密码: ') # 1. 获取连接对象. conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() # 3 执行SQL语句，获取结果集 # 细节1: %s代表要添加的内容, 在这一步已经确定好SQL语句的格式了, 之后无论传入什么内容, 都只会当做普通的字符来处理. sql = "select * from users where username=%s and password=%s;" # 细节2: 给占位符填充值, 即: 定义1个列表, 记录: 一会儿要填充到SQL语句中的值. params = [uname, pwd] # 细节3: 执行SQL语句时, 传入参数(列表)即可. row_count = cursor.execute(sql, params) # 4. 操作结果集. print("登陆成功" if row_count > 0 else "登陆失败") # 5. 释放资源. cursor.close() conn.close()

 6 pymysql操作事务

MySQL默认开启了事务的自动提交功能, 每个语句都是1个单独的事务, 执行之后, 就会提交. 

import pymysql # 1. 获取连接对象. conn = pymysql.connect(host='IP地址',port=3306, user='用户名',password='密码', database='数据库名称',charset='utf8') # 2 获取游标对象 cursor = conn.cursor() try: # 这里写的是, 可能出问题的代码, 即: 具体的转账逻辑. # 开启事务, 标记着: 转账从这里开始. conn.begin() # 3. 执行SQL语句, 获取结果集. # 3.1 张三 - 1000 sql1 = 'update account set money = money - 1000 where name="张三";' row_count1 = cursor.execute(sql1) # 模拟程序出问题了. # print(1 / 0) # 3.2 李四 + 1000 sql2 = 'update account set money = money + 1000 where name="李四";' row_count2 = cursor.execute(sql2) except Exception as e: # 走这里, 说明程序出问题了, 提示, 然后回滚即可. conn.rollback() # 事务回滚 print('程序出问题了, 请稍后再试!') else: # 走到这里, 说明try没有问题, 提交事务即可. conn.commit() # 提交事务 print('转账成功' if row_count1 == 1 and row_count2 == 1 else '转账失败') finally: # 释放资源, 无论try内容成功与否, 都会走这里. cursor.close() conn.close() print('释放资源完毕!')