Mysql数据库高级使用PyMySQL、SQL注入与防止、事务、索引

上文基础命令直达链接:MySQL命令行客户端常用命令汇总

PyMySQL的使用

在Python中连接操作mysql需要先安装第三方库pymysql,执行命令:pip install pymysql

• pymysql使用:

1. 导入pymysql模块

import pymysql


1. 创建连接对象
   调用pymysql模块中的connect()函数来创建连接对象,连接到数据库需要传入较多参数:

参数host：连接的mysql主机，如果本机 是&＃8217;localhost&＃8217;
参数port：连接的mysql主机的端口，默认是3306
参数user：连接的用户名
参数password：连接的密码
参数database：数据库的名称
参数charset：通信采用的编码方式，推荐使用utf8

cOnn= pymysql.connect(host="localhost",
port=3306,
user="root",
password="mysql",
database="python",
charset="utf8")


1. 获取游标对象
   获取游标对象的目标就是要执行sql语句，完成对数据库的增、删、改、查操作。代码执行完毕应执行关闭操作.

cursor= conn.cursor()


1. pymysql完成数据的查询操作示例

import pymysql
# 创建连接对象
cOnn= pymysql.connect(
host="localhost",
port=3306,
user="root",
password="mysql",
database="python",
charset="utf8"
)
# 获取游标对象
cursor = conn.cursor()
# 查询 SQL 语句
sql = "select * from students;"
# 执行 SQL 语句 返回值就是 SQL 语句在执行过程中影响的行数
row_count = cursor.execute(sql)
print("SQL 语句执行影响的行数%d" % row_count)
# 取出结果集中一行数据,　例如:(1, '张三')
# print(cursor.fetchone())
# 取出结果集中的所有数据, 例如:((1, '张三'), (2, '李四'), (3, '王五'))
for line in cursor.fetchall():
print(line)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()


1. pymysql完成对数据的增删改示例
   代码中使用的commit()和rollback()方法为事务的操作,在我另一篇文章单独讲到.

import pymysql
# 创建连接对象
cOnn= pymysql.connect(
host="localhost",
port=3306,
user="root",
password="mysql",
database="python",
charset="utf8"
)
# 获取游标对象
cursor = conn.cursor()
try:
# 添加 SQL 语句
# sql = "insert into students(name) values('胖太'), ('老师');"
# 删除 SQ L语句
# sql = "delete from students where id = 5;"
# 修改 SQL 语句
sql = "update students set name = '夏目' where id = 5;"
# 执行sql语句
row_count = cursor.execute(sql)
print("SQL语句影响的行数为%d" % row_count)
# 提交事务
conn.commit()
except Exception as e:
print(e)
# 提交失败，回滚事务，即撤销刚执行的SQL操作
conn.rollback()
# 关闭游标
cursor.close()
# 关闭链接
conn.close()


conn.commit() 表示将修改操作提交到数据库
conn.rollback() 表示回滚数据

SQL注入与防止

• SQL注入是什么?
  用户提交带有恶意的数据与SQL语句进行字符串方式拼接,从而影响后台SQL语句的含义,最终产生数据泄露的现象.
  例如,当前存在数据库&＃8221;jing_dong&＃8221;,其中有数据表&＃8221;goods&＃8221;,此表中存放有大量商品信息。
  在下面代码中模拟用户查询输入:

import pymysql
# 创建连接对象
cOnn= pymysql.connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
find_name = input("请输入商品名称：")
# 获取游标对象
cursor = conn.cursor()
sql = "select * from goods WHERE name = '%s'" % find_name
print("实际后台执行的SQL语句为：",sql)
count = cursor.execute(sql)
print("SQL语句影响的行数：%d" % count)
# 获取查询结果
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()


用户正常输入时,将返回我们需要的指定商品信息：

请输入商品名称：商务双肩背包
实际后台执行的SQL语句为： select * from goods WHERE name = '商务双肩背包'
SQL语句影响的行数：2
((19, '商务双肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'), (21, '商务双肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'))


用户恶意输入时，将会出现整体数据泄露的问题：

请输入商品名称：' or 1=1 or '
实际后台执行的SQL语句为： select * from goods WHERE name = '' or 1=1 or ''
SQL语句影响的行数：21
((1, 'r510vc 15.6英寸笔记本', 5, 2, Decimal('3399.000'), b'\x01', b'\x00'), (2, 'y400n 14.0英寸笔记本电脑', 5, 7, Decimal('4999.000'), b'\x01', b'\x00'), (3, 'g150th 15.6英寸游戏本', 4, 9, Decimal('8499.000'), b'\x01', b'\x00'), (4, 'x550cc 15.6英寸笔记本', 5, 2, Decimal('2799.000'), b'\x01', b'\x00'), (5, 'x240 超极本', 7, 7, Decimal('4880.000'), b'\x01', b'\x00'), (6, 'u330p 13.3英寸超极本', 7, 7, Decimal('4299.000'), b'\x01', b'\x00'), (7, 'svp13226scb 触控超极本', 7, 6, Decimal('7999.000'), b'\x01', b'\x00'), (8, 'ipad mini 7.9英寸平板电脑', 2, 8, Decimal('1998.000'), b'\x01', b'\x00'), (9, 'ipad air 9.7英寸平板电脑', 2, 8, Decimal('3388.000'), b'\x01', b'\x00'), (10, 'ipad mini 配备 retina 显示屏', 2, 8, Decimal('2788.000'), b'\x01', b'\x00'), (11, 'ideacentre c340 20英寸一体电脑 ', 1, 7, Decimal('3499.000'), b'\x01', b'\x00'), (12, 'vostro 3800-r1206 台式电脑', 1, 5, Decimal('2899.000'), b'\x01', b'\x00'), (13, 'imac me086ch/a 21.5英寸一体电脑', 1, 8, Decimal('9188.000'), b'\x01', b'\x00'), (14, 'at7-7414lp 台式电脑 linux ）', 1, 3, Decimal('3699.000'), b'\x01', b'\x00'), (15, 'z220sff f4f06pa工作站', 3, 4, Decimal('4288.000'), b'\x01', b'\x00'), (16, 'poweredge ii服务器', 3, 5, Decimal('5388.000'), b'\x01', b'\x00'), (17, 'mac pro专业级台式电脑', 3, 8, Decimal('28888.000'), b'\x01', b'\x00'), (18, 'hmz-t3w 头戴显示设备', 6, 6, Decimal('6999.000'), b'\x01', b'\x00'), (19, '商务双肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'), (20, 'x3250 m4机架式服务器', 3, 1, Decimal('6888.000'), b'\x01', b'\x00'), (21, '商务双肩背包', 6, 6, Decimal('99.000'), b'\x01', b'\x00'))


此处数据表内的全部信息都返回给了用户，原因在于用户恶意输入的内容和后台SQL查询语拼接，导致语义改变，最终产生数据泄露。
使SQL语义改变的最终原因就是字符串拼接产生的漏洞

• 防止SQL注入

  1. SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作

  2. 将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数
     代码示例：

import pymysql
# 创建连接对象
cOnn= pymysql.connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
find_name = input("请输入商品名称：")
# 获取游标对象
cursor = conn.cursor()
# 构造参数列表
params = [find_name]
sql = "select * from goods WHERE name = %s"
count = cursor.execute(sql,params)
# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可
# %s 不需要带引号
print("SQL语句影响的行数：%d" % count)
# 获取查询结果
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()


这样就不能再通过利用参数对SQL语句进行字符串拼接改变语义了。

事务

事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。

事务的使用场景:
在日常生活中，有时我们需要进行银行转账，这个银行转账操作背后就是需要执行多个SQL语句，假如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，要想解决这个问题就需要通过事务来完成。

• 事务的四大特性
  1. 原子性(Atomicity)：
     事务内不可分割，要么全部提交执行，要么全部回滚不执行。
  2. 一致性(Consistency)：
     在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
  3. 隔离性(Isolation)：
     一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  4. 持久性(Durability)：
     事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
• MySQL数据库支持的表的存储引擎

&＃8212; 查看MySQL数据库支持的表的存储引擎
show engines;

说明:常用的表的存储引擎是 InnoDB 和 MyISAM；
1. InnoDB 是支持事务的
2. MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

• 开启事务/提交事务

  1. 开启事务有两种方式：
     在开启事务的语句后的sql语句将遵循事务的特性成为一个整体。

  begin;
  start transaction;

  1. 提交事务:
     将本地缓存文件中的数据提交到物理表中，完成数据的更新。

  commit；

  1. 回滚事务
     放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态

  rollback；

说明:

• 开启事务后执行修改命令，变更数据会保存到本地缓存文件中，而不维护到物理表中

• MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作

• 使用命令select @@autocommit;查看当前自动提交事务的设置状态，默认为1，即每行sql语句自动提交事务。

• 当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该事务结束。

• set autocommit = 0 表示取消自动提交事务模式，需要手动执行commit完成事务的提交

• pymysql 里面的 conn.commit() 操作就是提交事务

• pymysql 里面的 conn.rollback() 操作就是回滚事务

索引

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

应用场景:
当数据库中数据量很大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

• 查看表中已有索引:

show index from 表名;

• 主键列会自动创建索引
• 为字段添加索引(示例为goods表name字段添加名为index_name的索引)：

alter table goods add index index_name(name);

• 联合索引

&＃8212; 创建联合索引
alter table goods add index (name,price);

1. 联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。
2. 减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，索引文件过多会增加磁盘空间的开销。

• 联合索引最左原则

在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面，否则联合索引失效

在使用联合索引的时候，我们要遵守一个最左原则,即index(name,price)支持 name 、name 和 price 组合查询,而不支持单独 price 查询，因为没有用到创建的联合索引。

• MySQL中索引的优点和缺点和使用原则
  优点：加快数据的查询速度
  缺点：创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加

使用原则：
1. 通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
2. 对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
3. 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
4. 在一字段上相同值比较多不要建立索引，比如在学生表的&＃8221;性别&＃8221;字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。