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

import time
import pymysql
from selenium.webdriver.common.by import By
from selenium import webdriver

def start_spider():
    # 数据爬取函数
    global items
    time.sleep(3)
    trs = driver.find_elements(By.XPATH,'//tbody/tr')
    for tr in trs:
        NO = tr.find_element(By.XPATH,'./td[1]').text   #序号
        code = tr.find_element(By.XPATH,'./td[2]').text   #股票代码
        title = tr.find_element(By.XPATH, './td[3]').text  #名称
        rec_price = tr.find_element(By.XPATH,'./td[5]').text    #最新报价
        updown_percent = tr.find_element(By.XPATH,'./td[6]').text    #涨跌幅
        updown_sum = tr.find_element(By.XPATH,'./td[7]').text  # 涨跌额
        deal_amount = tr.find_element(By.XPATH,'./td[8]').text  # 成交量
        deal_sum = tr.find_element(By.XPATH,'./td[9]').text  # 成交额
        swing = tr.find_element(By.XPATH,'./td[10]').text  # 振幅
        max = tr.find_element(By.XPATH,'./td[11]').text  # 最高
        min = tr.find_element(By.XPATH,'./td[12]').text  # 最低
        today = tr.find_element(By.XPATH,'./td[13]').text  # 今开
        yesterday = tr.find_element(By.XPATH,'./td[14]').text  # 昨放
        items.append([NO,code,title,rec_price,updown_percent,updown_sum,deal_amount,deal_sum,swing,max,min,today,yesterday])


def save_data(items):
    # 将数据保存到数据库当中
    mydb = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="123456",
        database="spider",
        charset='utf8'
    )
    cursor = mydb.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS Stock
                                      (NO VARCHAR(256),
                                      code VARCHAR(256),
                                      title VARCHAR(256),
                                      rec_price VARCHAR(256),
                                      updown_percent VARCHAR(256),
                                      updown_sum VARCHAR(256),
                                      deal_amount VARCHAR(256),
                                      deal_sum VARCHAR(256),
                                      swing VARCHAR(256),
                                      max VARCHAR(256),
                                      min VARCHAR(256),
                                      today VARCHAR(256),
                                      yesterday VARCHAR(256)
                                       )''')
    mydb.commit()
    for item in items:
        sql = "insert into Stock values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, (item[0],item[1],item[2],item[3],item[4],item[5],item[6],item[7],item[8],item[9],item[10],item[11],item[12]))
        mydb.commit()
    mydb.close()
    print("以上数据已保存到数据库！")


# 实现不同页面跳转
details = {
    "沪深A股": "#hs_a_board",
    "上证A股": "#sh_a_board",
    "深证A股": "#sz_a_board"
}
driver = webdriver.Chrome()
for key in details.keys():
    url = "http://quote.eastmoney.com/center/gridlist.html" + details[key]
    print(key+ ":" +url)
    driver.get(url)
    driver.implicitly_wait(3)
    items = []
    start_spider()
    for item in items:
        print(item)
    save_data(items)
driver.quit()

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
import time
import random
import pymysql

def start_spider():
    # 请求url
    driver.get(url)
    WebDriverWait(driver, 1000).until(
        EC.presence_of_all_elements_located(
            (By.ID, "j-courseCardListBox")
        )
    )
    # 将滚动条拉到最下面的位置，因为往下拉才能将这一页的信息全部加载出来
    driver.execute_script('document.documentElement.scrollTop=10000')
    # 随机延迟,等下元素全部刷新
    time.sleep(random.randint(3, 6))
    driver.execute_script('document.documentElement.scrollTop=0')

    id = 0
    for link in driver.find_elements(By.XPATH,'//div[@class="u-clist f-bgw f-cb f-pr j-href ga-click"]'):


        id += 1  # 课程号
        course_name = link.find_element(By.XPATH,'.//span[@class=" u-course-name f-thide"]').text   # 课程名称
        print("课程名称", course_name)
        school_name = link.find_element(By.XPATH,'.//a[@class="t21 f-fc9"]').text   # 学校名称
        print("学校名称", school_name)
        m_teacher = link.find_element(By.XPATH,'.//a[@class="f-fc9"]').text # 主讲教师
        print("主讲教师：", m_teacher)
        try:
            team_member = link.find_element(By.XPATH,'.//span[@class="f-fc9"]').text    # 团队成员
        except Exception as err:
            team_member = '无'
        print("团队：",team_member)
        join = link.find_element(By.XPATH,'.//span[@class="hot"]').text # 参加人数
        join.replace('参加', '').replace('、','').strip()
        print("参加人数", join)
        process = link.find_element(By.XPATH,'.//span[@class="txt"]').text  # 课程进度
        print("课程进度：", process)
        brief = link.find_element(By.XPATH,'.//span[@class="p5 brief f-ib f-f0 f-cb"]').text # 课程简介
        print(brief)
        print(f"课程号：{id}\t课程名称：{course_name}\t学校名称：{school_name}\t主讲教师：{m_teacher}\t团队：{team_member}\t参加人数：{join}\t课程进度：{process}")
        print(f"课程简介：{brief}",end="\n")
        conn = pymysql.connect(host="127.0.0.1",
            port=3306,
            user="root",
            password="123456",
            database="spider",
            charset='utf8')
        # 获取游标
        cursor = conn.cursor()
        # 插入数据，注意看有变量的时候格式
        try:
            cursor.execute(
                "INSERT INTO mooc (`id`,`cCourse`,`cCollege`,`cTeacher`,`cTeam`,`cCount`,`cProcess`,`cBrief`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",
                (str(id), course_name, school_name, m_teacher, team_member, join, process, brief))
        except Exception as err:
            pass
        conn.commit()
        conn.close()


driver = webdriver.Chrome()
url = "https://www.icourse163.org/search.htm?search=%E5%A4%A7%E6%95%B0%E6%8D%AE#/"
# 声明一个list，存储dict
data_list = []
start_spider()
driver.quit()