在Python2中，连接MySQL的库大多使用MySQLdb，但是官方库不支持Python3，所以这里推荐的库是PyMySQL。本节我们将讲解如何使用PyMySQL来操作MySQL数据库。一、准备工作开始前，请确保MySQL数据库已经安装并可以正常运行，需要安装PyMySQL库。2.连接数据库这里，首先尝试连接数据库。假设当前MySQL在本地运行，用户名为root，密码为123456，运行端口为3306。这里先使用PyMySQL连接MySQL，然后新建一个名为spiders的数据库，代码如下:importpymysqldb=pymysql.connect(host='localhost',user='root',password='123456',port=3306)cursor=db.cursor()cursor.execute('SELECTVERSION()')data=cursor.fetchone()print('Databaseversion:',data)cursor.execute("CREATEDATABASEspidersDEFAULTCHARACTERSETutf8")db.close()运行结果如下：Databaseversion:('5.6.22',)这里通过PyMySQL的connect()方法声明了一个MySQL连接对象db，此时需要传入MySQL运行所在的主机（即IP）。由于MySQL在本地运行，所以传入localhost。如果MySQL在远程运行，则传入其公网IP地址。后面的参数user为用户名，password为密码，port为端口（默认为3306）。连接成功后，需要调用cursor()方法获取MySQL操作游标，使用游标执行SQL语句。这里我们执行了两句SQL，可以直接用execute()方法执行。SQL的第一句是获取MySQL的当前版本，然后调用fetchone()方法获取第一条数据，同样获取版本号。第二个SQL语句执行创建数据库的操作。数据库名称为spiders，默认编码为UTF-8。由于这条语句不是查询语句，所以直接执行就成功创建了数据库爬虫。然后，使用这个数据库进行后续操作。3、创建表一般来说，创建数据库的操作只需要执行一次。当然，我们也可以手动创建数据库。以后我们的操作都是在蜘蛛数据库上进行的。创建数据库后，连接时需要额外指定一个参数db。接下来新建一个数据表students，然后执行建表的SQL语句。这里指定了三个字段，结构如下。字段名含义类型id学号varchar姓名namevarcharageageint建表示例代码如下：importpymysqldb=pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')cursor=db.cursor()sql='CREATETABLEIFNOTEXISTSstudents(idVARCHAR(255)NOTNULL,nameVARCHAR(255)NOTNULL,ageINTNOTNULL,PRIMARYKEY(id))'cursor.execute(sql)db.close()运行后，我们创建了一个名为studentssurface的数据。当然，为了演示目的，这里只指定最简单的字段。其实在爬取的过程中，我们会根据爬取的结果来设计具体的字段。4.插入数据下一步就是向数据库中插入数据。比如这里爬取了一个学生信息，学号为20120001，名字为Bob，年龄为20，那么如何将这个数据插入到数据库中呢？示例代码如下：importpymysqlid='20120001'user='Bob'age=20db=pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')cursor=db.cursor()sql='INSERTINTOstudents(id,name,age)values(%s,%s,%s)'try:cursor.execute(sql,(id,user,age))db.commit()except:db.rollback()db.close()这里先构造一条SQL语句，其Value值不是通过字符串拼接构造的，如：sql='INSERTINTOstudents(id,name,age)values('+id+','+name+','+age+')'比较麻烦和不直观，所以我们选择直接用formatter%s来实现。有几个Values和几个%s，我们只需要在execute()方法的第一个参数中传入SQL语句，Value值在一个统一的元组中传递。这种写法既可以避免字符串拼接的麻烦，也可以避免引号冲突的问题。值得注意的是，需要执行db对象的commit()方法来实现数据插入。该方法是真正将语句提交给数据库执行的方法。对于数据的插入、更新、删除操作，需要调用该方法生效。接下来，我们添加了一层异常处理。如果执行失败，调用rollback()进行数据回滚，相当于什么都没有发生。这是公事。事务机制可以保证数据的一致性，即这个事件要么发生了，要么没有发生。比如插入一条数据时，一半不会插入，要么全部插入，要么全不插入。这就是事务的原子性。此外，事务具有三个属性——一致性、隔离性和持久性。这4个属性通常被称为ACID属性，如下表所示。属性解释原子性（atomicity）事务是一个不可分割的工作单元，事务中包含的所有操作要么完成要么不完成一致性（consistency）事务必须使数据库从一种一致状态变为另一种状态。一致性和原子性是密切相关的。隔离（isolation）一个事务的执行不能被其他事务干扰，即一个事务内使用的操作和数据与其他并发事务隔离。不能互相干扰。持久性（durability），也称为永久性（permanence），是指事务一旦提交，它对数据库中数据的改变应该是永久性的。以下其他操作或故障应该不会对其产生任何影响。插入、更新、删除操作都是改变数据库的操作，而改变操作必须是一个事务，所以这些操作的标准写法是：try:cursor。execute(sql)db.commit()except:db.rollback()这样可以保证数据的一致性。这里的commit()和rollback()方法提供了对事务实现的支持。上面的数据插入操作是通过构造SQL语句来实现的，但是很明显，有一个极其不方便的地方，比如突然增加了性别字段，需要将SQL语句改为：INSERTINTOstudents(id,name,age,gender)values(%s,%s,%s,%s)对应的元组参数需要改成：(id,name,age,gender)这显然不是我们想要的。很多时候，我们想要达到的效果是插入的方法不需要改变，做成通用的方法，只需要传入一个动态变化的字典即可。比如构造这样一个字典：{'id':'20120001','name':'Bob','age':20}然后sql语句会根据字典动态构造，tuple也会动态构造，从而实现通用插入法。所以，这里我们需要重写insert方法：data={'id':'20120001','name':'Bob','age':20}table='students'keys=','.join(data.keys())values=','.join(['%s']*len(data))sql='INSERTINTO{table}({keys})VALUES({values})'.format(表=表，键=键，值=值）尝试：ifcursor.execute（sql，元组（data.values（）））：打印（'成功'）db.commit（）除外：打印（'失败'）db.rollback()db.close()我们这里传入的数据是一个字典，定义为数据变量。表名也定义为变量表。接下来，您需要构造一条动态SQL语句。首先，您需要构建插入的字段id、name和age。这里只需要取数据的键名，用逗号隔开即可。所以','.join(data.keys())的结果是id,name,age,然后需要构造多个%s作为占位符，可以构造几个字段。比如这里有3个字段，需要构造%s,%s,%s。这里首先定义长度为1的数组['%s']，然后通过乘法扩充为['%s','%s','%s']，然后join()方法就是调用，最后变成%s，%s，%s。***，我们再使用string的format()方法构造表名、字段名和占位符。最终SQL语句是动态构造的：INSERTINTOstudents(id,name,age)VALUES(%s,%s,%s)***，为execute()方法的第一个参数传入sql变量，第二个将参数传给data的key值构造的元组，即可成功插入数据。这样我们就实现了传入字典插入数据的方法，不需要修改SQL语句和插入操作。5、更新数据数据更新操作其实就是执行SQL语句，最简单的方法就是构造一个SQL语句，然后执行：sql='UPDATEstudentsSETage=%sWHEREname=%s'try:cursor.execute(sql,(25,'Bob'))db.commit()except:db.rollback()db.close()也是用占位符构造SQL，然后执行execute()方法，传入tuples参数，同样执行commit()方法来执行操作。如果你想做一个简单的数据更新，你可以使用这个方法。但是在实际的数据抓取过程中，大多数情况下是需要插入数据的，但是我们关心的是会不会有重复的数据。如果是这样，我们希望更新数据而不是重复保存。另外，就像上面提到的动态构造SQL的问题，所以这里我们可以实现另一种去重方式，如果数据存在，则更新数据；如果数据不存在，则插入数据。此外，这种方法支持灵活的字典值传递。例子如下：data={'id':'20120001','name':'Bob','age':21}table='students'keys=','.join(data.keys())values=','.join(['%s']*len(data))sql='INSERTINTO{table}({keys})VALUES({values})ONDUPLICATEKEYUPDATE'.format(table=table,keys=keys,values=values)update=','.join(["{key}=%s".format(key=key)forkeyindata])sql+=updatetry:ifcursor.execute复制代码(sql,tuple(data.values())*2):print('成功')db.commit()except:print('失败')db.rollback()db.close()这里构造的SQL语句是实际上是一个插入语句，但是我们后来添加了ONDUPLICATEKEYUPDATE。这行代码的意思是如果主键已经存在，则进行更新操作。比如我们传入的数据id还是20120001，但是age从20变成了21，这时候不会插入这个数据，而是直接更新id为20120001的数据。完整的SQL是这样构造的：INSERTINTOstudents(id,name,age)VALUES(%s,%s,%s)ONDUPLICATEKEYUPDATEid=%s,name=%s,age=%shere它变成了6%s。所以后面的execute()方法的第二个参数元组需要乘以2，变成原来的两倍。这样我们就可以实现主键不存在则插入数据，存在则更新数据的功能。6、删除数据删除操作比较简单，直接使用DELETE语句即可，但是需要指定要删除的目标表名和删除条件，仍然需要使用db的commit()方法才能生效。例子如下：table='students'condition='age>20'sql='DELETEFROM{table}WHERE{condition}'.format(table=table,condition=condition)try:cursor.execute(sql)db.commit()except:db.rollback()db.close()因为删除条件多种多样，运算符有大于、小于、等于、LIKE等，条件连接符有AND、OR等，所以不要继续构造复杂的分析条件。这里直接将条件作为字符串传递，实现删除操作。7、查询数据说完了插入、修改、删除操作，还剩下一个很重要的操作，就是查询。查询会使用SELECT语句，例子如下：sql='SELECT*FROMstudentsWHEREage>=20'try:cursor.execute(sql)print('Count:',cursor.rowcount)one=cursor.fetchone()print('One:',one)results=cursor.fetchall()print('Results:',results)print('ResultsType:',type(results))forrowinresults:print(row)复制代码except:print('Error')结果如下：Count:4One:('20120001','Bob',25)Results:(('20120011','Mary',21),('20120012','Mike',20),('20120013','James',22))结果类型：
