def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "daily_basic"),
metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("trade_date", String(8)), # 交易日期
Column("close", Float), # 当日收盘价
Column("turnover_rate", Float), # 换手率（%）
Column("turnover_rate_f", Float), # 换手率（自由流通股）
Column("volume_ratio", Float), # 量比
Column("pe", Float), # 市盈率（总市值/净利润， 亏损的PE为空
Column("pe_ttm", Float), # 市盈率（TTM，亏损的PE为空）
Column("pb", Float), # 市净率（总市值/净资产）
Column("ps", Float), # 市销率
Column("ps_ttm", Float), # 市销率（TTM）
Column("dv_ratio", Float), # 股息率 （%）
Column("dv_ttm", Float), # 股息率（TTM）（%）
Column("total_share", Float), # 总股本 （万股）
Column("float_share", Float), # 流通股本 （万股）
Column("free_share", Float), # 由流通股本 （万）
Column("total_mv", Float), # 总市值 （万元）
Column("circ_mv", Float) # 流通市值（万元）
)
def update_bulk_income_by_period_and_ts_code(base_name,
engine,
pro,
codes,
start_date,
end_date,
retry_count=3,
pause=2):
coverage = dbUtil.getTableRange(base_name="",
start_date=start_date,
end_date=end_date)
for i in coverage:
for rownum in range(0, len(codes)):
logger.debug("started processing data for " +
codes.iloc[rownum]['ts_code'] + " for period " + i)
if (int(codes.iloc[rownum]['list_date'][0:4]) <= int(i[1:5]) or
int(codes.iloc[rownum]['list_date'][0:4]) <= int(i[6:10])):
try:
to_insert = pro.income_vip(
ts_code=codes.iloc[rownum]['ts_code'],
start_date=i[1:5] + '0101',
end_date=i[6:10] + '1231')
logger.debug("start inserting data into DB")
to_insert.to_sql(base_name + i,
engine,
if_exists='append',
index=False)
logger.debug("end inserting data into DB")
except Exception as e:
logger.error(e)
logger.error("error processing data for range " + str(i) +
" for code " + codes.iloc[rownum]['ts_code'])
def update_daily_date(engine, pro, date, retry_count, pause):
"""日期方式更新 每日指标"""
df = get_daily_basic_date(pro, date, retry_count, pause)
df.to_sql(dbUtil.getTableName(int(date[0:4]), "daily_basic"),
engine,
if_exists='append',
index=False)
def update_bulk_daily_using_code_by_year(engine, pro, codes, start_date, end_date, retry_count, pause):
"""
股票代码方式更新 每日指标
"""
start_year = int(start_date[0:4])
end_year = int(end_date[0:4])
for i in range(start_year, end_year):
logger.info("starting processing year " + str(i))
if (i == start_year):
temp_start_date = start_date
temp_end_date = str(i) + "1231"
elif (i == end_year):
temp_start_date = str(i) + "0101"
temp_end_date = end_date
else:
temp_start_date = str(i) + "0101"
temp_end_date = str(i) + "1231"
for value in codes['ts_code']:
logger.debug("processing " + value + " for date " + temp_start_date + "-" + temp_end_date)
df = get_limit_list_code(pro, value, temp_start_date, temp_end_date, retry_count, pause)
try:
logger.debug("start inserting data into DB")
df.to_sql(dbUtil.getTableName(i, "limit_list"), engine, if_exists='append', index=False)
logger.debug("end inserting data into DB")
except IntegrityError as err:
logger.error("error processing data for year" + str(i) + " stock code " + value)
logger.error(err)
def update_daily_date(engine, pro, date, retry_count, pause):
"""日期方式更新 复权因子 行情"""
df = get_adj_factor_date(pro, date, retry_count, pause)
df.to_sql(dbUtil.getTableName(int(date[0:4]), "adj_factor"),
engine,
if_exists='append',
index=False)
def update_bulk_income_by_ts_code_and_insert_by_year(base_name, engine, pro, codes, sharding_column, failed_count=0,
failed_tolerent=3):
failed = []
for code in codes['ts_code']:
logger.debug("started processing data for " + code)
try:
to_insert = pro.income_vip(ts_code=code)
logger.debug("start inserting data into DB")
distinct_years = set(to_insert[sharding_column].str[0:4])
for year in distinct_years:
year_section = to_insert[to_insert[sharding_column].str[0:4] == year]
if (year == None):
year = 9999
year_section = to_insert[pd.isna(to_insert[sharding_column]) == True]
year_section.to_sql(dbUtil.getTableName(int(year), base_name=base_name), engine, if_exists='append',
index=False)
logger.debug("end inserting data into DB")
except Exception as e:
failed.append(code)
logger.error(e)
logger.error("error processing data for code " + code)
if (failed_count < failed_tolerent and len(failed)>0):
logger.warning("retrying now.")
failed_count = failed_count + 1
update_bulk_income_by_ts_code_and_insert_by_year(base_name=base_name, engine=engine, pro=pro,
codes=pd.DataFrame(failed, columns=['ts_code']),
sharding_column=sharding_column,
failed_count=failed_count)
else:
logger.error("the below code has failed after maximum attempts. " + ','.join(failed))
def update_bulk_express_by_ts_code_and_insert_by_year(base_name, engine, pro, codes, sharding_column, failed_count=0,
failed_tolerent=3):
failed = []
for code in codes['ts_code']:
logger.debug("started processing data for " + code)
try:
to_insert = pro.express_vip(ts_code=code, fields='ts_code,ann_date,end_date,revenue,operate_profit,total_profit,n_income,total_assets,total_hldr_eqy_exc_min_int,diluted_eps,diluted_roe,yoy_net_profit,bps,yoy_sales,yoy_op,yoy_tp,yoy_dedu_np,yoy_eps,yoy_roe,growth_assets,yoy_equity,growth_bps,or_last_year,op_last_year,tp_last_year,np_last_year,eps_last_year,open_net_assets,open_bps')
logger.debug("start inserting data into DB")
distinct_years = set(to_insert[sharding_column].str[0:4])
for year in distinct_years:
year_section = to_insert[to_insert[sharding_column].str[0:4] == year]
if (year == None):
year = 9999
year_section = to_insert[pd.isna(to_insert[sharding_column]) == True]
year_section.to_sql(dbUtil.getTableName(int(year), base_name=base_name), engine, if_exists='append',
index=False)
logger.debug("end inserting data into DB")
except Exception as e:
failed.append(code)
logger.error(e)
logger.error("error processing data for code " + code)
if (failed_count < failed_tolerent):
logger.warning("retrying now.")
failed_count = failed_count + 1
update_bulk_express_by_ts_code_and_insert_by_year(base_name=base_name, engine=engine, pro=pro,
codes=pd.DataFrame(failed, columns=['ts_code']),
sharding_column=sharding_column,
failed_count=failed_count)
else:
logger.error("the below code has failed after maximum attempts. " + ','.join(failed))
def update_bulk_fina_indicator_by_period_and_ts_code(base_name,
engine,
pro,
codes,
start_date,
end_date,
retry_count=3,
pause=2):
coverage = dbUtil.getTableRange(base_name="",
start_date=start_date,
end_date=end_date)
for i in coverage:
for rownum in range(0, len(codes)):
logger.debug("started processing data for " +
codes.iloc[rownum]['ts_code'] + " for period " + i)
if (int(codes.iloc[rownum]['list_date'][0:4]) <= int(i[1:5]) or
int(codes.iloc[rownum]['list_date'][0:4]) <= int(i[6:10])):
try:
to_insert = pro.fina_indicator_vip(
ts_code=codes.iloc[rownum]['ts_code'],
start_date=i[1:5] + '0101',
end_date=i[6:10] + '1231',
fields=
'ts_code,ann_date,end_date,eps,dt_eps,total_revenue_ps,revenue_ps,capital_rese_ps,surplus_rese_ps,undist_profit_ps,extra_item,profit_dedt,gross_margin,current_ratio,quick_ratio,cash_ratio,invturn_days,arturn_days,inv_turn,ar_turn,ca_turn,fa_turn,assets_turn,op_income,valuechange_income,interst_income,daa,ebit,ebitda,fcff,fcfe,current_exint,noncurrent_exint,interestdebt,netdebt,tangible_asset,working_capital,networking_capital,invest_capital,retained_earnings,diluted2_eps,bps,ocfps,retainedps,cfps,ebit_ps,fcff_ps,fcfe_ps,netprofit_margin,grossprofit_margin,cogs_of_sales,expense_of_sales,profit_to_gr,saleexp_to_gr,adminexp_of_gr,finaexp_of_gr,impai_ttm,gc_of_gr,op_of_gr,ebit_of_gr,roe,roe_waa,roe_dt,roa,npta,roic,roe_yearly,roa2_yearly,roe_avg,opincome_of_ebt,investincome_of_ebt,n_op_profit_of_ebt,tax_to_ebt,dtprofit_to_profit,salescash_to_or,ocf_to_or,ocf_to_opincome,capitalized_to_da,debt_to_assets,assets_to_eqt,dp_assets_to_eqt,ca_to_assets,nca_to_assets,tbassets_to_totalassets,int_to_talcap,eqt_to_talcapital,currentdebt_to_debt,longdeb_to_debt,ocf_to_shortdebt,debt_to_eqt,eqt_to_debt,eqt_to_interestdebt,tangibleasset_to_debt,tangasset_to_intdebt,tangibleasset_to_netdebt,ocf_to_debt,ocf_to_interestdebt,ocf_to_netdebt,ebit_to_interest,longdebt_to_workingcapital,ebitda_to_debt,turn_days,roa_yearly,roa_dp,fixed_assets,profit_prefin_exp,non_op_profit,op_to_ebt,nop_to_ebt,ocf_to_profit,cash_to_liqdebt,cash_to_liqdebt_withinterest,op_to_liqdebt,op_to_debt,roic_yearly,total_fa_trun,profit_to_op,q_opincome,q_investincome,q_dtprofit,q_eps,q_netprofit_margin,q_gsprofit_margin,q_exp_to_sales,q_profit_to_gr,q_saleexp_to_gr,q_adminexp_to_gr,q_finaexp_to_gr,q_impair_to_gr_ttm,q_gc_to_gr,q_op_to_gr,q_roe,q_dt_roe,q_npta,q_opincome_to_ebt,q_investincome_to_ebt,q_dtprofit_to_profit,q_salescash_to_or,q_ocf_to_sales,q_ocf_to_or,basic_eps_yoy,dt_eps_yoy,cfps_yoy,op_yoy,ebt_yoy,netprofit_yoy,dt_netprofit_yoy,ocf_yoy,roe_yoy,bps_yoy,assets_yoy,eqt_yoy,tr_yoy,or_yoy,q_gr_yoy,q_gr_qoq,q_sales_yoy,q_sales_qoq,q_op_yoy,q_op_qoq,q_profit_yoy,q_profit_qoq,q_netprofit_yoy,q_netprofit_qoq,equity_yoy,rd_exp'
)
logger.debug("start inserting data into DB")
to_insert.to_sql(base_name + i,
engine,
if_exists='append',
index=False)
logger.debug("end inserting data into DB")
except Exception as e:
logger.error(e)
logger.error("error processing data for range " + str(i) +
" for code " + codes.iloc[rownum]['ts_code'])
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "moneyflow"), metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("trade_date", String(8)), # 交易日期
Column("buy_sm_vol", Float), # 小单买入量（手）
Column("buy_sm_amount", Float), # 小单买入金额（万元）
Column("sell_sm_vol", Float), # 小单卖出量（手）
Column("sell_sm_amount", Float), # 小单卖出金额（万元）
Column("buy_md_vol", Float), # 中单买入量（手）
Column("buy_md_amount", Float), # 中单买入金额（万元）
Column("sell_md_vol", Float), # 中单卖出量（手）
Column("sell_md_amount", Float), # 中单卖出金额（万元）
Column("buy_lg_vol", Float), # 大单买入量（手）
Column("buy_lg_amount", Float), # 大单买入金额（万元）
Column("sell_lg_vol", Float), # 大单卖出量（手）
Column("sell_lg_amount", Float), # 大单卖出金额（万元）
Column("buy_elg_vol", Float), # 特大单买入量（手）
Column("buy_elg_amount", Float), # 特大单买入金额（万元）
Column("sell_elg_vol", Float), # 特大单卖出量（手）
Column("sell_elg_amount", Float), # 特大单卖出金额（万元）
Column("net_mf_vol", Float), # 净流入量（手）
Column("net_mf_amount", Float) # 净流入额（万元）
)
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "limit_list"), metadata,
Column("id", INT, primary_key=True),
Column("trade_date", String(8)), # 交易日期
Column("ts_code", String(10)), # 股票代码
Column("name", String(10)), # 股票名称
Column("close", Float), # 收盘价
Column("pct_chg", Float), # 涨跌幅
Column("amp", Float), # 振幅
Column("fc_ratio", Float), # 封单金额/日成交金额
Column("fl_ratio", Float), # 封单手数/流通股本
Column("fd_amount", Float), # 封单金额
Column("first_time", String(8)), # 首次涨停时间
Column("last_time", String(8)), # 最后封板时间
Column("open_times", Float), # 打开次数
Column("strth", Float), # 涨跌停强度
Column("limit", String(1)) # D跌停U涨停
)
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "adj_factor"),
metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("trade_date", String(8)), # 交易日期
Column("adj_factor", Float), # 复权因子
)
def update_bulk_fina_indicator_by_ts_code_and_insert_by_year(
base_name,
engine,
pro,
codes,
sharding_column,
failed_count=0,
failed_tolerent=3):
failed = []
for code in codes['ts_code']:
logger.debug("started processing data for " + code)
try:
to_insert = pro.fina_indicator_vip(
ts_code=code,
fields=
'ts_code,ann_date,end_date,eps,dt_eps,total_revenue_ps,revenue_ps,capital_rese_ps,surplus_rese_ps,undist_profit_ps,extra_item,profit_dedt,gross_margin,current_ratio,quick_ratio,cash_ratio,invturn_days,arturn_days,inv_turn,ar_turn,ca_turn,fa_turn,assets_turn,op_income,valuechange_income,interst_income,daa,ebit,ebitda,fcff,fcfe,current_exint,noncurrent_exint,interestdebt,netdebt,tangible_asset,working_capital,networking_capital,invest_capital,retained_earnings,diluted2_eps,bps,ocfps,retainedps,cfps,ebit_ps,fcff_ps,fcfe_ps,netprofit_margin,grossprofit_margin,cogs_of_sales,expense_of_sales,profit_to_gr,saleexp_to_gr,adminexp_of_gr,finaexp_of_gr,impai_ttm,gc_of_gr,op_of_gr,ebit_of_gr,roe,roe_waa,roe_dt,roa,npta,roic,roe_yearly,roa2_yearly,roe_avg,opincome_of_ebt,investincome_of_ebt,n_op_profit_of_ebt,tax_to_ebt,dtprofit_to_profit,salescash_to_or,ocf_to_or,ocf_to_opincome,capitalized_to_da,debt_to_assets,assets_to_eqt,dp_assets_to_eqt,ca_to_assets,nca_to_assets,tbassets_to_totalassets,int_to_talcap,eqt_to_talcapital,currentdebt_to_debt,longdeb_to_debt,ocf_to_shortdebt,debt_to_eqt,eqt_to_debt,eqt_to_interestdebt,tangibleasset_to_debt,tangasset_to_intdebt,tangibleasset_to_netdebt,ocf_to_debt,ocf_to_interestdebt,ocf_to_netdebt,ebit_to_interest,longdebt_to_workingcapital,ebitda_to_debt,turn_days,roa_yearly,roa_dp,fixed_assets,profit_prefin_exp,non_op_profit,op_to_ebt,nop_to_ebt,ocf_to_profit,cash_to_liqdebt,cash_to_liqdebt_withinterest,op_to_liqdebt,op_to_debt,roic_yearly,total_fa_trun,profit_to_op,q_opincome,q_investincome,q_dtprofit,q_eps,q_netprofit_margin,q_gsprofit_margin,q_exp_to_sales,q_profit_to_gr,q_saleexp_to_gr,q_adminexp_to_gr,q_finaexp_to_gr,q_impair_to_gr_ttm,q_gc_to_gr,q_op_to_gr,q_roe,q_dt_roe,q_npta,q_opincome_to_ebt,q_investincome_to_ebt,q_dtprofit_to_profit,q_salescash_to_or,q_ocf_to_sales,q_ocf_to_or,basic_eps_yoy,dt_eps_yoy,cfps_yoy,op_yoy,ebt_yoy,netprofit_yoy,dt_netprofit_yoy,ocf_yoy,roe_yoy,bps_yoy,assets_yoy,eqt_yoy,tr_yoy,or_yoy,q_gr_yoy,q_gr_qoq,q_sales_yoy,q_sales_qoq,q_op_yoy,q_op_qoq,q_profit_yoy,q_profit_qoq,q_netprofit_yoy,q_netprofit_qoq,equity_yoy,rd_exp'
)
logger.debug("start inserting data into DB")
distinct_years = set(to_insert[sharding_column].str[0:4])
for year in distinct_years:
year_section = to_insert[to_insert[sharding_column].str[0:4] ==
year]
if (year == None):
year = 9999
year_section = to_insert[pd.isna(
to_insert[sharding_column]) == True]
year_section.to_sql(dbUtil.getTableName(int(year),
base_name=base_name),
engine,
if_exists='append',
index=False)
logger.debug("end inserting data into DB")
except Exception as e:
failed.append(code)
logger.error(e)
logger.error("error processing data for code " + code)
if (failed_count < failed_tolerent):
logger.warning("retrying now.")
failed_count = failed_count + 1
update_bulk_fina_indicator_by_ts_code_and_insert_by_year(
base_name=base_name,
engine=engine,
pro=pro,
codes=pd.DataFrame(failed, columns=['ts_code']),
sharding_column=sharding_column,
failed_count=failed_count)
else:
logger.error("the below code has failed after maximum attempts. " +
','.join(failed))
def update_bulk_limit_list_by_day(engine, pro, start_date, end_date):
trade_cal = get_trade_cal(pro, start_date, end_date)
for a_day in trade_cal:
logger.debug("started processing data for date " + a_day)
df = get_limit_list_date(pro=pro, date=a_day)
try:
logger.debug("start inserting data into DB")
df.to_sql(dbUtil.getTableName(int(a_day[0:4]), "limit_list"), engine, if_exists='append', index=False)
logger.debug("end inserting data into DB")
except IntegrityError as err:
logger.error("error processing data for date" + str(a_day) + " as data for that day already exists")
logger.error(err)
except Exception as e:
logger.error("error processing data for date" + str(a_day))
logger.error(e)
def check_integrity_daily_data_table(base_name: str, engine, pro,
skipping_year_start: []):
"""
:param skipping_year:
:param base_name:
:param engine:
:return:
"""
missing = {}
codes = get_ts_code_and_list_date(engine)
years = dbUtil.getYears()
table_range = {}
if (len(skipping_year_start) > 0):
for i in skipping_year_start:
if (years.get(i) != None):
years.pop(i)
for start_year in years.keys():
table_range[int(str(start_year) +
"0101")] = int(str(years.get(start_year)) + "1231")
for table_start in table_range.keys():
table_end = table_range.get(table_start)
for code in codes['ts_code']:
list_date = int(codes[codes['ts_code'] == code]['list_date'])
if list_date > table_end:
# if list date after end of table, there should be no lines here
pass
elif table_start <= list_date <= table_end:
check_integrity_worker(base_name=base_name,
table_name=base_name + "_" +
str(table_start)[0:4] + "_" +
str(table_end)[0:4],
code=code,
start_date=list_date,
end_date=table_end,
engine=engine)
else:
# list date < table_start
check_integrity_worker(base_name=base_name,
table_name=base_name + "_" +
str(table_start)[0:4] + "_" +
str(table_end)[0:4],
code=code,
start_date=table_start,
end_date=table_end,
engine=engine)
def update_bulk_express_by_period_and_ts_code(base_name, engine, pro, codes, start_date, end_date, retry_count=3,
pause=2):
coverage = dbUtil.getTableRange(base_name="", start_date=start_date, end_date=end_date)
for i in coverage:
for rownum in range(0, len(codes)):
logger.debug("started processing data for " + codes.iloc[rownum]['ts_code'] + " for period " + i)
if (int(codes.iloc[rownum]['list_date'][0:4]) <= int(i[1:5]) or int(
codes.iloc[rownum]['list_date'][0:4]) <= int(i[6:10])):
try:
to_insert = pro.express_vip(ts_code=codes.iloc[rownum]['ts_code'], start_date=i[1:5] + '0101',
end_date=i[6:10] + '1231', fields='ts_code,ann_date,end_date,revenue,operate_profit,total_profit,n_income,total_assets,total_hldr_eqy_exc_min_int,diluted_eps,diluted_roe,yoy_net_profit,bps,yoy_sales,yoy_op,yoy_tp,yoy_dedu_np,yoy_eps,yoy_roe,growth_assets,yoy_equity,growth_bps,or_last_year,op_last_year,tp_last_year,np_last_year,eps_last_year,open_net_assets,open_bps')
logger.debug("start inserting data into DB")
to_insert.to_sql(base_name + i, engine, if_exists='append', index=False)
logger.debug("end inserting data into DB")
except Exception as e:
logger.error(e)
logger.error(
"error processing data for range " + str(i) + " for code " + codes.iloc[rownum]['ts_code'])
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "daily"), metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("trade_date", String(8)), # 交易日期
Column("open", Float), # 开盘价
Column("high", Float), # 最高价
Column("low", Float), # 最低价
Column("close", Float), # 收盘价
Column("pre_close", Float), # 昨收价
Column("change", Float), # 涨跌额
Column("pct_chg", Float), # 涨跌幅 （未复权，如果是复权请用 通用行情接口 ）
Column("vol", Float), # 成交量 （手）
Column("amount", Float) # 成交额 （千元）
)
def get_missing_dates(pro, engine, ts_code, dates, interface_type):
retry_count = 0
missing_from_tushare = 0
number_of_inserted_rows = 0
for date in dates:
isSuccessful = False
if (interface_type == 'daily'):
while (retry_count < 3 and not isSuccessful):
try:
df = pro.daily(ts_code=ts_code,
start_date=date,
end_date=date)
isSuccessful = True
except Exception as e:
logger.error(
"error pulling data from tushare for interface daily, code "
+ ts_code)
logger.error(e)
retry_count = retry_count + 1
if (isSuccessful):
if df.empty:
missing_from_tushare = missing_from_tushare + 1
else:
df['trade_date_int'] = int(df['trade_date'])
df.to_sql(dbUtil.getTableName(int(str(date)[0:4]),
"daily"),
engine,
if_exists='append',
index=False)
number_of_inserted_rows = number_of_inserted_rows + 1
else:
logger.error(
'error pulling data from tushare for interface daily, code'
+ ts_code + ' date ' + date)
return {
"missing_from_tushare": missing_from_tushare,
"number_of_inserted_rows": number_of_inserted_rows
}
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "limit_list"), metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("ann_date", String(8)),
Column("holder_name", String(10)), # 股东名称
Column("holder_type", String(1)), # 股东类型G高管P个人C公司
Column("in_de", String(2)), # 类型IN增持DE减持
Column("change_vol", Float), # 变动数量
Column("change_ratio", Float), # 占流通比例（%）
Column("after_share", Float), # 变动后持股
Column("after_ratio", Float), # 变动后占流通比例（%）
Column("avg_price", Float), # 平均价格
Column("total_share", Float) # 持股总数
)
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "express"), metadata,
Column("ts_code", String(10)), # 股票代码
Column("ann_date", String(8)),
Column("end_date", String(8)),
Column("revenue", Float), # 营业收入(元)
Column("operate_profit", Float), # 营业利润(元)
Column("total_profit", Float), # 利润总额(元)
Column("n_income", Float), # 净利润(元)
Column("total_assets", Float), # 总资产(元)
Column("total_hldr_eqy_exc_min_int", Float), # 股东权益合计(不含少数股东权益)(元)
Column("diluted_eps", Float), # 每股收益(摊薄)(元)
Column("diluted_roe", Float), # 净资产收益率(摊薄)(%)
Column("yoy_net_profit", Float), # 去年同期修正后净利润
Column("bps", Float), # 每股净资产
Column("yoy_sales", Float), # 同比增长率:营业收入
Column("yoy_op", Float), # 同比增长率:营业利润
Column("yoy_tp", Float), # 同比增长率:利润总额
Column("yoy_dedu_np", Float), # 同比增长率:归属母公司股东的净利润
Column("yoy_eps", Float), # 同比增长率:基本每股收益
Column("yoy_roe", Float), # 同比增减:加权平均净资产收益率
Column("growth_assets", Float), # 比年初增长率:总资产
Column("yoy_equity", Float), # 比年初增长率:归属母公司的股东权益
Column("growth_bps", Float), # 比年初增长率:归属于母公司股东的每股净资产
Column("or_last_year", Float), # 去年同期营业收入
Column("op_last_year", Float), # 去年同期营业利润
Column("tp_last_year", Float), # 去年同期利润总额
Column("np_last_year", Float), # 去年同期净利润
Column("eps_last_year", Float), # 去年同期每股收益
Column("open_net_assets", Float), # 期初净资产
Column("open_bps", Float) # 期初每股净资产
)
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "fina_indicator"),
metadata,
Column("ts_code", String(10)), # 股票代码
Column("ann_date", String(8)),
Column("end_date", String(8)),
Column("eps", Float), # 基本每股收益
Column("dt_eps", Float), # 稀释每股收益
Column("total_revenue_ps", Float), # 每股营业总收入
Column("revenue_ps", Float), # 每股营业收入
Column("capital_rese_ps", Float), # 每股资本公积
Column("surplus_rese_ps", Float), # 每股盈余公积
Column("undist_profit_ps", Float), # 每股未分配利润
Column("extra_item", Float), # 非经常性损益
Column("profit_dedt", Float), # 扣除非经常性损益后的净利润
Column("gross_margin", Float), # 毛利
Column("current_ratio", Float), # 流动比率
Column("quick_ratio", Float), # 速动比率
Column("cash_ratio", Float), # 保守速动比率
Column("invturn_days", Float), # 存货周转天数
Column("arturn_days", Float), # 应收账款周转天数
Column("inv_turn", Float), # 存货周转率
Column("ar_turn", Float), # 应收账款周转率
Column("ca_turn", Float), # 流动资产周转率
Column("fa_turn", Float), # 固定资产周转率
Column("assets_turn", Float), # 总资产周转率
Column("op_income", Float), # 经营活动净收益
Column("valuechange_income", Float), # 价值变动净收益
Column("interst_income", Float), # 利息费用
Column("daa", Float), # 折旧与摊销
Column("ebit", Float), # 息税前利润
Column("ebitda", Float), # 息税折旧摊销前利润
Column("fcff", Float), # 企业自由现金流量
Column("fcfe", Float), # 股权自由现金流量
Column("current_exint", Float), # 无息流动负债
Column("noncurrent_exint", Float), # 无息非流动负债
Column("interestdebt", Float), # 带息债务
Column("netdebt", Float), # 净债务
Column("tangible_asset", Float), # 有形资产
Column("working_capital", Float), # 营运资金
Column("networking_capital", Float), # 营运流动资本
Column("invest_capital", Float), # 全部投入资本
Column("retained_earnings", Float), # 留存收益
Column("diluted2_eps", Float), # 期末摊薄每股收益
Column("bps", Float), # 每股净资产
Column("ocfps", Float), # 每股经营活动产生的现金流量净额
Column("retainedps", Float), # 每股留存收益
Column("cfps", Float), # 每股现金流量净额
Column("ebit_ps", Float), # 每股息税前利润
Column("fcff_ps", Float), # 每股企业自由现金流量
Column("fcfe_ps", Float), # 每股股东自由现金流量
Column("netprofit_margin", Float), # 销售净利率
Column("grossprofit_margin", Float), # 销售毛利率
Column("cogs_of_sales", Float), # 销售成本率
Column("expense_of_sales", Float), # 销售期间费用率
Column("profit_to_gr", Float), # 净利润/营业总收入
Column("saleexp_to_gr", Float), # 销售费用/营业总收入
Column("adminexp_of_gr", Float), # 管理费用/营业总收入
Column("finaexp_of_gr", Float), # 财务费用/营业总收入
Column("impai_ttm", Float), # 资产减值损失/营业总收入
Column("gc_of_gr", Float), # 营业总成本/营业总收入
Column("op_of_gr", Float), # 营业利润/营业总收入
Column("ebit_of_gr", Float), # 息税前利润/营业总收入
Column("roe", Float), # 净资产收益率
Column("roe_waa", Float), # 加权平均净资产收益率
Column("roe_dt", Float), # 净资产收益率(扣除非经常损益)
Column("roa", Float), # 总资产报酬率
Column("npta", Float), # 总资产净利润
Column("roic", Float), # 投入资本回报率
Column("roe_yearly", Float), # 年化净资产收益率
Column("roa2_yearly", Float), # 年化总资产报酬率
Column("roe_avg", Float), # 平均净资产收益率(增发条件)
Column("opincome_of_ebt", Float), # 经营活动净收益/利润总额
Column("investincome_of_ebt", Float), # 价值变动净收益/利润总额
Column("n_op_profit_of_ebt", Float), # 营业外收支净额/利润总额
Column("tax_to_ebt", Float), # 所得税/利润总额
Column("dtprofit_to_profit", Float), # 扣除非经常损益后的净利润/净利润
Column("salescash_to_or", Float), # 销售商品提供劳务收到的现金/营业收入
Column("ocf_to_or", Float), # 经营活动产生的现金流量净额/营业收入
Column("ocf_to_opincome", Float), # 经营活动产生的现金流量净额/经营活动净收益
Column("capitalized_to_da", Float), # 资本支出/折旧和摊销
Column("debt_to_assets", Float), # 资产负债率
Column("assets_to_eqt", Float), # 权益乘数
Column("dp_assets_to_eqt", Float), # 权益乘数(杜邦分析)
Column("ca_to_assets", Float), # 流动资产/总资产
Column("nca_to_assets", Float), # 非流动资产/总资产
Column("tbassets_to_totalassets", Float), # 有形资产/总资产
Column("int_to_talcap", Float), # 带息债务/全部投入资本
Column("eqt_to_talcapital", Float), # 归属于母公司的股东权益/全部投入资本
Column("currentdebt_to_debt", Float), # 流动负债/负债合计
Column("longdeb_to_debt", Float), # 非流动负债/负债合计
Column("ocf_to_shortdebt", Float), # 经营活动产生的现金流量净额/流动负债
Column("debt_to_eqt", Float), # 产权比率
Column("eqt_to_debt", Float), # 归属于母公司的股东权益/负债合计
Column("eqt_to_interestdebt", Float), # 归属于母公司的股东权益/带息债务
Column("tangibleasset_to_debt", Float), # 有形资产/负债合计
Column("tangasset_to_intdebt", Float), # 有形资产/带息债务
Column("tangibleasset_to_netdebt", Float), # 有形资产/净债务
Column("ocf_to_debt", Float), # 经营活动产生的现金流量净额/负债合计
Column("ocf_to_interestdebt", Float), # 经营活动产生的现金流量净额/带息债务
Column("ocf_to_netdebt", Float), # 经营活动产生的现金流量净额/净债务
Column("ebit_to_interest", Float), # 已获利息倍数(EBIT/利息费用)
Column("longdebt_to_workingcapital", Float), # 长期债务与营运资金比率
Column("ebitda_to_debt", Float), # 息税折旧摊销前利润/负债合计
Column("turn_days", Float), # 营业周期
Column("roa_yearly", Float), # 年化总资产净利率
Column("roa_dp", Float), # 总资产净利率(杜邦分析)
Column("fixed_assets", Float), # 固定资产合计
Column("profit_prefin_exp", Float), # 扣除财务费用前营业利润
Column("non_op_profit", Float), # 非营业利润
Column("op_to_ebt", Float), # 营业利润／利润总额
Column("nop_to_ebt", Float), # 非营业利润／利润总额
Column("ocf_to_profit", Float), # 经营活动产生的现金流量净额／营业利润
Column("cash_to_liqdebt", Float), # 货币资金／流动负债
Column("cash_to_liqdebt_withinterest", Float), # 货币资金／带息流动负债
Column("op_to_liqdebt", Float), # 营业利润／流动负债
Column("op_to_debt", Float), # 营业利润／负债合计
Column("roic_yearly", Float), # 年化投入资本回报率
Column("total_fa_trun", Float), # 固定资产合计周转率
Column("profit_to_op", Float), # 利润总额／营业收入
Column("q_opincome", Float), # 经营活动单季度净收益
Column("q_investincome", Float), # 价值变动单季度净收益
Column("q_dtprofit", Float), # 扣除非经常损益后的单季度净利润
Column("q_eps", Float), # 每股收益(单季度)
Column("q_netprofit_margin", Float), # 销售净利率(单季度)
Column("q_gsprofit_margin", Float), # 销售毛利率(单季度)
Column("q_exp_to_sales", Float), # 销售期间费用率(单季度)
Column("q_profit_to_gr", Float), # 净利润／营业总收入(单季度)
Column("q_saleexp_to_gr", Float), # 销售费用／营业总收入 (单季度)
Column("q_adminexp_to_gr", Float), # 管理费用／营业总收入 (单季度)
Column("q_finaexp_to_gr", Float), # 财务费用／营业总收入 (单季度)
Column("q_impair_to_gr_ttm", Float), # 资产减值损失／营业总收入(单季度)
Column("q_gc_to_gr", Float), # 营业总成本／营业总收入 (单季度)
Column("q_op_to_gr", Float), # 营业利润／营业总收入(单季度)
Column("q_roe", Float), # 净资产收益率(单季度)
Column("q_dt_roe", Float), # 净资产单季度收益率(扣除非经常损益)
Column("q_npta", Float), # 总资产净利润(单季度)
Column("q_opincome_to_ebt", Float), # 经营活动净收益／利润总额(单季度)
Column("q_investincome_to_ebt", Float), # 价值变动净收益／利润总额(单季度)
Column("q_dtprofit_to_profit", Float), # 扣除非经常损益后的净利润／净利润(单季度)
Column("q_salescash_to_or", Float), # 销售商品提供劳务收到的现金／营业收入(单季度)
Column("q_ocf_to_sales", Float), # 经营活动产生的现金流量净额／营业收入(单季度)
Column("q_ocf_to_or", Float), # 经营活动产生的现金流量净额／经营活动净收益(单季度)
Column("basic_eps_yoy", Float), # 基本每股收益同比增长率(%)
Column("dt_eps_yoy", Float), # 稀释每股收益同比增长率(%)
Column("cfps_yoy", Float), # 每股经营活动产生的现金流量净额同比增长率(%)
Column("op_yoy", Float), # 营业利润同比增长率(%)
Column("ebt_yoy", Float), # 利润总额同比增长率(%)
Column("netprofit_yoy", Float), # 归属母公司股东的净利润同比增长率(%)
Column("dt_netprofit_yoy", Float), # 归属母公司股东的净利润-扣除非经常损益同比增长率(%)
Column("ocf_yoy", Float), # 经营活动产生的现金流量净额同比增长率(%)
Column("roe_yoy", Float), # 净资产收益率(摊薄)同比增长率(%)
Column("bps_yoy", Float), # 每股净资产相对年初增长率(%)
Column("assets_yoy", Float), # 资产总计相对年初增长率(%)
Column("eqt_yoy", Float), # 归属母公司的股东权益相对年初增长率(%)
Column("tr_yoy", Float), # 营业总收入同比增长率(%)
Column("or_yoy", Float), # 营业收入同比增长率(%)
Column("q_gr_yoy", Float), # 营业总收入同比增长率(%)(单季度)
Column("q_gr_qoq", Float), # 营业总收入环比增长率(%)(单季度)
Column("q_sales_yoy", Float), # 营业收入同比增长率(%)(单季度)
Column("q_sales_qoq", Float), # 营业收入环比增长率(%)(单季度)
Column("q_op_yoy", Float), # 营业利润同比增长率(%)(单季度)
Column("q_op_qoq", Float), # 营业利润环比增长率(%)(单季度)
Column("q_profit_yoy", Float), # 净利润同比增长率(%)(单季度)
Column("q_profit_qoq", Float), # 净利润环比增长率(%)(单季度)
Column("q_netprofit_yoy", Float), # 归属母公司股东的净利润同比增长率(%)(单季度)
Column("q_netprofit_qoq", Float), # 归属母公司股东的净利润环比增长率(%)(单季度)
Column("equity_yoy", Float), # 净资产同比增长率
Column("rd_exp", Float) # 研发费用
)
# create formatter
formatter = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
# add formatter to ch
ch.setFormatter(formatter)
fi.setFormatter(formatter)
# add ch to logger
logger.addHandler(ch)
logger.addHandler(fi)
engine = create_engine(config_service.getDefaultDB())
conn = engine.connect()
metadata = MetaData()
years = dbUtil.getYears()
for i in years.keys():
getTableMeta(i, metadata)
metadata.create_all(engine)
# df = get_ts_code_and_list_date(engine)
df = get_ts_code(engine)
# update_bulk_fina_indicator_by_period_and_ts_code(base_name='fina_indicator', engine=engine, pro=pro, codes=df, start_date='19950101',
# end_date=datetime.date.today().strftime("%Y%m%d"))
update_bulk_fina_indicator_by_ts_code_and_insert_by_year(
base_name='fina_indicator',
engine=engine,
pro=pro,
codes=df,
sharding_column='ann_date')
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "income"), metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("ann_date", String(8)),
Column("f_ann_date", String(8)),
Column("end_date", String(8)),
Column("report_type", String(3)),
Column("comp_type", String(3)),
Column("basic_eps", Float), # 基本每股收益
Column("diluted_eps", Float), # 稀释每股收益
Column("total_revenue", Float), # 营业总收入
Column("revenue", Float), # 营业收入
Column("int_income", Float), # 利息收入
Column("prem_earned", Float), # 已赚保费
Column("comm_income", Float), # 手续费及佣金收入
Column("n_commis_income", Float), # 手续费及佣金净收入
Column("n_oth_income", Float), # 其他经营净收益
Column("n_oth_b_income", Float), # 加:其他业务净收益
Column("prem_income", Float), # 保险业务收入
Column("out_prem", Float), # 减:分出保费
Column("une_prem_reser", Float), # 提取未到期责任准备金
Column("reins_income", Float), # 其中:分保费收入
Column("n_sec_tb_income", Float), # 代理买卖证券业务净收入
Column("n_sec_uw_income", Float), # 证券承销业务净收入
Column("n_asset_mg_income", Float), # 受托客户资产管理业务净收入
Column("oth_b_income", Float), # 其他业务收入
Column("fv_value_chg_gain", Float), # 加:公允价值变动净收益
Column("invest_income", Float), # 加:投资净收益
Column("ass_invest_income", Float), # 其中:对联营企业和合营企业的投资收益
Column("forex_gain", Float), # 加:汇兑净收益
Column("total_cogs", Float), # 营业总成本
Column("oper_cost", Float), # 减:营业成本
Column("int_exp", Float), # 减:利息支出
Column("comm_exp", Float), # 减:手续费及佣金支出
Column("biz_tax_surchg", Float), # 减:营业税金及附加
Column("sell_exp", Float), # 减:销售费用
Column("admin_exp", Float), # 减:管理费用
Column("fin_exp", Float), # 减:财务费用
Column("assets_impair_loss", Float), # 减:资产减值损失
Column("prem_refund", Float), # 退保金
Column("compens_payout", Float), # 赔付总支出
Column("reser_insur_liab", Float), # 提取保险责任准备金
Column("div_payt", Float), # 保户红利支出
Column("reins_exp", Float), # 分保费用
Column("oper_exp", Float), # 营业支出
Column("compens_payout_refu", Float), # 减:摊回赔付支出
Column("insur_reser_refu", Float), # 减:摊回保险责任准备金
Column("reins_cost_refund", Float), # 减:摊回分保费用
Column("other_bus_cost", Float), # 其他业务成本
Column("operate_profit", Float), # 营业利润
Column("non_oper_income", Float), # 加:营业外收入
Column("non_oper_exp", Float), # 减:营业外支出
Column("nca_disploss", Float), # 其中:减:非流动资产处置净损失
Column("total_profit", Float), # 利润总额
Column("income_tax", Float), # 所得税费用
Column("n_income", Float), # 净利润(含少数股东损益)
Column("n_income_attr_p", Float), # 净利润(不含少数股东损益)
Column("minority_gain", Float), # 少数股东损益
Column("oth_compr_income", Float), # 其他综合收益
Column("t_compr_income", Float), # 综合收益总额
Column("compr_inc_attr_p", Float), # 归属于母公司(或股东)的综合收益总额
Column("compr_inc_attr_m_s", Float), # 归属于少数股东的综合收益总额
Column("ebit", Float), # 息税前利润
Column("ebitda", Float), # 息税折旧摊销前利润
Column("insurance_exp", Float), # 保险业务支出
Column("undist_profit", Float), # 年初未分配利润
Column("distable_profit", Float), # 可分配利润
Column("update_flag", Float) # 更新标识，0未修改1更正过
)
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "balancesheet"),
metadata,
Column("ts_code", String(10)), # 股票代码
Column("ann_date", String(8)),
Column("f_ann_date", String(8)),
Column("end_date", String(8)),
Column("report_type", String(3)),
Column("comp_type", String(3)),
Column("total_share", Float), # 期末总股本
Column("cap_rese", Float), # 资本公积金
Column("undistr_porfit", Float), # 未分配利润
Column("surplus_rese", Float), # 盈余公积金
Column("special_rese", Float), # 专项储备
Column("money_cap", Float), # 货币资金
Column("trad_asset", Float), # 交易性金融资产
Column("notes_receiv", Float), # 应收票据
Column("accounts_receiv", Float), # 应收账款
Column("oth_receiv", Float), # 其他应收款
Column("prepayment", Float), # 预付款项
Column("div_receiv", Float), # 应收股利
Column("int_receiv", Float), # 应收利息
Column("inventories", Float), # 存货
Column("amor_exp", Float), # 待摊费用
Column("nca_within_1y", Float), # 一年内到期的非流动资产
Column("sett_rsrv", Float), # 结算备付金
Column("loanto_oth_bank_fi", Float), # 拆出资金
Column("premium_receiv", Float), # 应收保费
Column("reinsur_receiv", Float), # 应收分保账款
Column("reinsur_res_receiv", Float), # 应收分保合同准备金
Column("pur_resale_fa", Float), # 买入返售金融资产
Column("oth_cur_assets", Float), # 其他流动资产
Column("total_cur_assets", Float), # 流动资产合计
Column("fa_avail_for_sale", Float), # 可供出售金融资产
Column("htm_invest", Float), # 持有至到期投资
Column("lt_eqt_invest", Float), # 长期股权投资
Column("invest_real_estate", Float), # 投资性房地产
Column("time_deposits", Float), # 定期存款
Column("oth_assets", Float), # 其他资产
Column("lt_rec", Float), # 长期应收款
Column("fix_assets", Float), # 固定资产
Column("cip", Float), # 在建工程
Column("const_materials", Float), # 工程物资
Column("fixed_assets_disp", Float), # 固定资产清理
Column("produc_bio_assets", Float), # 生产性生物资产
Column("oil_and_gas_assets", Float), # 油气资产
Column("intan_assets", Float), # 无形资产
Column("r_and_d", Float), # 研发支出
Column("goodwill", Float), # 商誉
Column("lt_amor_exp", Float), # 长期待摊费用
Column("defer_tax_assets", Float), # 递延所得税资产
Column("decr_in_disbur", Float), # 发放贷款及垫款
Column("oth_nca", Float), # 其他非流动资产
Column("total_nca", Float), # 非流动资产合计
Column("cash_reser_cb", Float), # 现金及存放中央银行款项
Column("depos_in_oth_bfi", Float), # 存放同业和其它金融机构款项
Column("prec_metals", Float), # 贵金属
Column("deriv_assets", Float), # 衍生金融资产
Column("rr_reins_une_prem", Float), # 应收分保未到期责任准备金
Column("rr_reins_outstd_cla", Float), # 应收分保未决赔款准备金
Column("rr_reins_lins_liab", Float), # 应收分保寿险责任准备金
Column("rr_reins_lthins_liab", Float), # 应收分保长期健康险责任准备金
Column("refund_depos", Float), # 存出保证金
Column("ph_pledge_loans", Float), # 保户质押贷款
Column("refund_cap_depos", Float), # 存出资本保证金
Column("indep_acct_assets", Float), # 独立账户资产
Column("client_depos", Float), # 其中：客户资金存款
Column("client_prov", Float), # 其中：客户备付金
Column("transac_seat_fee", Float), # 其中:交易席位费
Column("invest_as_receiv", Float), # 应收款项类投资
Column("total_assets", Float), # 资产总计
Column("lt_borr", Float), # 长期借款
Column("st_borr", Float), # 短期借款
Column("cb_borr", Float), # 向中央银行借款
Column("depos_ib_deposits", Float), # 吸收存款及同业存放
Column("loan_oth_bank", Float), # 拆入资金
Column("trading_fl", Float), # 交易性金融负债
Column("notes_payable", Float), # 应付票据
Column("acct_payable", Float), # 应付账款
Column("adv_receipts", Float), # 预收款项
Column("sold_for_repur_fa", Float), # 卖出回购金融资产款
Column("comm_payable", Float), # 应付手续费及佣金
Column("payroll_payable", Float), # 应付职工薪酬
Column("taxes_payable", Float), # 应交税费
Column("int_payable", Float), # 应付利息
Column("div_payable", Float), # 应付股利
Column("oth_payable", Float), # 其他应付款
Column("acc_exp", Float), # 预提费用
Column("deferred_inc", Float), # 递延收益
Column("st_bonds_payable", Float), # 应付短期债券
Column("payable_to_reinsurer", Float), # 应付分保账款
Column("rsrv_insur_cont", Float), # 保险合同准备金
Column("acting_trading_sec", Float), # 代理买卖证券款
Column("acting_uw_sec", Float), # 代理承销证券款
Column("non_cur_liab_due_1y", Float), # 一年内到期的非流动负债
Column("oth_cur_liab", Float), # 其他流动负债
Column("total_cur_liab", Float), # 流动负债合计
Column("bond_payable", Float), # 应付债券
Column("lt_payable", Float), # 长期应付款
Column("specific_payables", Float), # 专项应付款
Column("estimated_liab", Float), # 预计负债
Column("defer_tax_liab", Float), # 递延所得税负债
Column("defer_inc_non_cur_liab", Float), # 递延收益-非流动负债
Column("oth_ncl", Float), # 其他非流动负债
Column("total_ncl", Float), # 非流动负债合计
Column("depos_oth_bfi", Float), # 同业和其它金融机构存放款项
Column("deriv_liab", Float), # 衍生金融负债
Column("depos", Float), # 吸收存款
Column("agency_bus_liab", Float), # 代理业务负债
Column("oth_liab", Float), # 其他负债
Column("prem_receiv_adva", Float), # 预收保费
Column("depos_received", Float), # 存入保证金
Column("ph_invest", Float), # 保户储金及投资款
Column("reser_une_prem", Float), # 未到期责任准备金
Column("reser_outstd_claims", Float), # 未决赔款准备金
Column("reser_lins_liab", Float), # 寿险责任准备金
Column("reser_lthins_liab", Float), # 长期健康险责任准备金
Column("indept_acc_liab", Float), # 独立账户负债
Column("pledge_borr", Float), # 其中:质押借款
Column("indem_payable", Float), # 应付赔付款
Column("policy_div_payable", Float), # 应付保单红利
Column("total_liab", Float), # 负债合计
Column("treasury_share", Float), # 减:库存股
Column("ordin_risk_reser", Float), # 一般风险准备
Column("forex_differ", Float), # 外币报表折算差额
Column("invest_loss_unconf", Float), # 未确认的投资损失
Column("minority_int", Float), # 少数股东权益
Column("total_hldr_eqy_exc_min_int", Float), # 股东权益合计(不含少数股东权益)
Column("total_hldr_eqy_inc_min_int", Float), # 股东权益合计(含少数股东权益)
Column("total_liab_hldr_eqy", Float), # 负债及股东权益总计
Column("lt_payroll_payable", Float), # 长期应付职工薪酬
Column("oth_comp_income", Float), # 其他综合收益
Column("oth_eqt_tools", Float), # 其他权益工具
Column("oth_eqt_tools_p_shr", Float), # 其他权益工具(优先股)
Column("lending_funds", Float), # 融出资金
Column("acc_receivable", Float), # 应收款项
Column("st_fin_payable", Float), # 应付短期融资款
Column("payables", Float), # 应付款项
Column("hfs_assets", Float), # 持有待售的资产
Column("hfs_sales", Float), # 持有待售的负债
Column("update_flag", Float) # 更新标识
)
def getTableMeta(year: int, metadata: MetaData) -> Table:
"""
get corresponding table meta data.
:param year: year of the data
:return: a Table object representing the table structure
"""
return Table(
dbUtil.getTableName(year, "cashflow"), metadata,
Column("id", INT, primary_key=True),
Column("ts_code", String(10)), # 股票代码
Column("ann_date", String(8)),
Column("f_ann_date", String(8)),
Column("end_date", String(8)),
Column("comp_type", String(3)),
Column("report_type", String(3)),
Column("net_profit", Float), # 净利润
Column("finan_exp", Float), # 财务费用
Column("c_fr_sale_sg", Float), # 销售商品、提供劳务收到的现金
Column("recp_tax_rends", Float), # 收到的税费返还
Column("n_depos_incr_fi", Float), # 客户存款和同业存放款项净增加额
Column("n_incr_loans_cb", Float), # 向中央银行借款净增加额
Column("n_inc_borr_oth_fi", Float), # 向其他金融机构拆入资金净增加额
Column("prem_fr_orig_contr", Float), # 收到原保险合同保费取得的现金
Column("n_incr_insured_dep", Float), # 保户储金净增加额
Column("n_reinsur_prem", Float), # 收到再保业务现金净额
Column("n_incr_disp_tfa", Float), # 处置交易性金融资产净增加额
Column("ifc_cash_incr", Float), # 收取利息和手续费净增加额
Column("n_incr_disp_faas", Float), # 处置可供出售金融资产净增加额
Column("n_incr_loans_oth_bank", Float), # 拆入资金净增加额
Column("n_cap_incr_repur", Float), # 回购业务资金净增加额
Column("c_fr_oth_operate_a", Float), # 收到其他与经营活动有关的现金
Column("c_inf_fr_operate_a", Float), # 经营活动现金流入小计
Column("c_paid_goods_s", Float), # 购买商品、接受劳务支付的现金
Column("c_paid_to_for_empl", Float), # 支付给职工以及为职工支付的现金
Column("c_paid_for_taxes", Float), # 支付的各项税费
Column("n_incr_clt_loan_adv", Float), # 客户贷款及垫款净增加额
Column("n_incr_dep_cbob", Float), # 存放央行和同业款项净增加额
Column("c_pay_claims_orig_inco", Float), # 支付原保险合同赔付款项的现金
Column("pay_handling_chrg", Float), # 支付手续费的现金
Column("pay_comm_insur_plcy", Float), # 支付保单红利的现金
Column("oth_cash_pay_oper_act", Float), # 支付其他与经营活动有关的现金
Column("st_cash_out_act", Float), # 经营活动现金流出小计
Column("n_cashflow_act", Float), # 经营活动产生的现金流量净额
Column("oth_recp_ral_inv_act", Float), # 收到其他与投资活动有关的现金
Column("c_disp_withdrwl_invest", Float), # 收回投资收到的现金
Column("c_recp_return_invest", Float), # 取得投资收益收到的现金
Column("n_recp_disp_fiolta", Float), # 处置固定资产、无形资产和其他长期资产收回的现金净额
Column("n_recp_disp_sobu", Float), # 处置子公司及其他营业单位收到的现金净额
Column("stot_inflows_inv_act", Float), # 投资活动现金流入小计
Column("c_pay_acq_const_fiolta", Float), # 购建固定资产、无形资产和其他长期资产支付的现金
Column("c_paid_invest", Float), # 投资支付的现金
Column("n_disp_subs_oth_biz", Float), # 取得子公司及其他营业单位支付的现金净额
Column("oth_pay_ral_inv_act", Float), # 支付其他与投资活动有关的现金
Column("n_incr_pledge_loan", Float), # 质押贷款净增加额
Column("stot_out_inv_act", Float), # 投资活动现金流出小计
Column("n_cashflow_inv_act", Float), # 投资活动产生的现金流量净额
Column("c_recp_borrow", Float), # 取得借款收到的现金
Column("proc_issue_bonds", Float), # 发行债券收到的现金
Column("oth_cash_recp_ral_fnc_act", Float), # 收到其他与筹资活动有关的现金
Column("stot_cash_in_fnc_act", Float), # 筹资活动现金流入小计
Column("free_cashflow", Float), # 企业自由现金流量
Column("c_prepay_amt_borr", Float), # 偿还债务支付的现金
Column("c_pay_dist_dpcp_int_exp", Float), # 分配股利、利润或偿付利息支付的现金
Column("incl_dvd_profit_paid_sc_ms", Float), # 其中:子公司支付给少数股东的股利、利润
Column("oth_cashpay_ral_fnc_act", Float), # 支付其他与筹资活动有关的现金
Column("stot_cashout_fnc_act", Float), # 筹资活动现金流出小计
Column("n_cash_flows_fnc_act", Float), # 筹资活动产生的现金流量净额
Column("eff_fx_flu_cash", Float), # 汇率变动对现金的影响
Column("n_incr_cash_cash_equ", Float), # 现金及现金等价物净增加额
Column("c_cash_equ_beg_period", Float), # 期初现金及现金等价物余额
Column("c_cash_equ_end_period", Float), # 期末现金及现金等价物余额
Column("c_recp_cap_contrib", Float), # 吸收投资收到的现金
Column("incl_cash_rec_saims", Float), # 其中:子公司吸收少数股东投资收到的现金
Column("uncon_invest_loss", Float), # 未确认投资损失
Column("prov_depr_assets", Float), # 加:资产减值准备
Column("depr_fa_coga_dpba", Float), # 固定资产折旧、油气资产折耗、生产性生物资产折旧
Column("amort_intang_assets", Float), # 无形资产摊销
Column("lt_amort_deferred_exp", Float), # 长期待摊费用摊销
Column("decr_deferred_exp", Float), # 待摊费用减少
Column("incr_acc_exp", Float), # 预提费用增加
Column("loss_disp_fiolta", Float), # 处置固定、无形资产和其他长期资产的损失
Column("loss_scr_fa", Float), # 固定资产报废损失
Column("loss_fv_chg", Float), # 公允价值变动损失
Column("invest_loss", Float), # 投资损失
Column("decr_def_inc_tax_assets", Float), # 递延所得税资产减少
Column("incr_def_inc_tax_liab", Float), # 递延所得税负债增加
Column("decr_inventories", Float), # 存货的减少
Column("decr_oper_payable", Float), # 经营性应收项目的减少
Column("incr_oper_payable", Float), # 经营性应付项目的增加
Column("others", Float), # 其他
Column("im_net_cashflow_oper_act", Float), # 经营活动产生的现金流量净额(间接法)
Column("conv_debt_into_cap", Float), # 债务转为资本
Column("conv_copbonds_due_within_1y", Float), # 一年内到期的可转换公司债券
Column("fa_fnc_leases", Float), # 融资租入固定资产
Column("end_bal_cash", Float), # 现金的期末余额
Column("beg_bal_cash", Float), # 减:现金的期初余额
Column("end_bal_cash_equ", Float), # 加:现金等价物的期末余额
Column("beg_bal_cash_equ", Float), # 减:现金等价物的期初余额
Column("im_n_incr_cash_equ", Float), # 现金及现金等价物净增加额(间接法)
Column("update_flag", Float) # 更新标识
)
def update_moneyflow_date(engine, pro, date, retry_count, pause):
"""日期方式更新 日线行情"""
df = get_moneyflow_date(pro, date, retry_count, pause)
df.to_sql(dbUtil.getTableName(int(date[0:4]), "moneyflow"), engine, if_exists='append', index=False)
