早前了解了tushare网站,网站提供证券相关的行业信息及每日交易数据,是金融从业尤其是量化从业人员常用数据网站。尝试从网站上取数搭建自己的金融数据体系,于是有了用从tushare上取数并插入mysql数据库中的尝试,简单记录一下
1、导包
####后续需要用到的库,期间pymysql总是连不上,pip install/uninstall mysql 尝试了多遍无果,最后还是找了个package文件放到目录下才搞定
import tushare as ts
import pandas as pd
import pymysql
ts.set_token('**************************')
pro = ts.pro_api()
2、连接数据库
####连接数据库,如history已存在就删掉
db = pymysql.connect(host="localhost", user="root",password="123456", database="data", charset='utf8' )
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS history")
3、建表
# 创建数据表SQL语句
sql = """CREATE TABLE history (
ts_code varchar(32) ,
trade_date varchar(32),
turnover_rate float,
volume_ratio float,
pe float ,
pb float ) """
cursor.execute(sql)
db.close() # 关闭数据库连接
print('successful')
4、数据准备,需将dataframe数据转换为dict,参考了一篇不错的博文,链接放后面
####取原始数据,数据缺失用0补充,并将dataframe转换为dict字典类型,待后续使用,通过dict[s]取键为s对应的值
b=pro.query('daily_basic', ts_code='600230.SH', trade_date='20210519',fields='ts_code,trade_date,turnover_rate,volume_ratio,pe,pb').fillna(0)
type(b)
c=b.to_dict('index')
i=len(result.keys())
c=b.to_dict('index')[0]
type(c)
5、定义一个取时间段函数
import datetime
def getDateList(start_date, end_date):
date_list = []
start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d')
end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d')
date_list.append(start_date.strftime('%Y-%m-%d').replace('-',''))
while start_date < end_date:
start_date += datetime.timedelta(days=1)
date_list.append(start_date.strftime('%Y-%m-%d').replace('-',''))
return date_list
6、批量取数插入mysql数据库中,有两种方式可尝试
6.1取指定日期所有股票信息,双重循环
#####通过时间循环,取多日所有股票的信息
trade_date=getDateList('2022-04-14','2022-04-15')
for trade_date in trade_date:
b=pro.query('daily_basic', ts_code='', trade_date=trade_date,fields='ts_code,trade_date,turnover_rate,volume_ratio,pe,pb').fillna(0)
result=b.to_dict('index') ####将dataframe转换为字典,每支股一天的信息存为一个字典值,其键为索引
i=len(result.keys()) ###计算result中有多少个键
db= pymysql.connect(host="localhost", user="root",password="123456", database="data", charset='utf8' ) # 打开数据库连接
cursor = db.cursor() # 使用cursor()方法获取操作游标
table='history' #mysql数据库里建的表
for i in range(i):
data=result[i] ####循环取每个键对应的值
keys = ', '.join(data.keys())
values =tuple(data.values())
sql = "INSERT INTO history(ts_code,trade_date,turnover_rate,volume_ratio,pe,pb) VALUES (%s, %s, %s, %s, %s, %s)"
try:
if cursor.execute(sql,values):
print('+1')
db.commit()
except:
print('failed')
db.rollback()
db.close
6.2取指定日期的部分股票数据,单重循环即可,多字段动态插入
######指定日期循环取当日每支股票的信息
code = ['600230.SH','600237.SH','002465.SZ','300732.SZ','600007.SH','300068.SZ','300552.SZ','601369.SH']
for ts_code in code:
b=pro.query('daily_basic', ts_code=ts_code, trade_date='20220407',fields='ts_code,trade_date,turnover_rate,volume_ratio,pe,pb')
data=b.to_dict('index')[0]
db= pymysql.connect(host="localhost", user="root",password="123456", database="data", charset='utf8' ) # 打开数据库连接
cursor = db.cursor() # 使用cursor()方法获取操作游标
table='history' #mysql数据库里建的表
keys = ', '.join(data.keys())
values =tuple(data.values())
sql = "INSERT INTO history(ts_code,trade_date,turnover_rate,volume_ratio,pe,pb) VALUES (%s, %s, %s, %s, %s, %s)"
try:
if cursor.execute(sql,values):
print("successful")
db.commit()
except:
print('failed')
db.rollback()
db.close
######不同的插入数据库的方式
######指定日期循环取当日每支股票的信息,数据更新插入
code = ['600230.SH']
for ts_code in code:
b=pro.query('daily_basic', ts_code=ts_code, trade_date='20220407',fields='ts_code,trade_date,turnover_rate,volume_ratio,pe,pb')
data=b.to_dict('index')[0]
db= pymysql.connect(host="localhost", user="root",password="123456", database="data", charset='utf8' ) # 打开数据库连接
cursor = db.cursor() # 使用cursor()方法获取操作游标
table='history' #mysql数据库里建的表
keysname = ', '.join(data.keys())
valuetype = ', '.join(['%s'] * len(data))
values =tuple(data.values())*2 ###有点没弄明白这里为啥要*2
sql = 'INSERT INTO {table}({keys}) VALUES({values}) ON DUPLICATE KEY UPDATE '.format(table=table, keys=keysname,values=valuetype)
updata = ', '.join(["{key} = %s".format(key=key) for key in data]) #连接起来实现更新数据
sql += updata
try:
if cursor.execute(sql,values):
print("successful")
db.commit()
except:
print('failed')
db.rollback()
db.close
参考的博文
将Pandas DataFrame转换为字典:https://blog.csdn.net/inmu21/article/details/116604963