def titulo_inicia_igual_que_su_descripcion_corta(self):
consulta = self.annotate(tit50=Left('titulo', 50),
desc50=Left(
'desc_corta',
50)).filter(tit50=F('desc50')).values(
'isbn', 'tit50', 'desc50')
return consulta
def test_basic(self):
authors = Author.objects.annotate(name_part=Left('name', 5))
self.assertQuerysetEqual(authors.order_by('name'), ['John ', 'Rhond'],
lambda a: a.name_part)
# If alias is null, set it to the first 2 lower characters of the name.
Author.objects.filter(alias__isnull=True).update(
alias=Lower(Left('name', 2)))
self.assertQuerysetEqual(authors.order_by('name'), ['smithj', 'rh'],
lambda a: a.alias)
def test_basic(self):
authors = Author.objects.annotate(name_part=Left("name", 5))
self.assertQuerysetEqual(authors.order_by("name"), ["John ", "Rhond"],
lambda a: a.name_part)
# If alias is null, set it to the first 2 lower characters of the name.
Author.objects.filter(alias__isnull=True).update(
alias=Lower(Left("name", 2)))
self.assertQuerysetEqual(authors.order_by("name"), ["smithj", "rh"],
lambda a: a.alias)
def test_get_aggregate_subquery_raises_error_on_on_aggregate_expression(
self):
"""
Test that an error is raised when passed a non-aggregate expression.
"""
with pytest.raises(ValueError):
get_aggregate_subquery(Person, Left('proofread_books__name', 5))
def getGatherYearMonth():
qs = db.Journal.objects.annotate(ll=Left('date',6)).values('ll').annotate(Count('ll')).order_by('-ll')
ym_list = []
for q in qs:
if len(q['ll']) >= 6:
ym_list.append(q['ll'][0:4] + '/' + q['ll'][4:6])
return ym_list
def get_queryset(self):
qs = super().get_queryset().order_by('-similarity', 'document_a_id')
run_id = self.request.GET.get('run_id')
document_id = self.request.GET.get('document_id')
if run_id:
# choose similar documents inside one run
qs = qs.filter(run_id=run_id)
elif document_id:
# choose similar documents for ONE document inside ONE project
qs = qs.filter(document_a_id=document_id,
document_b__project_id=F('document_a__project_id'))
else:
raise APIException(
'run_id or document_id query parameter required', code=404)
left = self.request.GET.get('text_max_length')
document_b_text_ann = F('document_b__documenttext__full_text')
if left not in ['0']:
try:
left = int(left) or self.text_max_length
except:
left = self.text_max_length
document_b_text_ann = Left(document_b_text_ann, left)
qs = qs.annotate(document_a_name=F('document_a__name'),
document_b_name=F('document_b__name'),
document_b_text=document_b_text_ann)
return qs.select_related('document_a', 'document_b',
'document_b__documenttext', 'run')
def test_transform(self):
with register_lookup(CharField, Ord):
authors = Author.objects.annotate(first_initial=Left('name', 1))
self.assertCountEqual(authors.filter(first_initial__ord=ord('J')),
[self.john])
self.assertCountEqual(authors.exclude(first_initial__ord=ord('J')),
[self.elena, self.rhonda])
def test_expressions_with_params(self):
constraint_name = 'scene_left_equal'
self.assertNotIn(constraint_name, self.get_constraints(Scene._meta.db_table))
constraint = ExclusionConstraint(
name=constraint_name,
expressions=[(Left('scene', 4), RangeOperators.EQUAL)],
)
with connection.schema_editor() as editor:
editor.add_constraint(Scene, constraint)
self.assertIn(constraint_name, self.get_constraints(Scene._meta.db_table))
def test_left_with_expressions(self):
# just use age as a placeholder for retrieving the first characters
# in the name
Author.objects.create(name='John Smith', age=7)
Author.objects.create(name='Rhonda', age=3)
authors = Author.objects.annotate(
name_part=Left('name', F('age'), output_field=CharField()))
self.assertQuerysetEqual(authors.order_by('name'), [
'John Sm',
'Rho',
], lambda a: a.name_part)
def test_left(self):
Author.objects.create(name='John Smith', alias='smithj')
Author.objects.create(name='Rhonda')
authors = Author.objects.annotate(name_part=Left('name', 5))
self.assertQuerysetEqual(authors.order_by('name'), [
'John ',
'Rhond',
], lambda a: a.name_part)
# if alias is null, set to first 2 lower characters of the name
Author.objects.filter(alias__isnull=True).update(alias=Lower(
Left('name', 2)), )
self.assertQuerysetEqual(authors.order_by('name'), [
'smithj',
'rh',
], lambda a: a.alias)
with self.assertRaisesMessage(ValueError,
"'length' must be greater than 0"):
Author.objects.annotate(raises=Left('name', 0))
def desc_corta_resumida(self):
consulta = self.annotate(longitud=Length('desc_corta')).annotate(
desc_resumida=Case(
When(longitud__gt=50,
then=Concat(Left('desc_corta', 50), V('...'))),
default=('desc_corta'),
output_field=CharField(),
)).filter(isbn__in=('1933988592', '1884777791', '1884777589',
'193239415X',
'1933988495')).values('isbn', 'desc_resumida',
'longitud')
return consulta
def _get_summary_categories() -> str:
"""Finds and returns the summary categories and their corresponding
account hints as JSON.
Returns:
The summary categories and their account hints, by their record
types and category types.
"""
rows = Record.objects \
.filter(Q(summary__contains="—"),
~Q(account__code__startswith="114"),
~Q(account__code__startswith="214"),
~Q(account__code__startswith="128"),
~Q(account__code__startswith="228")) \
.annotate(rec_type=Case(When(is_credit=True, then=Value("credit")),
default=Value("debit"),
output_field=CharField()),
cat_type=Case(
When(summary__regex=".+—.+—.+→.+",
then=Value("bus")),
When(summary__regex=".+—.+[→↔].+",
then=Value("travel")),
default=Value("general"),
output_field=CharField()),
category=Left("summary",
StrIndex("summary", Value("—")) - 1,
output_field=CharField())) \
.values("rec_type", "cat_type", "category", "account__code") \
.annotate(count=Count("category")) \
.order_by("rec_type", "cat_type", "category", "-count",
"account__code")
# Sorts the rows by the record type and the category type
categories = {}
for row in rows:
key = "%s-%s" % (row["rec_type"], row["cat_type"])
if key not in categories:
categories[key] = {}
if row["category"] not in categories[key]:
categories[key][row["category"]] = []
categories[key][row["category"]].append(row)
for key in categories:
# Keeps only the first account with most records
categories[key] = [categories[key][x][0] for x in categories[key]]
# Sorts the categories by the frequency
categories[key].sort(key=lambda x: (-x["count"], x["category"]))
# Keeps only the category and the account
categories[key] = [[x["category"], x["account__code"]]
for x in categories[key]]
# Converts the dictionary to a list, as the category may not be
# US-ASCII
return json.dumps(categories)
def search_view(request):
if "q" not in request.GET:
raise Http404("You must specify an item.")
try:
item_id = int(request.GET["q_id"])
# Handles string, not in query
except (ValueError, KeyError):
try:
item_id = Item.objects.only("id").get(name=request.GET["q"]).id
return redirect(f"{request.build_absolute_uri()}&q_id={item_id}")
except (KeyError, Item.DoesNotExist):
# TODO If user cannot find item, show a page with
# other possible options?
raise Http404
# TODO Add pic to charities
paginator = Paginator(
WantedItem.objects.annotate(
charity_description=Left("charity__description", 200),
how_to_donate=Left("charity__how_to_donate", 200),
).select_related("charity").values_list(
"charity_id",
"charity__name",
"charity__logo",
"charity_description",
"how_to_donate",
).filter(item_id=item_id).order_by("-id"),
20,
)
page_obj = paginator.get_page(request.GET.get("page"))
return render(
request,
"pages/search.html",
context={
"page_obj": page_obj,
"item_id": item_id,
"item_name": request.GET["q"],
},
)
def test_chr(self):
Author.objects.create(name='John Smith', alias='smithj')
Author.objects.create(name='Élena Jordan', alias='elena')
Author.objects.create(name='Rhonda')
authors = Author.objects.annotate(first_initial=Left('name', 1))
self.assertQuerysetEqual(authors.filter(first_initial=Chr(ord('J'))),
['John Smith'], lambda x: x.name)
self.assertQuerysetEqual(
authors.exclude(first_initial=Chr(ord('J'))),
['Élena Jordan', 'Rhonda'],
lambda x: x.name,
ordered=False,
)
def test_left(self):
"""
Tests left function applied to a column.
"""
q1 = Author.objects.values("num").annotate(first_initial=Left(
"name", 1), )
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, SUBSTR(tests_author.name, %s, %s) AS " +
"first_initial FROM tests_author",
)
self.assertEqual(params, (1, 1))
def get(self, request, version, format=None):
range_param = request.query_params.dict().pop("range", "day")
start_date = get_start_date(range_param)
jobs = (ProcessorJob.objects.filter(
created_at__gt=start_date).annotate(
reason=Left("failure_reason", 80)).values("reason").annotate(
job_count=Count("reason"),
sample_count=Count(
"original_files__samples",
distinct=True,
filter=Q(original_files__samples__is_processed=False),
),
).order_by("-job_count"))
return paginate_queryset_response(jobs, request)
def extract_authors(apps, schema_editor):
"""
Remove authors from subtitles, copy to author field, and remove from subtitle text.
Example: "blah -- some guy"
hyphens_location = 5 (1-indexed)
text = "some guy"
author = "blah"
"""
Subtitle = apps.get_model("home", "Subtitle")
subtitles = Subtitle.objects.annotate(
hyphens_location=StrIndex('text', V(' -- ')))
subtitles_with_authors = subtitles.filter(hyphens_location__gt=0)
subtitles_with_authors.update(author=Substr('text',
F('hyphens_location') + 4),
text=Left('text',
F('hyphens_location') - 1))
def get_queryset(self, base_queryset=RoundsAndLabsQueryset):
funds = ApplicationBase.objects.filter(path=OuterRef('parent_path'))
return base_queryset(self.model, using=self._db).type(SubmittableStreamForm).annotate(
lead=Coalesce(
F('roundbase__lead__full_name'),
F('labbase__lead__full_name'),
),
start_date=F('roundbase__start_date'),
end_date=F('roundbase__end_date'),
parent_path=Left(F('path'), Length('path') - ApplicationBase.steplen, output_field=CharField()),
fund=Subquery(funds.values('title')[:1]),
lead_pk=Coalesce(
F('roundbase__lead__pk'),
F('labbase__lead__pk'),
),
)
def test_ord_transform(self):
try:
CharField.register_lookup(Ord, 'ord')
Author.objects.create(name='John Smith', alias='smithj')
Author.objects.create(name='Élena Jordan', alias='elena')
Author.objects.create(name='Rhonda')
authors = Author.objects.annotate(first_initial=Left('name', 1))
self.assertQuerysetEqual(
authors.filter(first_initial__ord=ord('J')), ['John Smith'],
lambda x: x.name)
self.assertQuerysetEqual(
authors.exclude(first_initial__ord=ord('J')),
['Élena Jordan', 'Rhonda'],
lambda x: x.name,
ordered=False,
)
finally:
CharField._unregister_lookup(Ord, 'ord')
def handle(self, *args, **options):
ai_imtong = not options['mai_imtong']
makedirs(join(options['TsuLiauGiap'], 'ImTong'))
csvtongmia = join(options['TsuLiauGiap'], 'SuiSiann.csv')
lmjtongmia = join(options['TsuLiauGiap'], 'lmj.json')
lui = dict(
Luī.objects.annotate(luiclass=Concat(Value('lui-'),
'id',
output_field=CharField()),
sok=Left('miâ',
1)).values_list('luiclass', 'sok'))
with open(csvtongmia, 'wt', encoding='utf-8') as tong:
sia = DictWriter(tong,
fieldnames=[
'音檔',
'來源',
'漢字',
'羅馬字',
'口語調',
'長短',
])
sia.writeheader()
kui = 1
bio = 0.0
lts = 0.0
su_soo = 0
ji_soo = 0
lmj = set()
siannun = set()
for kui, 句 in enumerate(
句表.objects.order_by('來源_id', 'id').select_related('來源'),
start=1,
):
if len(句.kaldi切音時間) > 1:
print('有音檔á-bē tok開！')
continue
try:
句物件 = 拆文分析器.建立句物件(句.漢字, 句.羅馬字)
except 解析錯誤 as tshongoo:
print(tshongoo)
continue
原始音檔 = 句.音檔檔案
ku_tngte = get_duration(filename=原始音檔)
bio += ku_tngte
lts += ku_tngte
su_soo += len(句物件.網出詞物件())
ji_soo += len(句物件.篩出字物件())
for 字物件 in 句物件.篩出字物件():
字物件.音 = 字物件.音.lstrip('-').lower()
if not 字物件.敢是標點符號() and 字物件.音標敢著(臺灣閩南語羅馬字拼音):
lmj.add(字物件.看音())
siannun.add(
字物件.轉音(臺灣閩南語羅馬字拼音).看音().rstrip('0987654321'))
wavtongmia = self.tongmia.format(kui)
sia.writerow({
'音檔': wavtongmia,
'來源': 句.來源.文章名,
'漢字': 句.漢字,
'羅馬字': 句.羅馬字,
'口語調': hue_tacotron(lui, 句.羅馬字含口語調),
'長短': '{:.2f}'.format(ku_tngte),
})
kiatko_mia = join(options['TsuLiauGiap'], wavtongmia)
if ai_imtong:
run(
[
'sox',
原始音檔,
kiatko_mia,
],
check=True,
)
print(('結果粒積秒數：{:.2f} 本底音檔秒數：{:.2f}\n'
'總詞數：{} 總字數：{}\n'
'羅馬字種類（考慮書寫聲調，bô考慮輕聲、大小寫、變調類型）：{}\n'
'聲韻種類（bô考慮聲調、輕聲、大小寫）：{}\n').format(
bio, lts, su_soo, ji_soo, len(lmj), len(siannun)),
file=self.stderr)
with open(lmjtongmia, 'w') as tong:
json.dump(
{
'羅馬字': sorted(lmj),
'聲韻': sorted(siannun)
},
tong,
ensure_ascii=False,
sort_keys=True,
indent=2,
)
def test_non_ascii(self):
authors = Author.objects.annotate(first_initial=Left("name", 1))
self.assertCountEqual(authors.filter(first_initial=Chr(ord("É"))),
[self.elena])
self.assertCountEqual(authors.exclude(first_initial=Chr(ord("É"))),
[self.john, self.rhonda])
def get_queryset(self):
return MoonNews.objects.only('title', 'published', 'content', 'publisher__username', 'tags')\
.annotate(short_content=Left('content', 300))
def test_basic(self):
authors = Author.objects.annotate(first_initial=Left('name', 1))
self.assertCountEqual(authors.filter(first_initial=Chr(ord('J'))),
[self.john])
self.assertCountEqual(authors.exclude(first_initial=Chr(ord('J'))),
[self.elena, self.rhonda])
def test_expressions(self):
authors = Author.objects.annotate(
name_part=Left('name', Value(3), output_field=CharField()))
self.assertQuerysetEqual(authors.order_by('name'), ['Joh', 'Rho'],
lambda a: a.name_part)
def test_invalid_length(self):
with self.assertRaisesMessage(ValueError,
"'length' must be greater than 0"):
Author.objects.annotate(raises=Left('name', 0))
#!/usr/bin/env python
from django.db.models.functions import Left
from .models import MyModel
"""
https://docs.djangoproject.com/en/dev/ref/models/database-functions/#left
class Left(expression, length, **extra)
"""
qs = MyModel.objects.annotate(first_initial=Left('name', 1))
for r in qs.all():
print(r.first_initial)
def get_queryset(self):
request_data = self.request.data
qs = super().get_queryset().order_by('-similarity', 'text_unit_a_id')
run_id = request_data.get('run_id')
text_unit_id = request_data.get('text_unit_id')
last_run = 'last_run' in request_data # any value
document_id = request_data.get('document_id')
location_start = request_data.get('location_start')
location_end = request_data.get('location_end')
if 'selection' in request_data:
# we got location of the annotation as coordinates
selection = request_data['selection']
location = CoordTextMap.get_text_location_by_coords(
document_id, selection)
location_start, location_end = location
request_data['location_start'] = location_start
request_data['location_end'] = location_end
if run_id:
# choose ALL similar text units inside ONE run
qs = qs.filter(run_id=run_id)
elif text_unit_id:
# choose similar text units for ONE text unit inside ONE project
qs = qs.filter(text_unit_a_id=text_unit_id,
project_b=F('project_a'))
elif document_id and location_start is not None and location_end is not None:
# choose similar text units for {SOME chosen text units by location} inside ONE project
text_unit_ids = self.get_text_unit_a_ids(request_data)
qs = qs.filter(text_unit_a_id__in=text_unit_ids,
project_b=F('project_a'))
else:
raise APIException(
'"run_id" or "text_unit_id" or'
' ("document_id" and "location_start" and "location_end") or'
' ("document_id" and "selection")'
' query parameters required',
code=404)
if last_run and qs.exists():
last_run_id = qs.order_by('run_id').last().run_id
qs = qs.filter(run_id=last_run_id)
# limit text size if needed to speed up api
left = self.request.GET.get('text_max_length')
text_unit_a_text_ann = F('text_unit_a__textunittext__text')
text_unit_b_text_ann = F('text_unit_b__textunittext__text')
if left not in ['0']:
try:
left = int(left) or self.text_max_length
except:
left = self.text_max_length
text_unit_b_text_ann = Left(text_unit_b_text_ann, left)
qs = qs.annotate(document_a_name=F('document_a__name'),
document_b_name=F('document_b__name'),
text_unit_a_text=text_unit_a_text_ann,
text_unit_b_text=text_unit_b_text_ann)
return qs.select_related('document_a', 'text_unit_a',
'text_unit_a__textunittext', 'document_b',
'text_unit_b', 'text_unit_b__textunittext',
'run')
def test_expressions(self):
authors = Author.objects.annotate(
name_part=Left("name", Value(3, output_field=IntegerField())))
self.assertQuerysetEqual(authors.order_by("name"), ["Joh", "Rho"],
lambda a: a.name_part)
