def get_origin_data_from_db(stk_code):
# read data from database
data_df = get_total_table_data(conn_k, 'k' + stk_code)
# get necessary data
data_df_necessary = data_df.loc[:,
['open', 'high', 'close', 'low', 'volume']]
return data_df_necessary
def getSingleStkGrowth(codeParam):
result = []
for year in range(2001,int(get_current_date_str().split('-')[0]) +1):
for quarter in range(1,5):
table_name = 'growth' + str(year)+'0'+str(quarter)
if (int(str(year)+'0'+str(quarter)) < int(get_quarter_date())) & is_table_exist(conn_growth,stk_growth_data_db_name,table_name):
growth_df = get_total_table_data(conn=conn_growth,table_name = table_name)
singleGrowth = growth_df[growth_df.code == codeParam]\
.reset_index(drop=True)\
.to_dict(orient='index')
if len(singleGrowth):
singleGrowth = singleGrowth[0]
singleGrowth.update({'date':str(year)+'0'+str(quarter)})
result.append(singleGrowth)
return DataFrame(result).sort_values(by='date',ascending=True)
def get_total_volume_ss(code_param, field_list, time_span_list):
"""
完整获取单个stk的volume数据,ss:single stk
:param code_param:
:param field_list:
:param time_span_list:
:return:
"""
tick_df = get_total_table_data(conn_tick, 'tick' + code_param)
df_merge = gen_field_ave(tick_df, field_list, time_span_list)
field_list = merge_field_mean(field_list, time_span_list)
field_list.append('date')
return df_merge.loc[:, field_list]
def get_class_Q_ratio():
"""
获取上证、深证、创业板、中小板的季度变化率
:return:
"""
basic_Q = []
for b_ele in ['sh', 'sz', 'cyb', 'zxb']:
# 从数据库获取上证数据
df_b = get_total_table_data(conn_k, 'k' + b_ele)
# 增加季度
df_b_with_quarter = add_quarter_to_df(df_b)
# 增加季度增长率
df_b_Q = get_quarter_growth_ratio_df(df_b_with_quarter)
basic_Q.append({'b_ele': b_ele, 'b_Q': df_b_Q})
return basic_Q
def prepare_cnn_data_to_csv_ave(code_param, inc_time_span_param,
file_save_url_param):
"""
根据k数据整理衍生相应指标（均线，作为label的未来inc），并整理为列，存入csv中。
:param code_param:
:param inc_time_span_param:
:param file_save_url_param:
:return:
"""
# 获取stk的k数据
k_df = get_total_table_data(conn_k, 'k' + code_param)
# 获取均线
ave_origin = get_average_array_span(k_df, [7, 14, 30, 60, 180], 'close')
# 添加m30inc,前提是数据长度超过的时间轴上的前瞻距离
if len(ave_origin) > inc_time_span_param:
ave_with_m30inc = add_inc(ave_origin, 'close_mean30',
inc_time_span_param)
# 将df数据保存为csv
ave_with_m30inc.to_csv(file_save_url_param, index=False)
def get_ss_sb(codeParam, basic_ele):
"""
从profit数据库中获取每个stk的数据，转成Dataframe后按时间升序排序，返回！
:param codeParam:
:param basic_ele:
:return:
"""
result = []
if basic_ele == "profit":
for year in range(2001, int(get_current_date_str().split('-')[0]) + 1):
for quarter in range(1, 5):
table_name = 'profit' + str(year) + '0' + str(quarter)
if (int(str(year) + '0' + str(quarter)) < int(
get_quarter_date())) & is_table_exist(
conn_profit, stk_profit_data_db_name, table_name):
db_df = get_total_table_data(conn=conn_profit,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam]\
.reset_index(drop=True)\
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update(
{'date': str(year) + '0' + str(quarter)})
result.append(single_basic_ele_data)
else:
print(
"函数get_single_stk_single_basic：profit指标数据为空！季度为：" +
str(year) + '0' + str(quarter))
elif basic_ele == "growth":
for year in range(2001, int(get_current_date_str().split('-')[0]) + 1):
for quarter in range(1, 5):
table_name = 'growth' + str(year) + '0' + str(quarter)
if (int(str(year) + '0' + str(quarter)) < int(
get_quarter_date())) & is_table_exist(
conn_growth, stk_growth_data_db_name, table_name):
db_df = get_total_table_data(conn=conn_growth,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update(
{'date': str(year) + '0' + str(quarter)})
result.append(single_basic_ele_data)
else:
print(
"函数get_single_stk_single_basic：growth指标数据为空！季度为：" +
str(year) + '0' + str(quarter))
elif basic_ele == "operation":
for year in range(2001, int(get_current_date_str().split('-')[0]) + 1):
for quarter in range(1, 5):
table_name = 'operation' + str(year) + '0' + str(quarter)
if (int(str(year) + '0' + str(quarter)) < int(
get_quarter_date())) & is_table_exist(
conn_profit, stk_operation_data_db_name,
table_name):
db_df = get_total_table_data(conn=conn_operation,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update(
{'date': str(year) + '0' + str(quarter)})
result.append(single_basic_ele_data)
else:
print(
"函数get_single_stk_single_basic：operation指标数据为空！季度为："
+ str(year) + '0' + str(quarter))
elif basic_ele == "debtpaying":
for year in range(2001, int(get_current_date_str().split('-')[0]) + 1):
for quarter in range(1, 5):
table_name = 'debtpaying' + str(year) + '0' + str(quarter)
if (int(str(year) + '0' + str(quarter)) < int(
get_quarter_date())) & is_table_exist(
conn_debtpaying, stk_debtpaying_data_db_name,
table_name):
db_df = get_total_table_data(conn=conn_debtpaying,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update(
{'date': str(year) + '0' + str(quarter)})
result.append(single_basic_ele_data)
else:
print(
"函数get_single_stk_single_basic：debtpaying指标数据为空！季度为："
+ str(year) + '0' + str(quarter))
elif basic_ele == "cashflow":
for year in range(2001, int(get_current_date_str().split('-')[0]) + 1):
for quarter in range(1, 5):
table_name = 'cashflow' + str(year) + '0' + str(quarter)
if (int(str(year) + '0' + str(quarter)) < int(
get_quarter_date())) & is_table_exist(
conn_cashflow, stk_cashflow_data_db_name,
table_name):
db_df = get_total_table_data(conn=conn_cashflow,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update(
{'date': str(year) + '0' + str(quarter)})
result.append(single_basic_ele_data)
else:
print(
"函数get_single_stk_single_basic：cashflow指标数据为空！季度为："
+ str(year) + '0' + str(quarter))
else:
print("函数get_single_stk_single_basic：不识别的basic指标 " + basic_ele)
try:
result = DataFrame(result).sort_values(by='date', ascending=True)
except:
result = DataFrame()
print("函数get_single_stk_single_basic：遇到没有数据的指标！" + basic_ele)
return result
def get_ss_sb_sq(codeParam, basic_ele, quarter):
"""
下载“单个stk”的“单个basic方面”的“当前季度的basic信息”
ss:single stk
sb:single basic
sq:single quarter
:param codeParam:
:return:
"""
result = list()
if basic_ele == "profit":
table_name = 'profit' + quarter
if is_table_exist(conn_profit, stk_profit_data_db_name, table_name):
db_df = get_total_table_data(conn=conn_profit,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam]\
.reset_index(drop=True)\
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update({'date': quarter})
result.append(single_basic_ele_data)
else:
print("函数get_single_stk_single_basic：profit指标数据为空！季度为：" +
quarter)
elif basic_ele == "growth":
table_name = 'growth' + quarter
if is_table_exist(conn_growth, stk_growth_data_db_name, table_name):
db_df = get_total_table_data(conn=conn_growth,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update({'date': quarter})
result.append(single_basic_ele_data)
else:
print("函数get_single_stk_single_basic：growth指标数据为空！季度为：" +
quarter)
elif basic_ele == "operation":
table_name = 'operation' + quarter
if is_table_exist(conn_profit, stk_operation_data_db_name, table_name):
db_df = get_total_table_data(conn=conn_operation,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update({'date': quarter})
result.append(single_basic_ele_data)
else:
print("函数get_single_stk_single_basic：operation指标数据为空！季度为：" +
quarter)
elif basic_ele == "debtpaying":
table_name = 'debtpaying' + quarter
if is_table_exist(conn_debtpaying, stk_debtpaying_data_db_name,
table_name):
db_df = get_total_table_data(conn=conn_debtpaying,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update({'date': quarter})
result.append(single_basic_ele_data)
else:
print("函数get_single_stk_single_basic：debtpaying指标数据为空！季度为：" +
quarter)
elif basic_ele == "cashflow":
table_name = 'cashflow' + quarter
if is_table_exist(conn_cashflow, stk_cashflow_data_db_name,
table_name):
db_df = get_total_table_data(conn=conn_cashflow,
table_name=table_name)
single_basic_ele_data = db_df[db_df.code == codeParam] \
.reset_index(drop=True) \
.to_dict(orient='index')
if len(single_basic_ele_data):
single_basic_ele_data = single_basic_ele_data[0]
single_basic_ele_data.update({'date': quarter})
result.append(single_basic_ele_data)
else:
print("函数get_single_stk_single_basic：cashflow指标数据为空！季度为：" +
quarter)
else:
print("函数get_single_stk_single_basic：不识别的basic指标 " + basic_ele)
try:
result = DataFrame(result).sort_values(by='date', ascending=True)
except:
result = DataFrame()
print("函数get_single_stk_single_basic：遇到没有数据的指标！" + basic_ele)
return result