#-*- coding: utf-8 -*-
importrequestsfrom bs4 importBeautifulSoupimportlxmlimportjsonimportreimporttimeimporttushare as tsimportpandas as pdimportpymysqlfrom sqlalchemy importcreate_engine
pymysql.install_as_MySQLdb()defTstockbasic(conn):#调用stock_basic，获取股票代码、股票名称、所属行业、上市日期、上市场所
cursor=conn.cursor()#读取数据库已存储数据
sql_stk_data = pd.read_sql("select ts_code from stk_basic_data",con=conn).values#创建空列表，存储ts_code
code_list =[]
incount= upcount = fincount = fupcount =0
stk_basic= pro.stock_basic(list_status=‘L‘, fields=‘ts_code,symbol,name,industry,list_date,exchange‘)for index, row instk_basic.iterrows():#new_code = row[‘ts_code‘][7:]+row[‘ts_code‘][:6]
new_code = row[‘ts_code‘]
stk_code= row[‘symbol‘]
stk_name= row[‘name‘]
industry= row[‘industry‘]
list_date= row[‘list_date‘]
exchange= row[‘exchange‘]#code_list.append(new_code)
code_list.append(new_code[7:]+new_code[:6])#if new_code in sql_stk_data.values:
if new_code insql_stk_data:
sql= "update stk_basic_data set industry = ‘%s‘ where ts_code = ‘%s‘" %(industry,new_code)
cursor.execute(sql)
upcount+= 1
print(upcount)‘‘‘try:
cursor.execute(sql)
upcount +=1
print(upcount)
except:
fupcount +=1‘‘‘
else:
sql= "insert into stk_basic_data(ts_code,stk_code,stk_name,industry,list_date,exchange) values(‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)"%(new_code,stk_code,stk_name,industry,list_date,exchange)
cursor.execute(sql)try:
cursor.execute(sql)
incount+=1
print(incount)except:
fincount+=1
print("股票列表成功更新：%d 条数据，更新失败：%d条数据" %(upcount, fupcount))print("股票列表成功新增：%d 条数据，新增失败：%d条数据" %(incount, fincount))
mit()
conn.close()returncode_listif __name__ == "__main__":#设置tushare社区token
ts.set_token(‘ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7‘)
pro=ts.pro_api()#创建数据库连接
conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123456‘, database=‘quantitative_trading_data‘, charset=‘utf8‘)
engine= create_engine(‘mysql://root:[emailprotected]/quantitative_trading_data?charset=utf8‘)#获取当前年月日，传递给各函数
current = time.strftime("%Y%m%d", time.localtime())#调用Tstockbasich获取股票列表，并完成数据更新
code_list = Tstockbasic(conn)
