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

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

(一) MySQL存储
在Python2中连接MySQL库使用MySQLdb,但此库的官方不支持Python3,所以接下来使用的库是PyMySQL。

在使用之前需先安装好MySQL数据库并且能正常运行。此外还需要安装PyMySQL库。
MySQL在Linux上的源码安装方式参考这个网址:
https://zhuanlan.zhihu.com/p/52331876

1 连接数据库
现在运行MySQL的本地主机上连接数据库,假设用户名是michael,密码是michael123,端口是3508。现使用PyMySQL来连接MySQL,接着创建一个新的数据库,叫作spiders,代码如下:
注PyMySQL下载连接是:https://pypi.python.org/pypi/PyMySQL

import pymysql
# 首先连接数据库,提供主机地址,端口,数据库用户名及密码
db = pymysql.connect(host='localhost', port=3508, user='michael', password='michael123')
cursor = db.cursor() # 获取游标,利用游标执行SQL语句
cursor.execute('SELECT VERSION()') # 查询数据库版本信息
data = cursor.fetchone() # 获取前面的查询结果
print('Database version:', data)
# 创建数据库spiders
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8')
db.close()

输出如下所示:
Database version: ('5.7.24',)

通过PyMySQL的connect()方法声明一个MySQL连接对象db,需要传入运行MySQL的主机IP。如果在本地运行,就传入localhost 。如果MySQL在远程运行,则传入其主机IP地址。后面的参数user即用户名,password即密码,port即端口(这里使用3508)。

成功连接后,再调用cursor()方法获得MySQL的操作游标,利用游标来执行SQL语句。这里执行了两条SQL,直接用execute()方法执行即可。第一条SQL获得MySQL的当前版本,接着调用fetchone()方法获得第一条数据,也就是版本号。第二条SQL执行创建数据库的操作,数据库名叫作spiders,默认编码为UTF-8。

2 创建表
前面创建了spiders数据库,接下来在该数据库上创建数据表students,在创建数据表的命令中要指明参数db='spiders'。在执行SQL创建表之前,还要为数据表指定相应的字段信息。这里students数据表指定字段信息有3个,分别是:id, name, age,其中id和name字段的类型是varchar,age字段的类型是int。创建表代码如下:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael', password='michael123', 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()

运行上面代码没有出现报错信息就成功在spiders数据库上创建了students数据表。这里数据表的字段很简单,实际在获取网页数据时,根据获取到的数据设计特定的字段。

3 插入数据
在数据库中创建好数据表后,就可以在数据表中插入数据。现有一条信息是:学号20120001,姓名是michae,年龄是20。向数据表中插入数据时,也需要连接数据库,获取游标,例如下面代码所示:
import pymysql
id = '20120001'
user = 'michael'
age = 20
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
# 构造SQL语句,以格式化符 %s 来实现
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语句,其value值没有用字符串拼接方式来构造,这里选择直接用格式化符 %s 来实现。调用execute()方法时,第一个参数是SQL语句,Value值用统一的元组传过来。这样写可避免字符串拼接麻烦,还可避免引号冲突问题。

执行完execute()方法后,需要执行db对象的commit()方法才可实现数据插入,这个方法才是真正将语句提交到数据库执行的方法。对于数据插入、更新、删除操作,都需要调用该方法才能生效。

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

这里涉及事务的问题。事务机制可以确保数据的一致性, 也就是这件事要么发生了,要么没有发生。如插入一条数据,不会存在插入一半的情况, 要么全部插入,要么都不插入,这就是事务的原子性。另外,事务还有3个属性:一致性、隔离性和持久性。这4个属性通常称为ACID特性,具体如下所示。
原子性(atomicity):事务是一个不可分割的工作单位,事务中包括的所有操作要么都做,要么都不做;
一致性(consistency):事务必须使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的;
隔离性(isolation):一个事务的执行不能被其他事务干扰, 即一个事务内部的操作及使用的数据对并发的其他事
务是隔离的,并发执行的各个事务之间不能互相干扰;
持久性(durability):持续性也称永久性(permanence),指一个事务一旦提交, 它对数据库中数据的改变就应该
是永久性的。接下来的其他操作或故障不应该对其有任何影响。

插入、更新和删除操作都是对数据库进行更改的操作,而更改操作都必须为一个事务,所以这些操作的标准写法就是:
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()

这样可保证数据的一致性。commit()和rollback()方法为事务的实现提供了支持。

上面插入操作是通过构造SQL语句实现的,当要增加字段时,这个SQL语句就要做相应的修改,如增加性别字段:
INSERT INTO students(id, name, age, gender) values(%s, %s, %s, %s)
相应的元组参数也作修改:
(id, name, age, gender)

在正式运行中的程序,这样做修改就显得有些麻烦。要做到插入方法无需修改,可做成一个通用方法,只需传入一个动态化的字典就好。例如构造这样一个字典:
{
'id': '20120001',
'name': 'Bob',
'age': 20,
}

此时SQL语句会根据字典动态构造,元组也动态构造,这样就实现通用的插入方法。改写插入方法如下:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
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')
db.commit()
except:
print('Failed')
db.rollback()
db.close()

这里传入字典数据,并定义为data变量。表名也定义成变量table。接着构造一个动态的SQL语句。在构造SQL语句时直接根据data的键名进行构造,用逗号分隔。所以', '.join(data.keys())的结果是id, name, age。接下来根据data的长度构造 %s 占位符,在构造占位符时首先构造长度为1的数组['%s'],根据data字典长度用乘法扩充为['%s', '%s', '%s'],再调用join()方法,最终变成'%s', '%s', '%s'。最后,利用字符串的format()方法将表名、字段名和占位符构造出来。最终的动态SQL语句被构造成:
INSERT INTO students(id, name, age) VALUES(%s, %s, %s)

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

4 更新数据
更新操作也要执行SQL语句,最简单方式就是构造一个SQL语句,然后执行:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', 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,执行excute()方法,传入元组形式参数,同样执行commit()方法执行操作。简单的数据更新可用这个方法来做。

在网页数据抓取时,多数情况都需要插入数据,在插入数据时可能会有重复数据,如果出现重复则需要更新数据而不是重复保存一次。所以需要再实现一种去重的方法,如果数据存在,则更新数据;如果数据不存在,则插入数据。另外,这种做法支持灵活的字典传值。示例如下:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
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])
# update = ' id = %s, name = %s, age = %s'
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号相同,因id字段是主键,判断id号是否存在,这里age由原来的20变成21,此时这条数据就用更新方法而不会被插入。完整的SQL构造出来是这样的:
INSERT INTO students(id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s
这样代码中就有6个%s。所以在后面的excute()方法的第二个参数元组需要乘以2变成原来的2倍。这样就可以实现主键不存在就插入数据,存在就更新数据的功能。

5 删除数据
使用DELETE语句可删除数据,需要指定删除的目标表名和删除条件,同时需要使用db的commit()方法才能生效。如下所示:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
table = 'students' # 指定要操作的表名
condition = 'age > 20' # 指定操作条件(即删除条件)
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()

删除的条件有很多,运算符有大于、小于、等于、LIKE等,条件连接符有AND、OR等

6 查询数据
有了插入、修改和删除操作,还有一个查询操作。查询操作使用SELECT语句,如下示例所示:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
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('Results Type:', type(results)) # 查看类型,结果是二重元组类型
for row in results: # 遍历每个元素并输出
print(row)
except:
print('Error')
db.close()

输出如下所示:
Count: 4
One: ('20120001', 'Bob', 21)
Results: (('20120011', 'michael', 22), ('20120012', 'James', 25), ('20120013', 'Mary', 20))
Results Type: <class 'tuple'>
('20120011', 'michael', 22)
('20120012', 'James', 25)
('20120013', 'Mary', 20)

在代码中构造的SQL查询语句条件是age值大于等于20岁,由execute()方法执行SQL语句。这里不需db的commit()方法。代码中的各个方法和属性介绍:
cursor.rowcount属性:获取查询结果的条数,这里是4条。
cursor.fetchone()方法:获取查询结果的第一条数据,结果是元组形式,元组的元素顺序与字段是一 一对应的。
cursor.ftechall()方法:获取查询结果的剩余所有数据,结果类型是二重元组,每个元素是一条记录。

cursor.ftechall()方法输出是3条数据而不是4条数据,是因为它内部实现有一个偏移指针用来指向查询结果,最开始偏移指针指向第一条数据,取一次后,指针偏移到下一条数据。由于调用了fetchone()方法后指针指向下一条数据,所以fetchall()方法返回的是偏移指针指向的数据一直到结束的所有数据,所以就是3条。

此外,使用fetchall()方法以元组形式全部返回,当数据量很大时,占用的开销会非常高。此时可用while循环加fetchone()方法获取所有数据。可用下面方法逐条获取数据:
import pymysql
db = pymysql.connect(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
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')
db.close()

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