数据存储——数据库

2023-11-11

关系型数据库存储

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

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

MySQL的存储

本节我们主要介绍Python3下MySQL的存储。

在python2中，连接MySQL的库大多是使用MySQLdb，但是此库的官方并不支持Python3，所以这里推荐使用PyMySQL。接下来，我们就来讲解一下使用PyMySQL操作MySQL数据库的方法。

连接数据库

这里，首先尝试连接一下数据库。假设当前的 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 CHARACTER SET utf8')
db.close()

运行结果如下：

Database version: ('5.6.22',)

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

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

创建表

创建数据库后，在连接时需要额外指定一个参数db。
接下来，新建一个数据表students，此时执行创建表的SQL语句即可。示例代码如下：

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(20) not null,name varchar(20) not null,age smallint not null,primary key(id))'
cursor.execute(sql)
db.close()

运行之后，我们便创建了一个名为 students 的数据表。
当然，为了演示，这里只指定了最简单的几个字段。实际上，在爬虫过程中，我们会根据爬取结果设计特定的字段。

插入数据

下一步就是向数据库中插入数据了。例如，这里爬取了一个学生信息，学号为 20120001 ，名字为Bob ，年龄为20 ，那么如何将该条数据插入数据库呢？示例代码如下：

import pymysql

id = '20120001'
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()

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

接下来，我们加了一层异常处理。如果执行失败，则调用 rollback() 执行数据回滚，相当于什么都没有发生过。
在这里插入图片描述
插入、更新和删除操作都是对数据库进行更改的操作，而更改操作都必须为一个事务，所以这些操作的标准写法就是：

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

这样就可以保证数据的一致性。

上面的数据插入操作是通过构造SQL语句实现的，但是很明显，存在一个极不方便的地方，比如突然增加了性别字段gender，此时SQL语句就需要改为：

insert  into students(id,name,age,gender) values(%s,%s,%s,%s)

相应的元组参数需要改为：

(id,name,age,gender)

这显然不是我们想要的。在很多情况下，我们要达到的效果是插入方法无需改动，做成一个通用方法，只需要传入一个动态变化的字典就好了。比如，构造这样一个字典：

{'id':'20120001','name':'Bob','age':20}

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

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 语句了。

首先，需要构造插入的字段 id、name和age。这里只需要将 data 的键名拿过来，然后用逗号分隔即可。所以’,’ .join(data.keys()）的结果就是id,name,age，然后需要构造多个%s 当作占位符，有几个字段构造几个即可。比如，这里有三个字段，就需要构造%s,%s,%s。最后，利用字符串的 format()方法将表名、字段名和占位符构造出来。最终的 SQL 语句就被动态构造成了：

INSERT INTO students(id, name, age) VALUES (%s, %s, %s)

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

如此以来，我们便实现了传入一个字典来插入数据的方法，不需要再去修改 SQL 语句和插入操作了。

更新数据

数据更新操作实际上也是执行 SQL 语句，最简单的方式就是构造一个 SQL 语句，然后执行：

sql = 'update students set age=%s where name=%s'
try:
    cursor.execute(sql,(22,'Bob'))
    db.commit()
except:
    db.rollback()
db.close()

这里同样用占位符的方式构造 SQL 然后执行 execute()方法，传人元组形式的参数，同样执行 commit()方法执行操作。如果要做简单的数据更新的话，完全可以使用此方法。

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

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])
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 仍然为 20120001 ，但是年龄有所变化，由 20 变成了 21 ，此时这条数据不会被插入，而是直接更新 id 为 20120001 的数据。
完整的 SQL 构造出来是这样的：

insert into students(id,name,age) values(%s,%s,%s) on duplicate key update id = %s, name = %s, age = %s

这里就变成6个%s，所以在后面的execute()方法的第二个参数元组就需要乘以2变成原来的2倍。

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

删除数据

删除操作相对简单，直接使用 DELETE 语句即可，只是需要指定要删除的目标表名和删除条件，而且仍然需要使用 db的 commit() 方法才能生效。示例如下：

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 等，所以不再继续构造复杂的判断条件。这里直接将条件当作字符串来传递，以实现删除操作。

查询数据

说完插入、修改和删除等操作，还剩下非常重要的一个操作，那就是查询。查询会用到 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('Results Type:',type(results))
    for row in results:
        print(row)
except:
    print('Error')

运行结果如下：

Count: 4
One: ('20120001','Bob',25)
Results: (('20120011','Mary',21),('20120012','Mike',20),('20120013','James',22))
Results Type: <class 'tuple'>
('20120011','Mary',21)
('20120012','Mike',20)
('20120013','James',22)

这里我们构造了一条 SQL 语句，将年龄 20 岁及以上的学生查询出来，然后将其传给 execute()方法。注意，这里不再需要 db 的 commit() 方法。接着，调用 cursor 的rowcount 属性获取查询结果的条数，当前示例中是4条。

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

但是这里需要注意一个问题，这里显示的是3条数据而不是4条， fetchall() 方法不是获取所有数据吗？这是因为它的内部实现有一个偏移指针用来指向查询结果，最开始偏移指针指向第一条数据，取一次之后，指针偏移到下一条数据，这样再取的话，就会取到下一条数据了。我们最初调用 fetchone() 方法，这样结果的偏移指针就指向下一条数据，fetchall() 方法返回的是偏移指针指向的数据一直到结束的所有数据，所以该方法获取的结果就只剩3个了。

此外，我们还可以用 while 循环加 fetchone() 方法来获取所有数据，而不是用 fetchall() 全部一起获取出来。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')

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

非关系型数据库存储

NoSQL ，全称 Not Only SQL ，意为不仅仅是 SQL ，泛指非关系型数据库。NoSQL 是基于键值对的，而且不需要经过 SQL 层的解析，数据之间没有耦合性，性能非常高。

非关系型数据库又可细分如下：
键值存储数据库：代表有 Redis、Voldemort 和 Oracle BDB等。
列存储数据库：代表有 Cassandra、HBase 和 Riak等。
文档型数据库：代表有 CouchDB 和 MongoDB 等。
图形数据库：代表有 Neo4J、lnfoGrid 和 Infinite Graph等。

对于爬虫的数据存储来说，一条数据可能存在某些字段提取失败而缺失的情况，而且数据可能随时调整。另外，数据之间还存在嵌套关系。如果使用关系型数据库存储，一是需要提前建表，二是如果存在数据嵌套关系的话，需要进行序列化操作才可以存储，这非常不方便。如果用了非关系型数据库，就可以避免一些麻烦，更简单高效。

本节中，我们主要介绍 MongoDB 和 Redis 的数据存储操。

MongoDB存储

MongoDB 是由 C++ 语言编写的非关系型数据库，是一个基于分布式文件存储的开源数据库系统，其内容存储形式类似 JSON 对象，它的字段值可以包含其他文档、数组及文档数组，非常灵活。在这一节中，我们就来看看 Python3 下 MongoDB 的存储操作。

连接 MongoDB

连接 MongoDB 时，我们需要使用 PyMongo 库里面的 MongoClient 。一般来说，传入 MongoDB 的IP及端口即可，其中第一个参数为地址 host ，第二个参数为端口 port （如果不给它传递参数，默认是 27017)：

import pymongo

client = pymongo.Mongoclient(host='localhost',port=27017)

另外 MongoClient 第一个参数 host 还可以直接传入 MongoDB 连接字符串，它以 mongodb 开头，例如：

client = MongoClient('mongodb://localhost:27017/')

这也可以达到同样的连接效果。

指定数据库

MongoDB 中可以建立多个数据库，接下来我们需要指定操作哪个数据库。这里我们以 test 数据库为例来说明，下一步需要在程序中指定要使用的数据库：

db = client.test

这里调用 client 的 test 属性即可返回 test 数据库。当然，我们也可以这样指定：

db = client['test']

这两种方式是等价的。

指定集合

MongoDB 每个数据库又包含许多集合（ collection ），它们类似于关系型数据库中的表。下一步需要指定要操作的集合，这里指定一个集合名称为 students。与指定数据库类似，指定集合也有两种方式：

collection = db.students 
collection = db['students']

这样我们便声明了 Collection 对象.。

插入数据

接下来，便可以插入数据了。对于 students 这个集合，新建一条学生数据，这条数据以字典形式表示。

student = {
    ’id': '20170101',
    'name': 'Jordan',
    'age': 20,
    'gender: 'male'
}

这里指定了学生的学号、姓名、年龄和性别。接下来，直接调用 collection 的 insert() 方法即可插入数据，代码如下：

result = collection.insert(student) 
print(result)

在 MongoDB 中，每条数据其实都有一个＿id 属性来唯一标识。如果没有显式指明该属性， MongoDB 会自动产生一个 ObjectId 类型的＿id 属性。insert() 方法会在执行后返回＿id 值。

运行结果如下：

5932a68615c2606814c91f3d

当然，我们也可以同时插入多条数据，只需要以列表形式传递即可，示例如下：

student1 = { 
    'id': '20170101', 
    'name':'Jordan', 
    'age': 20, 
    'gender':'male' 
}
student2 = { 
    'id' : '20170202', 
    'name':'Mike', 
    'age': 21, 
    'gender':'male' 
}
result = collection.insert([student1, student2]) 
print(result)

运行的结果是对应_id的集合：

[ObjectId(’ 5932a80115c2606a59e8ao48'), ObjectId('5932a80115c2606a59e8ao49 ' )]

实际上，在 PyMongo 3.x 版本中，官方已经不推荐使用 insert() 方法了。当然，继续使用也没有什么问题。官方推荐使用 insert_one() 和 insert_many() 方法来分别插入单条记录和多条记录，示例如下:

result = collections.insert_one(student)
print(result)
print(result.inserted_id)

运行结果如下：

<pymongo.results.InsertOneResult object at 0x10d68b558>
5932a80115c2606a59e8f6c5

与insert() 方法不同，这次返回的是 InsertOneResult 对象，我们可以调用其 inserted_id 属性获取_id。

对于 insert_many()方法，我们可以将数据以列表形式传递。调用inserted_ids属性获取插入数据的_id列表。

查询

插入数据后，我们可以利用find_one() 或 find() 方法进行查询，其中 find_one() 查询得到的是单个结果，find() 则返回一个生成器对象。示例如下：

result = collection.find_one({'name':'Mike'})
print(type(result))
print(result)

我们这里查询name为Mike的数据，它返回的结果就是字典类型，运行结果如下：

<class 'dict'>
{'_id':ObjectId('5932a80115c2606a59e8a265'),'id':'20170202','name':'Mike','age':21,'gender':'male'}

可以发现，它多了＿id 属性，这就是 MongoDB 在插入过程中自动添加的。

此外，我们也可以根据 ObjectId 查询，此时需要使用 bson 库里面的 objectid:

from bson.objectid import ObjectId

result = collection.find_one({'_id':ObjectId('5932a80115c2606a59e8a265')})
print(result)

其查询结果依然是字典类型，具体如下：

{'_id':ObjectId('5932a80115c2606a59e8a265'),'id':'20170202','name':'Mike','age':21,'gender':'male'}

当然，如果查询结果不存在，则会返回 None。

对于多条数据的查询，我们可以使用 find() 方法。例如，这里查找年龄为 20 的数据，示例如下：

results = collection.find({'age':20})
print(results)
for result in results:
    print(result)

运行结果如下：

<pymongo.cursor.Cursor object at 0x1032d5128>
{'_id': Objectld( '593278c11Sc2602667ec6bae'), 'id ': '20170101', 'name':'Jordan','age': 20, 'gender':'male'} 
{'_id': Objectld('593278c81sc2602678bb2b8d'),'id':'20170102', ' name ':'Kevin', 'age' : 20, 'gender': 'male' } 
{'_id': Objectld ('593278d81Sc260269d764Sa8'), 'id ' : ' 20170103', ' name ' : 'Harden', ' age ': 20, 'gender ' : 'male '}

返回结果是 Cursor 类型，它相当于一个生成器，我们需要遍历取到所有的结果，其中每个结果都是字典类型。

如果要查询年龄大于20的数据，则写法如下：

result = collection.find({'age':{'$gt':20}})

这里查询的条件键值已经不是单纯的数字了，而是一个字典，其键名为比较符号$gt ，意思是大于，键值为 20。

这里将比较符号归纳为下图：
在这里插入图片描述
另外，还可以进行正则匹配查询。例如，查询名字以开头的学生数据，示例如下：

results= collection.find ({'name' : {'$regex' : '^'M .*'}} )

这里使用$regex 来指定正则匹配，^M.＊代表以M开头的正则表达式。

这里将一些功能符号再归类为下表：
在这里插入图片描述
关于这些操作的更详细用法，可以在 MongoDB 官方文档找到 https://docs.mongodb.com/manual/reference/ operator/query/

计数

要统计查询结果有多少条数据，可以调用 count() 方法。比如，统计所有数据条数：

count = collection.find().count() 
print(count)

或者统计符合某个条件的数据：

count= collection.find({'age' : 20}) .count() 
print(count)

运行结果是一个数值，即符合条件的数据条数

排序

排序时，直接调用 sort() 方法，并在其中传入排序的字段及升降序标志即可。示例如下：

results = collection.find().sort('name', pymongo.ASCENDING) 
print([result['name'] for result in results])

运行结果如下:

['Harden', 'Jordan','Kevin','Mark','Mike']

这里我们调用 pymongo.ASCENDING 指定升序。如果要降序排列，可以传入 pymongo.DESCENDING

偏移

在某些情况下，我们可能想只取某几个元素，这时可以利用 skip() 方法偏移几个位置，比如偏移2，就忽略前两个元素，得到第三个及以后的元素：

results = collection.find().sort('name' ,pymongo.ASCENDING).skip(2) 
print([result['name'] for result in results])

运行结果如下：

['Kevin','Mark','Mike']

另外，还可以用 limit() 方法指定要取的结果个数，示例如下：

results = collection.find().sort('name' ,pymongo.ASCENDING).skip(2).limit(2)
print ([result['name'] for result in results])

运行结果如下：

[’ Kevin' , 'Mark' ]

如果不使用 limit() 方法，原本会返回三个结果，加了限制后，会截取两个结果返回。

值得注意的是，在数据库数量非常庞大的时候，如千万、亿级别，最好不要使用大的偏移量来查询数据，因为这样很可能导致内存溢出。此时可以使用类似如下操作来查询：

from bson.objectid import ObjectId 
collection.find({'_id ':{$gt':ObjectId('593278c81Sc2602678bb2b8d')}})

这时需要记录好上次查询的 _id。

更新

对于数据更新，我们可以使用 update() 方法，指定更新的条件和更新后的数据即可。例如：

condition = {'name':'Kevin'}
student = collection.find_one(condition)
student['age'] = 25
result = collection.update(collection,student)
print(result)

这里我们要更新 name为Kevin 的数据的年龄：首先指定查询条件，然后将数据查询出来，修改年龄后调用 update() 方法将原条件和修改后的数据传入。

运行结果如下：

｛'ok':1,'nModified':1,'n':1,'updatedExisting':True}

返回结果是字典形式， ok 代表执行成功，nModified 代表影响的数据条数。

另外，我们也可以使用$set 操作符对数据进行更新，代码如下：

result = collection.update(condition,{'$set':student})

这样可以只更新 student 字典内存在的字段。如果原先还有其他字段，则不会更新，也不会删除。如果不用$set 的话，则会把之前的数据全部用 student 字典替换；如果原本存在其他字段，会被删除。

另外， update() 方法其实也是官方不推荐使用的方法。这里也分为 update_one() 方法和
update_many() 方法，用法更加严格，它们的第二个参数需要使用$类型操作符作为字典的键名，示例如下：

condition = {'name':'Kevin'}
student = collection.find_one(condition)
student['age'] = 26
result = collection.update_one(condition,{'$set':student})
print(result)
print(result.matched_count,result.modified_count)

这里调用了 update_one() 方法，第二个参数不能再直接传人修改后的字典，而是需要使用｛‘$set’:student ｝这样的形式，其返回结果是 UpdateResult 类型。然后分别调用 matched_count 和 modified_count属性，可以获得匹配的数据条数和影响的数据条数

运行结果如下：

<pymongo.results.UpdateResult object at 0x10d17b678>
1  1

如果调用 update_many() 方法，则会将所有符合条件的数据都更新，示例如下：

condition = {'age':{'$gt':20}}
result = collection.update_many(condition,{'$inc':{'age':1}})
print(result)
print(result.matched_count,result.modified_count)

运行结果如下：

<pymongo.results.UpdateResult object at 0x10d17b699>
3  3

可以看到，这时所匹配到的数据都会被更新。

删除

删除操作比较简单，直接调用 remove() 方法指定删除的条件即可，此时符合条件的所有数据均被删除。示例如下：

result = collection.remove({'name':'Kevin'})
print(result)

运行结果如下：

{'ok':1,'n':1}

另外，这里依然存在两个新的推荐方法——delete_one()和 delete_many()。示例如下：

result = collection.delete_one({'name':'Kevin'})
print(result)
print(result.deleted_count)
result = collection.delete_many({'age':{'$lt':25}})
print(result.deleted_count)

运行结果如下：

<pymongo.results.DeleteResult object at 0x10e6ba4c8
1
4

delete_one() 即删除第一条符合条件的数据， delete_many() 删除所有符合条件的数据。它们的
返回结果都是 DeleteResult 类型，可以调用 deleted_count 属性获取删除的数据条数。

其他操作

另外，PyMongo 还提供一些组合方法，如 find_one_and_delete()、find_one_and_replace()、和find_one_and_update()，它们是查找后删除、替换和更新操作，其用法与上述方法基本一致。

。。。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)