tushare全量下载股票行情数据与定时更新

本文链接：https://blog.csdn.net/weixin_44178352/article/details/119885760

该博客展示了如何使用Python的tushare库从Tushare Pro API获取全量股票行情数据并存储到SQL Server数据库中，以及如何定时更新最新交易日的数据。主要涉及数据处理、日期操作和SQL数据库交互。

摘要由CSDN通过智能技术生成

#-------------------------------------------------------------------------------
# Name: 全量下载股票行情数据/sql server
#-------------------------------------------------------------------------------

import tushare as ts
import pandas as pd
import time
import datetime
from sqlalchemy import create_engine
engine = create_engine('mssql+pymssql://sa:123456@127.0.0.1:1433/earnmoney',echo=True)
engine.connect()
ts.set_token('your token')
pro = ts.pro_api()
summary = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')
# print(summary)
sdata=pd.DataFrame(summary)
# print(sdata)
for i in range(len(sdata['ts_code'])):
try:
a=sdata['ts_code'][i]
stime=sdata['list_date'][i]
# etime=time.strftime("%Y%m%d", time.localtime())+datetime.timedelta(days=-1)
etime = time.strftime("%Y%m%d", time.localtime())
f=pro.daily(ts_code=a,start_date=stime,end_date=etime)
data=pd.DataFrame(f)
data['trade_date'] = data['trade_date'].apply(
pd.to_datetime)
data['close'] = data['close'].apply(pd.to_numeric)
data.to_sql(sdata['name'][i],con=engine)
except:
print(sdata['name'][i]+'失败！')
else:
print(sdata['name'][i] + '成功！')

#-------------------------------------------------------------------------------
# Name: 定时更新/sql server
#-------------------------------------------------------------------------------

import tushare as ts
import pandas as pd
import time
import datetime
from sqlalchemy import create_engine
engine = create_engine('mssql+pymssql://sa:123456@127.0.0.1:1433/earnmoney',echo=True)
engine.connect()
ts.set_token('your token')
pro = ts.pro_api()
summary = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')
# print(summary)
sdata=pd.DataFrame(summary)
conn = engine.connect()
for i in range(len(sdata['ts_code'])):
now_time = datetime.datetime.now()
a=sdata['ts_code'][i]
stime=(now_time+datetime.timedelta(days=-1)).strftime("%Y%m%d")
etime=(now_time).strftime("%Y%m%d")
f=pro.daily(ts_code=a,start_date=stime,end_date=etime)
data = pd.DataFrame(f)
data['trade_date'] = data['trade_date'].apply(
pd.to_datetime)
data['close'] = data['close'].apply(pd.to_numeric)
# print(data['trade_date'][0])
str="insert into [{a}] (ts_code,trade_date,[open],[high],[low],[close],pre_close,change,pct_chg,vol,amount) values('{b}','{c}',{d},{e},{f},{g},{h},{i},{j},{k},{l})".format(a=sdata['name'][i],b=data['ts_code'][0],c=data['trade_date'][0],d=data['open'][0],e=data['high'][0],f=data['low'][0],g=data['close'][0],h=data['pre_close'][0],i=data['change'][0],j=data['pct_chg'][0],k=data['vol'][0],l=data['amount'][0])
conn.execute(str)
print(sdata['name'][i]+':成功！')