def test():
sql = 'select * from chart_sidebar'
with get_mysql_client() as cursor:
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
exchange = row['exchange']
variety = row['variety']
sidebar = row['sidebar']
title = row['sidebar_name']
p_axis = row['p_axis']
s_axis = row['s_axis']
size = 1
graph = 1
compare = 1
sql = 'select id from chart_sidebar_copy where exchange=%s and variety=%s and name=%s'
with get_mysql_client() as cursor:
cursor.execute(
sql,
(exchange, variety, title if row['panorama'] == 0 else '全景图'))
sidebar_id = cursor.fetchone()['id']
if sidebar == 'panorama_3':
compare = 2
elif sidebar == 'panorama_4':
compare = 3
else:
compare = 1
if row['panorama'] == 1:
size = 1
else:
size = 2
if sidebar == 'panorama_7':
graph = 2
else:
graph = 1
sql = 'insert into chart_chart set title=%s, size=%s, graph=%s, compare=%s, p_axis=%s, s_axis=%s'
with get_mysql_client() as cursor:
cursor.execute(sql, (title, size, graph, compare, p_axis, s_axis))
chart_id = cursor.lastrowid
sql = 'insert into chart_sidebar_chart set chart_id=%s, sidebar_id=%s'
with get_mysql_client() as cursor:
cursor.execute(sql, (chart_id, sidebar_id))
sql = 'update chart_line set chart_id=%s where exchange=%s and variety=%s and sidebar=%s'
with get_mysql_client() as cursor:
cursor.execute(sql, (chart_id, exchange, variety, sidebar))
print('done')
def start():
lst = []
date = None
with get_mysql_client() as cr:
sql = """
select date
from alert_prediction_record
order by date desc
"""
cr.execute(sql)
vals = cr.fetchone()
date = str(vals.get('date', None))[:10]
sql1 = """
select id, varieties from alert_ai_varieties
"""
cr.execute(sql1)
lst = cr.fetchall()
cal_ranking()
for varieties_vals in lst:
varieties_name, varieties_id = varieties_vals.get(
'varieties'), varieties_vals.get('id')
judgment(varieties_name, varieties_id, date)
update_quotes(varieties_name, varieties_id, date)
cal_win_percent(date)
insert_quotes_vals(varieties_name, varieties_id)
cal_partical_man_number(varieties_id)
def get_list(self, timestamp=False):
q = Q(self.Table, result=Result(compile=mysql_compile)).\
fields(self.Column, self.Date)
if self.start:
q = q.where(self.Date >= self.start)
if self.end:
q = q.where(self.Date <= self.end)
if self.limit:
q = q.limit(self.offset, self.limit)
if self.desc:
q = q.order_by(self.Date.desc())
else:
q = q.order_by(self.Date)
sql, params = q.select()
logging.info("获取数据SQL:%s, 参数:%s", sql, params)
with get_mysql_client() as cursor:
cursor.execute(sql, params)
data = cursor.fetchall()
if timestamp:
result = [
(int(time.mktime(item[self.date_alias].timetuple())) * 1000,
item[self.column]) for item in data
if item[self.column] is not None
]
else:
result = [
item[self.column] for item in data
if item[self.column] is not None
]
# logging.info("数据为:%s", result)
return result
def judgment(varieties_name, varieties_id, date):
"""判断竞猜的正确与否,并写入数据库"""
end_day = datetime.strptime(date, '%Y-%m-%d')
data = ref_ship(varieties_name, 'SETTLE', limit=2, end=end_day)
prediction = 'up'
if data[0] < data[1]:
prediction = 'down'
with get_mysql_client() as cr:
sql = """
select id, prediction
from alert_prediction_record
where varieties_id = '%s' and date = '%s'
""" % (varieties_id, date)
cr.execute(sql)
lst = cr.fetchall()
for vals in lst:
if vals['prediction'] == prediction:
cr.execute(
"update alert_prediction_record set is_true = 't' where id = '%s'"
% vals['id'])
else:
cr.execute(
"update alert_prediction_record set is_true = 'f' where id = '%s'"
% vals['id'])
return
def get_list(self, timestamp=False):
q = None
std_obj = None
for data_obj in self.data_objects:
if q is None:
std_obj = data_obj
q = Q(std_obj.Table,
result=Result(compile=mysql_compile)).fields(
std_obj.Column)
else:
q = q.tables(q.tables() + data_obj.Table).\
on(std_obj.Date == data_obj.Date).\
fields(data_obj.Column)
if timestamp:
q = q.fields(std_obj.Date)
if self.start:
q = q.where(std_obj.Date >= self.start)
if self.end:
q = q.where(std_obj.Date <= self.end)
if self.limit:
q = q.limit(self.offset, self.limit)
if self.desc:
q = q.order_by(std_obj.Date.desc())
else:
q = q.order_by(std_obj.Date)
sql, params = q.select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
data = cursor.fetchall()
result = [item.values() for item in data]
logging.info("获取数据SQL:%s, 参数:%s, 数据为:%s", sql, params, result)
return result
def varieties(self):
if self._varieties is None:
sql = 'select varieties_id from formula_varieties where formula_id = %s'
with get_mysql_client() as cursor:
cursor.execute(sql, (self.id, ))
result = cursor.fetchall()
self._varieties = [item['varieties_id'] for item in result]
return self._varieties
def has_emit(self, msg, subscriber):
sql = ('select id from alert_alert where user_id=%s and triggered_by=%s '
'and variety=%s and price=%s and created_at>=%s;')
with get_mysql_client() as cursor:
cursor.execute(sql, (subscriber['user_id'], msg.formula_id,
msg.variety, msg.price,
self.today))
result = cursor.fetchone()
return bool(result)
def save(self, msg, subscriber):
# 持久化到数据库
sql = ('insert alert_alert set user_id=%s, body=%s, variety=%s, contract=%s, '
'price=%s, triggered_by=%s, created_at=%s, is_pushed=%s;')
with get_mysql_client() as cursor:
cursor.execute(sql, (subscriber['user_id'], msg.body, msg.variety,
msg.contract, msg.price, msg.formula_id, self.now, 1))
alert_id = cursor.lastrowid
return alert_id
def subscribers(self):
# 获取该消息的所有订阅者
if self._subscribers is None:
sql = 'select `id` from varieties_record where code=%s'
with get_mysql_client() as cursor:
cursor.execute(sql, (self.variety,))
result = cursor.fetchone()
if result:
varieties_id = result['id']
sql = ('select a.user_id, c.openid from user_subscription_varieties as a '
'left join user_subscription_formula as b on a.varieties_id=b.varieties_id '
'left join user as c on a.user_id=c.id where a.varieties_id=%s '
'and b.formula_id=%s and a.user_id=b.user_id;')
with get_mysql_client() as cursor:
cursor.execute(sql, (varieties_id, self.formula_id))
self._subscribers = cursor.fetchall()
else:
self._subscribers = []
return self._subscribers
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 sidebar():
sql = 'select distinct(variety) from chart_sidebar'
with get_mysql_client() as cursor:
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
variety = row['variety']
sql = 'insert into chart_sidebar_copy set exchange=%s, variety=%s, name=%s'
with get_mysql_client() as cursor:
cursor.execute(sql, ('', variety, '全景图'))
sql = 'select * from chart_sidebar where panorama=0'
with get_mysql_client() as cursor:
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
exchange = row['exchange']
variety = row['variety']
name = row['sidebar_name']
sql = 'insert into chart_sidebar_copy set exchange=%s, variety=%s, name=%s'
with get_mysql_client() as cursor:
cursor.execute(sql, (exchange, variety, name))
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 count(self):
q = Q(self.Table, result=Result(compile=mysql_compile)).fields(
self.Column.count().as_('count_value'))
if self.start:
q = q.where(self.Date >= self.start)
if self.end:
q = q.where(self.Date <= self.end)
sql, params = q.select()
logging.info("获取数据SQL:%s, 参数:%s", sql, params)
with get_mysql_client() as cursor:
cursor.execute(sql, params)
data = cursor.fetchone()
result = data['count_value']
logging.info("数据为:%s", result)
return result
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 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 cal_win_percent(date):
"""每天计算胜率，胜负次数，参与次数"""
with get_mysql_client() as cr:
sql = """
select IFNULL(t2.fail_number, 0) as fail_number, t1.user_id, IFNULL(t1.victor_number, 0) as victor_number
from
(select count(*) as victor_number, user_id
from alert_prediction_record
where is_true = 't' and if_visible = '1'
group by user_id) as t1
left outer join
(select count(*) as fail_number, user_id
from alert_prediction_record
where is_true = 'f' and if_visible = '1'
group by user_id) as t2
on t1.user_id = t2.user_id
UNION
select IFNULL(t4.fail_number, 0) as fail_number, t4.user_id, IFNULL(t3.victor_number, 0) as victor_number
from
(select count(*) as victor_number, user_id
from alert_prediction_record
where is_true = 't' and if_visible = '1'
group by user_id) as t3
right outer join
(select count(*) as fail_number, user_id
from alert_prediction_record
where is_true = 'f' and if_visible = '1'
group by user_id) as t4
on t3.user_id = t4.user_id
"""
cr.execute(sql)
head = cr.description
vals_lst = cr.fetchall()
for vals in vals_lst:
vals['part_number'] = vals['victor_number'] + vals['fail_number']
vals['win_percent'] = round(
float(vals['victor_number']) / float(
(vals['victor_number'] + vals['fail_number'])), 4)
sql2 = """update user
set victor_number = '%(victor_number)s',
part_number = '%(part_number)s',
fail_number = '%(fail_number)s',
win_percent = '%(win_percent)s'
where id = '%(user_id)s'""" % vals
cr.execute(sql2)
return True
def cal_partical_man_number(varieties_id):
"""每天算出各个品种的参与人数"""
with get_mysql_client() as cr:
sql = """
select user_id, id
from alert_prediction_record
where varieties_id = '%s'
group by user_id
""" % varieties_id
cr.execute(sql)
res = cr.fetchall()
sql1 = """
update alert_ai_varieties set count_user = '******' where id = '%s'
""" % (len(res), varieties_id)
cr.execute(sql1)
return True
def update_quotes(varieties_name, varieties_id, date):
"""把今天结算后的价格信息更新到数据库"""
res = get_quotes(varieties_name, date)
res['varieties_id'] = varieties_id
res['date'] = date
with get_mysql_client() as cr:
sql = """
update alert_quotes_record
set price = '%(price)s',
trend = '%(trend)s',
change_price = '%(change_price)s',
change_percent = '%(change_percent)s'
where varieties_id = '%(varieties_id)s' and
date = '%(date)s'
""" % res
cr.execute(sql)
return
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 cal_ranking():
"""按胜率计算排名"""
with get_mysql_client() as cr:
sql = """
select id, win_percent
from user
where part_number != '0'
order by win_percent desc, part_number desc
"""
cr.execute(sql)
lst = cr.fetchall()
id_lst = []
ranking = 1
# percent_lst = []
for vals in lst:
user_id = vals['id']
sql1 = """update user set ranking = '%s' where id = '%s' """ % (
ranking, user_id)
cr.execute(sql1)
ranking = ranking + 1
return
def count(self):
q = None
std_obj = None
for data_obj in self.data_objects:
if q is None:
std_obj = data_obj
q = Q(std_obj.Table,
result=Result(compile=mysql_compile)).fields(
std_obj.Column.count().as_('count_value'))
else:
q = q.tables(q.tables() + data_obj.Table).\
on(std_obj.Date == data_obj.Date)
if self.start:
q = q.where(std_obj.Date >= self.start)
if self.end:
q = q.where(std_obj.Date <= self.end)
sql, params = q.select()
with get_mysql_client() as cursor:
cursor.execute(sql, params)
data = cursor.fetchone()
result = data['count_value']
logging.info("获取数据SQL:%s, 参数:%s, 数据为:%s", sql, params, result)
return result
def insert_quotes_vals(varieties_name, varieties_id):
"""每天按工作日插入明天竞猜行情信息"""
today = str(datetime.now())[:10]
with get_mysql_client() as cr:
# 查询下个工作日的sql
sql1 = "select settlement_date from main_contract where settlement_date > '%s' and varieties='%s' order by settlement_date" % (
today, varieties_name)
cr.execute(sql1)
vals = cr.fetchone()
if vals:
next_work_day = vals.get('settlement_date')
# 插入下个工作日
cr.execute(
"select id from alert_quotes_record where date = '%s' and varieties_id = '%s' "
% (next_work_day, varieties_id))
vals_lst = cr.fetchall()
if len(vals_lst) == 1:
return
elif len(vals_lst) > 1:
# 删除多余记录
# delete_lst = []
for i in range(len(vals_lst) - 1):
# delete_lst.append(i)
cr.execute(
"delete from alert_quotes_record where id = %s " %
vals_lst[i]['id'])
else:
# 插入新记录
sql2 = """
INSERT INTO `alpha`.`alert_quotes_record`
(`price`, `date`, `created_at`, `updated_at`, `varieties_id`)
VALUES ('0', '%s', '%s', '%s', '%s')
""" % (next_work_day, str(datetime.now())[:19],
str(datetime.now())[:19], varieties_id)
cr.execute(sql2)
return
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)
