2023数据采集与融合技术实践作业4

作业1

要求：

▪ 熟练掌握 Selenium 查找 HTML 元素、爬取 Ajax 网页数据、等待 HTML 元素等内容。

▪使用 Selenium 框架+ MySQL 数据库存储技术路线爬取“沪深 A 股”、“上证 A 股”、“深证 A 股”3 个板块的股票数据信息。

o 候选网站：东方财富网：

http://quote.eastmoney.com/center/gridlist.html#hs_a_board

o 输出信息：MYSQL 数据库存储和输出格式如下，表头应是英文命名例如：序号id，股票代码：bStockNo……，由同学们自行定义设计表头：

• Gitee 文件夹链接

(1)代码

整体代码逻辑

spider=Spiders()
urls=["http://quote.eastmoney.com/center/gridlist.html#hs_a_board",
      "http://quote.eastmoney.com/center/gridlist.html#nav_sh_a_board",
      "http://quote.eastmoney.com/center/gridlist.html#nav_sz_a_board"]
for url in urls:
        spider.init(url)
        spider.processSpider()
        spider.closeUp()

创建一个 WebDriver 实例

        chrome_options = Options()
        chrome_options.add_argument('--headless')
        chrome_options.add_argument('--disable-gpu')
        chrome_options.add_argument('--no-sandbox')
        self.driver = webdriver.Chrome(options=chrome_options)

通过XPATH定位元素：

def processSpider(self):
    trs = self.driver.find_elements(By.XPATH,"//table[@id='table_wrapper-table']/tbody/tr")
                for tr in trs:
                    id = tr.find_element(By.XPATH,".//td[1]").text
                    StockNo = tr.find_element(By.XPATH,"./td[2]/a").text
                    StockName= tr.find_element(By.XPATH,"./td[3]/a").text
                    StockQuote = tr.find_element(By.XPATH,"./td[5]/span").text
                    Changerate = tr.find_element(By.XPATH,"./td[6]/span").text
                    Chg = tr.find_element(By.XPATH,"./td[7]/span").text
                    Volume = tr.find_element(By.XPATH,"./td[8]").text
                    Turnover = tr.find_element(By.XPATH,"./td[9]").text
                    StockAmplitude = tr.find_element(By.XPATH,"./td[10]").text
                    highest = tr.find_element(By.XPATH,"./td[11]/span").text
                    lowest = tr.find_element(By.XPATH,"./td[12]/span").text
                    Pricetoday = tr.find_element(By.XPATH,"./td[13]/span").text
                    PrevClose = tr.find_element(By.XPATH,"./td[14]").text

实现翻页

            if self.page_num < 3:
                self.page_num += 1
                nextPage = self.driver.find_element(By.XPATH,"//div[@class='dataTables_paginate paging_input']/a[2]")
                nextPage.click()
                time.sleep(4)
                #递归爬取
                self.processSpider()

数据库操作

    #初始化
    def init(self, url):
        # 爬取页数
        self.pagenum = 0  
        #板块的名称
        self.board=["沪深京A股票","上证A股","深证A股"] 
        self.driver.get(url)
        try:
            self.con = pymysql.connect(host="localhost", port=3306, user="root", passwd="....", db="spiders", charset="utf8")
            self.cursor = self.con.cursor(pymysql.cursors.DictCursor)
            for table_name in self.board:
                self.cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
                self.cursor.execute(f"CREATE TABLE {table_name}(id INT(4) PRIMARY KEY, StockNo VARCHAR(16), StockName VARCHAR(32), StockQuote VARCHAR(32), Changerate VARCHAR(32), Chg VARCHAR(32), Volume VARCHAR(32), Turnover VARCHAR(32), StockAmplitude VARCHAR(32), Highest VARCHAR(32), Lowest VARCHAR(32), Pricetoday VARCHAR(32), PrevClose VARCHAR(32))")
        except Exception as err :
            print(err)
#插入数据
    def insertDB(self,board,id,StockNo,StockName,StockQuote,Changerate,Chg,Volume,Turnover,StockAmplitude,Highest,Lowest,Pricetoday,PrevClose):
        try:
            sql = f"insert into {board}(id,StockNo,StockName,StockQuote,Changerate,Chg,Volume,Turnover,StockAmplitude,Highest,Lowest,Pricetoday,PrevClose) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            self.cursor.execute(sql,(id,StockNo,StockName,StockQuote,Changerate,Chg,Volume,Turnover,StockAmplitude,Highest,Lowest,Pricetoday,PrevClose))
        except Exception as err:
            print(err)
     #关闭
	def closeUp(self):
        try:
            self.con.commit()
            self.con.close()
            self.driver.close()
        except Exception as err:
            print(err)

查看数据库

(2)心得

1、google自动更新，导致原来下载的驱动器不能使用，于是下载对应版本的，再上网搜索取消谷歌浏览器自动更新

2、不同板块的url后缀不一样，于是可以修改后缀来实现不同板块的切换

3、爬取多页时要注意翻页的速度和爬取的页数，一般翻页时会设置休眠时间，来模拟人的翻页行为。

作业2

o 要求：

▪ 熟练掌握 Selenium 查找 HTML 元素、实现用户模拟登录、爬取 Ajax 网页数据、等待 HTML 元素等内容。

▪ 使用 Selenium 框架+MySQL 爬取中国 mooc 网课程资源信息（课程号、课程名称、学校名称、主讲教师、团队成员、参加人数、课程进度、课程简介）

o 候选网站：中国 mooc 网：https://www.icourse163.org

o 输出信息：MYSQL 数据库存储和输出格式

• Gitee 文件夹链接

(1)代码

先切换到登录界面所在的iframe中才可以输入账号密码

定位按键元素，输入账号密码信息实现自动登录

from selenium.webdriver.support import expected_conditions as EC
    #等待登录元素加载好再执行操作
    WebDriverWait(driver, 10, 0.5).until(
        EC.presence_of_element_located((By.XPATH, '//a[@class="f-f0 navLoginBtn"]'))).click()
    iframe = WebDriverWait(driver, 10, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@frameborder="0"]')))
    #切换到登录界面中
    driver.switch_to.frame(iframe)
    # 输入账号密码
    driver.find_element(By.XPATH, '//*[@id="phoneipt"]').send_keys("*****")
    time.sleep(3)
    driver.find_element(By.XPATH, '//*[@class="j-inputtext dlemail"]').send_keys("*****")
    time.sleep(3)
    #点击登录按钮
    driver.find_element(By.ID, 'submitBtn').click()

解析数据

courses = self.driver.find_elements_by_xpath("//div[@class='m-course-list']//div[@class='u-clist f-bgw f-cb f-pr j-href ga-click']")
for course in courses:
    name = course.find_element_by_xpath(".//div[@class='t1 f-f0 f-cb first-row']//a//span").text
    college = course.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']//a[@class='t21 f-fc9']").text
    teacher = course.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']//a[@class='f-fc9']").text
    teacher1 = teacher
    teachers= course.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0']//span[@class='f-fc9']").text
    team = teacher1 + teachers
    participants = course.find_element_by_xpath(".//div[@class='t2 f-fc3 f-nowrp f-f0 margin-top0']//span[@class='hot']").text
    url = course.find_element_by_xpath(".//div[@class='t1 f-f0 f-cb first-row']//a").get_attribute("href")
    time.sleep(3)

定位详情页数据

    #实例化另一个driver来爬取详情页
    chrome_options = Options()
    chrome_options.add_argument('--headless')
    chrome_options.add_argument('--disable-gpu')
    driver2 = webdriver.Chrome(chrome_options=chrome_options)
    driver2.get(url)
    time.sleep(2)

实现翻页，爬取三页

              if self.page_num < 3:
                self.page_num += 1
                nextPage = self.driver.find_element(By.XPATH,"//li[@class='ux-pager_btn ux-pager_btn__next']//a[@class='th-bk-main-gh']")
                nextPage.click()
                time.sleep(4)
                #递归爬取
                self.processSpider()

数据库操作

    #初始化
    def init(self, url):
            try:
                db = pymysql.connect(host='127.0.0.1', user='root', password='....', port=3306, database='spiders')
                cursor = db.cursor()
                cursor.execute('DROP TABLE IF EXISTS mooc')
                sql = '''CREATE TABLE mooc(cCourse varchar(64),cCollege varchar(64),cTeacher varchar(16),cTeam varchar(256),cCount varchar(16),
                cProcess varchar(32),cBrief varchar(2048))'''
                cursor.execute(sql)
            except Exception as e:
                print(e)
	#插入数据
    def insertDB(self,cCourse, cCollege, cTeacher, cTeam, cCount, cProcess, cBrief):
            try:
                cursor.execute('INSERT INTO mooc VALUES ("%s","%s","%s","%s","%s","%s","%s")' % (
                cCourse, cCollege, cTeacher, cTeam, cCount, cProcess, cBrief))
                db.commit()
            except Exception as e:
                print(e)
     #关闭
	def closeUp(self):
        try:
            self.con.commit()
            self.con.close()
            self.driver.close()
        except Exception as err:
            print(err)

查看数据库