Python3编写网络爬虫11-数据存储方式四-关系型数据库存储

关系型数据库存储

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

关系型数据库有很多种。如SQLite、MySQL、Oracle、SQL Server、DB2等。

1.MySQL的存储

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

确保已经安装好了mysql数据库 并保证能正常运行

安装pymysql ：

 pip3 install pymysql

验证：

<<< import pymysql
<<< pymysql.VERSION

输出版本信息 (0, 9, 2, None) 验证成功

1.1 连接数据库

尝试连接数据库 例如 mysql已经运行在本地 用户名为root 密码为123456 端口为3306
利用pymysql先连接mysql 创建一个新的数据库 spider

import pymysql

db = pymysql.connect(host="localhost",user="root",password="123456",port=3306)#声明一个mysql连接对象db
cursor = db.cursor()#获得mysql的操作游标 利用游标来执行sql语句
cursor.execute('SELECT VERSION()')#execute() 执行sql语句 查询当前mysql版本
data = cursor.fetchone()#获得第一条数据
print('Database version:',data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")#创建数据库 库名spiders 默认编码utf-8
db.close()#关闭数据库

通常创建数据库只需要执行一次就好了
创建数据库后，连接时需要额外指定一个参数db

1.2 创建表

创建数据表 students
指定三个字段：
字段名 含义 类型
id 学号 varchar
name 姓名 varchar
age 年龄 int

import pymysql

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()

在爬虫过程中 要根据爬取结果设计特定的字段

1.3 插入数据

示例： 爬取了一个学生信息 学号为20180001 名字为Bob 年龄为20

import pymysql

id = '20180001'
name = '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,name,age))
db.commit()#执行数据插入
except:
db.rollback()
db.close()

构造sql语句 没有使用字符串拼接的方式 等同于如下：
sql = 'INSERT INTO students(id,name,age) values('+ id +','+ name +','+ age +')'
采用直接格式化 %s 实现 有多少values 写多少%s 避免字符串拼接 引号冲突的麻烦

commit() 将语句提交到数据库执行 对于数据插入 更新 删除等操作都需要用到

rollback() 执行数据回滚

拓展：事务机制 （可以确保数据一致性）

例如插入一条数据，不会存在插入一半的情况，要么全部插入，要么不插入 这是事务的原子性
还有三个属性 一致性、隔离性、持久性 通常四个属性被称为ACID特性

原子性（atomicity） 事务是不可分割的工作单位 要么做，要么不做。

一致性（consistency）事务必须使数据库从一个一致性变到另一个一致性 与原子性密切相关。

隔离性（isolation） 一个事务执行不能被其他事务干扰，即一个事务内部操作及使用的数据对并发的其他事务是隔离的
并发执行的各个事务之间不能相互干扰。

持久性（durability）指一个事务一旦提交，它对数据库中数据改变就应该是永久的，
接下来的操作或故障不应该对其有任何影响。

插入，更新，删除操作都是对数据库进行更改的操作，更改操作都必须为一个事务，所以标准写法为：

try:
cursor.execute(sql)
db.commit()
except:
db.rollback()

优化

例如 上面的插入操作需要增加一个字段gender sql语句就要更改为

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

元组参数为 

（id,name,age,gender）

更改目标： 插入方法无需变动 传入一个动态的字典例如：
{
'id':'20180001',
'name':'Bob',
'age':20
}

更改为：SQL语句根据动态字典构造，元组参数动态构造

data = {
'id':'20180002',
'name':'Mike',
'age':24
}
table = 'students'#自定义表名
keys = ','.join(data.keys())#利用keys()函数 取出data中的键名 并用逗号拼接
values = ','.join(['%s'] * len(data))#自定义%s列表 获取到data长度 相乘 并用逗号拼接
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table,keys=keys,values=values)#利用format()函数构造表名、字段名和占位符
try:
if cursor.execute(sql,tuple(data.values())):#execute函数传入第二个参数 元组参数
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()

1.4 更新数据

sql = 'UPDATE students SET age = %s WHERE name = %s'#占位符构造sql
try:
cursor.execute(sql,(25,'Bob'))#执行execute 传入元组参数
db.commit()
except:
db.rollback()
db.close()

简单数据更新 完全可以使用以上方法

实际爬取过程中 大部分需要插入数据 涉及到有没有重复的数据 如果有就更新 不需要重复保存数据
实现数据去重 如果数据存在 更新数据 数据不存在就 插入数据

示例:

data = {
'id':'20180001',
'name':'Bod',
'age':26
}
table = 'students'#自定义表名
keys = ','.join(data.keys())#利用keys()函数 取出data中的键名 并用逗号拼接
values = ','.join(['%s'] * len(data))#自定义%s列表 获取到data长度 相乘 并用逗号拼接
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table,keys=keys,values=values)#利用format()函数构造表名、字段名和占位符
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
if cursor.execute(sql,tuple(data.values())*2):#execute函数传入第二个参数 元组参数
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()

实际构造的是插入语句 ON DUPLICATE KEY UPDATE 如果主键存在 就执行更新操作
完整的sql应该是：
sql = 'INSERT INTO students(id,name,age) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE id = %s,name=%s,age=%s'
%s 变成了6个 所以乘2

1.5 删除数据

删除操作相对简单 只需要执行DELETE语句 指定删除表名和条件

示例：

table = 'students'
condition = 'age > 20'

sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table,condition=condition)


try:
if cursor.execute(sql):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()

将条件当作字符串传递 实现删除操作。

1.6 查询数据

查询SELECT语句 示例如下：

sql = 'SELECT * FROM students WHERE age >= 20'

try:
cursor.execute(sql)
print('Count:',cursor.rowcount)#获取查询结果条数
one = cursor.fetchone()#获取第一条数据 返回元组形式
print('One:',one)
results = cursor.fetchall()#获取结果的所有数据
print('Results:',results)
print('Result Type:',type(results))#二维元组
for row in results:#遍历输出
print(row)
except:
print('Error')

内部查询时 偏移指针用来指向查询结果

此外还可以使用 while循环加 fetchone() 获取所有数据
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')

每循环一次 指针偏移一条数据 随用随取 简单高效。