第五部分_二) 数据存储_关系型数据库：MySQL存储方式)

一关系型数据库存储

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

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

（一） MySQL存储
在Python2中连接MySQL库使用MySQLdb,但此库的官方不支持Python3,所以此时此刻呢使用的库是PyMySQL.
在使用之前需先安装好MySQL数据库并且能正常运行.此外还需要安装PyMySQL库.
MySQL在Linux上的源码安装方式参考这个网址：

1 连接数据库
现在运行MySQL的本地主机上连接数据库,假设用户名是michael,密码是michael123,端口是350⑧现使用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: ('⑤⑦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-⑧

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:
('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存储方式)

（一） MySQL存储
在Python2中连接MySQL库使用MySQLdb,但此库的官方不支持Python3,所以此时此刻呢使用的库是PyMySQL.
在使用之前需先安装好MySQL数据库并且能正常运行.此外还需要安装PyMySQL库.
MySQL在Linux上的源码安装方式参考这个网址：

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

原子性(atomicity)：事务是一个不可分割的工作单位,事务中包括的所有操作要么都做,要么都不做；

一致性(consistency)：事务必须使数据库从一个一致性状态变到另一个一致性状态.一致性与原子性是密切相关的；

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

cursor.rowcount属性：获取查询结果的条数,这里是4条.

cursor.fetchone()方法：获取查询结果的第一条数据,结果是元组形式,元组的元素顺序与字段是一一对应的.

row = cursor.fetchone()
except:
print('Error')
db.close()
这样循环一次,指针就偏移一条数据,随用随取,简单高效.

相关推荐

编辑推荐

热门文章

网站首页

视频教程

下载频道

文章中心

问答中心

在线工具

第五部分_二) 数据存储_关系型数据库：MySQL存储方式)

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

原子性(atomicity)：事务是一个不可分割的工作单位,事务中包括的所有操作要么都做,要么都不做；

一致性(consistency)：事务必须使数据库从一个一致性状态变到另一个一致性状态.一致性与原子性是密切相关的；

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

cursor.rowcount属性：获取查询结果的条数,这里是4条.

cursor.fetchone()方法：获取查询结果的第一条数据,结果是元组形式,元组的元素顺序与字段是一 一对应的.

row = cursor.fetchone() except: print('Error') db.close() 这样循环一次,指针就偏移一条数据,随用随取,简单高效.

相关推荐

编辑推荐

热门文章

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

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

cursor.fetchone()方法：获取查询结果的第一条数据,结果是元组形式,元组的元素顺序与字段是一一对应的.

row = cursor.fetchone()
except:
print('Error')
db.close()
这样循环一次,指针就偏移一条数据,随用随取,简单高效.