MySQL 存储

关系型数据库是基于关系模型的数据库，而关系模型是通过二维表来保存的，所以关系型数据库中的数据的村方式就是行列组成的表，每一列代表一个字段，每一行代表一条记录。表可以看作某个实体的集合，实体之间存在的联系需要通过表与表之间的关联关系体现的，例如主键与外键的关系。由多个表组成数据库，就是关系型数据库

关系型数据库有多种。l例如： SQLite, MySQL, Oracle, SQL Server, DB2等

在 python2 中，连接 MySQL 的库大多是 MySQLdb, 但是此库的官方不支持 Python3, 所以这里推荐使用的库是 PyMySQL

准备工作：

在开始之前要确保安装了 MySQL ,并保证它能正常运行

安装

官网： MySQL :: MySQL Downloads

安装路径和数据路径，安装路径要记住，后面配置环境变量要用

后面就是下一步了

密码设置要记住，后面会常用

完成之后，右键开始菜单------搜素---mysql -----出现的操作台，输入刚刚的密码

顺利进入，就说明安装完成了

配置环境变量

找到安装路径bin 目录，点进去复制路径。在我的电脑---右键--- 属性----高级设置---环境变量----path ----新建---粘贴刚刚复制的路径---一路确定回去---win+R ----输入 mysql -uroot -p

然后输入密码---成功进入

然后安装： pymysql

pip install pymysql

连接数据库

首先尝试连接数据，假设当前 MySQL 运行在本地，用户名为 root 密码为 123456，运行端口为 3306 。这里利用 PyMySQL 先连接 MySQL ,然后创建一个新的数据库，叫作 spiders

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARSET utf8mb4")
db.close()

Database version: ('8.0.38',)

这里通过 PyMySQL 的 connect 方法声明了一个 MySQL连接对象 db , 此时需要传入的第一个参数是 MySQL 运行的 host(即 IP) ，由于 MySQL 运行在本地，所以传入的是 loaclhost ，如果 MySQL在远程运行，则传入其公网 IP 地址。后续参数分别是 user 用户名， password 密码和 port 端口号默认为 3306

连接成功后调用 cursor 方法获得了 MySQL 的操作游标，利用游标可以执行 SQL 语句。这里执行了两个 SQL 语句，直接调用 execute 方法即可执行。第一个 SQL 语句用于获取 MySQL 的当前版本，然后调用 fetchone 方法就得到了第一条数据，即版本号。第二个 SQL 语句用于创建数据库 spiders ，默认编码为 UTF-8，由于该语句不是查询语句，所以执行后成功创建了 spiders 数据库

创建表

创建一个 students 表

import pymysql

# 创建数据库后，在连接的时候需要额外指定一个参数 db
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY(id))'
cursor.execute(sql)
db.close()

插入数据

import pymysql

id = '20120001'
user = 'Bob'
age = 20

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
try:
cursor.execute(sql, (id, user, age))
db.commit()
except:
db.rollback()

db.close()

这里首先定义了需要插入的数据，然后构造了一个SQL 语句，这个语句还有另一种写法

sql = 'INSERT INTO students(id, name, age) values('+ id +', '+ name +','+ age +')'

这样的写法繁琐不直观，所以我们直接用格式化符 %s 来构造，有几个 value 就写几个 %s 。我们需要在 execute 方法的第一个参数传入该 SQL 语句，。 value 值统一用元组传过来就好。这样的写法既可以避免字符串拼接的麻烦，又可以避免引号冲突的问题

之后值得注意的是，需要执行 db 对象的 commit 方法才可以实现数据插入，这个方法才是真正将语句提交到数据库的方法。对于数据插入，更行，删除操作都需要调用该方法才能生效。

接下来，我们加了一层异常处理。如果执行失败，则调用 rollback 执行数据回滚，相当于什么都没有发生过

这里涉及事务问题。事务机制能够确保数据的一致性，也就是一件事要发生完整了，要么完全没有发生。例如插入一条数据，不会存在插入一半的情况

事务的4个属性：原子性，一直性，隔离性，持久性

上面的数据插入有一个很明显的不足，就是如果需要插入新字段 gender，就需要重新改动插入语句

sql = 'INSERT INTO students(id, name, age, gender) values(%s, %s, %s, %s)'

元组参数

(id, user, age, gender)

这显然不是我们要的，在很多情况下，我们要达到的效果是插入方法无需做改动，其做为通用方法，只需传入要给动态字典就好

{

'id': '20120001',

'name': 'Bob',

'age': 20

}

然后 SQL 语句会根据这个动态字典构造出来，元组也是这样才能实现通用插入方法

import pymysql

data = {
'id': '20120003',
'name': 'Bob',
'age': 21
}

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
table = 'students'
keys = ','.join(data.keys())
values = ','.join(['%s'] * len(data))
# sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
sql = 'INSERT INTO {table}({keys}) VALUES({values})'.format(table=table, keys=keys, values=values)
print(sql)
try:
if cursor.execute(sql, tuple(data.values())):
print('Successful')
db.commit()
except:
print('Faile')
db.rollback()
db.close()

这里我们传入的数据是字典，将其定义为了 data 变量，将 students 表定义为了变量 table 。接下来构造了一个动态 SQL语句

首先，需要构造插入的字段： id , name, age 。这里只要将 data 的键名拿过来，并且用逗号分隔即可。所以 ','.join(dat.keys()) 的结果就是 id,name,age，然后，需要构造多个 %s 当作占位符，由几个字段就构造几个。例如，这里有三个字段，就需要构造 %s, %s, %s 这里先是定义了长度为 1 的数组 ['%s'], 然后用乘法将其扩充为 ['%s', '%s', '%s']，再调用 join 方法，就变成了 %s, %s, %s，最后利用字符串的 format 方法将表明，字段名和占位符构造出来。于是SQL语句就构造出来了

最后，为 execute 方法的第一个参数传入 sql 变量，第二个参数传入由 data 的键值构造的元组，就可以成功插入数据了

更新数据

更新数据实际上也是执行 SQL语句

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
db.close()

这里同样用占位符的方式构造 SQL ，然后执行 execute 方法，传入元组形式的参数，同样执行 commit 方法，如果做的是简单的数据更新，完全可以用此方法

但是在实际的数据抓取过程中，大部分情况下需要插入数据，我们关心的是会不会出现重复数据，如果出现了，我们希望更新数据，而不是重复保存一次。另外，就像前面所说的动态构造SQL的问题，所以这里可以再实现一次去重的方法：如果保存的诗句存在，就更新数据，如果不存在，则插入数据。另外这样做法支持灵活的字典传值

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
data = {
'id': '20120002',
'name': 'Box',
'age': 22
}
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 在表里已经存在，只是年龄（age）有变化，此时就不会插入这条数据，而是直接更新 age 为新数据，这里构造出来的 SQL 语句有 6 个 %s

INSERT INTO students(id, name, age) VALUES(%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s,name = %s,age = %s

所以后面 execute 方法的第二个参数要乘以 2 ，是长度变成原来的两倍

如此一来，我们就可以实现主键不存在插入数据，主键存在则更新数据

删除数据

删除数据相对比较简单直接使用 DELETE语句即可，只是需要指定要删除的目标表名和删除条件，而且仍然要使用 db 的 commit 方法才能生效

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
table = 'students'
condition = 'age > 20'

sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
print(sql)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()

由于删除的条件多种多样，运算符有大于，小于，等于， LIKE等，条件连接符有 AND OR等

查询数据

SELECT 语句

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()

sql = 'SELECT * FROM students WHERE age > 10'
try:
cursor.execute(sql)
print('Count:',cursor.rowcount)
one = cursor.fetchone()
print('One', one)
results = cursor.fetchall()
print('Results:', results)
print('Rsults Type:', type(results))
for row in results:
print(row)
except:
print('Error')

Count: 2
One ('20120001', 'Bob', 20)
Results: (('20120002', 'Box', 22),)
Rsults Type: <class 'tuple'>
('20120002', 'Box', 22)

要想看到所有的效果，students 表里面至少要有两条以上的数据，且符合 gae > 10 的条件

这里我们构造了一个 SQL 语句，查询年龄为10 以上的学生，然后将其传给 execute 方法，注意这里不再需要 db 的 commit 方法，接着调用 cursor 的 rowcount 属性获取查询结果的条数

然后我们调用了 fetchone 方法，这个方法可以获取结果的第一条数据，返回结果以元组形式呈现，元组中的元素的顺序跟字段一一对应，即第一个元素就是第一个字段 id ，第二个元素就是第二个字段的 name ，以此类推。随后，我们调用了 fetchall 方法，可以得到结果的所有数据。之后将其结果和类型打印出来，是一个二重元组，其中每个元素都是一条记录，我们遍历这些元素并输出

但是需要注意一个问题，这里结果显示的是比总数据少一条的，这是因为 fetchall 方法内部有一个偏移指针，没获取一次数据就会向后移动一次，而在前面我们调用过一次 fetchone 方法，指针已经向后移动了一次，所以这里遍历开始的时候已经指向了第二条

我们也可以用 while 循环加 fetchone 方法组合来获取所有数据。因为 fetchall 会将结果以元组形式全部返回，如果数据量很大，那么占用的开销也会非常高。因此推荐逐条获取数据

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()

sql = 'SELECT * FROM students WHERE age > 10'
try:
cursor.execute(sql)
print('Count:',cursor.rowcount)
row = cursor.fetchone()
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')

这样没循环一次，指针就会偏移一条数据，随用随取，简单高效