async def run():
await Tortoise.init(db_url="sqlite://:memory:",
modules={"models": ["__main__"]})
await Tortoise.generate_schemas()
a1 = await Author.create(name="author1")
a2 = await Author.create(name="author2")
for i in range(10):
await Book.create(name=f"book{i}", author=a1, rating=i)
for i in range(5):
await Book.create(name=f"book{i}", author=a2, rating=i)
ret = await Book.annotate(count=Count("id")).group_by("author_id").values(
"author_id", "count")
print(ret)
# >>> [{'author_id': 1, 'count': 10}, {'author_id': 2, 'count': 5}]
ret = (await
Book.annotate(count=Count("id")
).filter(count__gt=6).group_by("author_id").values(
"author_id", "count"))
print(ret)
# >>> [{'author_id': 1, 'count': 10}]
ret = await Book.annotate(sum=Sum("rating")).group_by("author_id").values(
"author_id", "sum")
print(ret)
# >>> [{'author_id': 1, 'sum': 45.0}, {'author_id': 2, 'sum': 10.0}]
ret = (await
Book.annotate(sum=Sum("rating")
).filter(sum__gt=11).group_by("author_id").values(
"author_id", "sum"))
print(ret)
# >>> [{'author_id': 1, 'sum': 45.0}]
ret = await Book.annotate(avg=Avg("rating")).group_by("author_id").values(
"author_id", "avg")
print(ret)
# >>> [{'author_id': 1, 'avg': 4.5}, {'author_id': 2, 'avg': 2.0}]
ret = (await
Book.annotate(avg=Avg("rating")
).filter(avg__gt=3).group_by("author_id").values(
"author_id", "avg"))
print(ret)
# >>> [{'author_id': 1, 'avg': 4.5}]
# and use .values_list()
ret = (await
Book.annotate(count=Count("id")).group_by("author_id").values_list(
"author_id", "count"))
print(ret)
# >>> [(1, 10), (2, 5)]
# group by with join
ret = (await
Book.annotate(count=Count("id")).group_by("author__name").values(
"author__name", "count"))
print(ret)
async def test_aggregate_sum_with_f_expression(self):
await testmodels.DecimalFields.create(decimal=Decimal("0"), decimal_nodec=1)
await testmodels.DecimalFields.create(decimal=Decimal("9.99"), decimal_nodec=1)
await testmodels.DecimalFields.create(decimal=Decimal("27.27"), decimal_nodec=1)
values = (
await testmodels.DecimalFields.all()
.annotate(sum_decimal=Sum(F("decimal")))
.values("sum_decimal")
)
self.assertEqual(
values[0],
{"sum_decimal": Decimal("37.26")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(sum_decimal=Sum(F("decimal") + 1))
.values("sum_decimal")
)
self.assertEqual(
values[0],
{"sum_decimal": Decimal("40.26")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(sum_decimal=Sum(F("decimal") + F("decimal")))
.values("sum_decimal")
)
self.assertEqual(
values[0],
{"sum_decimal": Decimal("74.52")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(sum_decimal=Sum(F("decimal") + F("decimal_nodec")))
.values("sum_decimal")
)
self.assertEqual(
values[0],
{"sum_decimal": Decimal("4E+1")},
)
values = (
await testmodels.DecimalFields.all()
.annotate(sum_decimal=Sum(F("decimal") + F("decimal_null")))
.values("sum_decimal")
)
self.assertEqual(
values[0],
{"sum_decimal": None},
)
async def test_aggregate_sum_no_exist_field_with_f_expression(self):
with self.assertRaisesRegex(
FieldError,
"There is no non-virtual field not_exist on Model DecimalFields",
):
await testmodels.DecimalFields.all().annotate(
sum_decimal=Sum(F("not_exist"))).values("sum_decimal")
async def test_sum_values_list_group_by_with_join(self):
ret = (
await Book.annotate(sum=Sum("rating"))
.group_by("author__name")
.values_list("author__name", "sum")
)
self.assertListSortEqual(ret, [("author1", 45.0), ("author2", 10.0)])
async def test_aggregate_sum_different_field_type_at_left_with_f_expression(self):
with self.assertRaisesRegex(
FieldError, "Cannot use arithmetic expression between different field type"
):
await testmodels.DecimalFields.all().annotate(
sum_decimal=Sum(F("id") + F("decimal"))
).values("sum_decimal")
async def test_sum_filter_group_by(self):
ret = (await
Book.annotate(sum=Sum("rating")
).filter(sum__gt=11).group_by("author_id").values(
"author_id", "sum"))
self.assertEqual(len(ret), 1)
self.assertEqual(ret[0].get("sum"), 45.0)
async def test_default_order_annotated_query(self):
instance = await DefaultOrdered.create(one="2", second=1)
await FKToDefaultOrdered.create(link=instance, value=10)
await DefaultOrdered.create(one="1", second=1)
queryset = DefaultOrdered.all().annotate(res=Sum("related__value"))
queryset._make_query()
query = queryset.query.get_sql()
self.assertTrue("order by" not in query.lower())
async def test_sum_group_by_with_join(self):
ret = (
await Book.annotate(sum=Sum("rating"))
.group_by("author__name")
.values("author__name", "sum")
)
self.assertEqual(
ret,
[{"author__name": "author1", "sum": 45.0}, {"author__name": "author2", "sum": 10.0}],
)
async def command_usage(self, ctx: commands.Context):
usage_counts = (await CommandUsageCount.all().annotate(
use_count=Sum('counter')
).order_by('name').group_by('name').values_list('name', 'use_count'))
embed = discord.Embed(title='Command Usage')
asyncio.create_task(
run_paged_message(
ctx,
embed, [f'{name}: {count}' for name, count in usage_counts] +
[f'total: {sum(c for _, c in usage_counts)}'],
page_size=40))
async def test_sum_group_by(self):
ret = (
await Book.annotate(sum=Sum("rating")).group_by("author_id").values("author_id", "sum")
)
for item in ret:
author_id = item.get("author_id")
sum_ = item.get("sum")
if author_id == self.a1.pk:
self.assertEqual(sum_, 45.0)
elif author_id == self.a2.pk:
self.assertEqual(sum_, 10.0)
async def test_aggregation(self):
tournament = Tournament(name="New Tournament")
await tournament.save()
await Tournament.create(name="Second tournament")
await Event(name="Without participants",
tournament_id=tournament.id).save()
event = Event(name="Test", tournament_id=tournament.id)
await event.save()
participants = []
for i in range(2):
team = Team(name=f"Team {(i + 1)}")
await team.save()
participants.append(team)
await event.participants.add(participants[0], participants[1])
await event.participants.add(participants[0], participants[1])
tournaments_with_count = (await Tournament.all().annotate(
events_count=Count("events")).filter(events_count__gte=1))
self.assertEqual(len(tournaments_with_count), 1)
self.assertEqual(tournaments_with_count[0].events_count, 2)
event_with_lowest_team_id = (await Event.filter(
event_id=event.event_id
).first().annotate(lowest_team_id=Min("participants__id")))
self.assertEqual(event_with_lowest_team_id.lowest_team_id,
participants[0].id)
ordered_tournaments = (await Tournament.all().annotate(
events_count=Count("events")).order_by("events_count"))
self.assertEqual(len(ordered_tournaments), 2)
self.assertEqual(ordered_tournaments[1].id, tournament.id)
event_with_annotation = (await Event.all().annotate(
tournament_test_id=Sum("tournament__id")).first())
self.assertEqual(event_with_annotation.tournament_test_id,
event_with_annotation.tournament_id)
with self.assertRaisesRegex(ConfigurationError,
"name__id not resolvable"):
await Event.all().annotate(tournament_test_id=Sum("name__id")
).first()
async def test_aggregate_sum(self):
await testmodels.DecimalFields.create(decimal=Decimal("0"),
decimal_nodec=1)
await testmodels.DecimalFields.create(decimal=Decimal("9.99"),
decimal_nodec=1)
await testmodels.DecimalFields.create(decimal=Decimal("27.27"),
decimal_nodec=1)
values = (await testmodels.DecimalFields.all().annotate(
sum_decimal=Sum("decimal")).values("sum_decimal"))
self.assertEqual(
values[0],
{"sum_decimal": Decimal("37.26")},
)
async def run():
await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", modules={"models": ["__main__"]})
await Tortoise.generate_schemas()
tournament = await Tournament.create(name="New Tournament", desc="great")
await tournament.save()
await Tournament.create(name="Second tournament")
await Tournament.create(name=" final tournament ")
await Event(name="Without participants", tournament_id=tournament.id).save()
event = Event(name="Test", tournament_id=tournament.id)
await event.save()
participants = []
for i in range(10):
team = Team(name=f"Team {(i + 1)}")
await team.save()
participants.append(team)
await event.participants.add(*participants)
# await event.participants.add(participants[0], participants[1])
print(await Tournament.all().annotate(events_count=Count("events")).filter(events_count__lte=3).values())
print(
await Tournament
.annotate(events_count_with_filter=Count("events", _filter=Q(name="New Tournament")))
.filter(events_count_with_filter__gte=0).values()
)
print(await Event.annotate(lowest_team_id=Count("participants__id")).values())
print(await Tournament.annotate(events_count=Count("events")).order_by("events_count"))
print(await Event.annotate(tournament_test_id=Sum("tournament__id")).first())
print(
await Tournament.annotate(clean_desciption=Coalesce("desc", "hehe")).values()
)
print(
await Tournament.annotate(trimmed_name=Trim("name")).values()
)
print(
await Tournament.annotate(name_len=Length("name")).filter(
name_len__gt=len("New Tournament")
)
)
print(await Tournament.annotate(name_lo=Lower("name")).filter(name_lo="new tournament").values())
print(await Tournament.annotate(name_lo=Upper("name")).filter(name_lo="NEW TOURNAMENT").values())
print()
async def run():
await Tortoise.init(db_url="sqlite://:memory:",
modules={"models": ["__main__"]})
await Tortoise.generate_schemas()
tournament = await Tournament.create(name="New Tournament", desc="great")
await tournament.save()
await Tournament.create(name="Second tournament")
await Tournament.create(name=" final tournament ")
await Event(name="Without participants",
tournament_id=tournament.id).save()
event = Event(name="Test", tournament_id=tournament.id)
await event.save()
participants = []
for i in range(2):
team = Team(name=f"Team {(i + 1)}")
await team.save()
participants.append(team)
await event.participants.add(participants[0], participants[1])
await event.participants.add(participants[0], participants[1])
print(await Tournament.all().annotate(events_count=Count("events")
).filter(events_count__gte=1))
print(await Event.filter(id=event.id).first().annotate(
lowest_team_id=Min("participants__id")))
print(await Tournament.all().annotate(events_count=Count("events")
).order_by("events_count"))
print(await Event.all().annotate(tournament_test_id=Sum("tournament__id")
).first())
print(await Tournament.annotate(clean_desciption=Coalesce("desc")
).filter(clean_desciption=""))
print(await Tournament.annotate(trimmed_name=Trim("name")
).filter(trimmed_name="final tournament"))
print(await
Tournament.annotate(name_len=Length("name")
).filter(name_len__gt=len("New Tournament")))
print(await Tournament.annotate(name_lo=Lower("name")
).filter(name_lo="new tournament"))
print(await Tournament.annotate(name_lo=Upper("name")
).filter(name_lo="NEW TOURNAMENT"))
async def get_interval_data(cls, meta_ids: list, start: arrow.Arrow,
end: arrow.Arrow) -> list:
"""
获取时间区内统计数据和
:param meta_ids:
:param start:
:param end:
:param db:
:return:
"""
res = await StatisticsData.filter(
d_t__gte=start.datetime,
d_t__lte=end.datetime,
meta_id__in=meta_ids,
).group_by('meta_id').annotate(s=Sum('val')).values('meta_id', val='s')
return res
async def emojistats_server(self, ctx):
"""Shows statistics about the emoji usage on server."""
embeds = []
guild = ctx.guild
get_member = lambda member_id: guild.get_member(member_id)
data = (await models.EmojiUsageStat.filter(guild_id=guild.id).annotate(
sum=Sum("amount")).group_by("emoji_id").order_by("-sum").values())
last_usage = (await models.EmojiUsageStat.filter(
guild_id=guild.id).order_by("-last_usage").limit(1).values())
last_usage = last_usage[0]
for data in functions.list_to_matrix(data):
emoji = ctx.get_emoji(ctx.guild, last_usage["emoji_id"])
if not emoji:
emoji = "\U0001f5d1"
embed = discord.Embed(color=self.bot.color)
embed.set_author(name=guild, icon_url=guild.icon.url)
embed.description = "{}\n\nEn son: {}".format(
self.get_emoji_stats(ctx, data, key="sum"),
"{} {} `({})`".format(
emoji,
get_member(last_usage["user_id"]).mention,
util_time.humanize(last_usage["last_usage"]),
),
)
embeds.append(embed)
menu = menus.MenuPages(
timeout=30,
clear_reactions_after=True,
source=paginator.EmbedSource(data=embeds),
)
try:
await menu.start(ctx)
except IndexError:
await ctx.send("Kayıt bulunamadı!")
async def run():
await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", modules={"models": ["__main__"]})
# await Tortoise.generate_schemas()
#
# a1 = await Author.create(name="author1")
# a2 = await Author.create(name="author2")
# for i in range(10):
# await Book.create(name=f"book{i}", author_id=a1.id, rating=i)
# for i in range(5):
# await Book.create(name=f"book{i}", author=a2, rating=i)
ret = await Book.annotate(count=Count('id')).group_by('author_id').values()
ret2 = await Book.all().group_by('author_id').annotate(count = Count('id')).values()
print(ret)
print(ret2)
print()
# >>> [{'author_id': 1, 'count': 10}, {'author_id': 2, 'count': 5}]
ret = (
await Book.annotate(count=Count("id"))
.filter(count__gt=6)
.group_by("author_id")
.values("author_id", "count")
)
print(ret)
# >>> [{'author_id': 1, 'count': 10}]
ret = await Book.annotate(sum=Sum("rating")).group_by("author_id").values("author_id", "sum")
print(ret)
# >>> [{'author_id': 1, 'sum': 45.0}, {'author_id': 2, 'sum': 10.0}]
ret = (
await Book.annotate(sum=Sum("rating"))
.filter(sum__gt=11)
.group_by("author_id")
.values("author_id", "sum")
)
print(ret)
# >>> [{'author_id': 1, 'sum': 45.0}]
ret = await Book.annotate(avg=Avg("rating")).group_by("author_id").values("author_id", "avg")
print(ret)
# >>> [{'author_id': 1, 'avg': 4.5}, {'author_id': 2, 'avg': 2.0}]
ret = (
await Book.annotate(avg=Avg("rating"))
.filter(avg__gt=3)
.group_by("author_id")
.values("author_id", "avg")
)
print(ret)
# >>> [{'author_id': 1, 'avg': 4.5}]
# and use .values_list()
ret = (
await Book.annotate(count=Count("id"))
.group_by("author_id")
.values_list("author_id", "count")
)
print(ret)
# >>> [(1, 10), (2, 5)]
# group by with join
ret = (
await Book.annotate(count=Count("id"))
.group_by("author__name")
.values("author__name", "count")
)
print(ret)
