python 爬取所有股票代码并存入sqlite数据库

 

import QH_东方财富爬虫对象 as QH_DongFang
import qh_sqlite_ORM as QH_DB
import csv
import datetime

curr_time = datetime.datetime.now()     #获取当前时间
time_str1 = datetime.datetime.strftime(curr_time,'%Y-%m-%d %H:%M:%S')
time_str = datetime.datetime.strftime(curr_time,'%Y%m%d')    #转为str格式   yyyymmdd

# def QH_Add_ID_Date(qh_data):
#     """
#     添加ID和时间
#     :return:
#     """
#     qh_relut_list = []
#     qh_relut_one_list = []
#     for qh_row in qh_ss0:
#         qh_relut_list_row = []
#         qh_relut_one_list_row = []
#         qh_row[25] = ""
#         qh_id = """{}_{}""".format(time_str, qh_row[24])
#         qh_relut_list_row.append(qh_id)
#         qh_relut_list_row = qh_relut_list_row + qh_row
#         qh_id = qh_row[24]
#         qh_relut_one_list_row.append(qh_id)
#         qh_relut_one_list_row = qh_relut_one_list_row + qh_row
#         print(qh_id)
#         qh_relut_list.append(qh_relut_list_row)
#         qh_relut_one_list.append(qh_relut_one_list_row)


def qh_read_csv(qh_name_flth):
    with open(qh_name_flth,"r",encoding="gbk") as qh_csv_filed:
        qh_csv_read = csv.reader(qh_csv_filed)
        qh_csv_read_list = []
        for qh_row in qh_csv_read:
            qh_csv_read_list.append(qh_row)
            # print(qh_csv_read_list)
    return qh_csv_read_list

# http://push2.eastmoney.com/api/qt/kamt/get?fields1=f1,f2,f3,f4&fields2=f51,f52,f53,f54&ut=b2884a393a59ad64002292a3e90d46a5&cb=jQuery11240680496494524653_1620227591939&_=1620227591946
# http://91.push2.eastmoney.com/api/qt/clist/get?cb=jQuery11240680496494524653_1620227591937&pn=2&pz=20&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&fid=f3&fs=m:0+t:7,m:1+t:3&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152&_=1620227591945
qh_DongFang_0 = QH_DongFang.QH_TenXun_Spider()
qh_df_JieGuo_a = qh_DongFang_0.QH_DongFang_Main()
qh_DongFang_0.QH_XiuGai_CanShu(qh_CanShu="m:0+t:7,m:1+t:3")
qh_df_JieGuo_b = qh_DongFang_0.QH_DongFang_Main()
qh_df_JieGuo = qh_df_JieGuo_a + qh_df_JieGuo_b
print(qh_df_JieGuo)


qh_relut_list = []
qh_relut_one_list = []
for qh_row in qh_df_JieGuo:
    qh_relut_list_row = []
    qh_relut_one_list_row = []

    qh_id = """{}_{}{}""".format(time_str,qh_row[11],qh_row[10])
    qh_relut_list_row.append(qh_id)
    curr_time = datetime.datetime.now()  # 获取当前时间
    time_str1 = datetime.datetime.strftime(curr_time, '%Y-%m-%d %H:%M:%S')
    qh_relut_list_row.append(time_str1)
    qh_relut_list_row = qh_relut_list_row + qh_row
    # print(qh_relut_list_row)

    qh_id = """{}{}""".format(qh_row[11],qh_row[10])
    qh_relut_one_list_row.append(qh_id)
    curr_time = datetime.datetime.now()  # 获取当前时间
    time_str1 = datetime.datetime.strftime(curr_time, '%Y-%m-%d %H:%M:%S')
    qh_relut_one_list_row.append(time_str1)
    qh_relut_one_list_row = qh_relut_one_list_row + qh_row

    qh_relut_list.append(qh_relut_list_row)
    qh_relut_one_list.append(qh_relut_one_list_row)

print(qh_relut_list)
print(qh_relut_one_list)

#存入分类沪深数据库唯一
qh_db1 = "qh_gupiao_stock"
qh_foled_csv = "qh_DongFang_HuShen.csv"
qh_set_csv = "qh_DongFang_HuShen_set.csv"
qh_table1 = "qh_DongFang_List_table"
qh_table2 = "qh_DongFang_List_one_table"
#获取标题
qh_filed_name = []
qh_filed_name = qh_read_csv(qh_foled_csv)[0]
print(qh_filed_name)
#获取表配置
qh_filed_name_set = []
qh_filed_name_set = qh_read_csv(qh_set_csv)
print(qh_filed_name_set)

#创建数据库
qh_db_c = QH_DB.QH_SQLite_DB_Tool(qh_db_name = "qh_gupiao_stock")

#创建表
qh_db_c.qh_add_table(qh_table1,qh_filed_name_set)
qh_db_c.qh_add_data(qh_table1,qh_filed_name,qh_relut_list)

#创建表
qh_db_c.qh_add_table(qh_table2,qh_filed_name_set)
qh_db_c.qh_add_data(qh_table2,qh_filed_name,qh_relut_one_list)