def test_concat(self):
self.assertEqual(
Q(T.tb).where(
T.tb.cl.concat(1, 2, 'str', T.tb.cl2) != 'str2').select('*'),
(
'SELECT * FROM "tb" WHERE "tb"."cl" || %s || %s || %s || "tb"."cl2" <> %s',
[1, 2, 'str', 'str2'],
))
self.assertEqual(
Q(T.tb).where(
T.tb.cl.concat_ws(' + ', 1, 2, 'str', T.tb.cl2) != 'str2').
select('*'), (
'SELECT * FROM "tb" WHERE concat_ws(%s, "tb"."cl", %s, %s, %s, "tb"."cl2") <> %s',
[' + ', 1, 2, 'str', 'str2'],
))
self.assertEqual(
Q(T.tb, result=Result(compile=mysql_compile)).where(
T.tb.cl.concat(1, 2, 'str', T.tb.cl2) != 'str2').select('*'),
(
'SELECT * FROM `tb` WHERE CONCAT(`tb`.`cl`, %s, %s, %s, `tb`.`cl2`) <> %s',
[1, 2, 'str', 'str2'],
))
self.assertEqual(
Q(T.tb, result=Result(compile=mysql_compile)).where(
T.tb.cl.concat_ws(' + ', 1, 2, 'str', T.tb.cl2) != 'str2').
select('*'), (
'SELECT * FROM `tb` WHERE CONCAT_WS(%s, `tb`.`cl`, %s, %s, %s, `tb`.`cl2`) <> %s',
[' + ', 1, 2, 'str', 'str2'],
))
def EXCHANGE(self, env=None, limit=1, contract=None, **kwargs):
_table_name = "future_exchange"
_column = "price_sell"
_currency = 'USD'
_future = "1w"
_description = "汇率"
_desc = ["date"]
_date_column = "date"
_field_name = inspect.stack()[0].function
tb = getattr(T, _table_name)
cl = getattr(tb, _column).as_(_field_name)
f_l = [cl]
if kwargs.get("need_date", False):
dl = getattr(tb, _date_column).as_("date")
f_l.append(dl)
q = Q(tb, result=Result(compile=mysql_compile)).fields(tuple(f_l)). \
where(tb.currency == _currency).where(tb.future == _future).limit(
limit).order_by(
tb.date.desc())
q = self._add_date_limit(q, tb, _date_column, **kwargs)
return q.select()
def sync(self, folder: str) -> None:
with sqlite3.connect(self.db_name) as connection:
row = connection.execute(*compile(
Q(T.icalendar).fields(
'*').where(T.icalendar.user == self.user
and T.icalendar.url == self.url))).fetchone()
url = row[1]
sync_dir = path.join(folder, self.user, 'ics')
if not path.exists(sync_dir):
makedirs(sync_dir)
IcsCalendar(
url,
SimpleCalEventsIntervals(
tz.tzlocal(),
FileTimeIntervals(
path.join(sync_dir,
urllib.parse.quote(self.url,
safe=''))))).sync()
connection.execute(
*Q(T.icalendar, result=Result(
compile=compile)).where(T.icalendar.user == self.user
and T.icalendar.url == self.url).
update({T.icalendar.sync_time: int(round(time.time() *
1000))}))
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 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 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 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('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 add(self, user: str) -> str:
token = str(uuid.uuid4())
with sqlite3.connect(self.db_name) as connection:
connection.execute(*Q(T.session, result=Result(
compile=compile)).insert({
T.session.user: user,
T.session.token: token
}))
return token
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 add(self, email):
# type: (str) -> None
with sqlite3.connect(self.db_name) as connection:
connection.execute(
*Q(
T.user, result=Result(compile=compile)
).insert(
{
T.user.email: email
}
)
)
def _get_table(self):
q = Q(self.left.Table, result=Result(mysql_compile))
if isinstance(self.right, BaseData):
q = q.tables(q.tables() + self.right.Table).on(
self.left.Date == self.right.Date)
right_column = self.right.Column
else:
right_column = self.right
column = self._get_column(right_column)
date = self.left.Date.as_(self.date_alias)
q = q.fields(column, date)
return q.as_table(str(id(self)))
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 add(self, team: str, token: str, profile: str) -> None:
with sqlite3.connect(self.db_name) as connection:
connection.execute(*Q(T.slack, result=Result(
compile=compile)).insert({
T.slack.user: self.user,
T.slack.team: team,
T.slack.token: token,
T.slack.profile: profile,
T.slack.busy_text: '',
T.slack.busy_emoji: '',
T.slack.available_text: '',
T.slack.available_emoji: '',
}))
def update_available(self, available_text: str, available_emoji: str) -> None:
with sqlite3.connect(self.db_name) as connection:
connection.execute(
*Q(
T.slack, result=Result(compile=compile)
).where(
T.slack.user == self.user and
T.slack.token == self.token
).update({
T.slack.available_text: available_text,
T.slack.available_emoji: available_emoji,
})
)
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 add(self, url, name):
# type: (str, str) -> None
with sqlite3.connect(self.db_name) as connection:
connection.execute(
*Q(
T.icalendar, result=Result(compile=compile)
).insert(
{
T.icalendar.user: self.user,
T.icalendar.url: url,
T.icalendar.name: name,
T.icalendar.sync_time: 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 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 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 list(self, variety, price_code=[], result_type=dict, **kwargs):
f_tb = T.formula
fv_tb = T.formula_varieties
vr_tb = T.varieties_record
tbs = ((f_tb + fv_tb).on(f_tb.id == fv_tb.formula_id)
& vr_tb).on(fv_tb.varieties_id == vr_tb.id)
q = Q(tbs, result=Result(compile=mysql_compile)). \
fields(f_tb.formula, f_tb.id, f_tb.unit). \
where(vr_tb.code == variety).where(f_tb.enable == 1)
for code in price_code:
t_code = "%" + code + ",%"
q = q.where(vr_tb.trigger_price_code.like(t_code))
data = self.get_record(*q.select(), **kwargs)
if result_type == tuple:
data = [(x["formula"], x["id"], x["unit"]) for x in data]
return data
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 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)))
A_OR_B_SYMBOL = "fenji_a_or_b_symbol"
A_OR_B_INNER_CODE = "fenji_a_or_b_inner_code"
A_LISTING = "a_listing"
B_LISTING = "b_listing"
MU_LISTING = "mu_listing"
class BASE_CONST(object):
ID = "order_book_id"
TRD_DATE = "trade_date"
class EQUITY_INFO(BASE_CONST):
ST_FLAG = "st_flag"
class DATEFORMAT(object):
DATE_DATE = "trade_date_dt" # datetime type
DATE_INT = "trade_date_int" # yyyyMMdd
DATE_STR = "trade_date_str" # "yyyy-MM-dd"
class INDEX_COMP(BASE_CONST):
COMP = "component_ids"
query = Q(result=Result(compile=mysql_compile))
stk_stop_calendar = T.STK_STP_CALENDAR
stk_code = T.STK_CODE
def __init__(_self, tables=None, result=None):
super().__init__(tables=tables,
result=result
or Result(compile=self._compile))
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 __init__(self, **kwargs):
self.db = mysql.connect(**kwargs)
self.result = Result(mysql_compile)
self.log = True
self.connected = False
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}))