一 关系型数据库存储
关系型数据库是基于关系模型的数据库，而关系模型是通过二维表来保存的，所以它的存储方式就是行列组成的表，每一列是一个字段，每一行是一条记录。表可以看作某个实体的集合，而实体之间存在联系，这就需要表与表之间的关联关系来体现，如主键外键的关联关系。多个表组成一个数据库，也就是关系型数据库。
关系型数据库有多种，如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()
这样循环一次，指针就偏移一条数据，随用随取，简单高效。
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：第五部分(二) 数据存储（关系型数据库：MySQL存储方式） - Python技术站
微信扫一扫 
支付宝扫一扫 