数据采集第五次作业
作业一:
要求：
熟练掌握 Selenium 查找HTML元素、爬取Ajax网页数据、等待HTML元素等内容。
使用Selenium框架爬取京东商城某类商品信息及图片。
候选网站：http://www.jd.com/
关键词：学生自由选择
输出信息：MYSQL的输出信息如下
1）作业结果
代码：
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
import urllib.request
import threading
import sqlite3
import os
import datetime
from selenium.webdriver.common.keys import Keys
import time
class JD:
header = {
"User-Agent": "Mozilla/5.0(Windows;U;Windows NT 6.0 x64;en-US;rv:1.9pre)Gecko/2008072531 Minefield/3.0.2pre"
}
imagepath = "image_gc"
def startUp(self, url, key):
chrome_options = Options()
chrome_options.add_argument("——headless")
chrome_options.add_argument("——disable-gpu")
self.driver = webdriver.Chrome(options=chrome_options)# 去掉‘chrome_’就行啦
self.threads = []
self.No = 0
self.imgNo = 0
try:
self.con = sqlite3.connect("gc2.db")
self.cursor = self.con.cursor()
try:
self.cursor.execute("drop table gc2")
except:
pass
try:
sql = "create table gc2(mNo varchar(32) primary key,mMark varchar(255),mPrice varchar(32),mNote varchar(1024),mFile varchar(255))"
self.cursor.execute(sql)
except:
pass
except Exception as err:
print(err)
try:
if not os.path.exists(JD.imagepath):
os.mkdir(JD.imagepath)
images = os.listdir(JD.imagepath)
for image in images:
s = os.path.join(JD.imagepath, image)
os.remove(s)
except Exception as err:
print(err)
self.driver.get(url)
keyinput = self.driver.find_element_by_id("key")
keyinput.send_keys(key)
keyinput.send_keys(Keys.ENTER)
def closeUp(self):
try:
self.con.commit()
self.con.close()
self.driver.close()
except Exception as err:
print(err)
def insertDB(self, mNo, mMark, mPrice, mNote, mFile):
try:
sql = "insert into gc2 (mNo,mMark,mPrice,mNote,mFile) values (?,?,?,?,?)"
self.cursor.execute(sql, (mNo, mMark, mPrice, mNote, mFile))
except Exception as err:
print(err)
def showDB(self):
try:
con = sqlite3.connect("gc2.db")
cursor = con.cursor()
print("%-8s%-16s%-8s%-16s%s" % ("No", "Mark", "Price", "Image", "Note"))
cursor.execute("select mNO,mMark,mPrice,mFile,mNote from gc2 order by mNo")
rows = cursor.fetchall()
for row in rows:
print("%-8s%-16s%-8s%-16s%s" % (row[0], row[1], row[2], row[3], row[4]))
con.close()
except Exception as err:
print(err)
def downloadDB(self, src1, src2, mFile):
data = None
if src1:
try:
req = urllib.request.Request(src1, headers=JD.header)
resp = urllib.request.urlopen(req, timeout=100)
data = resp.read()
except:
pass
if not data and src2:
try:
req = urllib.request.Request(src2, headers=JD.header)
resp = urllib.request.urlopen(req, timeout=100)
data = resp.read()
except:
pass
if data:
print("download begin!", mFile)
fobj = open(JD.imagepath + "\\" + mFile, "wb")
fobj.write(data)
fobj.close()
print("download finish!", mFile)
def processJD(self):
time.sleep(10)
try:
print(self.driver.current_url)
lis = self.driver.find_elements_by_xpath("//div[@id='J_goodsList']//li[@class='gl-item']")
time.sleep(1)
for li in lis:
time.sleep(1)
try:
src1 = li.find_element_by_xpath(".//div[@class='p-img']//a//img").get_attribute("src")
time.sleep(1)
except:
src1 = ""
try:
src2 = li.find_element_by_xpath(".//div[@class='p-img']//a//img").get_attribute("data-lazy-img")
time.sleep(1)
except:
src2 = ""
try:
price = li.find_element_by_xpath(".//div[@class='p-price']//i").text
time.sleep(1)
except:
price = "0"
note = li.find_element_by_xpath(".//div[@class='p-name p-name-type-2']//em").text
mark = note.split(" ")[0]
mark = mark.replace("爱心东东\n", "")
mark = mark.replace(",", "")
note = note.replace("爱心东东\n", "")
note = note.replace(",", "")
time.sleep(1)
self.No = self.No + 1
no = str(self.No)
while len(no) < 6:
no = "0" + no
print(no, mark, price)
if src1:
src1 = urllib.request.urljoin(self.driver.current_url, src1)
p = src1.rfind(".")
mFile = no + src1[p:]
elif src2:
src2 = urllib.request.urljoin(self.driver.current_url, src2)
p = src2.rfind(".")
mFile = no + src2[p:]
if src1 or src2:
T = threading.Thread(target=self.downloadDB, args=(src1, src2, mFile))
T.setDaemon(False)
T.start()
self.threads.append(T)
else:
mFile = ""
self.insertDB(no, mark, price, note, mFile)
try:
self.driver.find_element_by_xpath("//span[@class='p-num']//a[@class='pn-next disabled']")
except:
nextPage = self.driver.find_element_by_xpath("//span[@class='p-num']//a[@class='pn-next']")
time.sleep(10)
nextPage.click()
self.processJD()
except Exception as err:
print(err)
def executeJD(self, url,key):
starttime = datetime.datetime.now()
print("starting!")
self.startUp(url, key)
print("processing!")
self.processJD()
print("closing!")
self.closeUp()
for t in self.threads:
t.join()
print("complete!")
endtime = datetime.datetime.now()
elapsed = (endtime - starttime).seconds
print("Total", elapsed, "seconds elasped")
url = "https://www.jd.com"
spider = JD()
while True:
print("1.爬取")
print("2.显示")
print("3.退出")
s = input("请选择（1，2，3）；")
if s == "1":
JD().executeJD(url,'手机')
continue
elif s == "2":
JD().showDB()
continue
elif s == "3":
break
结果贴图
2）心得体会
复现老师的代码，终于用上了selenium，感觉想必scrapy确实很方便啊
而且那个感觉很像做前端的感觉
这个代码看懂了感觉就比较好继续下面两个任务了
webdriver.Chrome(chrome_options=chrome_options)是因为版本问题，所以会有警告，并不影响代码，但是看起来别扭
就是被sqlite折腾了
作业二：
要求：
熟练掌握 Selenium 查找HTML元素、爬取Ajax网页数据、等待HTML元素等内容。
使用Selenium框架+ MySQL数据库存储技术路线爬取“沪深A股”、“上证A股”、“深证A股”3个板块的股票数据信息。
候选网站：东方财富网：http://quote.eastmoney.com/center/gridlist.html#hs_a_board
输出信息：MYSQL数据库存储和输出格式如下，表头应是英文命名例如：序号id，股票代码：bStockNo……，由同学们自行定义设计表头：
1）作业结果
代码：
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
import datetime
import time
import pymysql
import pandas as pd
class CFW:
header = {
"User-Agent": "Mozilla/5.0(Windows;U;Windows NT 6.0 x64;en-US;rv:1.9pre)Gecko/2008072531 Minefield/3.0.2pre"
}
def startUp(self, url, num):
chrome_options = Options()
chrome_options.add_argument("——headless")
chrome_options.add_argument("——disable-gpu")
self.driver = webdriver.Chrome(options=chrome_options) # 去掉‘chrome_’就行啦
try:
self.con = pymysql.connect(host="localhost", port=3306, user="root", passwd='gh02120425', db="gc2", charset="utf8")
self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
# 如果不加num判定，最后只能输出最后一个表的结果
if num == 1:
self.cursor.execute("delete from CFW_1")
if num == 2:
self.cursor.execute("delete from CFW_2")
if num == 3:
self.cursor.execute("delete from CFW_3")
self.opened = True
except Exception as err:
print(err)
self.opened = False
self.driver.get(url)
def closeUp(self):
try:
self.con.commit()
self.con.close()
self.driver.close()
self.opened = False
except Exception as err:
print(err)
def showCFW(self, num):
try:
self.con = pymysql.connect(host="localhost", port=3306, user="root", passwd='gh02120425', db="gc2",
charset="utf8")
self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
self.opened = True
if num == 1:
sqlcmd = "select * from CFW_1"
# 利用pandas 模块导入mysql数据
a = pd.read_sql(sqlcmd, self.con)
print(a)
if num == 2:
sqlcmd = "select * from CFW_2"
# 利用pandas 模块导入mysql数据
a = pd.read_sql(sqlcmd, self.con)
print(a)
if num == 3:
sqlcmd = "select * from CFW_3"
# 利用pandas 模块导入mysql数据
a = pd.read_sql(sqlcmd, self.con)
print(a)
#直接用sql查询语句，感觉像是数据库作业
self.opened = False
except Exception as err:
print("出错，无法打印")
print(err)
def processCFW(self, num):
time.sleep(5)
try:
lis = self.driver.find_elements_by_xpath("//table[@id='table_wrapper-table']/tbody/tr")
for li in lis:
time.sleep(1)
# 具体xpath
gNo = li.find_element_by_xpath(".//td[1]").text
gNode = li.find_element_by_xpath(".//td[2]").text
gName = li.find_element_by_xpath(".//td[3]").text
gPrice = li.find_element_by_xpath(".//td[5]").text
gLimit = li.find_element_by_xpath(".//td[6]").text
gAmount = li.find_element_by_xpath(".//td[7]").text
gTurnover = li.find_element_by_xpath(".//td[8]").text
gTransaction = li.find_element_by_xpath(".//td[9]").text
gSwing = li.find_element_by_xpath(".//td[10]").text
gHigh = li.find_element_by_xpath(".//td[11]").text
gLow = li.find_element_by_xpath(".//td[12]").text
gToday = li.find_element_by_xpath(".//td[13]").text
gTomorrow = li.find_element_by_xpath(".//td[14]").text
gRatio = li.find_element_by_xpath(".//td[15]").text
gTurnoverRate = li.find_element_by_xpath(".//td[16]").text
gPE = li.find_element_by_xpath(".//td[17]").text
gPB = li.find_element_by_xpath(".//td[18]").text
print(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction,
gSwing, gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB)
#选择导入哪个数据库
if num == 1:
self.cursor.execute(
"insert into CFW_1(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction, gSwing, "
"gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB)"
"values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction,
gSwing, gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB))
if num == 2:
self.cursor.execute(
"insert into CFW_2(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction, gSwing, "
"gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB)"
"values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction,
gSwing, gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB))
if num == 3:
self.cursor.execute(
"insert into CFW_3(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction, gSwing, "
"gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB)"
"values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
(gNo, gNode, gName, gPrice, gLimit, gAmount, gTurnover, gTransaction,
gSwing, gHigh, gLow, gToday, gTomorrow, gRatio, gTurnoverRate, gPE, gPB))
except Exception as err:
print(err)
def executeCFW(self, url,num):
starttime = datetime.datetime.now()
print("starting!")
self.startUp(url, num)
print("processing!")
self.processCFW(num)
print("closing!")
self.closeUp()
print("complete!")
endtime = datetime.datetime.now()
elapsed = (endtime - starttime).seconds
print("Total", elapsed, "seconds elasped")
# 用了笨办法，就用num区分三种股票了
url_1 = "http://quote.eastmoney.com/center/gridlist.html#hs_a_board"
url_2 = "http://quote.eastmoney.com/center/gridlist.html#sh_a_board"
url_3 = "http://quote.eastmoney.com/center/gridlist.html#sz_a_board"
spider = CFW()
while True:
print("1.爬取")
print("2.显示")
print("3.退出")
s = input("请选择（1，2，3）；")
if s == "1":
print("沪深A股")
CFW().executeCFW(url_1, 1)
print("上证A股")
CFW().executeCFW(url_2, 2)
print("深证A股")
CFW().executeCFW(url_3, 3)
continue
elif s == "2":
CFW().showCFW(1)
CFW().showCFW(2)
CFW().showCFW(3)
continue
elif s == "3":
break
结果贴图
命令行打印以沪深A股为例：
数据库：
2）心得体会
第二题跟第一题差不多，就是要爬取三个种类的股票，用了笨办法，直接弄了三个，
有尝试过更简洁的办法，感觉思路不对，没有写出来
作业三:
要求：
熟练掌握 Selenium 查找HTML元素、实现用户模拟登录、爬取Ajax网页数据、等待HTML元素等内容。
使用Selenium框架+MySQL爬取中国mooc网课程资源信息（课程号、课程名称、学校名称、主讲教师、团队成员、参加人数、课程进度、课程简介）
候选网站：中国mooc网：https://www.icourse163.org
输出信息：MYSQL数据库存储和输出格式
1）作业结果
代码
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.keys import Keys
import pymysql
import pandas as pd
import time
import datetime
class MOOC:
header = {
"User-Agent": "Mozilla/5.0(Windows;U;Windows NT 6.0 x64;en-US;rv:1.9pre)Gecko/2008072531 Minefield/3.0.2pre"
}
def startUp(self, url, key):
chrome_options = Options()
chrome_options.add_argument("——headless")
chrome_options.add_argument("——disable-gpu")
self.driver = webdriver.Chrome(options=chrome_options)
try:
self.con = pymysql.connect(host="localhost", port=3306, user="root", passwd='gh02120425',
db="gc2", charset="utf8")
self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
self.cursor.execute("delete from MOOC")
self.opened = True
except Exception as err:
print(err)
self.opened = False
self.driver.get(url)
keyinput = self.driver.find_element_by_name("search")
keyinput.send_keys(key)
keyinput.send_keys(Keys.ENTER)
def closeUp(self):
try:
self.con.commit()
self.con.close()
self.driver.close()
self.opened = False
except Exception as err:
print(err)
def showMOOC(self):
try:
self.con = pymysql.connect(host="localhost", port=3306, user="root", passwd='gh02120425', db="gc2",
charset="utf8")
self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
self.opened = True
sqlcmd = "select * from MOOC"
a = pd.read_sql(sqlcmd, self.con)
print(a)
self.opened = False
except Exception as err:
print("出错，无法打印")
print(err)
def processMOOC(self):
time.sleep(5)
try:
lis = self.driver.find_elements_by_xpath("//div[@class='u-clist f-bgw f-cb f-pr j-href ga-click']")
gId = 0
for li in lis:
time.sleep(1)
gId = gId + 1
gCourse = li.find_element_by_xpath(".//div[@class='t1 f-f0 f-cb first-row']").text
gCollege = li.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']/a[@class='t21 f-fc9']").text
gTeacher = li.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']/a[@class='f-fc9']").text
try:
# 默认第一个教师为主讲教师
# 有一些教师团队只有主讲一人，所以主讲=教师团队处理
# mooc上教师之间用、分隔，就去掉好了
gTeam = li.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']/span[@class='f-fc9']").text
gTeam = "".join(gTeam.split('、'))
except:
gTeam = li.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']/a[@class='f-fc9']").text
gCount = li.find_element_by_xpath(".//span[@class='p5 brief f-ib f-f0 f-cb']").text
gProcess = li.find_element_by_xpath(".//span[@class='hot']").text
gBrief = li.find_element_by_xpath(".//span[@class='txt']").text
print(gId, gCourse, gCollege, gTeacher, gTeam, gCount, gProcess, gBrief)
self.cursor.execute(
"insert into MOOC(gId,gCourse,gCollege,gTeacher,gTeam,gCount,gProcess,gBrief)"
"values (%s, %s, %s, %s, %s, %s, %s, %s)",
(gId, gCourse, gCollege, gTeacher, gTeam, gCount, gProcess, gBrief))
except Exception as err:
print(err)
def executeMOOC(self, url, key):
starttime = datetime.datetime.now()
print("starting!")
self.startUp(url, key)
print("processing!")
self.processMOOC()
print("closing!")
self.closeUp()
print("complete!")
endtime = datetime.datetime.now()
elapsed = (endtime - starttime).seconds
print("Total", elapsed, "seconds elasped")
url = "https://www.icourse163.org/"
spider = MOOC()
while True:
print("1.爬取")
print("2.显示")
print("3.退出")
s = input("请选择（1，2，3）；")
if s == "1":
# 网页结构都一样，所以可以通过键盘输入关键词
key = input('输入关键字：')
MOOC().executeMOOC(url, key)
continue
elif s == "2":
MOOC().showMOOC()
continue
elif s == "3":
break
结果贴图
2）心得体会
爬取慕课课程信息，还是跟前两题大致相同
就是感觉mooc的网页信息好套娃啊hhhh
标签看起来也比较奇怪，有一些标签无法找到，还有其他一些，都是小问题
