用python从网页上抓取数据存入mysql数据库

import pymysql
db = pymysql.connect(host='localhost',
                     user='****',
                     password='******',
                     database='cr')
cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
table_sql = "DROp TABLE IF EXISTS Stock"  # 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute(table_sql)  # 使用预处理语句创建表
table_sql = "CREATE TABLE Stock(id bigint NOT NULL AUTO_INCREMENT," 
            "stock_Name CHAr(10)," 
            "A_code CHAr(20)," 
            "H_code CHAr(20)," 
            "A_price CHAr(20)," 
            "A_UpDown CHAr(20)," 
            "H_priceHkD CHAr(20)," 
            "H_priceRMB CHAr(20)," 
            "H_UpDown CHAr(20)," 
            "CompareHA CHAr(20)," 
            "A_Volume CHAr(20)," 
            "H_Volume CHAr(20)," 
            "primary KEY (id) USING BTREE) "
cursor.execute(table_sql)  # 执行建表与插入sql
db.commit()  # 提交到数据库执行
from selenium import webdriver
from time import sleep
#打开一个浏览器
browser = webdriver.Firefox()
#准备一个网址
url = 'https://vip.stock.finance.sina.com.cn/mkt/#hsgs_hgt_ah'
res = browser.get(url)
sleep(3)
page = 1
while page < 4:
    str1 = browser.find_element_by_css_selector("#tbl_wrap > table:nth-child(1) > tbody:nth-child(2)").text
sleep(3)
    filename = 'data.txt'  # 在当前路径下创建一个’data.txt’的文本文件
    with open(filename, "a") as f:  # 如果filename不存在会自动创建， 'w'表示写数据，写之前会清空文件中的原有数据！
        f.write(str1)
        f.write("n")
        sleep(3)
    if page == 3:
        break
    else:
        next_page = browser.find_element_by_css_selector("div.pages:nth-child(2) > a:nth-child(5)").click()
        sleep(3)
    page = page + 1
browser.quit()
fr = open('data.txt')
lines = fr.readlines()
for line in lines:
    if (len(line) <= 1):
        continue;
    data = line.strip().split(' ')
    list1 = [x.strip() for x in data if x.strip()]
    # print(list1)
    tuple1 = tuple(list1)
    insertSql = 'INSERT INTO Stock(stock_Name,A_code,H_code,A_price,A_UpDown,H_priceHkD,H_priceRMB,H_UpDown,CompareHA,A_Volume,H_Volume) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    db.ping(reconnect=True)
    cursor.execute(insertSql, tuple1)
    db.commit()
cursor.close()  # 关闭游标
db.close()