如何使用MySQL，这些操作你得明白？
如何使用MySQL，这些操作你得明白？
阅读文本大概需要 6 分钟。
昨天给大家介绍了安装MySQL数据库的方法，大家都学会了吗？
MySQL数据库是基于关系模型的数据库，而关系模型是通过二维表来保存的，所以它的储存方式就是行列组成的表，每一列是一个字段，每一行是一条记录。今天我们主要介绍Python3下使用PyMySQL操作MySQL数据库的方法。
1.准备工作
在开始之前，我们需要安装好PyMySQL库，安装方法仍然是我们老生常谈的方法，用pip安装，命令如下：
pip3 install pymysql
执行完命令后即可完成安装。
2.连接数据库
假设当前的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.7.23',)连接成功后，需要调用cursor（）方法获得MySQL的操作游标，利用游标来执行SQL语句。这里我们执行了两句SQL，直接用execute（）方法即可。第一句SQL用于获得MySQL的当前版本，然后调用fetchone（）方法获得第一条数据，也就得到了版本号。
第二句SQL执行创建数据库的操作，数据库命名为spiders，默认编码为UTF-8。
3.创建表
一般来说，创建数据库的操作只需执行一次就好。当然，我们也可以手动创建数据库。以后我们的操作都在spiders数据库上执行。创建数据库后，在连接时需要额外指定一个参数db。
例如，我们创建一个数据包students，此时执行创建表的SQL语句即可。这里指定3个字段（id、name、age），创建该表的示例代码如下：
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()运行之后，我们便创建了一个名为students的数据表。
4.插入数据
例如，这里爬取了一个学生信息，学号为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 student(id,name,age) values(%s,%s,%s)'
try:
cursor.execute(sql,(id,user,age))
db.commit()
except:
db.rollback()
db.close()这里首先构造了一个SQL语句，其Value值没有用字符串拼接的方式来构造，选择直接用格式化符%s来实现，有几个Value写几个%s，我们只需在execute（）方法的第一个参数传入SQL语句，Value值用统一的元组传过来就好了。
之后值得注意的是，需要执行db对象的commit（）方法才可实现数据插入，这个方法才是真正将语句提交到数据库执行的方法。对于数据插入、更新、删除操作，都需要调用该方法才能生效。
接下来，我们加一层异常处理，如果执行失败，则调用rollback（）执行数据回滚，相当于什么都没发生过。
上面数据插入的操作是通过构造SQL语句实现的，但有一个极其不方便的地方，比如突然增加了性别字段gender，此时SQL语句就需要改成：
INSERT INTO students(id,name,age,gender) values(%s,%s,%s,%s)相应的元组参数则需要改成：
(id,name,age,gender)这显然不是我们需要的。在很多情况下，我们要达到的效果就是插入方法无需改动，做成一个通用方法，只需要传入一个动态变化的字典就好了。
然后SQL语句会根据字典动态构造，元组也动态构造，这样才能实现通用的插入方法。所以，这里我们需要改写一下插入方法：
import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,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()如此以来，我们便实现了传入一个字典来插入数据的方法，不需要再去修改SQL语句和插入操作了。
5.更新数据
数据更新操作实际上也是执行SQL语句，最简单的方式就是构造一个SQL语句，然后执行：
sql = 'UPDATE students SET age =%s WHERE name = %s'
try:
cursor.execute(sql,(25,'Bob'))
db.commit()
except:
db.rollback()
db.close()在实际的数据抓取过程中，大部分情况下需要插入数据，但我们关心的是会不会出现重复数据，如果出现了，我们希望更新数据而不是重复保存一次。因此，这里可以再实现一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据。示例如下：
import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,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)
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 KEYUPDATE。这行代码的意思是如果主键已经存在，就执行更新操作。
比如，我们传入的数据id仍然为20120001，但是年龄有所变化，由20变成了21，此时这条数据不会被插入，而是直接更新id为20120001的数据。
6.删除数据
删除操作先对简单，直接使用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()7.查询数据
查询会用到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岁及以上的学生查询出来，然后将其给excute（）方法。注意，这里不需要db的commit（）方法。接着，调用cursor的rowcount属性获取查询结果的条数，当前示例中是4条。
但是这里需要注意一个问题，这里显示的是3条数据而不是4条，fetchall（）方法不是获取所有数据吗？这个问题留给大家思考，下一篇公布答案。
总结
今天我们介绍了如何使用PyMySQL操作MySQL数据库的方法，主要包括：插入、更新、删除、查询数据，后面我会给大家找一些经典的实战案例，让大家在实战中熟悉运用这些操作来储存数据。
