Python学习之旅—Mysql数据库进阶(数据备份+Pymysql进阶+索引原理与慢查询优化)
前言
本篇博客将在上一篇的基础上，重点讲解如下三个重要的知识点：数据备份，PyMysql进阶和慢查询优化。本节课的知识比较重要，希望各位好好掌握。
一.数据备份
在谈到Mysql的数据备份时，我们需要明白物理备份，逻辑备份，以及导出表的相关概念：
#1. 物理备份：是指直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows；相当于就是直接拷贝文件
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。相当于将库和表的相关语句
导入到一个文件中，需要恢复时，直接将文件中的sql语句再执行一遍就可以创建出原来的库和表，并且将原来的插入数据语句也执行一遍。
#3. 导出表： 将表导入到文本文件中。
一、使用mysqldump实现逻辑备份
【001】备份单个数据库
#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
-h 后面指定远程服务器的IP地址，如果不指定，那么就是本地IP地址；
-u：本地root用户 -p :root用户的密码
#示例：
#单库备份
mysqldump -uroot -pcisco --database day45 > D:\\day45_bak_2017_10_30.sql
上面这句话的意思是将day45这个数据库，以及它的表和记录全部转换为sql语句，>表示将左边转换的结果全部定向到db1.sql这个文件中(覆盖备份)；我们可以指定该文件的地址；
通常在备份文件时，我们会指定文件的名称，名称中通常有时间戳地址.注意地址中不需要有引号
运行完上面的单个数据库备份语句后，我们去D盘下查看，发现目标.sql文件，证明我们备份成功：
现在我们删掉原来数据库中的day45这个数据库，然后采用备份文件来恢复：
【002】恢复单个数据库
现在我们开始恢复day45这个数据库，先来看下命令：
恢复库 mysql -uroot -pcisco < D:\\day45_bak_2017_10_30.sql
这句话的意思就是将备份文件中的语句再执行一遍，然后恢复原来的数据库，表和记录
执行完上面命令后，我们再来数据库中看看是否恢复成功：
【003】备份多个数据库
备份多个库
mysqldump -uroot -pcisco --databases day43 day44 day45 > D:\\day43_day45_day44_bak_2017_10_30.sql
恢复多个库
mysql -uroot -pcisco < D:\\day43_day45_day44_bak_2017_10_30.sql
同样的效果，在此不再演示。
【004】备份表
备份多个表
mysqldump -uroot -pcisco day45 employee department userinfo > D:\\day45_t1_t2_employee_bak_2017_10_30.sql
注意和备份数据库的区别：前面我们加了--database参数表示后面我们备份的都是数据库，例如前面例子的day43,day44，day45
都是数据库，如果不加--database，直接像这样写：day45 employee department userinfo，表示我们备份的是数据库day45下面
的多张表
恢复表 mysql -uroot -pcisco day45 < D:\\day45_t1_t2_employee_bak_2017_10_30.sql
我们先来备份这几张表，然后进入day45数据库删除这几张表，最后再来执行恢复表的命令：
此时再进入day45数据库下查看这几张表，发现成功备份成功：
【005】如果我想要备份所有的数据库。可以这样：
mysqldump -uroot -pcisco --all-databases > D:\\all.sql
恢复库
mysql -uroot -pcisco < D:\\all.sql
【006】恢复表的第二种方式
前面是我们实际生产环境下恢复表的一种方式，下面介绍恢复表的另一种方式：
#方法二： mysql> use day5; mysql> SET SQL_LOG_BIN=0; mysql> source D:\\day45_t1_t2_employee_bak_2017_10_30.sql #注：如果备份/恢复单个库时，可以修改sql文件 DROP database if exists school; create database school; use school;
【007】表的导出
SELECT... INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定义字段分隔符
OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来
LINES TERMINATED BY '\n' ; //定义换行符
mysql 命令导出文本文件
示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html
需求：当表运行一段时间后，我们发现表的结构不合理，但此时表中已经存在了一些记录！如果想要修改表的结构，那就要对表进行重新修改，我们首先要做的是就是需要将表中的数据导出来，然后再将原来的表删除掉。我们一起来看下面的示例。创建employee和department这两张表，然后将查询的结果导入到一个文件中，如下所示：
select * from employee into outfile 'D:\\day46.dep.txt' fields terminated by ',' lines terminated by '\n';
注意：这里正常来执行，会报如下的错误，由于版本问题，笔者在这里并没有演示出来：
这是Mysql在5.5版本后新加入的特性，这条语句的意思是将mysql中的记录导出来，一旦数据库权限出泄露，它会直接使用该条语句将数据库中表的数据导入出来，非常危险。所以Mysql数据库进行了限制。如果我们确实想执行导出记录的操作，那么我们就使用--secure-file-priv来指定导出记录的目标文件路径，同时管理员应该对该路径进行限制，表示只有管理员对这个路径才有读写权限。因为这样即使你黑客有写权限，但是你无法从目标路径将文件拷贝走，这样就保证了数据的安全。该值默认为空，我们查看下：
我们在命令行下无法对其进行设置，因为它是一个只读变量：
因此只能由管理员在配置文件中配置：D:\ProfessionalSoftwares\MySQL\my.ini中的[mysqld]下面配置：secure_file_priv='D:\\'
然后我们再来执行如上的这条语句即可，然后去D盘下查看是否有备份文件，发现存在。
【007】表的导入
LOAD DATA INFILE 导入文本文件
LOAD DATA INFILE 'D:\\day46.emp.txt'
INTO TABLE employee
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
我们先清空employee表中的数据，然后使用我们刚才的文件day46.emp.txt文件来恢复表中的记录：
然后我们再来查看下employee中的数据，发现记录已经成功被导入进来。
【008】数据库之间的迁移
务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -pcisco --databases db1 | mysql -h 目标IP -uroot -p456
即在两台服务器之间的数据库之间进行数据库的迁移，我们可以自己试试将自己的库迁移给别人
二.Pymysql进阶
【001】在操作Pymysql数据库之前，我们需要先安装pymysql模块：pip install pymysql;
【002】连接数据库
在这里我们首先尝试连接一下数据库，假设当前的MySQL运行在本地，用户名为root，密码为cisco，
运行端口为3306:
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='cisco',
)
cur = conn.cursor()
cur.execute('SELECT VERSION()')
data = cur.fetchone()
print('Database version:', data)
cur.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")
cur.close()
db.close()
运行结果为：Database version: ('5.5.40',)
我们一起来分析下上面的执行语句：
在这里我们通过pymysql的connect方法声明了一个MySQL连接对象conn，需要传入MySQL运行的host即IP，此处由于MySQL在本地运行，所以传入的是localhost，
如果MySQL在远程运行，则传入其公网IP地址，user即用户名，password即密码，port即端口默认3306。 连接成功之后，我们需要再调用cursor()方法获得MySQL的操作游标cur，利用游标来执行SQL语句，例如在这里我们执行了两句SQL，用execute()方法执行相应的SQL语句即可，
第一句SQL是获得MySQL当前版本，然后调用fetchone()方法来获得第一条数据，也就得到了版本号，另外我们还执行了创建数据库的操作，数据库名称叫做spiders，默认编码为utf-8，
由于该语句不是查询语句，所以直接执行后我们就成功创建了一个数据库spiders。
【003】创建表
创建表
一般来说上面的创建数据库操作我们只需要执行一次就好了，当然我们也可以手动来创建数据库，以后我们的操作都是在此数据库上
操作的，所以后文介绍的MySQL连接会直接指定当前数据库spiders，所有操作都是在spiders数据库内执行的。
所以这里MySQL的连接就需要额外指定一个参数db。
然后接下来我们执行创建表的SQL语句，创建一个用户表students，在这里指定三个字段，结构如下：
代码如下：
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='cisco'
)
cur = conn.cursor()
sql = 'CREATE TABLE IF NOT EXISTS spiders.students (id VARCHAR(255) NOT NULL,' \
' name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
cur.execute(sql)
cur.close()
conn.close()
【004】插入数据(记住必须使用数据库连接对象conn.commit()提交查询，要不然不会执行成功)：
sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
try:
cursor.execute(sql, (id, user, age))
db.commit()
except:
db.rollback()
在上面的代码中我们需要注意的是：当需要真正执行相关操作时，需要执行db对象的commit()方法，这个方法才是真正将语句提交到数据库执行的方法，对于数据插入、更新、删除操作都需要调用该方法才能生效。接下来我们加了一层异常处理，如果执行失败，则调用rollback()执行数据回滚，相当于什么都没有发生过一样。
在这里就涉及一个事务的问题，事务机制可以确保数据的一致性，也就是这件事要么发生了，要么没有发生，比如插入一条数据，不会存在插入一半的情况，要么全部插入，要么整个一条都不插入，这就是事务的原子性，另外事务还有另外三个属性，一致性、隔离性、持久性，通常成为ACID特性。
归纳如下：
插入、更新、删除操作都是对数据库进行更改的操作，更改操作都必须为一个事务，所以对于这些操作的标准写法就是：
try:
cursor.execute(sql)
conn.commit()
excexcept:
conn.rollback()
这样我们便可以保证数据的一致性，在这里的commit()和rollback()方法就是为事务的实现提供了支持。
好，在上面我们了解了数据插入的操作，是通过构造一个SQL语句来实现的，但是很明显，这里有一个及其不方便的地方，比如又加了一个性别gender，假如突然增加了一个字段，那么我们构造的SQL语句就需要改成：
INSERT INTO students(id, name, age, gender) values(%s, %s, %s, %s)
同时相应的元组参数就要变成
(id, name, age, gender)
这显然不是我们想要的，在很多情况下，我们要达到的效果是插入方法无需改动，做成一个通用方法，只需要传入一个动态变化的字典给就好了。比如我们构造这样一个字典：
{
'id': '20120001',
'name': 'Bob',
'age': 20
}
然后SQL语句会根据字典动态构造，元组也动态构造，这样才能实现通用的插入方法。所以在这里我们需要将插入方法改写一下：
data = {
'id': '20120001',
'name': 'Bob',
'age': 20
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
if cursor.execute(sql, tuple(data.values())):
print('Successful')
conn.commit()
except:
print('Failed')
conn.rollback()
db.close()
在这里我们传入的数据是字典的形式，定义为data变量，表名也定义成变量table。接下来我们就需要构造一个动态的SQL语句了。
首先我们需要构造插入的字段，id, name, age，在这里只需要将data的键名拿过来，然后用逗号分隔即可。所以', '.join(data.keys())的结果就是id, name, age，然后我们需要构造多个%s当作占位符，有几个字段构造几个，比如在这里有两个字段，就需要构造%s, %s, %s，所以在这里首先定义了长度为1的数组['%s']，然后用乘法将其扩充为['%s', '%s', '%s']，再调用join()方法，最终变成%s, %s, %s。所以我们再利用字符串的format()方法将表名，字段名，占位符构造出来，最终sql语句就被动态构造成了INSERT INTO students(id, name, age) VALUES (%s, %s, %s)，最后再execute()方法的第一个参数传入sql，第二个参数传入data的键值构造的元组，就可以成功插入数据了。
如此以来，我们便实现了传入一个字典来插入数据的方法，不需要再去修改SQL语句和插入操作了；综上所述一个完整通用的插入记录的pymysql代码如下所示：
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
database='spiders',
password='cisco'
)
cur = conn.cursor()
data = {
'id': '20120001',
'name': 'Bob',
'age': 20
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
# INSERT INTO students(id, name, age) VALUES (%s, %s, %s)
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
if cur.execute(sql, tuple(data.values())):
print('Successful')
conn.commit()
except Exception as e:
print('Failed', e)
conn.rollback()
cur.close()
conn.close()
【005】更新数据(记住必须使用数据库连接对象进行commit操作：conn.commit()，要不然不会执行成功)
数据更新操作实际上也是执行SQL语句，最简单的方式就是构造一个SQL语句然后执行。
sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
db.close()
在这里同样是用占位符的方式构造SQL，然后执行excute()方法，传入元组形式的参数，同样执行commit()方法执行操作。
如果要做简单的数据更新的话，使用此方法是完全可以的。
但是在实际数据抓取过程中，在大部分情况下是需要插入数据的，但是我们关心的是会不会出现重复数据，如果出现了重复数据，我们更希望的做法一般是更新数据而不是重复保存一次，另外就是像上文所说的动态构造SQL的问题，所以在这里我们在这里重新实现一种做法。这种做法可以做到去重，如果重复则更新数据，如果数据不存在则插入数据，另外支持灵活的字典传值，代码如下：
data = {
'id': '20120001',
'name': 'Bob',
'age': 21
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
if cursor.execute(sql, tuple(data.values())*2):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()
在这里构造的SQL语句其实是插入语句，但是在后面加了ON DUPLICATE KEY UPDATE，这个的意思是如果主键已经存在了，那就执行更新操作，比如在这里我们传入的数据id仍然为20120001，但是年龄有所变化，由20变成了21，但在这条数据不会被插入，而是将id为20120001的数据更新。
在这里完整的SQL构造出来是这样的：
INSERT INTO students(id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s
相比上面介绍的插入操作的SQL，后面多了一部分内容，那就是更新的字段，ON DUPLICATE KEY UPDATE使得主键已存在的数据进行更新，后面跟的是更新的字段内容。所以这里就变成了6个%s。所以在后面的execute()方法的第二个参数元组就需要乘以2变成原来的2份。
如此一来，我们就可以实现主键不存在便插入数据，存在则更新数据的功能了。
【006】删除数据(记住必须使用数据库连接对象进行commit操作：conn.commit()，要不然不会执行成功)
删除操作相对简单，使用Delete语句即可，需要指定要删除的目标表名和删除条件，而且仍然需要使用db的commit()方法才能生效。
table = 'students'
condition = 'age > 20'
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
conn.commit()
except:
conn.rollback()
db.close()
在这里我们指定了表的名称，删除条件。因为删除条件可能会有多种多样，运算符比如有大于、小于、等于、LIKE等等，条件连接符比如有AND、OR等等，所以不再继续构造复杂的判断条件，在这里直接将条件当作字符串来传递，以实现删除操作。
【007】查询(不需要执行conn.commit操作)
说完插入、修改、删除等操作，还剩下非常重要的一个操作，那就是查询。在这里查询用到Select语句，我们先用一个实例来感受一下：
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cur.execute(sql)
print('Count:', cursor.rowcount)
one = cur.fetchone()
print('One:', one)
results = cur.fetchall()
print('Results:', results)
print('Results Type:', type(results))
for row in results:
print(row)
except:
print('Error')
运行结果如下所示：
Count: 3
one: ('20120001', 'Bob', 20)
Results: (('20120002', 'Carson', 21), ('20120003', 'Tom', 22))
Results Type: <class 'tuple'>
('20120002', 'Carson', 21)
('20120003', 'Tom', 22)
在这里我们构造了一条SQL语句，将年龄20岁及以上的学生查询出来，然后将其传给execute()方法即可，注意在这里不再需要db的commit()方法。然后我们可以调用cursor的rowcount属性获取查询结果的条数，当前示例中获取的结果条数是4条。
然后我们调用了fetchone()方法，这个方法可以获取结果的第一条数据，返回结果是元组形式，元组的元素顺序跟字段一一对应，也就是第一个元素就是第一个字段id，第二个元素就是第二个字段name，以此类推。随后我们又调用了fetchall()方法，它可以得到结果的所有数据，然后将其结果和类型打印出来，它是二重元组，每个元素都是一条记录。我们将其遍历输出，将其逐个输出出来。
但是这里注意到一个问题，显示的是4条数据，fetall()方法不是获取所有数据吗？为什么只有3条？这是因为它的内部实现是有一个偏移指针来指向查询结果的，最开始偏移指针指向第一条数据，取一次之后，指针偏移到下一条数据，这样再取的话就会取到下一条数据了。所以我们最初调用了一次fetchone()方法，这样结果的偏移指针就指向了下一条数据，fetchall()方法返回的是偏移指针指向的数据一直到结束的所有数据，所以fetchall()方法获取的结果就只剩3个了。所以在这里要理解偏移指针的概念。
所以我们还可以用while循环加fetchone()的方法来获取所有数据，而不是用fetchall()全部一起获取出来，fetchall()会将结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。所以推荐使用如下的方法来逐条取数据：
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount)
row = cursor.fetchone()
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')
这样每循环一次，指针就会偏移一条数据，随用随取，简单高效。
【008】Pymsql实现用户的登入认证
上面我们使用pymysql模块实现了对Mysql数据的增删查改，在这里我们继续
import pymysql
user = input(">>>").strip()
pwd = input(">>").strip()
# 发起连接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='cisco',
database='day45',
charset='utf8'
)
# 拿到游标
cur = conn.cursor()
#查询
select_sql = 'select id,username from userinfo where username=%s and password=%s;'
rows = cur.execute(select_sql, (user, pwd))
print(rows) #只要rows不为0表示我们就找到了符合条件的记录
if rows: #rows自带布尔值
print("登入成功")
else:
print("登入失败")
cur.close()
conn.close()
【009】Pymysql模块中的一些重点方法
方法一：executemany()：在上面的例子中，我们都是使用execute执行插入语句，但是这样做的缺点在于我们无法同时插入多条记录，如果想同时插入多条记录，我们可以使用executemany方法，该方法接收两个参数，一个是我们的sql语句表达式，另一个是我们需要插入的值，只不过这里的值是一个列表，而使用execute时是一个元组，来看下面的例子：
data = {
'id': '20120001',
'name': 'Bob',
'age': 20
}
data1 = {
'id': '20120002',
'name':'Carson',
'age':21
}
data2 = {
'id': '20120003',
'name':'Tom',
'age':22
}
result = cur.executemany(sql, [tuple(data1.values()), tuple(data2.values())])
# 第一个参数为我们写好的sql表达式，第二个参数为一个列表，列表中的每个元素都是元祖
方法二：fetchall()
fetchall方法表示将获取的记录全部取出来，得到的记录是一个元组，来看下面的例子：
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
database='spiders',
password='cisco'
)
cur = conn.cursor()
sql = 'select * from students where age >= 20'
try:
rows = cur.execute(sql)
results = cur.fetchall()
print(rows) # 打印的是8，表示待查询的表中有8条记录
print(results) # 打印的是表中的记录，结果是一个元组，每个元组中的元素又是一个元组，且元组里面的元素和表中的字段一一对应
except Exception as e:
print('Error', e)
cur.close()
conn.close()
上面代码的执行结果如下所示：
8 8 (('20120001', 'Bob', 20), ('20120002', 'Carson', 21), ('20120003', 'Tom', 22), ('20120004', 'Alex', 22),
('20120005', 'Egon', 22), ('20120006', 'Eva', 22), ('20120007', 'Shello', 22), ('20120008', 'Agina', 22))
上面我们执行完毕的结果都是以元组的形式返回，如果我们想以字典的形式返回，怎么做？我们只需要在创建游标时指定使用字典的形式展示即可，因为有时我们有这样的需求：
cur = conn.cursor(cursor=pymysql.cursors.DictCursor) [{'age': 20, 'id': '20120001', 'name': 'Bob'}, {'age': 21, 'id': '20120002', 'name': 'Carson'},
{'age': 22, 'id': '20120003', 'name': 'Tom'}, {'age': 22, 'id': '20120004', 'name': 'Alex'},
{'age': 22, 'id': '20120005', 'name': 'Egon'}, {'age': 22, 'id': '20120006', 'name': 'Eva'},
{'age': 22, 'id': '20120007', 'name': 'Shello'}, {'age': 22, 'id': '20120008', 'name': 'Agina'}]
如上所示，每个元素都是字典，这样的好处在于我们可以清楚地看到表中字段。
方法三：scroll()
我们还可以在实际操作中移动游标的位置，例如我此时使用fetchone()方法拿到了第一条数据，此时我想从第三条开始取数据，怎么办？我们需要将游标移动到第三条数据处，那就得使用scroll()方法。而移动游标有两种方式：绝对位置移动和相对位置移动
绝对位置移动：我想从第3条记录开始取
cur.scroll(2, mode='absolute') 此时游标的位置处于第二条记录处，那么下次取数据时，就会开始取第三条记录
# 我想从第7条位置开始取记录
cur.scroll(6, mode='absolute')
# 在上面的基础上，我想取第三条记录，而此时游标的位置为7，那我应该将游标的位置设置在第2个位置
# 可以使用绝对位置移动，绝对位置移动即每次都从头开始计算
cur.scroll(2, mode='absolute')
# 我们也可以使用相对位置开始移动，即相对你当前所处的位置开始移动
print(cur.fetchone()) # 当前游标所处的位置为1
cur.scroll(1, mode='relative') # 由于是相对于当前游标所处的位置移动，所以只需要移动1位即可，然后游标的位置就到2
print(cur.fetchone()) # 我想取第3条记录
cur.scroll(1, mode='relative') # 再次移动1位，然后游标的位置就到4，下次就可以直接取第五条记录
print(cur.fetchone()) # 取出第5条记录
# 我们还可以倒着去取记录
print(cur.fetchone()) # 此时游标的位置在第9条记录那
cur.scroll(-1,mode='relative') # 然后游标上移一个位置，到8，下次取数据时，又从第9条记录开始取
print(cur.fetchone())
方法四：lastrowid
如果我们想获取表中的自增ID到哪了，可以直接使用cursor.lastrowid,该方法可以获取表中最后一条记录的自增ID。
这个属性经常用在多对多关系中，表的插入时使用
==================================================================================================
结语：
关于本次博客的主要内容就更新到这里，希望大家好好掌握今天所学的知识。
浙公网安备 33010602011771号