《数据采集与预处理》课件7：静态网页爬取模块-数据存储pymysql

数据存储PyMySQLtxt文件存储Csv文件存储复习1、了解mysql数据库及PyMySQL的安装2、掌握使用PyMySQL步骤（获取链接、获取游标、执行SQL、事务提交、释放资源）3、理解pymysql操作表（增、删、改、查）过程4、通过案例掌握pymysql向表增加一条或多条数据教学目标1操作MySQL的表静态网页数据采集–数据存储PyMySQLPyMySQL安装及基本使用2案例讲解（豆瓣图形-新书快递）3PyMySQL安装关系型数据库是基于关系模式的数据库，而关系模式是通过二维表来保存的，它的存储方式就是行列组成的表，每一列是一个字段，每一行是一条记录。表可看做实体的集合，多个表组成一个数据库，即数据库。关系型数据库有多种，具体如下：SQLiteMySQLMysql的安装过程详细见超星平台OracleSQLServerDB2主要介绍Python3下MySQL的存储，Python3提供了PyMySQL库实现对MySQL数据库的操作。PyMySQL安装Python标准库不支持MySQL,只能通过一些开源库实现Python与MySQL的交互。在Python2中使用的是MySQLDB库，Python3中使用PyMysql库。PyMySQL是从Python连接到MySQL数据库服务器的接口。它实现了Python数据库APIv2.0，并包含一个纯Python的MySQL客户端库。PyMySQL的目标是成为MySQLdb的替代品。PyMySQL的安装过程，和其他第三库无本质区别。CMD命令行下输入如下代码。

安装完成后，可查看/PyMySQL/PyMySQL的官方文档快速入门。能够养成使用官方API文档快速学习的习惯。pipinstallpymysql-i/simplePyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象

connect获取游标对象

cursor执行SQL语句提交事务，关闭链接connect()对象支持的方法如下：cursor()对象支持的方法如下：方法：说明cursor()使用该连接创建并返回游标commit()提交当前事务rollback()回滚当前事务close()关闭连接方法：说明execute(op)执行一个数据库的查询命令fetchone()取得结果集的下一行fetchmany(size)获取结果集的下几行fetchall()获取结果集中的所有行rowcount()返回数据条数或影响行数close()关闭游标对象PyMySQL基本使用PyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象

数据库链接对象connect是Python和mysql数据库之间的桥梁和纽带，通过设置数据库的链接参数进行构造。使用pymysql模块中的connect函数来进行对象获取。

connect行数的使用方法为pymysql.connect(参数名=参数值)，常用的链接链接参数如下表所示。参数名类型说明Host字符串主机名或MySQL数据库服务器IPPort数字端口，MySQL数据库的默认端口为3306User字符串链接MySQL数据库的用户名Password字符串链接MySQL数据库的密码Db字符串链接的数据库的名称Charset字符串字符集，设置字符集主要是避免中文乱码cursorclass字符串设置默认的游标类型，默认为元祖类型PyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象

首先importpymysql导入模块，使用pymysql.connect获取到链接对象。参考代码如下。importpymysql#导入模块

connect=pymysql.connect(host="",#代表连接本地数据库user="root",#root为mysql的超级用户password="密码",#密码请使用实际密码替换db="spier",#连接的数据库名称charset="utf8")#设置字符集utf-8由于安装时没改变MySQL的默认端口3306，所以可以省略不写。运行代码，如果不报错，代表参数正确，链接成功。pymysql.connect(host=mysql_host,port=port,user=user,password=passwd,db=db,charset='')PyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象获取游标对象在链接对象中获取游标对象，为下一步执行SQL语句准备。SQL语句的执行是构建在游标对象基础上。核心语句为cursor=connect.cursor()。PyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象获取游标对象执行SQL语句获取到游标对象后，就可以使用cusor.execute(SQL语句)可以向连接的数据库中发送DDL、DML类型的SQL语句，如Insertinto、Update、Delete、select等。cursor=connect.cursor()#获取游标对象

sql=“select*fromtable”

cursor.execute(sql)#执行SQL

PyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象获取游标对象执行SQL语句小组讨论：mysql中创建教师表teacherinfo、然后进行增、删、改、查询的命令列名数据类型是否为空是否主键备注IdIntNotnullYes流水号，自增长TNochar(6)NotnullNo教师工号TNameVarchar(10)NotnullNo教师姓名RemarkVarchar(50)NullNo备注信息教师信息表TeacherInfoPyMySQL基本使用PyMySQL的使用包括以下四个步骤：创建数据库链接对象获取游标对象执行SQL语句提交事务,关闭链接PyMySQL默认是开启事务的，必须通过mit（）函数进行提交才能完成对数据库的操作。在提交事务的过程也有可能出现失败，如果失败使用connect.rollback()进行事务回滚。完成对数据库的操作后，需要使用cursor.close()、connect.close()来关闭游标以及数据库的链接，释放资源。结合以上四个步骤，使用PyMySQL向TeacherInfo插入一行数据“03070，周勇”。参考代码如下。1操作MySQL的表静态网页数据采集–数据存储PyMySQLPyMySQL安装及基本使用2案例讲解（豆瓣图形-新书快递）3操作MySQL的表1、

创建表创建python数据库后，可新建一个数据表students，该表的结果如下：创建students表，具体代码如下：在python数据库下已创建students表，具体如图所示：字段名：说明类型id学号varcharname姓名varcharage年龄intimportpymysqldb=pymysql.connect(host='lcoalhost',user='root',password='root',port=3306)cursor=db.cursor()sql='createtableifnotexistsstudents(idvarchar(255)notnull,namevarchar(255),ageintnotnull,primarykey(id))'cursor.execute(sql)db.close()操作MySQL的表2、插入数据数据库涉及事务问题，事务存在ACID特性，即原子性、一致性、隔离型和持久性，具体如表所示：插入、更新和删除操作都是对数据库进行更改的操作，更改操作都必须为一个事务，则更改操作标准的代码实现如下：属性解释原子性（atomicity）事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做一致性（consistency）事务必须使数据库从一个一致性状态变到另一个一致性状态。一致性与原子是密切相关的隔离性（isolation）一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰持久性（durability）持久性也称永久性（permaence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响try:cursor.execute(sql)mit()except:db.rollback()该实现方式便可保证数据的一致性，commit()和rollback()方法为事务提供了支持。操作MySQL的表2、插入数据向数据库python插入数据，具体代码如下：importpymysqlid='20120001'user='Bob'age=20db=pymysql.connect(host='lcoalhost',user='root',password='root',port=3306)cursor=db.cursor()sql='insertintostuendts(id,name,age)values(%s,%s,%s)‘#使用占位符%s来传递参数，形成完整的SQL语句try:cursor.execute(sql,(id,user,age))mit()except:db.rollback()db.close()操作MySQL的表2、插入数据上面数据插入的操作是通过构造SQL语句实现的，若表新增字段，比如gender性别字段，SQL语句需要修改：insertintostuendts(id,name,age,gender)values(%s,%s,%s,%s)相应的元祖参数需要改成：(id,name,age,gender)这种方式极不方便，可传入一个动态变化的字典，插入方式无需改的就可新增字段，比如构造如下字典：(id,name,age,gender){'id':'20120001','name':'Bob','age':20}操作MySQL的表2、插入数据使用动态字典插入数据，具体的代码如下：importpymysqldata={'id':'20120002','name':'Andy','age':20}table='students'keys=','.join(data.keys())values=','.join(['%s']*len(data))db=pymysql.connect(host='lcoalhost',user='root',password='root',port=3306)cursor=db.cursor()sql='insertinto{table}({keys})values({values})'.format(table=table,keys=keys,values=values)try:ifcursor.execute(sql,tuple(data.values())):print('Successful')mit()except:print('Failed')db.rollback()db.close()操作MySQL的表3、更新数据数据更新操作也是执行SQL语句，简单方式就是构造一个SQL语句，具体代码如下：同理插入数据，数据更新操作也可通过字典传值方式灵活实现importpymysqldb=pymysql.connect(host='lcoalhost',user='root',password='root',port=3306)cursor=db.cursor()sql='updatestudentssetage=%swherename=%s'try:cursor.execute(sql,(23,'Bob'))mit()except:db.rollback()db.close()操作MySQL的表4、删除数据删除操作相对简单，直接使用DELETE语句即可，具体代码如下：importpymysqltable='students'condition='age>22'db=pymysql.connect(host='lcoalhost',user='root',password='root',port=3306)cursor=db.cursor()sql='deletefrom{table}where{condition}'.format(table=table,condition=condition)try:cursor.execute(sql)mit()except:db.rollback()db.close()查询数据也是非常重要的操作，会用到SELECT语句，具体代码如下：importpymysqldb=pymysql.connect(host='lcoalhost',user='root',password='root',port=3306)cursor=db.cursor()sql='select*fromstudentswhereage>=20'try:cursor.execute(sql)print('Count:',cursor.rowcount)row=cursor.fetchone()whilerow:print('Row:',row)row=cursor.fetchone()except:print('Error')操作MySQL的表5、查询数据查询数据，具体结果如图所示：也可以考虑用fetchall()函数获得集合，然后用forin语句取出每条记录1操作MySQL的表静态网页数据采集–数据存储PyMySQLPyMySQL安装及基本使用2案例讲解（豆瓣图形-新书快递）3案例讲解（豆瓣图书-新书速递）案例描述爬取豆瓣网新书速递信息，数据保存在MySQL数据库中，表结构自行定义。爬取信息包括书名、评分、作者、出版社、出版时间、图书介绍。豆瓣图书新书速递的网址为/latest?icn=index-latestbook-all。目标页面如图所示。案例讲解（豆瓣图书-新书速递）案例分析豆瓣图书-新书速递案例主要练习将爬取数据存储到MySQL数据库中。页面结构相对简单，只有一页数据，不需要考虑分页，但是从呈现效果看，作者、出版社和出版时间是连接在一起的，需要考虑数据的拆分问题。我们首先从页面结构分析开始。案例的HTML结构如图所示。布局采用分栏设计，虚构类和非虚构类图书包裹在不同的div中。内部的书籍信息呈现采用ul和li布局。在编写XPath表达式时需要考虑兼顾虚构类和非虚构类图书的选取。数据包裹在id为content的div中，使用属性进行定位，初步确定XPath表达式为“//div[@id='content']//li”。XPath表达式的正确与否要使用浏览器的开发者工具进行验证。在Element选项卡中，按住“CTRL+F”快捷键，输入表达式，刚好选取40行数据，与页面中的图书的数量一致。读者也可点击上下箭头，观察每行选取的数据。案例讲解（豆瓣图书-新书速递）案例实现过程1、爬取豆瓣图书-新书速递页面2、在mysql数据库中直接创建bookinfor表（2和3可以交换顺序）3、利用xpath规则解析需要的字段4、利用pymysql库构建将爬取的图书信息存储到mysql的bookinfor表案例讲解（豆瓣图书-新书速递）案例实现过程1、爬取豆瓣图书-新书速递页面（通用爬虫框架实现，这里省略）2、在mysql数据库中直接创建bookinfor表列名类型是否为空是否为主键备注IdIntNoYes自增长