def map_types(df):
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
if "object" in str(j):
dtypedict.update({i: NVARCHAR(length=255)})
if "float" in str(j):
dtypedict.update({i: FLOAT(precision=2, asdecimal=True)})
if "int" in str(j):
dtypedict.update({i: INTEGER()})
return dtypedict
class MatchAgainstClause(ColumnElement):
"""
A MySQL FULLTEXT Search Clause
The return value from MySQL for this clause is the row relevance.
"""
type = FLOAT()
def __init__(self, columns, against, bool=False):
self.columns = ClauseList(*columns)
self.against = bindparam('search', against)
self.bool = bool
class UnixTimestampHires(UnboundTypeDecorator):
""" Unix timestamp with microsecond resolution. Older mysql versions have
a TIMESTAMP/DATETIME with second resolution. Represented as a DOUBLE in
the database. Result is a naive datetime.
"""
impl = FLOAT(53)
def process_bind_param(self, value, dialect):
return value if value is None else value.replace(
tzinfo=pytz.UTC).timestamp()
def process_result_value(self, value, dialect):
return value if value is None else datetime.utcfromtimestamp(value)
def test_defaults(self):
assert self._comp(DATE()) == 'DATE'
assert self._comp(DATETIME()) == 'TIMESTAMP(6)'
assert self._comp(TIMESTAMP()) == 'TIMESTAMP(6)'
assert self._comp(TIME()) == 'TIME(6)'
assert self._comp(CHAR()) == 'CHAR(1)'
assert self._comp(VARCHAR()) == 'LONG VARCHAR'
assert self._comp(NCHAR()) == 'CHAR CHAR SET UNICODE'
assert self._comp(NVARCHAR()) == 'LONG VARCHAR CHAR SET UNICODE'
assert self._comp(CLOB()) == 'CLOB'
assert self._comp(TEXT()) == 'CLOB'
assert self._comp(DECIMAL()) == 'DECIMAL(5, 0)'
assert self._comp(NUMERIC()) == 'NUMERIC(5, 0)'
assert self._comp(INTEGER()) == 'INTEGER'
assert self._comp(FLOAT()) == 'FLOAT'
assert self._comp(REAL()) == 'REAL'
assert self._comp(SMALLINT()) == 'SMALLINT'
assert self._comp(BIGINT()) == 'BIGINT'
assert self._comp(BOOLEAN()) == 'BYTEINT'
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 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')
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_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')
print(url + '\n')
#情感分析
df = pd.DataFrame(all_result)
df['id'] = df.index
from snownlp import SnowNLP
def _sentiment(text):
s = SnowNLP(text)
return s.sentiments
df["sentiment"] = df.short.apply(_sentiment)
#保存到数据库
import pymysql
from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR, INT, FLOAT, BIGINT
connect_info = 'mysql+pymysql://root:rootroot@localhost:3306/db1?charset=UTF8MB4'
engine = create_engine(connect_info)
df.to_sql(name='douban_t',
con=engine,
if_exists='replace',
index=False,
dtype={
'id': BIGINT,
'n_star': INT,
'short': VARCHAR(length=255),
'sentiment': FLOAT(12, 10)
})
# 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
period = time_T-time_0-1
coef = 1 #set deviation coefficient
eps = 0.00001
class ReservationModel(db.Model):
"""Docstring Here."""
__tablename__ = "reservation"
id = db.Column(db.Integer, primary_key=True)
head_count = db.Column(db.Integer, nullable=False)
reserve_datetime = db.Column(db.DateTime, default=datetime.now)
created_at = db.Column(db.DateTime, default=datetime.now)
updated_at = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
account_id = db.Column(db.Integer,
db.ForeignKey("account.id"),
nullable=False)
account = db.relationship(AccountModel, backref="account", lazy=True)
status = db.Column(CHAR(16), nullable=False, default="failed")
token = db.Column(db.String(128), nullable=False)
total_price = db.Column(FLOAT(7, 2), nullable=False)
def __init__(self, head_count, account, total_price, token):
self.head_count = head_count
self.account = account
self.total_price = total_price
self.token = token
def save_to_db(self):
"""Docstring here."""
db.session.add(self)
db.session.commit()
def json(self):
return {
"id": self.id,
"head_count": self.head_count,
"reserve_datetime": self.reserve_datetime,
"account": self.account.json()
}
def set_status(self, status: str):
self.status = status
def charge_with_stripe(self, item_details: dict,
token: str) -> stripe.Charge:
stripe.api_key = os.environ.get("STRIPE_API_KEY")
line_times = [{
"price_data": {
"currency": CURRENCY,
"product_data": item_details,
"unit_amount": price,
},
"quantity": head_count
}]
return stripe.checkout.Session.create(payment_method_types=["card"],
line_times=line_times,
source=token,
success_url="",
cancel_url="")
def generate_json_ticket(self) -> dict:
"""Generate a JSON ticket summary of a reservation."""
ticket = (self.query.from_statement(
db.text(SELECT_TICKET_QUERY).params(res_id=self.id)).with_entities(
*TICKET_COLUMNS).first())
ticket_dict = dict(zip(TICKET_COLUMNS, ticket))
ticket_dict["price_breakdown"] = ticket_dict["price_breakdown"].decode(
)
return ticket_dict
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')
