数据采集与融合技术实践四

第四次作业

一、作业内容

作业①:

要求：

熟练掌握 Selenium 查找HTML元素、爬取Ajax网页数据、等待HTML元素等内容。
使用Selenium框架+ MySQL数据库存储技术路线爬取“沪深A股”、“上证A股”、“深证A股”3个板块的股票数据信息。
候选网站：东方财富网：http://quote.eastmoney.com/center/gridlist.html#hs_a_board

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

Gitee文件夹链接
Git文件夹

序号股票代码股票名称最新报价涨跌幅涨跌额成交量成交额振幅最高最低今开昨收
1 688093 N世华 28.47 62.22% 10.92 26.13万 7.6亿 22.34 32.0 28.08 30.2 17.55
2......

点击查看代码

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
import requests
import threading
import time
import urllib

import pymysql
chrome_options = Options()
chrome_options.add_argument('--headless')  #设置启动时不可见
chrome_options.add_argument('--disable-gpu')  #设置不使用GPU加速
  #使用此方法访问页面
# locator = (By.XPATH, '//*[@id="key_S"]')
# WebDriverWait(driver, 10,0.5).until(EC.presence_of_element_located(locator))  #等待输入框加载完成
# key = driver.find_element(By.XPATH,'//*[@id="key_S"]').send_keys("书包")   #找到输入框，并输入书包
# Button = driver.find_element(By.XPATH,'//input[@class="button"]').click() #寻找搜索按钮并点击
count=0 #这个变量用来控制爬取的数目
Name = []  #存储商品的名称
Price = []  #存储商品的价格
Url = []   #存储图片的URL

def gerDriver(i):
    key = ["hs", "sh", "sz"]
    keyword = urllib.parse.quote(key[i])
    driver = webdriver.Chrome(options=chrome_options)  # 创建Chrome浏览器
    url = f"http://quote.eastmoney.com/center/gridlist.html#{keyword}_a_board"
    driver.get(url)
    return driver

def getUrl(j):
    driver = gerDriver(j)
    global count
    for i in range(70):
        js = 'window.scrollTo(0,%s)' % (i * 100)  # js脚本
        driver.execute_script(js)
    lists = driver.find_element(By.XPATH,"//tbody")
    list = lists.find_elements(By.XPATH,"./tr")

    # print(list)
    print("序号\t股票代码\t股票名称\t最新报价\t涨跌幅\t涨跌额\t成交量\t成交额\t振幅\t\t最高\t\t最低\t\t今开\t\t昨收")
    for i in list:
        if(count > 114):
            return
        column = i.find_elements(By.XPATH,"td")
        no  = column[0].text
        code = column[1].text
        name = column[2].text
        lastprice = column[4].text
        upf = column[5].text
        upe = column[6].text
        okl = column[7].text
        oke = column[8].text
        af = column[9].text
        high = column[10].text
        low = column[11].text
        today = column[12].text
        yesterday = column[13].text
        print(no+ "\t" + code + "\t" + name + "\t" + lastprice+ "\t" + upf+ "\t" + upe + "\t" + okl + "\t" + oke+ "\t",end ='')
        print(af + "\t" +high+ "\t" + low + "\t" + today + "\t" + yesterday)

        conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="admin123", db="my_db_01",
                               charset="utf8")
        # 获取游标
        cursor = conn.cursor()
        # 插入数据，注意看有变量的时候格式
        try:
            cursor.execute(
                "INSERT INTO stock(`序号`,`股票代码`,`股票名称`,`最新报价`,`涨跌幅`,`涨跌额`,`成交量`,`成交额`,`振幅`,`最高`,`最低`,`今开`,`昨收`) "
                                "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                (no,code,name,lastprice,upf,upe,okl,oke,af,high,low,today,yesterday))
            # 提交
        except Exception as err:
            print("error is ")
            print(err)
            # 关闭连接
        conn.commit()
        conn.close()



        # name = i.find_element(By.XPATH,'./td[@class="mywidth"]/a').text
        # price = i.find_element(By.XPATH,'./p[@class="price"]/span').text

        # print(name)
        # Name.append(name)
        # Price.append(price)
for i in range(0,3):
    getUrl(i)
    # if(i < 3):
    #     next = driver.find_element(By.XPATH,'//a[@class="next paginate_button"]')
    #     next.click()
# for i in range(0,len(Name)):
#     print(Name[i] + Price[i])

码云链接

结果截图

心得体会

这道题目就相对而言比较简单，和上次的实验没有很大的出路，基本一个套路就可以解决

作业②:

要求：

熟练掌握 Selenium 查找HTML元素、实现用户模拟登录、爬取Ajax网页数据、等待HTML元素等内容。
使用Selenium框架+MySQL爬取中国mooc网课程资源信息（课程号、课程名称、学校名称、主讲教师、团队成员、参加人数、课程进度、课程简介）
候选网站：中国mooc网：https://www.icourse163.org

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

Gitee文件夹链接
Git文件夹

Id cCourse cCollege cTeacher cTeam cCount cProcess cBrief
1 Python数据分析与展示北京理工大学嵩天嵩天 470 2020年11月17日 ~ 2020年12月29日 “我们正步入一个数据或许比软件更重要的新时代。——Tim O'Reilly” ……
2......

点击查看代码

from selenium import webdriver
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
import time
import random
import pymysql

# 声明一个谷歌驱动器，并设置不加载图片，间接加快访问速度
options = webdriver.ChromeOptions()
options.add_experimental_option('prefs', {'profile.managed_default_content_settings.images': 2})
browser = webdriver.Chrome(options=options)
url = "https://www.icourse163.org/search.htm?search=%E5%A4%A7%E6%95%B0%E6%8D%AE#/"
# 声明一个list，存储dict
data_list = []

# # QQ登录过程
# going1 = browser.find_element(By.XPATH,'//a[@class="f-f navLoginBbtn"]').click()
# going2 = browser.find_element(By.XPATH,'//span[@class="f-ib icon last-login-holder"]').click()
# going3 = browser.find_element(By.XPATH,'//a[@class="link"]').click()
#
# user = browser.find_element(By.XPATH,'//input[@type="text"]')
# password = browser.find_element(By.XPATH,'//input[@type="password"]')
# user.send_keys('账号')
# password.send_keys('密码')
# submit = browser.find_element(By.XPATH,'//input[@submit]')

#慕课使用手机账号密码登录



def start_spider():
    # 请求url
    browser.get(url)
    going = browser.find_element(By.XPATH,'//a[@class="f-f0 navLoginBtn"]').click()
    time.sleep(3)
    temp_iframe_id = browser.find_element(By.XPATH, "//div[@class='ux-login-set-container']//iframe")
    browser.switch_to.frame(temp_iframe_id)
    account = browser.find_element(By.XPATH,'//input[@type="tel"]')
    password = browser.find_element(By.XPATH,'//input[@class="j-inputtext dlemail"]')
    account.send_keys("your telephone")
    password.send_keys("your password")
    into = browser.find_element(By.XPATH,'//a[@id="submitBtn"]').click()
    # 显示等待商品信息加载完成
    WebDriverWait(browser, 1000).until(
        EC.presence_of_all_elements_located(
            (By.ID, "j-courseCardListBox")
        )
    )
    # 将滚动条拉到最下面的位置，因为往下拉才能将这一页的商品信息全部加载出来
    browser.execute_script('document.documentElement.scrollTop=10000')
    # 随机延迟,等下元素全部刷新
    time.sleep(random.randint(3, 6))
    browser.execute_script('document.documentElement.scrollTop=0')

    # 开始提取信息,找到ul标签下的全部li标签
    count = 0
    for link in browser.find_elements(By.XPATH,'//div[@class="u-clist f-bgw f-cb f-pr j-href ga-click"]'):
        count += 1
        # 课程号
        #  course_id = link.find_element_by_xpath('.//div[@class="p-name"]//em').text
        # 课程名称
        course_name = link.find_element(By.XPATH,'.//span[@class=" u-course-name f-thide"]').text
        print("course name ", course_name)
        school_name = link.find_element(By.XPATH,'.//a[@class="t21 f-fc9"]').text
        print("school ", school_name)
        # 主讲教师
        m_teacher = link.find_element(By.XPATH,'.//a[@class="f-fc9"]').text
        print("laoshi：", m_teacher)
        # 团队成员
        try:
            team_member = link.find_element(By.XPATH,'.//span[@class="f-fc9"]').text
        except Exception as err:
            team_member = 'none'
        # print("团队：",team_member)
        # 参加人数
        join = link.find_element(By.XPATH,'.//span[@class="hot"]').text
        join.replace('参加', '')
        print("参加人数", join)
        # 课程进度
        process = link.find_element(By.XPATH,'.//span[@class="txt"]').text
        print('jingdu ', process)
        # 课程简介
        introduction = link.find_element(By.XPATH,'.//span[@class="p5 brief f-ib f-f0 f-cb"]').text
        print(introduction)

        conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="admin123", db="my_db_01", charset="utf8")
        # 获取游标
        cursor = conn.cursor()
        # 插入数据，注意看有变量的时候格式
        try:
            cursor.execute(
                "INSERT INTO mooc (`id`,`course`,`college`,`teacher`,`team`,`count`,`process`,`brief`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",
                (str(count), course_name, school_name, m_teacher, team_member, join, process, introduction))
            # 提交
        except Exception as err:
            print("error is ")
            print(err)
            # 关闭连接
        conn.commit()
        conn.close()

        # 遍历


def main():
    start_spider()


if __name__ == '__main__':
    main()
    # 退出浏览器
    browser.quit()

码云链接