1. 准备工作
在开始之前,请确保已经安装好了 MySQL 数据库并保证它能正常运行,而且需要安装好 PyMySQL 库。
$ pip3 install PyMySQL
在进行python访问MySQL数据库时,程序报错:OperationalError: (2003, “Can’t connect to MySQL server on ‘localhost’ ([WinError 10061] 由于目标计算机积极拒绝,无法连接。)”)
解决办法:https://blog.csdn.net/BigData_Mining/article/details/88344513
2. 连接数据库-connect
首先,导包import pymysql
;
其次,利用pymysql.connect()
声明一个 MySQL 连接对象 db,传入 MySQL 运行的 host(即 IP)、user (即用户名),password(即密码)、port (即端口,默认为 3306);
然后,调用 cursor
方法获得 MySQL 的操作游标,利用游标来执行 SQL 语句。
再之,利用execute
方法执行sql语句;
最后,调用close
方法关闭数据库。
注意:一般来说,创建数据库的操作只需要执行一次就好了。当然,我们也可以手动创建数据库。
import pymysql
#声明一个 MySQL 连接对象 db
db = pymysql.connect(
host='localhost', #数据库在本机上
user='root', ##用户名
password='123', #用户密码
port=3306, #运行端口
database="test", #连接的数据库
charset="utf8"
)
cursor = db.cursor() #获得 MySQL 的操作游标,利用游标来执行 SQL 语句
#用execute方法执行SQL语句
cursor.execute('SELECT VERSION()') #获得 MySQL 的当前版本
data = cursor.fetchone() #调用fetchone方法获得第一条数据,也就得到了版本号。
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8") #创建一个新的数据库,名字叫作 spiders
db.close()
3. 创建表-CREATE语句
以后,我们的操作都在 spiders 数据库上执行。
创建数据库后,在连接时需要额外指定一个参数 db。
接下来,新创建一个数据表 students,此时执行创建表的 SQL 语句即可。
字 段 名 | 含 义 | 类 型 |
---|---|---|
id | 学号 | varchar |
name | 姓名 | varchar |
age | 年龄 | int |
创建该表的示例代码如下:
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(255) NOT NULL,
name VARCHAR(255) NOT NULL,
age INT NOT NULL,
PRIMARY KEY (id)
)"""
cursor.execute(sql)
db.close()
4. 插入数据-INSERT语句
值得注意的是,需要执行 db 对象的 commit
方法才可实现数据插入,这个方法才是真正将语句提交到数据库执行的方法。对于数据插入、更新、删除操作,都需要调用该方法才能生效。
接下来,我们加了一层异常处理。如果执行失败,则调用 rollback 执行数据回滚,相当于什么都没有发生过。
直接插入数据
例如,这里爬取了一个学生信息,学号为 20120001,名字为 Bob,年龄为 20:
import pymysql
id = '20120001'
user = '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, user, age)) # 执行sql语句
db.commit() # 提交到数据库执行
except:
db.rollback() # 异常处理,如果发生错误则回滚rollback
db.close()
问题:
构建字典实现插入数据(推荐)
首先,把需要传入的数据设置为字典形式,并将其定义为 data 变量,表名也定义成变量 table。
其次,构造一个动态的 SQL 语句了:
——首先,需要构造插入的字段 id、name 和 age。
——将 data 的键名拿过来,然后用逗号分隔即可。所以 ‘, ‘.join(data.keys()) 的结果就是 id, name, age,
——需要构造多个 % s 当作占位符,有几个字段构造几个即可。比如,这里有三个字段,就需要构造 % s, % s, % s。这里首先定义了长度为 1 的数组 [’% s’],然后用乘法将其扩充为 [’% s’, ‘% s’, ‘% s’]
——再调用 join 方法,最终变成 % s, % s, % s。
——最后,利用字符串的 format 方法将表名、字段名和占位符构造出来。
最后,为 execute 方法的第一个参数传入 sql 变量,第二个参数传入 data 的键值构造的元组,就可以成功插入数据了。
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()
5.事务机制
事务机制可以确保数据的一致性,也就是这件事要么发生了,要么没有发生。比如插入一条数据,不会存在插入一半的情况,要么全部插入,要么都不插入,这就是事务的原子性。另外,事务还有 3 个属性 —— 一致性、隔离性和持久性。这 4 个属性通常称为 ACID 特性,具体如表所示。
属 性 | 解 释 |
---|---|
原子性(atomicity) | 事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做 |
一致性(consistency) | 事务必须使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的 |
隔离性(isolation) | 一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰 |
持久性(durability) | 持续性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响 |
插入、更新和删除操作都是对数据库进行更改的操作,而更改操作都必须为一个事务,所以这些操作的标准写法就是:
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
这样便可以保证数据的一致性。这里的 commit 和 rollback 方法就为事务的实现提供了支持。
6. 更新数据-UPDATE语句
简单的数据更新
#1.用占位符的方式构造sql语句
sql = 'UPDATE students SET age = % s WHERE name = % s'
#2.执行sql语句
try:
cursor.execute(sql, (25, 'Bob')) #执行 execute 方法,传入元组形式的参数
db.commit() #执行 commit 方法执行操作
except:
db.rollback()
db.close()
去重下的更新
在实际的数据抓取过程中,大部分情况下需要插入数据,但是我们关心的是会不会出现重复数据,如果出现了,我们希望更新数据而不是重复保存一次。
如果数据存在,则更新数据;如果数据不存在,则插入数据。
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 倍。
如此一来,我们就可以实现主键不存在便插入数据,存在则更新数据的功能了。
7. 删除数据-DELETE 语句
删除操作相对简单,直接使用 DELETE 语句即可,只是需要指定要删除的目标表名和删除条件,而且仍然需要使用 db 的 commit 方法才能生效。
示例如下:
table = 'students' #指定表
condition = 'age > 20' #删除的条件
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql) # 执行SQL语句
db.commit() # 执行SQL语句
except:
db.rollback() # 发生错误时回滚
db.close()# 关闭连接
因为删除条件有多种多样,运算符有大于、小于、等于、LIKE 等,条件连接符有 AND、OR 等,所以不再继续构造复杂的判断条件。这里直接将条件当作字符串来传递,以实现删除操作。
8.查询数据-SELECT 语句
查询会用到 SELECT 语句,示例如下:
sql = 'SELECT * FROM students WHERE age >= 20' #构造了一条 SQL 语句,将年龄 20 岁及以上的学生查询出来,然后将其传给 execute 方法
try:
cursor.execute(sql)
print('Count:', cursor.rowcount) #调用 cursor 的 rowcount 属性获取查询结果的条数
one = cursor.fetchone() #调用fetchone方法,获取结果的第一条数据,返回结果是元组形式,元组的元素顺序跟字段一一对应,即第一个元素就是第一个字段 id,第二个元素就是第二个字段 name,以此类推。
print('One:', one)
results = cursor.fetchall() #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)
但是这里需要注意一个问题,这里显示的是 3 条数据而不是 4 条,fetchall 方法不是获取所有数据吗?这是因为它的内部实现有一个偏移指针用来指向查询结果,最开始偏移指针指向第一条数据,取一次之后,指针偏移到下一条数据,这样再取的话,就会取到下一条数据了。我们最初调用了一次 fetchone 方法,这样结果的偏移指针就指向下一条数据,fetchall 方法返回的是偏移指针指向的数据一直到结束的所有数据,所以该方法获取的结果就只剩 3 个了。
9.获取数据-fetchone\fetchall\rowcount
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性,并返回执行execute()方法后影响的行数。
cursor.fetchone() | 调用fetchone方法,获取结果的第一条数据,返回结果是元组形式,元组的元素顺序跟字段一一对应 |
cursor.fetchall() | 返回的结果是二重元组,每个元素都是一条记录,将其遍历输出出来。 |
推荐的取数据方法:
用 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')
10.错误处理
异常 | 描述 |
---|---|
Warning | 当有严重警告时触发,例如插入数据是被截断等等。必须是 StandardError 的子类。 |
Error | 警告以外所有其他错误类。必须是 StandardError 的子类。 |
InterfaceError | 当有数据库接口模块本身的错误(而不是数据库的错误)发生时触发。 必须是Error的子类。 |
DatabaseError | 和数据库有关的错误发生时触发。 必须是Error的子类。 |
DataError | 当有数据处理时的错误发生时触发,例如:除零错误,数据超范围等等。 必须是DatabaseError的子类。 |
OperationalError | 指非用户控制的,而是操作数据库时发生的错误。例如:连接意外断开、 数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。 必须是DatabaseError的子类。 |
IntegrityError | 完整性相关的错误,例如外键检查失败等。必须是DatabaseError子类。 |
InternalError | 数据库的内部错误,例如游标(cursor)失效了、事务同步失败等等。 必须是DatabaseError子类。 |
ProgrammingError | 程序错误,例如数据表(table)没找到或已存在、SQL语句语法错误、 参数数量错误等等。必须是DatabaseError的子类。 |
NotSupportedError | 不支持错误,指使用了数据库不支持的函数或API等。例如在连接对象上 使用.rollback()函数,然而数据库并不支持事务或者事务已关闭。 必须是DatabaseError的子类。 |
参考文献
根据崔庆才《Python 3 开发网络爬虫》整理