python：pymysql概念、基本操作和注入问题讲解

一、概念

如果使用之前学习的MySQL客户端来完成插入10000条数据的操作，那么这个工作量无疑是巨大的，在这个时候就可以使用pymysql，更高效。

PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，用于 Python 程序与 MySQL 数据库的交互。pymysql连接速度快，协议优化，更适宜于用来处理大批量数据，

二、数据准备

例：
--1. 创建 "京东" 数据库
create database jing_dong charset=utf8;-- 使用 "京东" 数据库
use jing_dong;-- 创建一个商品goods数据表
create table goods(id int unsigned primary key auto_increment not null,name varchar(150) not null,cate_name varchar(40) not null,brand_name varchar(40) not null,price decimal(10,3) not null default 0,is_show bit not null default 1,is_saleoff bit not null default 0
);-- 向goods表中插入数据insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);# 创建用户表
create TABLE user(id int PRIMARY KEY AUTO_INCREMENT,user varchar(30),pwd varchar(30)
);
insert into user(user,pwd) VALUE ('root','123456');

三、安装pymysql

pycharm的客户端安装指令：
命令1: pip  install pymysql  #指令1
命令2: pip  install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple/ #指令2

下面方式也可以安装：

四、pymysql使用

PyMySQL中的增(INSERT)、删(DELETE)、改(UPDATE)操作与事务密切相关，以下是它们之间的关系：

基本关系

1.事务是一组操作的集合：在PyMySQL中，增删改操作通常是在事务的上下文中执行的。

2.默认自动提交：PyMySQL默认启用了自动提交(autocommit=True)，每个增删改语句会立即生效；如果关闭自动提交(autocommit=False)，则需要显式提交事务才能使更改永久生效。

关键点

原子性：事务内的所有增删改操作要么全部成功，要么全部失败回滚(rollback);

一致性：事务执行前后数据库保持一致性状态;

隔离性：事务间的增删改操作相互隔离;

持久性：一旦事务提交，增删改的结果将永久保存。

什么是一致性状态？
一致性状态指的是数据库中的数据满足所有预定义的业务规则和完整性约束，包括：实体完整性：主键不能为空参照完整性：外键关系必须有效用户定义的完整性：如账户余额不能为负、年龄必须大于0等业务规则数据关系正确性：如总金额=单价×数量等计算关系

事务机制确保了PyMySQL中增删改操作的可靠性和数据完整性。

（一）使用步骤

pymysql使用步骤:
1.导入模块
2.创建连接
3.创建游标
4.执行sql
5.关闭游标
6.关闭连接

（二）查询操作

# 1.导入pymysql模块
import pymysql# 2.创建连接对象
conn = pymysql.connect(host='localhost',port=3306,user='root',password='root',database='jing_dong'
)
# 3.创建游标对象
cur = conn.cursor()
# 4.执行sql
row = cur.execute('SELECT * FROM goods')
print(f'影响了{row}行')
# fetchone: 一次拿一条数据
# data1 = cur.fetchone()
# print(data1)
# fetchall: 一次拿剩下的所有的数据
data2 = cur.fetchall()
print(data2)
# 5.关闭游标
cur.close()
# 6.关闭连接
conn.close()

额外分享：关于InnoDB引擎和MyISAM引擎（方便看懂后面代码）

InnoDB要求显式提交不是为了增加复杂度，而是为了提供：更强大的数据完整性保障更灵活的业务逻辑控制更安全的错误恢复机制更高的并发性能

（三）增

# 1.导入pymysql模块
from pymysql import connect# 2.创建连接对象
conn = connect(host='127.0.0.1',port=3306,user='root',password='root',database='jing_dong'
)
# 3.创建游标对象
cur = conn.cursor()
# 4.执行sql语句
row = cur.execute("insert into goods(name,cate_name,brand_name) value ('测试','测试','测试')")
print(f'影响了{row}行')
# innoDB引擎要求必须提交,myisam引擎不支持事务(不commit也可以)
conn.commit()
# 5.关闭游标
cur.close()
# 6.关闭连接
conn.close()

（四）改

# 1.导入pymysql模块
from pymysql import connect# 2.创建连接对象
conn = connect(host='127.0.0.1',port=3306,user='root',password='root',database='jing_dong'
)
# 3.创建游标对象
cur = conn.cursor()
# 4.执行sql语句
row = cur.execute("update goods set price = 99999 where name='测试'")
print(f'影响了{row}行')
# innoDB引擎要求必须提交,myisam引擎不支持事务(不commit也可以)
conn.commit()
# 5.关闭游标
cur.close()
# 6.关闭连接
conn.close()

（五）删

# 1.导入pymysql模块
from pymysql import connect# 2.创建连接对象
conn = connect(host='127.0.0.1',port=3306,user='root',password='root',database='jing_dong'
)
# 3.创建游标对象
cur = conn.cursor()
# 4.执行sql语句
try:row = cur.execute("DELETE FROM goods WHERE name='测试'")print(f'影响了{row}行')# a = 1 / 0
except Exception as e:print(f'老弟啊,出错了呀...详情:{e}')conn.rollback()# innoDB引擎要求必须提交,myisam引擎不支持事务(不commit也可以)
conn.commit()
# 5.关闭游标
cur.close()
# 6.关闭连接
conn.close()

五、关于pymysql注入问题

首先，一个实际应用中：

例：
# 用户登录
username = input('请输入用户名:')
password = input('请输入密码:')
# 后台python代码连接数据库判断
# 1.导入pymysql模块
import pymysql
# 2.创建连接对象
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',database='jing_dong'
)
# 3.创建游标对象
cur = conn.cursor()
# 4.执行sql语句(判断是否登录成功)
row = cur.execute(f"select * from user where user='{username}' and pwd='{password}'")
if row:print('登录成功')
else:print('登录失败')
# 5.关闭游标
cur.close()
# 6.关闭连接
conn.close()

用户可能出现下面情况（注入问题）：

例：
用户录入：
用户名:随意  密码: ' or 1=1 or '
导致系统构造后的mysql语句为：select * from user where user='root' and pwd='  'or 1=1 or '  ', 无法达成原始验证目的（or与前面的and被同样识别）。

处理方法（不能太信任用户）：

解决sql注入问题: sql单独定义,用%s占位,把所有参数放到列表里,把sql和参数列表传给execute，这样系统构造mysql语句将不会出错（严格的代码/数据分开）。

# 用户登录
username = input('请输入用户名:')
password = input('请输入密码:')
# 后台python代码连接数据库判断
# 1.导入pymysql模块
import pymysql
# 2.创建连接对象
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root',database='jing_dong'
)
# 3.创建游标对象
cur = conn.cursor()
# 4.执行sql语句(判断是否登录成功)
# 解决sql注入问题:  sql单独定义,用%s占位,把所有参数放到列表里,把sql和参数列表传给execute
sql = "select * from user where user=%s and pwd=%s"
parm = [username,password]
row = cur.execute(sql,parm)
if row:print('登录成功')
else:print('登录失败')
# 5.关闭游标
cur.close()
# 6.关闭连接
conn.close()

今天的分享到此为止。