第五部分（⼆）数据存储（关系型数据库：MySQL存储⽅式）
⼀关系型数据库存储
关系型数据库是基于关系模型的数据库，⽽关系模型是通过⼆维表来保存的，所以它的存储⽅式就是⾏列组成的表，每⼀列是⼀个字段，每⼀⾏是⼀条记录。表可以看作某个实体的集合，⽽实体之间存在关系型数据库有多种，如SQLite、MySQL、Oracle、SQL Server、DB2等。
（⼀）  MySQL存储
在Python2中连接MySQL库使⽤MySQLdb，但此库的官⽅不⽀持Python3，所以接下来使⽤的库是PyMySQL。
在使⽤之前需先安装好MySQL数据库并且能正常运⾏。此外还需要安装PyMySQL库。
MySQL在Linux上的源码安装⽅式参考这个⽹址：
1    连接数据库
现在运⾏MySQL的本地主机上连接数据库，假设⽤户名是michael，密码是michael123，端⼝是3508。现使⽤PyMySQL来连接MySQL，接着创建⼀个新的数据库，叫作spiders，代码如下：
sql包含哪几个部分
注PyMySQL下载连接是：
import pymysql
# ⾸先连接数据库，提供主机地址，端⼝，数据库⽤户名及密码
db = t(host='localhost', port=3508, user='michael', password='michael123')
cursor = db.cursor()                              # 获取游标，利⽤游标执⾏SQL语句
data = cursor.fetchone()                      # 获取前⾯的查询结果
print('Database version:', data)
# 创建数据库spiders
db.close()
输出如下所⽰：
Database version: ('5.7.24',)
通过PyMySQL的connect()⽅法声明⼀个MySQL连接对象db，需要传⼊运⾏MySQL的主机IP。如果在本地运⾏，就传⼊localhost 。如果MySQL在远程运⾏，则传⼊其主机IP地址。后⾯的参数user即⽤户成功连接后，再调⽤cursor()⽅法获得MySQL的操作游标，利⽤游标来执⾏SQL语句。这⾥执⾏了两条SQL，直接⽤execute()⽅法执⾏即可。第⼀条SQL获得MySQL的当前版本，接着调⽤fetchone()⽅法2    创建表
前⾯创建了spiders数据库，接下来在该数据库上创建数据表students，在创建数据表的命令中要指明参数db='spiders'。在执⾏SQL创建表之前，还要为数据表指定相应的字段信息。这⾥students数据表指import pymysql
db = t(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))'
db.close()
运⾏上⾯代码没有出现报错信息就成功在spiders数据库上创建了students数据表。这⾥数据表的字段很简单，实际在获取⽹页数据时，根据获取到的数据设计特定的字段。
3    插⼊数据
在数据库中创建好数据表后，就可以在数据表中插⼊数据。现有⼀条信息是：学号20120001，姓名是michae，年龄是20。向数据表中插⼊数据时，也需要连接数据库，获取游标，例如下⾯代码所⽰：import pymysql
id = '20120001'
user = 'michael'
age = 20
db = t(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:
dbmit()
except:
db.close()
在这个插⼊数据代码中，先构造⼀个SQL语句，其value值没有⽤字符串拼接⽅式来构造，这⾥选择直接⽤格式化符 %s 来实现。调⽤execute()⽅法时，第⼀个参数是SQL语句，Value值⽤统⼀的元组传过执⾏完execute()⽅法后，需要执⾏db对象的commit()⽅法才可实现数据插⼊，这个⽅法才是真正将语句提交到数据库执⾏的⽅法。对于数据插⼊、更新、删除操作，都需要调⽤该⽅法才能⽣效。
接下来，加了⼀层异常处理。如果执⾏失败，则调⽤rollback()执⾏数据回滚，相当于什么都没有发⽣过。
这⾥涉及事务的问题。事务机制可以确保数据的⼀致性，也就是这件事要么发⽣了，要么没有发⽣。如插⼊⼀条数据，不会存在插⼊⼀半的情况，要么全部插⼊，要么都不插⼊，这就是事务的原⼦性。原⼦性(atomicity)：事务是⼀个不可分割的⼯作单位，事务中包括的所有操作要么都做，要么都不做；
⼀致性(consistency)：事务必须使数据库从⼀个⼀致性状态变到另⼀个⼀致性状态。⼀致性与原⼦性是密切相关的；
隔离性(isolation)：⼀个事务的执⾏不能被其他事务⼲扰，即⼀个事务内部的操作及使⽤的数据对并发的其他事
务是隔离的，并发执⾏的各个事务之间不能互相⼲扰；
持久性(durability)：持续性也称永久性(permanence)，指⼀个事务⼀旦提交，它对数据库中数据的改变就应该
是永久性的。接下来的其他操作或故障不应该对其有任何影响。
插⼊、更新和删除操作都是对数据库进⾏更改的操作，⽽更改操作都必须为⼀个事务，所以这些操作的标准写法就是：
try:
dbmit()
except:
这样可保证数据的⼀致性。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 = t(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:
ute(sql, tuple(data.values())):
print('Successful')
dbmit()
except:
print('Failed')
db.close()
这⾥传⼊字典数据，并定义为data变量。表名也定义成变量table。接着构造⼀个动态的SQL语句。在构造SQL语句时直接根据data的键名进⾏构造，⽤逗号分隔。所以', '.join(data.keys())的结果是id, nam INSERT INTO students(id, name, age) VALUES(%s, %s, %s)
最后，为execute()⽅法的第⼀个参数传⼊sql变量，第⼆个参数传⼈data的键值构造的元组，就可以成功插⼊数据。
4    更新数据
更新操作也要执⾏SQL语句，最简单⽅式就是构造⼀个SQL语句，然后执⾏：
import pymysql
db = t(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
dbmit()
except:
db.close()
这⾥⽤占位符构造SQL，执⾏excute()⽅法，传⼊元组形式参数，同样执⾏commit()⽅法执⾏操作。简单的数据更新可⽤这个⽅法来做。
在⽹页数据抓取时，多数情况都需要插⼊数据，在插⼊数据时可能会有重复数据，如果出现重复则需要更新数据⽽不是重复保存⼀次。所以需要再实现⼀种去重的⽅法，如果数据存在，则更新数据；如果import pymysql
db = t(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:
ute(sql, tuple(data.values())*2):
print('Successful')
dbmit()
except:
print('Failed')
db.close()
这⾥构造的SQL语句其实是插⼊语句，但是在后⾯加了ON DUPLICATE KEY UPDATE，这表⽰如果主键已经存在，就执⾏更新操作。⽐如，传⼊的数据id号相同，因id字段是主键，判断id号是否存在，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 = t(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:
dbmit()
except:
db.close()
删除的条件有很多，运算符有⼤于、⼩于、等于、LIKE等，条件连接符有AND、OR等。
6    查询数据
有了插⼊、修改和删除操作，还有⼀个查询操作。查询操作使⽤SELECT语句，如下⽰例所⽰：
import pymysql
db = t(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
sql = 'SELECT * FROM students WHERE age >= 20'
try:
print('Count:', wcount)          # 查询到的总记录条数
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.fetchone()⽅法：获取查询结果的第⼀条数据，结果是元组形式，元组的元素顺序与字段是⼀⼀对应的。
cursor.ftechall()⽅法：获取查询结果的剩余所有数据，结果类型是⼆重元组，每个元素是⼀条记录。
cursor.ftechall()⽅法输出是3条数据⽽不是4条数据，是因为它内部实现有⼀个偏移指针⽤来指向查询结果，最开始偏移指针指向第⼀条数据，取⼀次后，指针偏移到下⼀条数据。由于调⽤了fetchone()⽅此外，使⽤fetchall()⽅法以元组形式全部返回，当数据量很⼤时，占⽤的开销会⾮常⾼。此时可⽤while循环加fetchone()⽅法获取所有数据。可⽤下⾯⽅法逐条获取数据：
import pymysql
db = t(host='localhost', port=3508, user='michael',
password='michael123', db='spiders')
cursor = db.cursor()
sql = 'SELECT * FROM students WHERE age >= 20'
try:
print('Count:', wcount)
row = cursor.fetchone()
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')
db.close()
这样循环⼀次，指针就偏移⼀条数据，随⽤随取，简单⾼效。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。