def test_compositeexpr_as_alias(self):
pk = CompositeExpr(T.tb.obj_id, T.tb.land_id, T.tb.date).as_(
('al1', 'al2', 'al3'))
today = datetime.datetime.now().replace(hour=0,
minute=0,
second=0,
microsecond=0)
self.assertEqual(
Q(T.tb).fields(pk,
T.tb.title).where(pk == (1, 'en', today)).select(),
('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE "al1" = %s AND "al2" = %s AND "al3" = %s',
[1, 'en', today]))
self.assertEqual(
Q(T.tb).fields(pk,
T.tb.title).where(pk != (1, 'en', today)).select(),
('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE "al1" <> %s AND "al2" <> %s AND "al3" <> %s',
[1, 'en', today]))
self.assertEqual(
Q(T.tb).fields(pk, T.tb.title).where(
pk.in_(((1, 'en', today), (2, 'en', today)))).select(),
('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE "al1" = %s AND "al2" = %s AND "al3" = %s OR "al1" = %s AND "al2" = %s AND "al3" = %s',
[1, 'en', today, 2, 'en', today]))
self.assertEqual(
Q(T.tb).fields(pk, T.tb.title).where(
pk.not_in(((1, 'en', today), (2, 'en', today)))).select(),
('SELECT "tb"."obj_id" AS "al1", "tb"."land_id" AS "al2", "tb"."date" AS "al3", "tb"."title" FROM "tb" WHERE NOT ("al1" = %s AND "al2" = %s AND "al3" = %s OR "al1" = %s AND "al2" = %s AND "al3" = %s)',
[1, 'en', today, 2, 'en', today]))
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 test_t25_1(self):
self.assertEqual(
compile(Q().fields(
Q().tables(T.a).fields(F('*').count()).as_('tot'),
Q().tables(T.a).fields(F('*').count()).as_('another_tot'))),
('SELECT (SELECT COUNT(*) FROM "a") AS "tot", (SELECT COUNT(*) FROM "a") AS "another_tot"',
[]))
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 test_where_subquery(self):
sub_q = Q().fields(T.author.id).tables(
T.author).where(T.author.status == 'active')
q = Q().fields(T.book.id).tables(T.book).where(
T.book.author_id.in_(sub_q))
self.assertEqual(compile(q), (
'SELECT "book"."id" FROM "book" WHERE "book"."author_id" IN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s)',
['active']))
def test_insert(self):
self.assertEqual(
Q(T.stats).insert(OrderedDict((
(T.stats.object_type, 'author'),
(T.stats.object_id, 15),
(T.stats.counter, 1),
)),
on_duplicate_key_update=OrderedDict(
((T.stats.counter, T.stats.counter +
func.VALUES(T.stats.counter)), )),
duplicate_key=(T.stats.object_type,
T.stats.object_id)),
('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s) ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")',
['author', 15, 1]))
self.assertEqual(
Q(T.stats).insert(OrderedDict((
('object_type', 'author'),
('object_id', 15),
('counter', 1),
)),
on_duplicate_key_update=OrderedDict(
(('counter', T.stats.counter +
func.VALUES(T.stats.counter)), )),
duplicate_key=('object_type', 'object_id')),
('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s) ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")',
['author', 15, 1]))
self.assertEqual(
Q().fields(T.stats.object_type, T.stats.object_id,
T.stats.counter).tables(T.stats).insert(
values=('author', 15, 1),
on_duplicate_key_update=OrderedDict(
((T.stats.counter, T.stats.counter +
func.VALUES(T.stats.counter)), )),
duplicate_key=(T.stats.object_type,
T.stats.object_id)),
('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES %s, %s, %s ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")',
['author', 15, 1]))
self.assertEqual(
Q().fields(T.stats.object_type, T.stats.object_id,
T.stats.counter).tables(T.stats).insert(
values=(
('author', 15, 1),
('author', 16, 1),
),
on_duplicate_key_update=OrderedDict(
((T.stats.counter, T.stats.counter +
func.VALUES(T.stats.counter)), )),
duplicate_key=(T.stats.object_type,
T.stats.object_id)),
('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s), (%s, %s, %s) ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")',
['author', 15, 1, 'author', 16, 1]))
self.assertEqual(
Q().fields(T.stats.object_type, T.stats.object_id,
T.stats.counter).tables(T.stats).insert(
values=('author', 15, 1), ignore=True),
('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES %s, %s, %s ON CONFLICT DO NOTHING',
['author', 15, 1]))
def test_as_table(self):
author_query_alias = Q(T.author).fields(T.author.id).where(
T.author.status == 'active').as_table('author_query_alias')
self.assertEqual(
compile(Q().fields(T.book.id, T.book.title).tables(
(T.book & author_query_alias).on(
T.book.author_id == author_query_alias.id))),
('SELECT "book"."id", "book"."title" FROM "book" INNER JOIN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s) AS "author_query_alias" ON ("book"."author_id" = "author_query_alias"."id")',
['active']))
def test_in(self):
self.assertEqual(
Q(T.tb).where(T.tb.cl == [
1,
T.tb.cl3,
5,
]).where(T.tb.cl2 == [
1,
T.tb.cl4,
]).select('*'), (
'SELECT * FROM "tb" WHERE "tb"."cl" IN (%s, "tb"."cl3", %s) AND "tb"."cl2" IN (%s, "tb"."cl4")',
[
1,
5,
1,
],
))
self.assertEqual(
Q(T.tb).where(T.tb.cl != [
1,
3,
5,
]).select('*'), (
'SELECT * FROM "tb" WHERE "tb"."cl" NOT IN (%s, %s, %s)',
[
1,
3,
5,
],
))
self.assertEqual(
Q(T.tb).where(T.tb.cl.in_([
1,
3,
5,
])).select('*'), (
'SELECT * FROM "tb" WHERE "tb"."cl" IN (%s, %s, %s)',
[
1,
3,
5,
],
))
self.assertEqual(
Q(T.tb).where(T.tb.cl.not_in([
1,
3,
5,
])).select('*'), (
'SELECT * FROM "tb" WHERE "tb"."cl" NOT IN (%s, %s, %s)',
[
1,
3,
5,
],
))
def test_alias_subquery(self):
alias = Q().fields(T.book.id.count()).tables(
T.book).where((T.book.pub_date > '2015-01-01')
& (T.book.author_id == T.author.id)).group_by(
T.book.author_id).as_("book_count")
q = Q().fields(T.author.id, alias).tables(
T.author).where(T.author.status == 'active').order_by(alias.desc())
self.assertEqual(compile(q), (
'SELECT "author"."id", (SELECT COUNT("book"."id") FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") AS "book_count" FROM "author" WHERE "author"."status" = %s ORDER BY "book_count" DESC',
['2015-01-01', 'active']))
def test_fields_subquery(self):
sub_q = Q().fields(T.book.id.count().as_("book_count")).tables(
T.book).where(T.book.pub_date > '2015-01-01').group_by(
T.book.author_id)
q = Q().fields(T.author.id,
sub_q.where(T.book.author_id == T.author.id)).tables(
T.author).where(T.author.status == 'active')
self.assertEqual(compile(q), (
'SELECT "author"."id", (SELECT COUNT("book"."id") AS "book_count" FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") FROM "author" WHERE "author"."status" = %s',
['2015-01-01', 'active']))
def test_union(self):
a = Q(T.item).where(T.item.status != -1).fields(
T.item.type, T.item.name, T.item.img)
b = Q(T.gift).where(T.gift.storage > 0).columns(
T.gift.type, T.gift.name, T.gift.img)
self.assertEqual((
a.as_set(True) | b
).order_by("type", "name", desc=True).limit(100, 10).select(), (
'(SELECT "item"."type", "item"."name", "item"."img" FROM "item" WHERE "item"."status" <> %s) UNION ALL (SELECT "gift"."type", "gift"."name", "gift"."img" FROM "gift" WHERE "gift"."storage" > %s) ORDER BY %s DESC, %s DESC LIMIT %s OFFSET %s',
[-1, 0, 'type', 'name', 10, 100],
))
def test_expression(self):
self.assertEqual(
Q(T.tb1).select(E('5 * 3 - 2*8').as_('sub_value')), (
'SELECT (5 * 3 - 2*8) AS "sub_value" FROM "tb1"',
[],
))
self.assertEqual(
Q(T.tb1).select(E('(5 - 2) * 8 + (6 - 3) * 8').as_('sub_value')), (
'SELECT ((5 - 2) * 8 + (6 - 3) * 8) AS "sub_value" FROM "tb1"',
[],
))
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 test_function(self):
self.assertEqual(
Q(T.tb).where(func.FUNC_NAME(T.tb.cl) == 5).select('*'), (
'SELECT * FROM "tb" WHERE FUNC_NAME("tb"."cl") = %s',
[
5,
],
))
self.assertEqual(
Q(T.tb).where(T.tb.cl == func.RAND()).select('*'), (
'SELECT * FROM "tb" WHERE "tb"."cl" = RAND()',
[],
))
def test_insert_select(self):
self.assertEqual(
Q().fields(
T.stats.object_type, T.stats.object_id,
T.stats.counter).tables(T.stats).insert(
values=Q().fields(T.old_stats.object_type,
T.old_stats.object_id,
T.old_stats.counter).tables(T.old_stats),
on_duplicate_key_update=OrderedDict(
((T.stats.counter,
T.stats.counter + T.old_stats.counter), )),
duplicate_key=(T.stats.object_type, T.stats.object_id)),
('INSERT INTO "stats" ("object_type", "object_id", "counter") SELECT "old_stats"."object_type", "old_stats"."object_id", "old_stats"."counter" FROM "old_stats" ON CONFLICT ("object_type", "object_id") DO UPDATE SET "counter" = "stats"."counter" + "old_stats"."counter"',
[]))
def test_prefix(self):
self.assertEqual(
Q(T.tb).where(~(T.tb.cl == 3)).select('*'), (
'SELECT * FROM "tb" WHERE NOT "tb"."cl" = %s',
[
3,
],
))
self.assertEqual(
Q(T.tb).where(Not(T.tb.cl == 3)).select('*'), (
'SELECT * FROM "tb" WHERE NOT "tb"."cl" = %s',
[
3,
],
))
def test_field(self):
# Get field as table attribute
self.assertEqual(type(T.book.title), Field)
self.assertEqual(compile(T.book.title), ('"book"."title"', []))
self.assertEqual(type(T.book.title.as_('a')), A)
self.assertEqual(compile(T.book.title.as_('a')), ('"a"', []))
self.assertEqual(type(T.book.title__a), A)
self.assertEqual(compile(T.book.title__a), ('"a"', []))
# Get field as class F attribute (Legacy)
self.assertEqual(type(F.book__title), Field)
self.assertEqual(compile(F.book__title), ('"book"."title"', []))
self.assertEqual(type(F.book__title.as_('a')), A)
self.assertEqual(compile(F.book__title.as_('a')), ('"a"', []))
self.assertEqual(type(F.book__title__a), A)
self.assertEqual(compile(F.book__title__a), ('"a"', []))
# Test with context
al = T.book.status.as_('a')
self.assertEqual(
compile(Q().tables(T.book).fields(T.book.id, al).where(
al.in_(('new', 'approved')))),
('SELECT "book"."id", "book"."status" AS "a" FROM "book" WHERE "a" IN (%s, %s)',
['new', 'approved']))
def as_html(self, sync_url: typing.Callable[[str], str]) -> str:
result = ''
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()
try:
sync = datetime.datetime.fromtimestamp(row[3] / 1000)
except OSError:
sync = datetime.datetime.min
name = row[2]
url = row[1]
delta: datetime.timedelta = datetime.datetime.now() - sync
if delta.seconds > 60:
sync_html = f'<mark>Sync was at {str(sync)}</mark>'
else:
sync_html = f'<code>Sync was at {str(sync)}</code>'
result = f'''
<h2>{name}</h2>
<span><strong>Url: </strong>{url}</span></br>
{sync_html}</br>
<span><a href="{sync_url(url)}">Sync it now!</a></span></br>
'''
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 test_order_by(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
q = q.order_by(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC',
[]))
q = q.order_by(T.author.age.desc())
self.assertEqual(compile(q), (
'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC',
[]))
self.assertEqual(type(q.order_by()), ExprList)
self.assertEqual(compile(q.order_by()), (
'"author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC',
[]))
self.assertEqual(
compile(q.order_by([T.author.id.desc(), T.author.status])),
('SELECT * FROM "author" ORDER BY "author"."id" DESC, "author"."status" ASC',
[]))
self.assertEqual(compile(q.order_by([])),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q.order_by(reset=True)),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q), (
'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC',
[]))
def test_distinct(self):
q = Q().tables(T.author).fields(T.author.first_name,
T.author.last_name, T.author.age)
self.assertEqual(compile(q), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
q = q.distinct(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT DISTINCT ON ("author"."first_name", "author"."last_name") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
q = q.distinct(T.author.age)
self.assertEqual(compile(q), (
'SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(type(q.distinct()), ExprList)
self.assertEqual(
compile(q.distinct()),
('"author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(compile(q.distinct([T.author.id, T.author.status])), (
'SELECT DISTINCT ON ("author"."id", "author"."status") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(compile(q.distinct([])), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(compile(q.distinct(reset=True)), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(compile(q), (
'SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
def test_group_by(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
q = q.group_by(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name"',
[]))
q = q.group_by(T.author.age)
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(type(q.group_by()), ExprList)
self.assertEqual(
compile(q.group_by()),
('"author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(compile(q.group_by([T.author.id, T.author.status])), (
'SELECT * FROM "author" GROUP BY "author"."id", "author"."status"',
[]))
self.assertEqual(compile(q.group_by([])),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q.group_by(reset=True)),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"',
[]))
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 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 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 test_result(self):
class CustomResult(Result):
custom_attr = 5
def custom_method(self, arg1, arg2):
return (self._query, arg1, arg2)
def find_by_name(self, name):
return self._query.where(T.author.name == name)
q = Q(result=CustomResult(compile=mysql_compile)).fields(
T.author.id, T.author.name).tables(T.author)
self.assertEqual(q.custom_attr, 5)
q2, arg1, arg2 = q.custom_method(5, 10)
self.assertIsNot(q2, q)
self.assertEqual(q2.select(), q.select())
self.assertEqual(
q2.select(),
('SELECT `author`.`id`, `author`.`name` FROM `author`', []))
self.assertEqual(arg1, 5)
self.assertEqual(arg2, 10)
q3 = q.find_by_name('John')
self.assertIsNot(q3, q)
self.assertEqual(q3.select(), (
'SELECT `author`.`id`, `author`.`name` FROM `author` WHERE `author`.`name` = %s',
['John']))
async def select_by_login(cls, login):
async with database.query() as Q:
return await (Q().tables(
(T.company & T.user & T.m2m_user_company
).on((T.company.id == T.m2m_user_company.company_id)
& (T.user.id == T.m2m_user_company.user_id))).fields(
T.company.id, T.company.name).where(
T.user.login == login).selectall())
async def select_by_login(cls, login):
async with database.query() as Q:
return await (Q().tables((T.user & T.user_permission_schema).on(
T.user.id == T.user_permission_schema.user_id)).fields(
T.user.login, T.user_permission_schema.target,
T.user_permission_schema.permission,
T.user_permission_schema.domen).where(
T.user.login == login).selectall())
async def select_roles_by_login(cls, login):
async with database.query() as Q:
return await (Q().tables(
(T.role & T.m2m_user_role &
T.user).on((T.role.id == T.m2m_user_role.role_id)
& (T.user.id == T.m2m_user_role.user_id))).fields(
T.role.name).where(
(T.user.login == login)).selectall())
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)))
