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

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

PyMySQL的使用

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

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

参数host：连接的mysql主机，如果本机 是'localhost'
参数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语句的含义,最终产生数据泄露的现象.
    例如,当前存在数据库''jing_dong'',其中有数据表''goods'',此表中存放有大量商品信息。
    在下面代码中模拟用户查询输入:     
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数据库支持的表的存储引擎

-- 查看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);

  • 联合索引

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

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

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

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

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

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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,793评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,567评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,342评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,825评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,814评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,680评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,033评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,687评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,175评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,668评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,775评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,419评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,020评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,206评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,092评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,510评论 2赞 343