def __ensure_table(self, connection: Connection) -> None:
info = connection.execute(*compile(
Q(T.sqlite_master).fields(
'*').where((T.sqlite_master.name == 'version')
& (T.sqlite_master.type == 'table')))).fetchone()
if not info:
connection.execute(
'CREATE TABLE version (number INTEGER NOT NULL)')
connection.execute(
*Q(T(self.table), result=Result(
compile=compile)).insert({T(self.table).number: 0}))
def init(self):
table = 'ref_ship'
q = Q(T(table), result=Result(compile=mysql_compile)). \
fields(T(table)['table_name'],
T(table)['column']).where((T(table)['varieties'] == self.variety) &
(T(table)['price_code'] == self.price_code))
sql, params = q.select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
result = cursor.fetchone()
if not result:
raise ShipNotExists('varieties:%s,price_code:%s' %
(self.variety, self.price_code))
self.table = result['table_name']
self.column = result['column']
logging.info("获取数据SQL:%s, 参数:%s, 数据为:%s", sql, params, result)
def number(self):
with sqlite3.connect(self.db_name) as connection:
self.__ensure_table(connection)
version = connection.execute(
*compile(Q(T(self.table)).fields('number'))).fetchone()[0]
return version
def Table(self):
ft = self._ft
ct = T('contracts')
dt = T('day_kline')
dq = Q(dt, result=Result(compile=mysql_compile))
dq = dq.fields(
Field(Expr('max(`day_kline`.`date_time`)')).as_('latest'),
dt['contract'])
dq = dq.group_by(dt['contract'])
lt = dq.as_table('future_latest')
q = Q(ft, result=Result(compile=mysql_compile))
q = q.tables((q.tables() + ct)).on(ft['contract'] == ct['contract'])
q = q.tables((q.tables() + lt)).on(ft['contract'] == lt['contract'])
q = q.tables((q.tables() + dt)).on(ft['contract'] == dt['contract'])
q = q.where(lt['latest'] == dt['date_time'])
q = q.fields(dt[self.column], ct['expire_date'].as_(self.date_alias))
return q.as_table(str(id(self)))
def Table(self):
t = T(self.table_name)
if self._convert:
field = (t[self.column] * self.benchmark).as_(self.column)
else:
field = t[self.column]
return Q(t, result=Result(mysql_compile)).fields(field, t[self.date].as_(self.date_alias)).\
where(t['symbol'] == self.symbol).as_table(str(id(self)))
def init(self):
sql, params = Q(T('symbol'), result=Result(compile=mysql_compile)).\
fields('*').where(T('symbol')['symbol'] == self.symbol).select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
data = cursor.fetchone()
if not data:
raise SymbolNotExists('symbol(%s) not exists!' % self.symbol)
for attr in self.attr_list:
if attr == 'column':
self.column = self.column or data.get(attr) or 'amount'
elif attr == 'date_column':
self.date = data.get(attr) or self.date
else:
setattr(self, attr, data.get(attr))
logging.info("获取数据SQL:%s, 参数:%s, 数据为:%s", sql, params, data)
return data
def Table(self):
if self.serial == 'main_contract' or self.serial == 'index_contract':
return self.main.Table
t_c = 'main_contract'
t_k = 'day_kline'
q = Q(T(t_c), result=Result(compile=mysql_compile))
q = q.tables((q.tables() + T(t_k)).on(T(t_c)[self.date] == T(t_k)['date_time']))
if self.column == 'settlement_price':
exp = 'if(`day_kline`.`settlement_price`, `day_kline`.`settlement_price`, `day_kline`.`price_close`)'
field = Field(Expr(exp)).as_(self.column)
else:
field = T(t_k)[self.column]
q = q.fields(T(t_c)[self.date].as_(self.date_alias), field)
q = q.where(T(t_c)['varieties'] == self.variety)
q = q.where(T(t_c)[self.serial] == T(t_k)['contract'])
q = q.where(T(t_c)[self.date] <= datetime.date.today())
return q.as_table(str(id(self)))
def Table(self):
t = T('day_kline')
if self.column == 'settlement_price':
exp = 'if(`day_kline`.`settlement_price`, `day_kline`.`settlement_price`, `day_kline`.`price_close`)'
field = Field(Expr(exp)).as_(self.column)
else:
field = t[self.column]
return Q(t, result=Result(mysql_compile)).fields(field, t[self.date].as_(self.date_alias)).\
where(t['contract'] == self.contract).as_table(str(id(self)))
def init(self):
mt = T('main_contract')
q = Q(mt, result=Result(compile=mysql_compile))
q = q.fields('*').where(mt['varieties'] == self.variety)
q = q.order_by(mt['settlement_date'].desc()).limit(1)
sql, params = q.select()
logging.info("获取数据SQL:%s, 参数:%s", sql, params)
with get_mysql_client() as cursor:
cursor.execute(sql, params)
result = cursor.fetchone()
logging.info("数据为:%s", result)
rows = []
for i in range(1, 13):
row = "select '%s' contract" % result.get('serial_contract' +
str(i))
rows.append(row)
exp = ' union '.join(rows)
ft = T(Expr(exp)).as_('future')
self._ft = ft
def filter(self, contract, years):
ct = T('contracts')
q = Q(ct, result=Result(compile=mysql_compile))
q = q.fields(ct['expire_date']).where(ct['contract'] == contract).limit(1)
sql, params = q.select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
expire_date = cursor.fetchone()['expire_date']
origin = expire_date.strftime('%y%m')
new = str(expire_date.year + int(years))[-2:] + str(expire_date.month).zfill(2)
return contract.replace(origin, new)
def filter(self, variety, serial):
if serial == '0':
_serial = 'main_contract'
else:
_serial = 'serial_contract' + serial
mt = T('main_contract')
q = Q(mt, result=Result(compile=mysql_compile))
q = q.fields(mt[_serial].as_('serial'))
q = q.where((mt['varieties'] == variety) & (mt[_serial] != None )).order_by(mt['settlement_date'].desc()).limit(1)
sql, params = q.select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
contract = cursor.fetchone()['serial']
return contract
def Table(self):
t = T(self.table)
return Q(t, result=Result(mysql_compile)).fields(t[self.column], t[self.date].as_(self.date_alias)).\
where((t['varieties'] == self.variety) &
(t[self.date] <= datetime.date.today())).as_table(str(id(self)))
def Table(self):
t = T(self.table)
return Q(t, result=Result(mysql_compile)).fields(t[self.column], t[self.date].as_(self.date_alias)).as_table(str(id(self)))
def update(self, number: int) -> None:
with sqlite3.connect(self.db_name) as connection:
self.__ensure_table(connection)
connection.execute(
*Q(T(self.table), result=Result(
compile=compile)).update({T(self.table).number: number}))
def alert_history():
# 第一次提示当日预警汇总
now_str = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
alert_table = T('alert_alert')
user_table = T('user')
q = Q(alert_table, result=Result(compile=mysql_compile))
q = q.tables(q.tables() +
user_table).on(alert_table['user_id'] == user_table['id'])
q = q.fields(alert_table['id'].count().as_('count_value'),
user_table['openid'], user_table['id'])
q = q.group_by(alert_table['user_id'])
q = q.where(alert_table['created_at'] >= datetime.date.today())
sql, params = q.select()
logging.info('sql: %s, params: %s', sql, params)
with get_mysql_client() as cursor:
cursor.execute(sql, params)
result = cursor.fetchall()
logging.info('result: %s', result)
for item in result:
if item['count_value'] and item['openid']:
q = Q(alert_table, result=Result(compile=mysql_compile))
q = q.fields(alert_table['body'])
q = q.where((alert_table['created_at'] >= datetime.date.today())
& (alert_table['user_id'] == item['id']))
q = q.order_by(alert_table['id'])
q = q.limit(3)
sql, params = q.select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
result = cursor.fetchall()
msg = '\n'.join([d['body'] for d in result])
url = "%s/#/history" % conf.http.get('host')
redirect_uri = '%s?url=%s' % (conf.WECHAT_AUTH_URL, url)
redirect_uri = parse.quote(redirect_uri)
auth_url = (
'https://open.weixin.qq.com/connect/oauth2/authorize?appid={appid}&'
'redirect_uri={uri}&response_type=code&scope=snsapi_userinfo&'
'agentid=AGENTID&state=STATE&connect_redirect=1#wechat_redirect'
).format(appid=conf.WECHAT_APPID, uri=redirect_uri)
data = {
'appId':
conf.WECHAT_APPID,
'messages': [{
'key': 'first',
'tplData': {
'value': 'alpha预警'
}
}, {
'key': 'keyword1',
'tplData': {
'value': '行情预警'
}
}, {
'key': 'keyword2',
'tplData': {
'value': msg
}
}, {
'key': 'keyword3',
'tplData': {
'value': now_str
}
}, {
'key': 'remark',
'tplData': {
'value': ''
}
}],
'openId':
item['openid'],
'templateId':
conf.template_id,
'url':
auth_url
}
send_alert_history(data)