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)