初学Python,试着用requests + BeautifulSoup + MySQLdb抓取豆瓣图书TOP250的各类数据同时存入数据库。
1.目标
url:
豆瓣图书TOP250:https://book.douban.com/top250?start=0
一共有250个图书，每一页返回25条数据，start从0到最大225。
html:
图1.png
每一页的25条数据都在<td valign="top">里面，对应有图书名、出版社、评分(可选)、参评人数、一句话介绍等数据，目的就是把以上数据筛选出来存入MySQL中。
2.开始
先用命令创建一个MySQL数据库,一定要设置默认字符集为utf8,否则可能会导致无法插入中文数据:
MacBook-Pro:~ Tan$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE NEWDATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
douban.py用于网络请求和数据解析
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import requests
from bs4 import BeautifulSoup
import sys
import time
from dbManager import DBManager
reload(sys)
sys.setdefaultencoding( "utf-8" )
# 获取图书TOP250
class Douban():
"""docstring for Douban"""
def __init__(self):
self.baseurl = 'https://book.douban.com/top250?start='
self.agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36'
self.headers = {'User-Agent':self.agent}
self.page = 0
self.maxPage = 250 - 25
self.db = DBManager()
#拼接当前url
def getCurrentUrl(self):
url = self.baseurl + str(self.page)
print 'url:%s' % url
return url
#TOP250数据都在<td valign="top">里面
def has_valign_but_no_width(self,tag):
return tag.has_attr('valign') and not tag.has_attr('width')
#获取每一页的数据
def loadPage(self):
url = self.getCurrentUrl()
result = requests.get(url,headers=self.headers).content
soup = BeautifulSoup(result,'html.parser', from_encoding='utf-8')
content = soup.find_all(self.has_valign_but_no_width)
#遍历,解析有用的数据
for item in content:
title = item.a['title']
publishingHouse = item.p.string
price = publishingHouse.split('/')[-1]
ratingNums = item.find('span',class_ = 'rating_nums').string
ratingPeoples = item.find('span',class_ = 'pl').string[1:-1].strip()
inq = ''
if item.find('span',class_ = 'inq'):
inq = item.find('span',class_ = 'inq').string
print 'title:%s\npublishingHouse:%s\nprice:%s\nratingNums:%s\nratingPeoples:%s\ninq:%s\n' % (title,publishingHouse,price,ratingNums,ratingPeoples,inq)
#插入数据库
self.db.insert(title,publishingHouse,price,ratingNums,ratingPeoples,inq)
#抓取完一页 休息3s
time.sleep(3)
#抓取下一页
self.page += 25
if self.page <= self.maxPage:
self.loadPage()
else:
# 爬完就关闭数据库
self.db.closeDB()
if __name__ == '__main__':
douban = Douban()
douban.loadPage()
dbManager用于数据存储
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )
class DBManager():
"""docstring for DBManager"""
SQL_CREATE = '''CREATE TABLE IF NOT EXISTS DOUBANBOOK (id int unsigned not null AUTO_INCREMENT primary key,
title varchar(20) not null,
publishingHouse varchar(50),
price varchar(10),
ratingNums varchar(10),
ratingPeoples varchar(20),
inq varchar(20))'''
SQL_INSERT = '''INSERT INTO DOUBANBOOK (title,publishingHouse,price,ratingNums,ratingPeoples,inq) VALUES (%s,%s,%s,%s,%s,%s)'''
def __init__(self):
self.host = 'localhost'
self.dbName = '数据库名'
self.userName = 'root'
self.password = '密码'
self._db = MySQLdb.connect(host = self.host,
user = self.userName,
passwd = self.password,
db = self.dbName,
charset="utf8")
self._cursor = self._db.cursor()
self._execute(sql=self.SQL_CREATE)
def _execute(self,params=None,sql=''):
if params:
self._cursor.execute(sql,params)
else:
self._cursor.execute(sql)
def insert(self,title,publishingHouse,price,ratingNums,ratingPeoples,inq):
try:
self._execute(params=(title,publishingHouse,price,ratingNums,ratingPeoples,inq),sql=self.SQL_INSERT)
self._db.commit()
except Exception as e:
print 'db error %s' % e
self._db.rollback()
def closeDB(self):
self._db.close()
3.最后
运行douban.py:
url:https://book.douban.com/top250?start=0
title:追风筝的人
publishingHouse:[美] 卡勒德·胡赛尼 / 李继宏 / 上海人民出版社 / 2006-5 / 29.00元
price: 29.00元
ratingNums:8.9
ratingPeoples:283124人评价
inq:为你，千千万万遍
title:小王子
publishingHouse:[法] 圣埃克苏佩里 / 马振聘 / 人民文学出版社 / 2003-8 / 22.00元
price: 22.00元
ratingNums:9.0
ratingPeoples:225146人评价
inq:献给长成了大人的孩子们
title:围城
publishingHouse:钱锺书 / 人民文学出版社 / 1991-2 / 19.00
price: 19.00
ratingNums:8.9
ratingPeoples:188666人评价
inq:对于“人艰不拆”四个字最彻底的违抗
title:解忧杂货店
publishingHouse:[日] 东野圭吾 / 李盈春 / 南海出版公司 / 2014-5 / 39.50元
price: 39.50元
ratingNums:8.6
ratingPeoples:233024人评价
inq:一碗精心熬制的东野牌鸡汤，拒绝很难
图2.png
数据库结构:
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(20) | NO | | NULL | |
| publishingHouse | varchar(50) | YES | | NULL | |
| price | varchar(10) | YES | | NULL | |
| ratingNums | varchar(10) | YES | | NULL | |
| ratingPeoples | varchar(20) | YES | | NULL | |
| inq | varchar(20) | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
接下来，准备用这个数据库数据配合flask_restful做一个简单的api，返回给App使用。
