forked from dearLeaflet/Quant2
/
tushare_data.py
222 lines (194 loc) · 7.22 KB
/
tushare_data.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
# coding=utf-8
import datetime
import numpy
import tushare
import pandas
import myDb
import util
'''
获取股票相关数据
'''
import params
def get_tushare_pro() -> object:
return tushare.pro_api(params.TUSHARE_TOCKEN)
def get_hs300s() -> object:
"""
获取沪深300指数成份股,
return:code :股票代码
name :股票名称
date :日期
weight:权重
"""
print('获取沪深300成份股列表')
return tushare.get_hs300s()
def get_hs30s() -> object:
"""
获取沪深30支随机股票
return:code :股票代码
name :股票名称
date :日期
weight:权重
:return:
"""
df = pandas.DataFrame(tushare.get_hs300s())
shuffle = numpy.arange(0, 299, 10)
df = df.sort_values(by='name')
df = df.iloc[shuffle, :]
return df
def get_stock_code_date():
db = myDb.db_connect()
cursor = db.cursor()
try:
sql = "select DISTINCT i.CODE, i.TRADE_DATE from daily_info i order by i.CODE desc, i.TRADE_DATE"
cursor.execute(sql)
# 获取所有记录列表
return cursor.fetchall()
except Exception:
print("Error: unable to fetch data")
finally:
db.close()
def tick_insert(code, date):
if '20180630' < date < '20190707' and code <= '600570':
stock_tick_date = tushare.get_tick_data(code=code, date=date, src='tt')
stock_tick_date['code'] = code
stock_tick_date['date'] = date
return pandas.DataFrame(stock_tick_date)
return
def tick_insert1(code, date):
stock_tick_date = tushare.get_tick_data(code=code, date=date, src='tt')
stock_tick_date['code'] = code
stock_tick_date['date'] = date
return pandas.DataFrame(stock_tick_date)
def today_ticks_insert():
"""
插入当日历史分笔
:return:
"""
db = myDb.db_connect()
cursor = db.cursor()
hs300 = get_hs300s()
print('获取当日分笔数据并入库......')
for index, row in hs300.iterrows():
date = datetime.datetime.now().strftime('%Y%m%d')
detail = tick_insert1(code=row['code'], date=date)
if detail is None:
continue
for index1, detail_row in detail.iterrows():
sql = "INSERT INTO tick_data(ID, CODE, DATE, TIME, PRICE, PCHANGE, VOLUME, AMOUNT, TYPE) VALUES (" \
+ '\'' + str(row['code']) + str(date) + str(detail_row['time']).replace(":", "") + '\'' + ',' \
+ '\'' + str(row['code']) + '\'' + ',' \
+ '\'' + str(date) + '\'' + ',' \
+ '\'' + str(detail_row['time']) + '\'' + ',' \
+ '\'' + str(detail_row['price']) + '\'' + ',' \
+ '\'' + str(detail_row['change']) + '\'' + ',' \
+ '\'' + str(detail_row['volume']) + '\'' + ',' \
+ '\'' + str(detail_row['amount']) + '\'' + ',' \
+ '\'' + str(detail_row['type']) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
db.close()
print('当日分笔数据并入库完毕')
return
def data_convert(date_str):
return str(datetime.datetime.strptime(date_str, '%Y%m%d').strftime('%Y-%m-%d'))
def fetch_data_db(sql):
"""
获取数据库数据
:param sql:
:return:
"""
db = myDb.db_connect()
cursor = db.cursor()
try:
print(sql)
cursor.execute(sql)
rs = cursor.fetchall()
return rs
except:
print("获取数据")
finally:
db.close()
return
def daily_today_insert(price_type):
"""
沪深300成份股日线数据入库
daily_info
price_type:string 复权类型
qfq:前复权
hfq:后复权
None:不复权,默认值
:return:
"""
print("插入复前权历史日线数据......")
db = myDb.db_connect()
cursor = db.cursor()
hs300 = get_hs300s()
table_2_insert = price_type + "_daily_info" if (price_type != "bfq") else "daily_info"
for index, stocks in hs300.iterrows():
ts_code = util.stock_code_change(stocks['code'])
date = datetime.datetime.now().strftime('%Y%m%d')
daily = tushare.pro_bar(ts_code=ts_code, adj=price_type, start_date=date)
for index, row in daily.iterrows():
sql = "INSERT INTO " + table_2_insert + "(ID, CODE, TRADE_DATE, OPEN, CLOSE, HIGH, " \
+ "LOW, PRE_CLOSE,PCHANGE, PCT_CHANGE, VOL, AMOUNT) VALUES(" \
+ '\'' + str(row['ts_code'][:6]) + str(row['trade_date'][:10]) + '\'' + ',' \
+ '\'' + str(row['ts_code'][:6]) + '\'' + ',' \
+ '\'' + data_convert(row['trade_date'][:8]) + '\'' + ',' \
+ '\'' + str(row['open']) + '\'' + ',' \
+ '\'' + str(row['close']) + '\'' + ',' \
+ '\'' + str(row['high']) + '\'' + ',' \
+ '\'' + str(row['low']) + '\'' + ',' \
+ '\'' + str(row['pre_close']) + '\'' + ',' \
+ '\'' + str(row['change']) + '\'' + ',' \
+ '\'' + str(row['pct_chg']) + '\'' + ',' \
+ '\'' + str(row['vol']) + '\'' + ',' \
+ '\'' + str(row['amount']) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
cursor.close()
print("完成插入前复权历史日线数据")
def hist_daily_insert(price_type):
"""
沪深300成份股历史日线数据入库
daily_info
:return:
"""
print("插入历史复权日线数据......:", price_type)
db = myDb.db_connect()
cursor = db.cursor()
hs300 = get_hs300s()
table_2_insert = price_type + "_daily_info" if (price_type != "bfq") else "daily_info"
for index, stocks in hs300.iterrows():
ts_code = util.stock_code_change(stocks['code'])
daily = tushare.pro_bar(ts_code=ts_code, adj=price_type)
for index1, row in daily.iterrows():
if row['trade_date'][:8] < '20180630':
continue
sql = "INSERT INTO " + table_2_insert + "(ID, CODE, TRADE_DATE, OPEN, CLOSE, HIGH, LOW, PRE_CLOSE," \
+ "PCHANGE, PCT_CHANGE, VOL, AMOUNT) VALUES(" \
+ '\'' + str(row['ts_code'][:6]) + str(row['trade_date'][:10]) + '\'' + ',' \
+ '\'' + str(row['ts_code'][:6]) + '\'' + ',' \
+ '\'' + str(row['trade_date'][:8]) + '\'' + ',' \
+ '\'' + str(row['open']) + '\'' + ',' \
+ '\'' + str(row['close']) + '\'' + ',' \
+ '\'' + str(row['high']) + '\'' + ',' \
+ '\'' + str(row['low']) + '\'' + ',' \
+ '\'' + str(row['pre_close']) + '\'' + ',' \
+ '\'' + str(row['change']) + '\'' + ',' \
+ '\'' + str(row['pct_chg']) + '\'' + ',' \
+ '\'' + str(row['vol']) + '\'' + ',' \
+ '\'' + str(row['amount']) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
cursor.close()
print("完成插入历史复权日线数据:", price_type)
return
def get_index_daily(ts_code):
"""
获取指数信息
:param ts_code:
:return:
"""
pro = get_tushare_pro()
df = pro.index_daily(ts_code=ts_code)
return df