import pandas as pd
from selenium import webdriver
from selenium.webdriver.common.by import By
from scrapy.selector import Selector
import time
from sqlalchemy import create_engine

print("你想要爬取的页码：")
want_page = input()
print("你想要爬的板块")
want_bank = input()
browser = webdriver.Chrome()
url = 'http://quote.eastmoney.com/center/gridlist.html#hs_a_board'
browser.get(url)
time.sleep(2)
if want_bank == "上证A股":
    browser.find_element(By.XPATH, '//*[@id="nav_sh_a_board"]').click()
    time.sleep(3)
if want_bank == "深证A股":
    browser.find_element(By.XPATH, '//*[@id="nav_sz_a_board"]').click()
    time.sleep(3)

# 滑倒底部
js_bottom = 'document.documentElement.scrollTop=1000000'
browser.execute_script(js_bottom)
time.sleep(2)

# 页面
page = browser.find_element(By.CLASS_NAME, 'paginate_input')
page.clear()
page.send_keys(want_page)
time.sleep(2)

# 确定
que_ding = browser.find_element(By.CLASS_NAME, 'paginte_go')
que_ding.click()
time.sleep(5)

content = browser.page_source
print(content)
# 退出
browser.quit()
selector = Selector(text=content)
rows = selector.xpath("//div[@id='table_wrapper']//tbody//tr")

data = []
# 遍历每个<tr>元素
for row in rows:
    lis = []
    f1 = row.xpath(".//td[1]//text()").extract_first()
    f2 = row.xpath(".//td[2]//text()").extract_first()
    f3 = row.xpath(".//td[3]//text()").extract_first()
    f4 = row.xpath(".//td[5]//text()").extract_first()
    f5 = row.xpath(".//td[6]//text()").extract_first()
    f6 = row.xpath(".//td[7]//text()").extract_first()
    f7 = row.xpath(".//td[8]//text()").extract_first()
    f8 = row.xpath(".//td[9]//text()").extract_first()
    f9 = row.xpath(".//td[10]//text()").extract_first()
    f10 = row.xpath(".//td[11]//text()").extract_first()
    f11 = row.xpath(".//td[12]//text()").extract_first()
    f12 = row.xpath(".//td[13]//text()").extract_first()
    f13 = row.xpath(".//td[14]//text()").extract_first()
    lis.append(f1)
    lis.append(f2)
    lis.append(f3)
    lis.append(f4)
    lis.append(f5)
    lis.append(f6)
    lis.append(f7)
    lis.append(f8)
    lis.append(f9)
    lis.append(f10)
    lis.append(f11)
    lis.append(f12)
    lis.append(f13)
    data.append(lis)
df = pd.DataFrame(data=data,
                  columns=['序号', '股票代码', '股票名称', '最新报价', '涨跌幅', '涨跌额', '成交量', '成交额',
                           '振幅', '最高', '最低', '今开', '昨收'])
print(df)
engine = create_engine("mysql+mysqlconnector://root:021022@127.0.0.1:3306/mybatis")
df.to_sql("sstock", engine, if_exists="replace", index=False)