def insert_data(self, dataframe):
dataframe.to_sql('etl_hh',
schema='adhoc_parser',
con=engine,
if_exists='append',
index=False,
dtype={
'id': INT(),
'name': String(255),
'has_test': Boolean(),
'published_at': DateTime(),
'created_at': DateTime(),
'url': String(255),
'area_name': String(255),
'salary_from': INT(),
'salary_to': INT(),
'salary_currency': String(10),
'salary.gross': Boolean(),
'address.city': String(255),
'address.street': String(255),
'address_building': String(255),
'address_raw': String(500),
'metro_name': String(255),
'employer_id': INT(),
'employer_name': String(255),
'snippet_requirement': TEXT(),
'snippet_responsibility': TEXT(),
'contacts_name': String(255),
'contacts_email': String(255),
})
def _update_ts(self):
cal_date = self._get_nearest_cal_date()
if cal_date is None:
return
trade_date = cal_date.strftime('%Y%m%d')
df = get_pro().daily_basic(ts_code='', trade_date=trade_date)
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'close': FLOAT(),
'y': INT(),
'm': INT(),
'turnover_rate': FLOAT(),
'turnover_rate_f': FLOAT(),
'volume_ratio': FLOAT(),
'pe': FLOAT(),
'pe_ttm': FLOAT(),
'pb': FLOAT(),
'ps': FLOAT(),
'ps_ttm': FLOAT(),
'dv_ratio': FLOAT(),
'dv_ttm': FLOAT(),
'total_share': FLOAT(),
'float_share': FLOAT(),
'free_share': FLOAT(),
'total_mv': FLOAT(),
'circ_mv': FLOAT()
}
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
class Translation(Entity):
using_options(tablename='translation')
language = Field(camelot.types.Language, index=True)
source = Field(Unicode(500), index=True)
# value needs to be indexed as well, because when starting up we
# want to load only the translations that have a value specified
value = Field(Unicode(500), index=True)
cid = Field(INT(), default=0, index=True)
uid = Field(INT(), default=0, index=True)
# cache, to prevent too much of the same sql queries
_cache = dict()
class Admin(EntityAdmin):
verbose_name_plural = _('Translations')
form_size = (700, 150)
section = 'configuration'
list_display = ['source', 'language', 'value', 'uid']
list_filter = ['language']
list_actions = [ExportAsPO()]
field_attributes = {'language': {'default': default_language}}
@classmethod
def translate(cls, source, language):
"""Translate source to language, return None if no translation is found"""
if source:
key = (source, language)
if key in cls._cache:
return cls._cache[key]
translation = cls.query.filter_by(
source=unicode(source),
language=language).filter(Translation.uid != 0).first()
if translation:
cls._cache[key] = translation.value
return translation.value
return None
return ''
@classmethod
def translate_or_register(cls, source, language):
"""Translate source to language, if no translation is found, register the
source as to be translated and return the source"""
if source:
source = unicode(source)
translation = cls.translate(source, language)
if not translation:
if not cls.query.filter_by(source=source,
language=language).first():
if (source, language) not in cls._cache:
from elixir import session
registered_translation = Translation(source=source,
language=language)
cls._cache[(source, language)] = source
session.flush([registered_translation])
logger.debug('registed %s with id %s' %
(source, registered_translation.id))
return source
return translation
return ''
def ReadAndWriteSQL(engine):
engine = DB_Connect()
sql_str = 'SELECT * FROM user;'
df = pd.read_sql(sql=sql_str, con=engine) #从表中读数据
print(df)
df.to_sql(name='test2',
con=engine,
if_exists='append',
index=False,
dtype={
'id': INT(),
'name': CHAR(length=255),
'age': INT()
}) #把数据存进表中，并创建表
print(df)
def init_month_matrix_basic():
table_name = 'stock_month_matrix_basic'
sql = 'select * from trade_date where m != 0 ;'
yms = pd.read_sql_query(sql, get_engine())
df = None
for i, row in yms.iterrows():
first_trade_date_str = row['first'].strftime('%Y%m%d')
last_last_date_str = row['last'].strftime('%Y%m%d')
data = __pro.daily_basic(ts_code='', trade_date=last_last_date_str)
print(last_last_date_str)
if df is None:
df = data
else:
df = df.append(data)
df_add_y_m(df, 'trade_date')
df.reset_index(drop=True)
df = df.iloc[::-1]
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'close': FLOAT(),
'y': INT(),
'm': INT(),
'turnover_rate': FLOAT(),
'turnover_rate_f': FLOAT(),
'volume_ratio': FLOAT(),
'pe': FLOAT(),
'pe_ttm': FLOAT(),
'pb': FLOAT(),
'ps': FLOAT(),
'ps_ttm': FLOAT(),
'dv_ratio': FLOAT(),
'dv_ttm': FLOAT(),
'total_share': FLOAT(),
'float_share': FLOAT(),
'free_share': FLOAT(),
'total_mv': FLOAT(),
'circ_mv': FLOAT()
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
class Dividend(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(), 'm': INT(), 'ann_date': DATE(),
'div_proc': VARCHAR(length=10),
'stk_div': Float(precision=53), 'stk_bo_rate': Float(precision=53), 'stk_co_rate': Float(precision=53),
'cash_div': Float(precision=53),
'cash_div_tax': Float(precision=53), 'record_date': DATE(), 'ex_date': DATE(), 'pay_date': DATE(),
'div_listdate': VARCHAR(length=10),
'imp_ann_date': DATE(), 'base_date': DATE(), 'base_share': Float(precision=53)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'dividend'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
def _clean(self):
self._df = self._df[self._df['div_proc'].str.contains('实施')]
def _add_y_m(self):
self._add_y()
def _second_process(self):
table_name = self._interface + '_stat'
df = self._df
grouped = df.groupby('y')
r = grouped[['stk_div', 'cash_div']].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'})
r = r.sort_values(by=['y'], ascending=False)
data = {'ts_code': np.full((len(r)), self._key), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]}
df = pd.DataFrame(data)
dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)}
df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
def __init__(self):
self.game_dtype = {
'datetime': DATETIME,
'change_time': VARCHAR(20),
'company': VARCHAR(50),
'game_id': INT(),
'line': VARCHAR(20),
'odds_away': FLOAT(5, 2),
'odds_home': FLOAT(5, 2),
'score': VARCHAR(20),
'odds_down': FLOAT(5, 2),
'odds_over': FLOAT(5, 2),
'score_away': VARCHAR(20),
'score_home': VARCHAR(20),
'team_away': VARCHAR(20),
'team_away_f': VARCHAR(20),
'team_away_id': INT(),
'team_away_rank': VARCHAR(20),
'team_home': VARCHAR(20),
'team_home_f': VARCHAR(20),
'team_home_id': INT(),
'team_home_rank': VARCHAR(20),
'grr': FLOAT(5, 2),
'kelly_away': FLOAT(5, 2),
'kelly_home': FLOAT(5, 2),
'kelly_tie': FLOAT(5, 2),
'odds_away': FLOAT(5, 2),
'odds_home': FLOAT(5, 2),
'odds_tie': FLOAT(5, 2),
'probability_away': FLOAT(5, 2),
'probability_home': FLOAT(5, 2),
'probability_tie': FLOAT(5, 2),
'team_home_en': VARCHAR(50),
'team_away_en': VARCHAR(50),
'league': VARCHAR(20),
'league_f': VARCHAR(20),
'league_en': VARCHAR(20),
'league_id': INT(),
}
def insert_data(self, dataframe):
self.logger.info('create engine and connect to database')
engine = sqlalchemy.create_engine(
f'{self.database_type}://{self.database_login}:{self.database_password}@{self.database_url}/{self.database_name}'
)
self.logger.info('prepare to insert data')
dataframe.to_sql('etl_hh',
schema='adhoc_parser',
con=engine,
if_exists='append',
index=False,
dtype={
'id': INT(),
'name': String(255),
'has_test': Boolean(),
'published_at': DateTime(),
'created_at': DateTime(),
'url': String(255),
'area_name': String(255),
'salary_from': INT(),
'salary_to': INT(),
'salary_currency': String(10),
'salary.gross': Boolean(),
'address.city': String(255),
'address.street': String(255),
'address_building': String(255),
'address_raw': String(500),
'metro_name': String(255),
'employer_id': INT(),
'employer_name': String(255),
'snippet_requirement': TEXT(),
'snippet_responsibility': TEXT(),
'contacts_name': String(255),
'contacts_email': String(255),
})
self.logger.warning('data are inserted now')
def _second_process(self):
table_name = self._interface + '_stat'
df = self._df
grouped = df.groupby('y')
r = grouped[['stk_div', 'cash_div']].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'})
r = r.sort_values(by=['y'], ascending=False)
data = {'ts_code': np.full((len(r)), self._key), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]}
df = pd.DataFrame(data)
dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)}
df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
class FixtureVersion(Entity):
"""Keep track of the version the fixtures have in the current database, the subversion
revision number is a good candidate to be used as a fixture version.
:return: an integer representing the current version, 0 if no version found
"""
using_options(tablename='fixture_version')
fixture_version = Field(INT(), index=True, required=True, default=0)
fixture_class = Field(Unicode(256),
index=True,
required=False,
unique=True)
@classmethod
def get_current_version(cls, fixture_class=None):
"""Get the current version of the fixtures in the database for a certain
fixture class.
:param fixture_class: the fixture class for which to get the version
"""
obj = cls.query.filter_by(fixture_class=fixture_class).first()
if obj:
return obj.fixture_version
return 0
@classmethod
def set_current_version(cls, fixture_class=None, fixture_version=0):
"""Set the current version of the fixtures in the database for a certain
fixture class.
:param fixture_class: the fixture class for which to get the version
:param fixture_version: the version number to which to set the fixture version
"""
from sqlalchemy.orm.session import Session
obj = cls.query.filter_by(fixture_class=fixture_class).first()
if not obj:
obj = FixtureVersion(fixture_class=fixture_class)
obj.fixture_version = fixture_version
Session.object_session(obj).flush([obj])
class Memento( Entity ):
"""Keeps information on the previous state of objects, to keep track
of changes and enable restore to that previous state"""
using_options( tablename = 'memento' )
model = Field( Unicode( 256 ), index = True, required = True )
primary_key = Field( INT(), index = True, required = True )
creation_date = Field( DateTime(), default = datetime.datetime.now )
authentication = ManyToOne( 'AuthenticationMechanism',
required = True,
ondelete = 'restrict',
onupdate = 'cascade' )
description = property( lambda self:'Change' )
class Admin( EntityAdmin ):
verbose_name = _( 'History' )
verbose_name_plural = _( 'History' )
list_display = ['creation_date',
'authentication',
'model',
'primary_key',
'description']
list_filter = [filters.ComboBoxFilter('model')]
df = df_raw.dropna()
df.to_sql(con=engine,
name=instrument,
if_exists='replace',
index_label='ID',
chunksize=10000,
dtype={
'DATE': DATE(),
'TIME': TIME(),
'OPEN': DECIMAL(6, 5),
'HIGH': DECIMAL(6, 5),
'LOW': DECIMAL(6, 5),
'CLOSE': DECIMAL(6, 5),
'TICKVOL': INT(),
'RETURN1': DECIMAL(6, 5),
'RETURN5': DECIMAL(6, 5),
'RETURN15': DECIMAL(6, 5),
'RETURN60': DECIMAL(6, 5),
'RETURN240': DECIMAL(6, 5),
'RETURN1440': DECIMAL(6, 5),
'RETURN4320': DECIMAL(6, 5),
'PCTL15': DECIMAL(6, 5),
'PCTL60': DECIMAL(6, 5),
'PCTL240': DECIMAL(6, 5),
'PCTL1440': DECIMAL(6, 5),
'PCTL4320': DECIMAL(6, 5),
'MA200DIF': DECIMAL(6, 5),
'MA1260DIF': DECIMAL(6, 5),
'MA3000DIF': DECIMAL(6, 5),
r = r.reset_index()
r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'})
r = r.sort_values(by=['y'], ascending=False)
data = {'ts_code': np.full((len(r)), self._biz_code), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]}
df = pd.DataFrame(data)
dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)}
df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
_type_mapping = {
'balancesheet': {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'end_date': DATE(),
'y': INT(),
'm': INT(),
'report_type': VARCHAR(length=1), 'comp_type': VARCHAR(length=1),
'total_share': Float(precision=53),
'cap_rese': Float(precision=53), 'undistr_porfit': Float(precision=53),
'surplus_rese': Float(precision=53),
'special_rese': Float(precision=53), 'money_cap': Float(precision=53),
'trad_asset': Float(precision=53),
'notes_receiv': Float(precision=53), 'accounts_receiv': Float(precision=53),
'oth_receiv': Float(precision=53),
'prepayment': Float(precision=53), 'div_receiv': Float(precision=53),
'int_receiv': Float(precision=53),
'inventories': Float(precision=53), 'amor_exp': Float(precision=53),
'nca_within_1y': Float(precision=53),
'sett_rsrv': Float(precision=53), 'loanto_oth_bank_fi': Float(precision=53),
'premium_receiv': Float(precision=53),
def init_fina_indicator(ts_code, force=None):
table_name = 'stock_fina_indicator'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'end_date': DATE(),
'y': INT(),
'm': INT(),
'eps': FLOAT(),
'dt_eps': FLOAT(),
'total_revenue_ps': FLOAT(),
'revenue_ps': FLOAT(),
'capital_rese_ps': FLOAT(),
'surplus_rese_ps': FLOAT(),
'undist_profit_ps': FLOAT(),
'extra_item': FLOAT(),
'profit_dedt': FLOAT(),
'gross_margin': FLOAT(),
'current_ratio': FLOAT(),
'quick_ratio': FLOAT(),
'cash_ratio': FLOAT(),
'invturn_days': FLOAT(),
'arturn_days': FLOAT(),
'inv_turn': FLOAT(),
'ar_turn': FLOAT(),
'ca_turn': FLOAT(),
'fa_turn': FLOAT(),
'assets_turn': FLOAT(),
'op_income': FLOAT(),
'valuechange_income': FLOAT(),
'interst_income': FLOAT(),
'daa': FLOAT(),
'ebit': FLOAT(),
'ebitda': FLOAT(),
'fcff': FLOAT(),
'fcfe': FLOAT(),
'current_exint': FLOAT(),
'noncurrent_exint': FLOAT(),
'interestdebt': FLOAT(),
'netdebt': FLOAT(),
'tangible_asset': FLOAT(),
'working_capital': FLOAT(),
'networking_capital': FLOAT(),
'invest_capital': FLOAT(),
'retained_earnings': FLOAT(),
'diluted2_eps': FLOAT(),
'bps': FLOAT(),
'ocfps': FLOAT(),
'retainedps': FLOAT(),
'cfps': FLOAT(),
'ebit_ps': FLOAT(),
'fcff_ps': FLOAT(),
'fcfe_ps': FLOAT(),
'netprofit_margin': FLOAT(),
'grossprofit_margin': FLOAT(),
'cogs_of_sales': FLOAT(),
'expense_of_sales': FLOAT(),
'profit_to_gr': FLOAT(),
'saleexp_to_gr': FLOAT(),
'adminexp_of_gr': FLOAT(),
'finaexp_of_gr': FLOAT(),
'impai_ttm': FLOAT(),
'gc_of_gr': FLOAT(),
'op_of_gr': FLOAT(),
'ebit_of_gr': FLOAT(),
'roe': FLOAT(),
'roe_waa': FLOAT(),
'roe_dt': FLOAT(),
'roa': FLOAT(),
'npta': FLOAT(),
'roic': FLOAT(),
'roe_yearly': FLOAT(),
'roa2_yearly': FLOAT(),
'roe_avg': FLOAT(),
'opincome_of_ebt': FLOAT(),
'investincome_of_ebt': FLOAT(),
'n_op_profit_of_ebt': FLOAT(),
'tax_to_ebt': FLOAT(),
'dtprofit_to_profit': FLOAT(),
'salescash_to_or': FLOAT(),
'ocf_to_or': FLOAT(),
'ocf_to_opincome': FLOAT(),
'capitalized_to_da': FLOAT(),
'debt_to_assets': FLOAT(),
'assets_to_eqt': FLOAT(),
'dp_assets_to_eqt': FLOAT(),
'ca_to_assets': FLOAT(),
'nca_to_assets': FLOAT(),
'tbassets_to_totalassets': FLOAT(),
'int_to_talcap': FLOAT(),
'eqt_to_talcapital': FLOAT(),
'currentdebt_to_debt': FLOAT(),
'longdeb_to_debt': FLOAT(),
'ocf_to_shortdebt': FLOAT(),
'debt_to_eqt': FLOAT(),
'eqt_to_debt': FLOAT(),
'eqt_to_interestdebt': FLOAT(),
'tangibleasset_to_debt': FLOAT(),
'tangasset_to_intdebt': FLOAT(),
'tangibleasset_to_netdebt': FLOAT(),
'ocf_to_debt': FLOAT(),
'ocf_to_interestdebt': FLOAT(),
'ocf_to_netdebt': FLOAT(),
'ebit_to_interest': FLOAT(),
'longdebt_to_workingcapital': FLOAT(),
'ebitda_to_debt': FLOAT(),
'turn_days': FLOAT(),
'roa_yearly': FLOAT(),
'roa_dp': FLOAT(),
'fixed_assets': FLOAT(),
'profit_prefin_exp': FLOAT(),
'non_op_profit': FLOAT(),
'op_to_ebt': FLOAT(),
'nop_to_ebt': FLOAT(),
'ocf_to_profit': FLOAT(),
'cash_to_liqdebt': FLOAT(),
'cash_to_liqdebt_withinterest': FLOAT(),
'op_to_liqdebt': FLOAT(),
'op_to_debt': FLOAT(),
'roic_yearly': FLOAT(),
'total_fa_trun': FLOAT(),
'profit_to_op': FLOAT(),
'q_opincome': FLOAT(),
'q_investincome': FLOAT(),
'q_dtprofit': FLOAT(),
'q_eps': FLOAT(),
'q_netprofit_margin': FLOAT(),
'q_gsprofit_margin': FLOAT(),
'q_exp_to_sales': FLOAT(),
'q_profit_to_gr': FLOAT(),
'q_saleexp_to_gr': FLOAT(),
'q_adminexp_to_gr': FLOAT(),
'q_finaexp_to_gr': FLOAT(),
'q_impair_to_gr_ttm': FLOAT(),
'q_gc_to_gr': FLOAT(),
'q_op_to_gr': FLOAT(),
'q_roe': FLOAT(),
'q_dt_roe': FLOAT(),
'q_npta': FLOAT(),
'q_opincome_to_ebt': FLOAT(),
'q_investincome_to_ebt': FLOAT(),
'q_dtprofit_to_profit': FLOAT(),
'q_salescash_to_or': FLOAT(),
'q_ocf_to_sales': FLOAT(),
'q_ocf_to_or': FLOAT(),
'basic_eps_yoy': FLOAT(),
'dt_eps_yoy': FLOAT(),
'cfps_yoy': FLOAT(),
'op_yoy': FLOAT(),
'ebt_yoy': FLOAT(),
'netprofit_yoy': FLOAT(),
'dt_netprofit_yoy': FLOAT(),
'ocf_yoy': FLOAT(),
'roe_yoy': FLOAT(),
'bps_yoy': FLOAT(),
'assets_yoy': FLOAT(),
'eqt_yoy': FLOAT(),
'tr_yoy': FLOAT(),
'or_yoy': FLOAT(),
'q_gr_yoy': FLOAT(),
'q_gr_qoq': FLOAT(),
'q_sales_yoy': FLOAT(),
'q_sales_qoq': FLOAT(),
'q_op_yoy': FLOAT(),
'q_op_qoq': FLOAT(),
'q_profit_yoy': FLOAT(),
'q_profit_qoq': FLOAT(),
'q_netprofit_yoy': FLOAT(),
'q_netprofit_qoq': FLOAT(),
'equity_yoy': FLOAT(),
'rd_exp': FLOAT(),
'update_flag': VARCHAR(length=1)
}
columns = '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,update_flag'
df = __pro.fina_indicator(ts_code=ts_code,
start_date='19901201',
end_date='20210101',
columns=columns)
if len(df) == 0:
print('=' * 32, 'code:{}'.format(ts_code))
print('error exit middle')
exit(4)
# clean
# df = df.drop_duplicates(["end_date"], keep="first")
df = drop_more_nan_row(df, 'end_date')
df_add_y_m(df, 'end_date')
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_cashflow(ts_code, force=None):
table_name = 'stock_cashflow'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'f_ann_date': DATE(),
'y': INT(),
'm': INT(),
'end_date': DATE(),
'comp_type': VARCHAR(length=1),
'report_type': VARCHAR(length=1),
'net_profit': BIGINT(),
'finan_exp': BIGINT(),
'c_fr_sale_sg': BIGINT(),
'recp_tax_rends': BIGINT(),
'n_depos_incr_fi': BIGINT(),
'n_incr_loans_cb': BIGINT(),
'n_inc_borr_oth_fi': BIGINT(),
'prem_fr_orig_contr': BIGINT(),
'n_incr_insured_dep': BIGINT(),
'n_reinsur_prem': BIGINT(),
'n_incr_disp_tfa': BIGINT(),
'ifc_cash_incr': BIGINT(),
'n_incr_disp_faas': BIGINT(),
'n_incr_loans_oth_bank': BIGINT(),
'n_cap_incr_repur': BIGINT(),
'c_fr_oth_operate_a': BIGINT(),
'c_inf_fr_operate_a': BIGINT(),
'c_paid_goods_s': BIGINT(),
'c_paid_to_for_empl': BIGINT(),
'c_paid_for_taxes': BIGINT(),
'n_incr_clt_loan_adv': BIGINT(),
'n_incr_dep_cbob': BIGINT(),
'c_pay_claims_orig_inco': BIGINT(),
'pay_handling_chrg': BIGINT(),
'pay_comm_insur_plcy': BIGINT(),
'oth_cash_pay_oper_act': BIGINT(),
'st_cash_out_act': BIGINT(),
'n_cashflow_act': BIGINT(),
'oth_recp_ral_inv_act': BIGINT(),
'c_disp_withdrwl_invest': BIGINT(),
'c_recp_return_invest': BIGINT(),
'n_recp_disp_fiolta': BIGINT(),
'n_recp_disp_sobu': BIGINT(),
'stot_inflows_inv_act': BIGINT(),
'c_pay_acq_const_fiolta': BIGINT(),
'c_paid_invest': BIGINT(),
'n_disp_subs_oth_biz': BIGINT(),
'oth_pay_ral_inv_act': BIGINT(),
'n_incr_pledge_loan': BIGINT(),
'stot_out_inv_act': BIGINT(),
'n_cashflow_inv_act': BIGINT(),
'c_recp_borrow': BIGINT(),
'proc_issue_bonds': BIGINT(),
'oth_cash_recp_ral_fnc_act': BIGINT(),
'stot_cash_in_fnc_act': BIGINT(),
'free_cashflow': BIGINT(),
'c_prepay_amt_borr': BIGINT(),
'c_pay_dist_dpcp_int_exp': BIGINT(),
'incl_dvd_profit_paid_sc_ms': BIGINT(),
'oth_cashpay_ral_fnc_act': BIGINT(),
'stot_cashout_fnc_act': BIGINT(),
'n_cash_flows_fnc_act': BIGINT(),
'eff_fx_flu_cash': BIGINT(),
'n_incr_cash_cash_equ': BIGINT(),
'c_cash_equ_beg_period': BIGINT(),
'c_cash_equ_end_period': BIGINT(),
'c_recp_cap_contrib': BIGINT(),
'incl_cash_rec_saims': BIGINT(),
'uncon_invest_loss': BIGINT(),
'prov_depr_assets': BIGINT(),
'depr_fa_coga_dpba': BIGINT(),
'amort_intang_assets': BIGINT(),
'lt_amort_deferred_exp': BIGINT(),
'decr_deferred_exp': BIGINT(),
'incr_acc_exp': BIGINT(),
'loss_disp_fiolta': BIGINT(),
'loss_scr_fa': BIGINT(),
'loss_fv_chg': BIGINT(),
'invest_loss': BIGINT(),
'decr_def_inc_tax_assets': BIGINT(),
'incr_def_inc_tax_liab': BIGINT(),
'decr_inventories': BIGINT(),
'decr_oper_payable': BIGINT(),
'incr_oper_payable': BIGINT(),
'others': BIGINT(),
'im_net_cashflow_oper_act': BIGINT(),
'conv_debt_into_cap': BIGINT(),
'conv_copbonds_due_within_1y': BIGINT(),
'fa_fnc_leases': BIGINT(),
'end_bal_cash': BIGINT(),
'beg_bal_cash': BIGINT(),
'end_bal_cash_equ': BIGINT(),
'beg_bal_cash_equ': BIGINT(),
'im_n_incr_cash_equ': BIGINT()
}
df = __pro.cashflow(ts_code=ts_code,
start_date='19901201',
end_date='20210101')
# clean
# df = df.drop_duplicates(["end_date"], keep="first")
df = drop_more_nan_row(df, 'end_date')
df_add_y_m(df, 'end_date')
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_income(ts_code, force=None):
table_name = 'stock_income'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} '.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'f_ann_date': DATE(),
'y': INT(),
'm': INT(),
'end_date': DATE(),
'report_type': VARCHAR(length=1),
'comp_type': VARCHAR(length=1),
'basic_eps': FLOAT(),
'diluted_eps': FLOAT(),
'total_revenue': FLOAT(),
'revenue': FLOAT(),
'int_income': FLOAT(),
'prem_earned': FLOAT(),
'comm_income': FLOAT(),
'n_commis_income': FLOAT(),
'n_oth_income': FLOAT(),
'n_oth_b_income': FLOAT(),
'prem_income': FLOAT(),
'out_prem': FLOAT(),
'une_prem_reser': FLOAT(),
'reins_income': FLOAT(),
'n_sec_tb_income': FLOAT(),
'n_sec_uw_income': FLOAT(),
'n_asset_mg_income': FLOAT(),
'oth_b_income': FLOAT(),
'fv_value_chg_gain': FLOAT(),
'invest_income': FLOAT(),
'ass_invest_income': FLOAT(),
'forex_gain': FLOAT(),
'total_cogs': FLOAT(),
'oper_cost': FLOAT(),
'int_exp': FLOAT(),
'comm_exp': FLOAT(),
'biz_tax_surchg': FLOAT(),
'sell_exp': FLOAT(),
'admin_exp': FLOAT(),
'fin_exp': FLOAT(),
'assets_impair_loss': FLOAT(),
'prem_refund': FLOAT(),
'compens_payout': FLOAT(),
'reser_insur_liab': FLOAT(),
'div_payt': FLOAT(),
'reins_exp': FLOAT(),
'oper_exp': FLOAT(),
'compens_payout_refu': FLOAT(),
'insur_reser_refu': FLOAT(),
'reins_cost_refund': FLOAT(),
'other_bus_cost': FLOAT(),
'operate_profit': FLOAT(),
'non_oper_income': FLOAT(),
'non_oper_exp': FLOAT(),
'nca_disploss': FLOAT(),
'total_profit': FLOAT(),
'income_tax': FLOAT(),
'n_income': FLOAT(),
'n_income_attr_p': FLOAT(),
'minority_gain': FLOAT(),
'oth_compr_income': FLOAT(),
't_compr_income': FLOAT(),
'compr_inc_attr_p': FLOAT(),
'compr_inc_attr_m_s': FLOAT(),
'ebit': FLOAT(),
'ebitda': FLOAT(),
'insurance_exp': FLOAT(),
'undist_profit': FLOAT(),
'distable_profit': FLOAT(),
'update_flag': VARCHAR(length=1)
}
df = __pro.income(ts_code=ts_code,
start_date='19901201',
end_date='20210101')
# clean
df = drop_more_nan_row(df, 'end_date')
# format
df_add_y_m(df, 'end_date')
#
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_balancesheet(ts_code, force=None):
table_name = 'stock_balancesheet'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'f_ann_date': DATE(),
'y': INT(),
'm': INT(),
'end_date': DATE(),
'report_type': VARCHAR(length=1),
'comp_type': VARCHAR(length=1),
'total_share': BIGINT(),
'cap_rese': BIGINT(),
'undistr_porfit': BIGINT(),
'surplus_rese': BIGINT(),
'special_rese': BIGINT(),
'money_cap': BIGINT(),
'trad_asset': BIGINT(),
'notes_receiv': BIGINT(),
'accounts_receiv': BIGINT(),
'oth_receiv': BIGINT(),
'prepayment': BIGINT(),
'div_receiv': BIGINT(),
'int_receiv': BIGINT(),
'inventories': BIGINT(),
'amor_exp': BIGINT(),
'nca_within_1y': BIGINT(),
'sett_rsrv': BIGINT(),
'loanto_oth_bank_fi': BIGINT(),
'premium_receiv': BIGINT(),
'reinsur_receiv': BIGINT(),
'reinsur_res_receiv': BIGINT(),
'pur_resale_fa': BIGINT(),
'oth_cur_assets': BIGINT(),
'total_cur_assets': BIGINT(),
'fa_avail_for_sale': BIGINT(),
'htm_invest': BIGINT(),
'lt_eqt_invest': BIGINT(),
'invest_real_estate': BIGINT(),
'time_deposits': BIGINT(),
'oth_assets': BIGINT(),
'lt_rec': BIGINT(),
'fix_assets': BIGINT(),
'cip': BIGINT(),
'const_materials': BIGINT(),
'fixed_assets_disp': BIGINT(),
'produc_bio_assets': BIGINT(),
'oil_and_gas_assets': BIGINT(),
'intan_assets': BIGINT(),
'r_and_d': BIGINT(),
'goodwill': BIGINT(),
'lt_amor_exp': BIGINT(),
'defer_tax_assets': BIGINT(),
'decr_in_disbur': BIGINT(),
'oth_nca': BIGINT(),
'total_nca': BIGINT(),
'cash_reser_cb': BIGINT(),
'depos_in_oth_bfi': BIGINT(),
'prec_metals': BIGINT(),
'deriv_assets': BIGINT(),
'rr_reins_une_prem': BIGINT(),
'rr_reins_outstd_cla': BIGINT(),
'rr_reins_lins_liab': BIGINT(),
'rr_reins_lthins_liab': BIGINT(),
'refund_depos': BIGINT(),
'ph_pledge_loans': BIGINT(),
'refund_cap_depos': BIGINT(),
'indep_acct_assets': BIGINT(),
'client_depos': BIGINT(),
'client_prov': BIGINT(),
'transac_seat_fee': BIGINT(),
'invest_as_receiv': BIGINT(),
'total_assets': BIGINT(),
'lt_borr': BIGINT(),
'st_borr': BIGINT(),
'cb_borr': BIGINT(),
'depos_ib_deposits': BIGINT(),
'loan_oth_bank': BIGINT(),
'trading_fl': BIGINT(),
'notes_payable': BIGINT(),
'acct_payable': BIGINT(),
'adv_receipts': BIGINT(),
'sold_for_repur_fa': BIGINT(),
'comm_payable': BIGINT(),
'payroll_payable': BIGINT(),
'taxes_payable': BIGINT(),
'int_payable': BIGINT(),
'div_payable': BIGINT(),
'oth_payable': BIGINT(),
'acc_exp': BIGINT(),
'deferred_inc': BIGINT(),
'st_bonds_payable': BIGINT(),
'payable_to_reinsurer': BIGINT(),
'rsrv_insur_cont': BIGINT(),
'acting_trading_sec': BIGINT(),
'acting_uw_sec': BIGINT(),
'non_cur_liab_due_1y': BIGINT(),
'oth_cur_liab': BIGINT(),
'total_cur_liab': BIGINT(),
'bond_payable': BIGINT(),
'lt_payable': BIGINT(),
'specific_payables': BIGINT(),
'estimated_liab': BIGINT(),
'defer_tax_liab': BIGINT(),
'defer_inc_non_cur_liab': BIGINT(),
'oth_ncl': BIGINT(),
'total_ncl': BIGINT(),
'depos_oth_bfi': BIGINT(),
'deriv_liab': BIGINT(),
'depos': BIGINT(),
'agency_bus_liab': BIGINT(),
'oth_liab': BIGINT(),
'prem_receiv_adva': BIGINT(),
'depos_received': BIGINT(),
'ph_invest': BIGINT(),
'reser_une_prem': BIGINT(),
'reser_outstd_claims': BIGINT(),
'reser_lins_liab': BIGINT(),
'reser_lthins_liab': BIGINT(),
'indept_acc_liab': BIGINT(),
'pledge_borr': BIGINT(),
'indem_payable': BIGINT(),
'policy_div_payable': BIGINT(),
'total_liab': BIGINT(),
'treasury_share': BIGINT(),
'ordin_risk_reser': BIGINT(),
'forex_differ': BIGINT(),
'invest_loss_unconf': BIGINT(),
'minority_int': BIGINT(),
'total_hldr_eqy_exc_min_int': BIGINT(),
'total_hldr_eqy_inc_min_int': BIGINT(),
'total_liab_hldr_eqy': BIGINT(),
'lt_payroll_payable': BIGINT(),
'oth_comp_income': BIGINT(),
'oth_eqt_tools': BIGINT(),
'oth_eqt_tools_p_shr': BIGINT(),
'lending_funds': BIGINT(),
'acc_receivable': BIGINT(),
'st_fin_payable': BIGINT(),
'payables': BIGINT(),
'hfs_assets': BIGINT(),
'hfs_sales': BIGINT(),
'update_flag': VARCHAR(length=1)
}
# call
df = __pro.balancesheet(ts_code=ts_code,
start_date='19901201',
end_date='20210101')
# clean
# df = df.drop_duplicates(["end_date"], keep="first")
df = drop_more_nan_row(df, 'end_date')
# format
df_add_y_m(df, 'end_date')
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_dividend(ts_code, force=None):
table_name = 'stock_dividend_detail'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
fields = 'ts_code,end_date,ann_date,div_proc,stk_div,stk_bo_rate,stk_co_rate,cash_div,cash_div_tax,record_date,ex_date,pay_date,div_listdate,imp_ann_date,base_date,base_share'
df = __pro.dividend(ts_code=ts_code, fields=fields)
df = df[df['div_proc'].str.contains('实施')]
df_add_y(df, 'end_date')
df.reset_index(drop=True)
dtype = {
'ts_code': VARCHAR(length=10),
'end_date': DATE(),
'div_proc': VARCHAR(length=10),
'stk_div': DECIMAL(precision=10, scale=8),
'cash_div': DECIMAL(precision=12, scale=8),
'ex_date': DATE(),
'y': INT()
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
'''
statistical
'''
table_name = 'stock_dividend'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
grouped = df.groupby('y')
r = grouped['stk_div', 'cash_div'].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={
('stk_div', 'sum'): 'stk_div',
('cash_div', 'sum'): 'cash_div',
('y'): 'y'
})
r = r.sort_values(by=['y'], ascending=False)
data = {
'ts_code': np.full((len(r)), ts_code),
'y': r['y'],
'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]
}
df = pd.DataFrame(data)
dtype = {
'ts_code': VARCHAR(length=10),
'end_date': DATE(),
'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8),
'cash_div': DECIMAL(precision=12, scale=8)
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_trade_date():
template_start = '{}00101'
template_end = '{}91231'
data = None
for i in range(4):
print(i)
t = 199 + i
start, end = template_start.format(t), template_end.format(t)
df = __pro.query('trade_cal', start_date=start, end_date=end)
if data is not None:
data = data.append(df, ignore_index=True)
else:
data = df
print('start:{},date:{}'.format(start, len(data)))
# data.to_sql('trade_date_o', get_engine(), if_exists='replace', schema=db_name)
df = data
df_add_y_m(df, 'cal_date')
# df['y'] = df['cal_date'].apply(lambda s: int(s[:4]))
# df['m'] = df['cal_date'].apply(lambda s: int(s[4:6]))
df.set_index(['y', 'm', 'cal_date'])
df = df[df['is_open'] == 1]
df = df.reindex(columns=['y', 'm', 'cal_date', 'is_open', 'exchange'])
df.to_sql(
'trade_date_detail',
get_engine(),
index=False,
dtype={
'cal_date': DATE(),
'y': Integer(),
'm': INT(),
'is_open': INT(),
'exchange': VARCHAR(8)
},
# dtype={'cal_date': 'M8[d]'},
if_exists='replace')
'''
分组插入扩展表
'''
grouped_m = df.groupby(['y', 'm'])
# for a, g in grouped_m:
# print(a)
# print(g)
r1 = grouped_m['cal_date'].agg([np.min, np.max])
r1 = r1.rename(columns={'amin': 'first', 'amax': 'last'})
r1['y'] = pd.Series(r1.index.get_level_values('y'), index=r1.index)
r1['m'] = pd.Series(r1.index.get_level_values('m'), index=r1.index)
grouped_m = df.groupby(['y'])
r2 = grouped_m['cal_date'].agg([np.min, np.max])
r2 = r2.rename(columns={'amin': 'first', 'amax': 'last'})
r2['y'] = pd.Series(r2.index.get_level_values('y'), index=r2.index)
r2['m'] = pd.Series(np.zeros(len(r2)), index=r2.index)
r = r1.append(r2, ignore_index=True)
r = r.reindex(columns=['y', 'm', 'first', 'last'])
r.to_sql('trade_date',
get_engine(),
index=False,
dtype={
'first': DATE(),
'last': DATE(),
'y': Integer(),
'm': INT()
},
if_exists='replace')
class Translation(Entity):
"""Table to store user generated translations or customization.
"""
__tablename__ = 'translation'
language = Column(camelot.types.Language, index=True, nullable=False)
source = Column(Unicode(500), index=True, nullable=False)
# value needs to be indexed as well, because when starting up we
# want to load only the translations that have a value specified
value = Column(Unicode(500), index=True)
cid = Column(INT(), default=0, index=True)
uid = Column(INT(), default=0, index=True)
# cache, to prevent too much of the same sql queries
_cache = dict()
class Admin(EntityAdmin):
verbose_name_plural = _('Translations')
form_size = (700, 150)
list_display = ['source', 'language', 'value', 'uid']
list_filter = ['language']
list_actions = [ExportAsPO()]
field_attributes = {'language': {'default': default_language}}
@classmethod
def translate(cls, source, language):
"""Translate source to language, return None if no translation is found"""
if source:
key = (source, language)
if key in cls._cache:
return cls._cache[key]
query = Session().query(cls)
query = query.filter(
sql.and_(cls.source == unicode(source),
cls.language == language, cls.uid != 0))
translation = query.first()
if translation:
cls._cache[key] = translation.value
return translation.value
return None
return ''
@classmethod
def translate_or_register(cls, source, language):
"""Translate source to language, if no translation is found, register the
source as to be translated and return the source"""
if source:
source = unicode(source)
translation = cls.translate(source, language)
if not translation:
session = Session()
query = session.query(cls)
translation = query.filter_by(source=source,
language=language).first()
if not translation:
if (source, language) not in cls._cache:
registered_translation = Translation(source=source,
language=language)
cls._cache[(source, language)] = source
session.flush([registered_translation])
logger.debug('registed %s with id %s' %
(source, registered_translation.id))
return source
return translation
return ''
class BalanceSheet(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'end_date': DATE(), 'y': INT(),
'm': INT(),
'report_type': VARCHAR(length=1), 'comp_type': VARCHAR(length=1), 'total_share': Float(precision=53),
'cap_rese': Float(precision=53), 'undistr_porfit': Float(precision=53),
'surplus_rese': Float(precision=53),
'special_rese': Float(precision=53), 'money_cap': Float(precision=53), 'trad_asset': Float(precision=53),
'notes_receiv': Float(precision=53), 'accounts_receiv': Float(precision=53),
'oth_receiv': Float(precision=53),
'prepayment': Float(precision=53), 'div_receiv': Float(precision=53), 'int_receiv': Float(precision=53),
'inventories': Float(precision=53), 'amor_exp': Float(precision=53), 'nca_within_1y': Float(precision=53),
'sett_rsrv': Float(precision=53), 'loanto_oth_bank_fi': Float(precision=53),
'premium_receiv': Float(precision=53),
'reinsur_receiv': Float(precision=53), 'reinsur_res_receiv': Float(precision=53),
'pur_resale_fa': Float(precision=53),
'oth_cur_assets': Float(precision=53), 'total_cur_assets': Float(precision=53),
'fa_avail_for_sale': Float(precision=53),
'htm_invest': Float(precision=53), 'lt_eqt_invest': Float(precision=53),
'invest_real_estate': Float(precision=53),
'time_deposits': Float(precision=53), 'oth_assets': Float(precision=53), 'lt_rec': Float(precision=53),
'fix_assets': Float(precision=53), 'cip': Float(precision=53), 'const_materials': Float(precision=53),
'fixed_assets_disp': Float(precision=53), 'produc_bio_assets': Float(precision=53),
'oil_and_gas_assets': Float(precision=53),
'intan_assets': Float(precision=53), 'r_and_d': Float(precision=53), 'goodwill': Float(precision=53),
'lt_amor_exp': Float(precision=53), 'defer_tax_assets': Float(precision=53),
'decr_in_disbur': Float(precision=53),
'oth_nca': Float(precision=53), 'total_nca': Float(precision=53), 'cash_reser_cb': Float(precision=53),
'depos_in_oth_bfi': Float(precision=53), 'prec_metals': Float(precision=53),
'deriv_assets': Float(precision=53),
'rr_reins_une_prem': Float(precision=53), 'rr_reins_outstd_cla': Float(precision=53),
'rr_reins_lins_liab': Float(precision=53), 'rr_reins_lthins_liab': Float(precision=53),
'refund_depos': Float(precision=53),
'ph_pledge_loans': Float(precision=53), 'refund_cap_depos': Float(precision=53),
'indep_acct_assets': Float(precision=53),
'client_depos': Float(precision=53), 'client_prov': Float(precision=53),
'transac_seat_fee': Float(precision=53),
'invest_as_receiv': Float(precision=53), 'total_assets': Float(precision=53),
'lt_borr': Float(precision=53),
'st_borr': Float(precision=53), 'cb_borr': Float(precision=53), 'depos_ib_deposits': Float(precision=53),
'loan_oth_bank': Float(precision=53), 'trading_fl': Float(precision=53),
'notes_payable': Float(precision=53),
'acct_payable': Float(precision=53), 'adv_receipts': Float(precision=53),
'sold_for_repur_fa': Float(precision=53),
'comm_payable': Float(precision=53), 'payroll_payable': Float(precision=53),
'taxes_payable': Float(precision=53),
'int_payable': Float(precision=53), 'div_payable': Float(precision=53), 'oth_payable': Float(precision=53),
'acc_exp': Float(precision=53), 'deferred_inc': Float(precision=53),
'st_bonds_payable': Float(precision=53),
'payable_to_reinsurer': Float(precision=53), 'rsrv_insur_cont': Float(precision=53),
'acting_trading_sec': Float(precision=53),
'acting_uw_sec': Float(precision=53), 'non_cur_liab_due_1y': Float(precision=53),
'oth_cur_liab': Float(precision=53),
'total_cur_liab': Float(precision=53), 'bond_payable': Float(precision=53),
'lt_payable': Float(precision=53),
'specific_payables': Float(precision=53), 'estimated_liab': Float(precision=53),
'defer_tax_liab': Float(precision=53),
'defer_inc_non_cur_liab': Float(precision=53), 'oth_ncl': Float(precision=53),
'total_ncl': Float(precision=53),
'depos_oth_bfi': Float(precision=53), 'deriv_liab': Float(precision=53), 'depos': Float(precision=53),
'agency_bus_liab': Float(precision=53), 'oth_liab': Float(precision=53),
'prem_receiv_adva': Float(precision=53),
'depos_received': Float(precision=53), 'ph_invest': Float(precision=53),
'reser_une_prem': Float(precision=53),
'reser_outstd_claims': Float(precision=53), 'reser_lins_liab': Float(precision=53),
'reser_lthins_liab': Float(precision=53),
'indept_acc_liab': Float(precision=53), 'pledge_borr': Float(precision=53),
'indem_payable': Float(precision=53),
'policy_div_payable': Float(precision=53), 'total_liab': Float(precision=53),
'treasury_share': Float(precision=53),
'ordin_risk_reser': Float(precision=53), 'forex_differ': Float(precision=53),
'invest_loss_unconf': Float(precision=53),
'minority_int': Float(precision=53), 'total_hldr_eqy_exc_min_int': Float(precision=53),
'total_hldr_eqy_inc_min_int': Float(precision=53), 'total_liab_hldr_eqy': Float(precision=53),
'lt_payroll_payable': Float(precision=53), 'oth_comp_income': Float(precision=53),
'oth_eqt_tools': Float(precision=53),
'oth_eqt_tools_p_shr': Float(precision=53), 'lending_funds': Float(precision=53),
'acc_receivable': Float(precision=53),
'st_fin_payable': Float(precision=53), 'payables': Float(precision=53), 'hfs_assets': Float(precision=53),
'hfs_sales': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'balancesheet'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
con.commit()
cursor.close()
con.close()
# SQLAlchemy创建表连接
connect_info = 'mysql+mysqlconnector://' + mySqlUserName + ':' + mySqlPassword + '@' + mySqlAddress + ':3306/' + mySqlDataBase
engine = create_engine(connect_info)
# 插入数据，如果存在则替换
print("Insert data into " + mySqlDataBase + ":" + mySqlTable + "\n")
table_0.to_sql(name= mySqlTable,
con=engine,
if_exists='replace',
index_label='id',
dtype = {
'id': INT(),
'Ticker': VARCHAR(length=10),
'Year': SMALLINT(),
'EPSIE': FLOAT()
}
)
# 读取数据
print("load data from " + mySqlDataBase + ":" + mySqlTable + "\n")
sql = "SELECT Ticker, Year, EPSIE FROM " + mySqlTable
table_0 = pd.read_sql(sql, con=engine)
company_name = np.unique(table_0['Ticker'].tolist())
print(table_0.Year)
time_0 = 2009 #start time
time_T = 2019 #end time
class Fixture(Entity):
"""Keep track of static data loaded into the database"""
using_options(tablename='fixture')
model = Field(Unicode(256), index=True, required=True)
primary_key = Field(INT(), index=True, required=True)
fixture_key = Field(Unicode(256), index=True, required=True)
fixture_class = Field(Unicode(256), index=True, required=False)
@classmethod
def findFixtureReference(cls, entity, fixture_key, fixture_class=None):
entity_name = unicode(entity.__name__)
return cls.query.filter_by(model=unicode(entity_name),
fixture_key=fixture_key,
fixture_class=fixture_class).first()
@classmethod
def findFixture(cls, entity, fixture_key, fixture_class=None):
"""Find a registered fixture, return None if no fixture is found"""
reference = cls.findFixtureReference(entity, fixture_key,
fixture_class)
if reference:
return entity.get(reference.primary_key)
@classmethod
def findFixtureKey(cls, entity, primary_key):
"""Find the fixture key for an object of type entity with primary key
:return: fixture_key
"""
entity_name = unicode(entity.__name__)
fixture = cls.query.filter_by(model=entity_name,
primary_key=primary_key).first()
if fixture:
return fixture.fixture_key
else:
return None
@classmethod
def findFixtureKeyAndClass(cls, obj):
"""Find the fixture key and class of an object
@param obj: the object we are looking for
@return: (fixture_key, fixture_class) if the object is a registered fixture, (None, None) otherwise
"""
entity_name = unicode(obj.__class__.__name__)
fixture = cls.query.filter_by(model=entity_name,
primary_key=obj.id).first()
if fixture:
return (fixture.fixture_key, fixture.fixture_class)
else:
return (None, None)
@classmethod
def findFixtureKeysAndClasses(cls, entity):
"""Load all fixture keys of a certain entity in batch
:param entity: the model class for which the fixtures should be found
:return: a dictionary mapping the primary key of a on object of type entity to its (fixture key, fixture class)
"""
entity_name = unicode(entity.__name__)
return dict(
(fixture.primary_key, (fixture.fixture_key, fixture.fixture_class))
for fixture in cls.query.filter_by(model=entity_name).all())
@classmethod
def insertOrUpdateFixture(cls,
entity,
fixture_key,
values,
fixture_class=None):
from sqlalchemy.orm.session import Session
obj = cls.findFixture(entity, fixture_key, fixture_class)
store_fixture = False
if not obj:
obj = entity()
store_fixture = True
obj.from_dict(values)
Session.object_session(obj).flush([obj])
if store_fixture:
#
# The fixture itself might have been deleted, but the reference might be intact,
# so this should be updated
#
reference = cls.findFixtureReference(entity, fixture_key,
fixture_class)
if not reference:
reference = cls(model=unicode(entity.__name__),
primary_key=obj.id,
fixture_key=fixture_key,
fixture_class=fixture_class)
else:
reference.primary_key = obj.id
Session.object_session(reference).flush([reference])
return obj
@classmethod
def removeAllFixtures(cls, entity):
for fixture_key, fixture_class in cls.findFixtureKeysAndClasses(
entity).values():
cls.removeFixture(entity, fixture_key, fixture_class)
@classmethod
def removeFixture(cls, entity, fixture_key, fixture_class):
"""Remove a fixture from the database"""
# remove the object itself
from sqlalchemy.orm.session import Session
obj = cls.findFixture(entity, fixture_key, fixture_class)
print 'remove', unicode(obj)
obj.delete()
Session.object_session(obj).flush([obj])
# if this succeeeds, remove the reference
reference = cls.findFixtureReference(entity, fixture_key,
fixture_class)
reference.delete()
Session.object_session(reference).flush([reference])
class Income(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'y': INT(), 'm': INT(),
'end_date': DATE(),
'report_type': VARCHAR(length=1), 'comp_type': VARCHAR(length=1), 'basic_eps': Float(precision=53),
'diluted_eps': Float(precision=53), 'total_revenue': Float(precision=53), 'revenue': Float(precision=53),
'int_income': Float(precision=53), 'prem_earned': Float(precision=53), 'comm_income': Float(precision=53),
'n_commis_income': Float(precision=53), 'n_oth_income': Float(precision=53),
'n_oth_b_income': Float(precision=53),
'prem_income': Float(precision=53), 'out_prem': Float(precision=53), 'une_prem_reser': Float(precision=53),
'reins_income': Float(precision=53), 'n_sec_tb_income': Float(precision=53),
'n_sec_uw_income': Float(precision=53),
'n_asset_mg_income': Float(precision=53), 'oth_b_income': Float(precision=53),
'fv_value_chg_gain': Float(precision=53),
'invest_income': Float(precision=53), 'ass_invest_income': Float(precision=53),
'forex_gain': Float(precision=53),
'total_cogs': Float(precision=53), 'oper_cost': Float(precision=53), 'int_exp': Float(precision=53),
'comm_exp': Float(precision=53), 'biz_tax_surchg': Float(precision=53), 'sell_exp': Float(precision=53),
'admin_exp': Float(precision=53), 'fin_exp': Float(precision=53),
'assets_impair_loss': Float(precision=53),
'prem_refund': Float(precision=53), 'compens_payout': Float(precision=53),
'reser_insur_liab': Float(precision=53),
'div_payt': Float(precision=53), 'reins_exp': Float(precision=53), 'oper_exp': Float(precision=53),
'compens_payout_refu': Float(precision=53), 'insur_reser_refu': Float(precision=53),
'reins_cost_refund': Float(precision=53),
'other_bus_cost': Float(precision=53), 'operate_profit': Float(precision=53),
'non_oper_income': Float(precision=53),
'non_oper_exp': Float(precision=53), 'nca_disploss': Float(precision=53),
'total_profit': Float(precision=53),
'income_tax': Float(precision=53), 'n_income': Float(precision=53), 'n_income_attr_p': Float(precision=53),
'minority_gain': Float(precision=53), 'oth_compr_income': Float(precision=53),
't_compr_income': Float(precision=53),
'compr_inc_attr_p': Float(precision=53), 'compr_inc_attr_m_s': Float(precision=53),
'ebit': Float(precision=53),
'ebitda': Float(precision=53), 'insurance_exp': Float(precision=53), 'undist_profit': Float(precision=53),
'distable_profit': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'income'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
class CashFlow(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'y': INT(), 'm': INT(),
'end_date': DATE(),
'comp_type': VARCHAR(length=1), 'report_type': VARCHAR(length=1), 'net_profit': Float(precision=53),
'finan_exp': Float(precision=53), 'c_fr_sale_sg': Float(precision=53),
'recp_tax_rends': Float(precision=53),
'n_depos_incr_fi': Float(precision=53), 'n_incr_loans_cb': Float(precision=53),
'n_inc_borr_oth_fi': Float(precision=53),
'prem_fr_orig_contr': Float(precision=53), 'n_incr_insured_dep': Float(precision=53),
'n_reinsur_prem': Float(precision=53),
'n_incr_disp_tfa': Float(precision=53), 'ifc_cash_incr': Float(precision=53),
'n_incr_disp_faas': Float(precision=53),
'n_incr_loans_oth_bank': Float(precision=53), 'n_cap_incr_repur': Float(precision=53),
'c_fr_oth_operate_a': Float(precision=53), 'c_inf_fr_operate_a': Float(precision=53),
'c_paid_goods_s': Float(precision=53),
'c_paid_to_for_empl': Float(precision=53), 'c_paid_for_taxes': Float(precision=53),
'n_incr_clt_loan_adv': Float(precision=53),
'n_incr_dep_cbob': Float(precision=53), 'c_pay_claims_orig_inco': Float(precision=53),
'pay_handling_chrg': Float(precision=53), 'pay_comm_insur_plcy': Float(precision=53),
'oth_cash_pay_oper_act': Float(precision=53), 'st_cash_out_act': Float(precision=53),
'n_cashflow_act': Float(precision=53),
'oth_recp_ral_inv_act': Float(precision=53), 'c_disp_withdrwl_invest': Float(precision=53),
'c_recp_return_invest': Float(precision=53), 'n_recp_disp_fiolta': Float(precision=53),
'n_recp_disp_sobu': Float(precision=53), 'stot_inflows_inv_act': Float(precision=53),
'c_pay_acq_const_fiolta': Float(precision=53), 'c_paid_invest': Float(precision=53),
'n_disp_subs_oth_biz': Float(precision=53), 'oth_pay_ral_inv_act': Float(precision=53),
'n_incr_pledge_loan': Float(precision=53), 'stot_out_inv_act': Float(precision=53),
'n_cashflow_inv_act': Float(precision=53),
'c_recp_borrow': Float(precision=53), 'proc_issue_bonds': Float(precision=53),
'oth_cash_recp_ral_fnc_act': Float(precision=53), 'stot_cash_in_fnc_act': Float(precision=53),
'free_cashflow': Float(precision=53), 'c_prepay_amt_borr': Float(precision=53),
'c_pay_dist_dpcp_int_exp': Float(precision=53),
'incl_dvd_profit_paid_sc_ms': Float(precision=53), 'oth_cashpay_ral_fnc_act': Float(precision=53),
'stot_cashout_fnc_act': Float(precision=53), 'n_cash_flows_fnc_act': Float(precision=53),
'eff_fx_flu_cash': Float(precision=53), 'n_incr_cash_cash_equ': Float(precision=53),
'c_cash_equ_beg_period': Float(precision=53), 'c_cash_equ_end_period': Float(precision=53),
'c_recp_cap_contrib': Float(precision=53), 'incl_cash_rec_saims': Float(precision=53),
'uncon_invest_loss': Float(precision=53), 'prov_depr_assets': Float(precision=53),
'depr_fa_coga_dpba': Float(precision=53),
'amort_intang_assets': Float(precision=53), 'lt_amort_deferred_exp': Float(precision=53),
'decr_deferred_exp': Float(precision=53), 'incr_acc_exp': Float(precision=53),
'loss_disp_fiolta': Float(precision=53),
'loss_scr_fa': Float(precision=53), 'loss_fv_chg': Float(precision=53), 'invest_loss': Float(precision=53),
'decr_def_inc_tax_assets': Float(precision=53), 'incr_def_inc_tax_liab': Float(precision=53),
'decr_inventories': Float(precision=53), 'decr_oper_payable': Float(precision=53),
'incr_oper_payable': Float(precision=53),
'others': Float(precision=53), 'im_net_cashflow_oper_act': Float(precision=53),
'conv_debt_into_cap': Float(precision=53),
'conv_copbonds_due_within_1y': Float(precision=53), 'fa_fnc_leases': Float(precision=53),
'end_bal_cash': Float(precision=53),
'beg_bal_cash': Float(precision=53), 'end_bal_cash_equ': Float(precision=53),
'beg_bal_cash_equ': Float(precision=53),
'im_n_incr_cash_equ': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'cashflow'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
class FinaIndicator(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'end_date': DATE(), 'y': INT(), 'm': INT(),
'eps': Float(precision=53),
'dt_eps': Float(precision=53), 'total_revenue_ps': Float(precision=53), 'revenue_ps': Float(precision=53),
'capital_rese_ps': Float(precision=53), 'surplus_rese_ps': Float(precision=53),
'undist_profit_ps': Float(precision=53),
'extra_item': Float(precision=53), 'profit_dedt': Float(precision=53), 'gross_margin': Float(precision=53),
'current_ratio': Float(precision=53), 'quick_ratio': Float(precision=53),
'cash_ratio': Float(precision=53),
'invturn_days': Float(precision=53), 'arturn_days': Float(precision=53), 'inv_turn': Float(precision=53),
'ar_turn': Float(precision=53), 'ca_turn': Float(precision=53), 'fa_turn': Float(precision=53),
'assets_turn': Float(precision=53), 'op_income': Float(precision=53),
'valuechange_income': Float(precision=53),
'interst_income': Float(precision=53), 'daa': Float(precision=53), 'ebit': Float(precision=53),
'ebitda': Float(precision=53),
'fcff': Float(precision=53), 'fcfe': Float(precision=53), 'current_exint': Float(precision=53),
'noncurrent_exint': Float(precision=53), 'interestdebt': Float(precision=53),
'netdebt': Float(precision=53),
'tangible_asset': Float(precision=53), 'working_capital': Float(precision=53),
'networking_capital': Float(precision=53),
'invest_capital': Float(precision=53), 'retained_earnings': Float(precision=53),
'diluted2_eps': Float(precision=53),
'bps': Float(precision=53), 'ocfps': Float(precision=53), 'retainedps': Float(precision=53),
'cfps': Float(precision=53),
'ebit_ps': Float(precision=53), 'fcff_ps': Float(precision=53), 'fcfe_ps': Float(precision=53),
'netprofit_margin': Float(precision=53), 'grossprofit_margin': Float(precision=53),
'cogs_of_sales': Float(precision=53),
'expense_of_sales': Float(precision=53), 'profit_to_gr': Float(precision=53),
'saleexp_to_gr': Float(precision=53),
'adminexp_of_gr': Float(precision=53), 'finaexp_of_gr': Float(precision=53),
'impai_ttm': Float(precision=53),
'gc_of_gr': Float(precision=53), 'op_of_gr': Float(precision=53), 'ebit_of_gr': Float(precision=53),
'roe': Float(precision=53), 'roe_waa': Float(precision=53), 'roe_dt': Float(precision=53),
'roa': Float(precision=53),
'npta': Float(precision=53), 'roic': Float(precision=53), 'roe_yearly': Float(precision=53),
'roa2_yearly': Float(precision=53), 'roe_avg': Float(precision=53), 'opincome_of_ebt': Float(precision=53),
'investincome_of_ebt': Float(precision=53), 'n_op_profit_of_ebt': Float(precision=53),
'tax_to_ebt': Float(precision=53),
'dtprofit_to_profit': Float(precision=53), 'salescash_to_or': Float(precision=53),
'ocf_to_or': Float(precision=53),
'ocf_to_opincome': Float(precision=53), 'capitalized_to_da': Float(precision=53),
'debt_to_assets': Float(precision=53),
'assets_to_eqt': Float(precision=53), 'dp_assets_to_eqt': Float(precision=53),
'ca_to_assets': Float(precision=53),
'nca_to_assets': Float(precision=53), 'tbassets_to_totalassets': Float(precision=53),
'int_to_talcap': Float(precision=53),
'eqt_to_talcapital': Float(precision=53), 'currentdebt_to_debt': Float(precision=53),
'longdeb_to_debt': Float(precision=53),
'ocf_to_shortdebt': Float(precision=53), 'debt_to_eqt': Float(precision=53),
'eqt_to_debt': Float(precision=53),
'eqt_to_interestdebt': Float(precision=53), 'tangibleasset_to_debt': Float(precision=53),
'tangasset_to_intdebt': Float(precision=53), 'tangibleasset_to_netdebt': Float(precision=53),
'ocf_to_debt': Float(precision=53), 'ocf_to_interestdebt': Float(precision=53),
'ocf_to_netdebt': Float(precision=53),
'ebit_to_interest': Float(precision=53), 'longdebt_to_workingcapital': Float(precision=53),
'ebitda_to_debt': Float(precision=53), 'turn_days': Float(precision=53), 'roa_yearly': Float(precision=53),
'roa_dp': Float(precision=53), 'fixed_assets': Float(precision=53),
'profit_prefin_exp': Float(precision=53),
'non_op_profit': Float(precision=53), 'op_to_ebt': Float(precision=53), 'nop_to_ebt': Float(precision=53),
'ocf_to_profit': Float(precision=53), 'cash_to_liqdebt': Float(precision=53),
'cash_to_liqdebt_withinterest': Float(precision=53), 'op_to_liqdebt': Float(precision=53),
'op_to_debt': Float(precision=53),
'roic_yearly': Float(precision=53), 'total_fa_trun': Float(precision=53),
'profit_to_op': Float(precision=53),
'q_opincome': Float(precision=53), 'q_investincome': Float(precision=53),
'q_dtprofit': Float(precision=53),
'q_eps': Float(precision=53), 'q_netprofit_margin': Float(precision=53),
'q_gsprofit_margin': Float(precision=53),
'q_exp_to_sales': Float(precision=53), 'q_profit_to_gr': Float(precision=53),
'q_saleexp_to_gr': Float(precision=53),
'q_adminexp_to_gr': Float(precision=53), 'q_finaexp_to_gr': Float(precision=53),
'q_impair_to_gr_ttm': Float(precision=53),
'q_gc_to_gr': Float(precision=53), 'q_op_to_gr': Float(precision=53), 'q_roe': Float(precision=53),
'q_dt_roe': Float(precision=53), 'q_npta': Float(precision=53), 'q_opincome_to_ebt': Float(precision=53),
'q_investincome_to_ebt': Float(precision=53), 'q_dtprofit_to_profit': Float(precision=53),
'q_salescash_to_or': Float(precision=53), 'q_ocf_to_sales': Float(precision=53),
'q_ocf_to_or': Float(precision=53),
'basic_eps_yoy': Float(precision=53), 'dt_eps_yoy': Float(precision=53), 'cfps_yoy': Float(precision=53),
'op_yoy': Float(precision=53), 'ebt_yoy': Float(precision=53), 'netprofit_yoy': Float(precision=53),
'dt_netprofit_yoy': Float(precision=53), 'ocf_yoy': Float(precision=53), 'roe_yoy': Float(precision=53),
'bps_yoy': Float(precision=53), 'assets_yoy': Float(precision=53), 'eqt_yoy': Float(precision=53),
'tr_yoy': Float(precision=53), 'or_yoy': Float(precision=53), 'q_gr_yoy': Float(precision=53),
'q_gr_qoq': Float(precision=53),
'q_sales_yoy': Float(precision=53), 'q_sales_qoq': Float(precision=53), 'q_op_yoy': Float(precision=53),
'q_op_qoq': Float(precision=53), 'q_profit_yoy': Float(precision=53), 'q_profit_qoq': Float(precision=53),
'q_netprofit_yoy': Float(precision=53), 'q_netprofit_qoq': Float(precision=53),
'equity_yoy': Float(precision=53),
'rd_exp': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'fina_indicator'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')