def mane_GRCh37_list(request): sql = """ SELECT DISTINCT t1.transcript_id, t1.stable_id as ens_stable_id, t1.stable_id_version as ens_stable_id_version, relationship_type.shortname as mane_type, t2.stable_id as refseq_stable_id, t2.stable_id_version as refseq_stable_id_version, gn1.name as ens_gene_name, t3.stable_id as grch37_stable_id, t3.stable_id_version as grch37_stable_id_version, IF(tl3.five_utr_checksum = tl1.five_utr_checksum,'True','False') as five_prime_utr, 'True' as cds, IF(tl3.three_utr_checksum = tl1.three_utr_checksum,'True','False') as three_prime_utr FROM transcript t1 JOIN transcript_release_tag trt1 ON t1.transcript_id=trt1.feature_id JOIN transcript_release_tag_relationship ON trt1.transcript_release_id=transcript_release_tag_relationship.transcript_release_object_id JOIN transcript_release_tag trt2 ON transcript_release_tag_relationship.transcript_release_subject_id=trt2.transcript_release_id JOIN transcript t2 ON trt2.feature_id=t2.transcript_id JOIN relationship_type ON transcript_release_tag_relationship.relationship_type_id=relationship_type.relationship_type_id JOIN transcript_gene tg1 ON t1.transcript_id=tg1.transcript_id JOIN gene gene1 ON tg1.gene_id=gene1.gene_id JOIN gene_names gn1 ON gene1.name_id=gn1.external_id and gn1.primary_id=1 JOIN transcript t3 ON t3.stable_id = t1.stable_id AND t3.assembly_id = 1 JOIN translation_transcript tt1 ON tt1.transcript_id = t1.transcript_id JOIN translation tl1 ON tl1.translation_id = tt1.translation_id JOIN translation_transcript tt3 ON tt3.transcript_id = t3.transcript_id JOIN translation tl3 ON tl3.translation_id = tt3.translation_id WHERE t1.assembly_id = 1001 and tl3.seq_checksum = tl1.seq_checksum ORDER BY gn1.name; """ with connections['tark'].cursor() as cursor: cursor.execute(sql) results = ReleaseUtils.dictfetchall(cursor) page = request.GET.get('page', 1) paginator = Paginator(results,25) try: results = paginator.page(page) except PageNotAnInteger: results = paginator.page(1) except EmptyPage: results = paginator.page(paginator.num_pages) return render( request, 'mane_GRCh37_list.html', context={ 'results': results } )
def manelist(request): sql = """ SELECT DISTINCT t1.transcript_id, t1.stable_id as ens_stable_id, t1.stable_id_version as ens_stable_id_version, relationship_type.shortname as mane_type, t2.stable_id as refseq_stable_id, t2.stable_id_version as refseq_stable_id_version, gn1.name as ens_gene_name FROM transcript t1 JOIN transcript_release_tag trt1 ON t1.transcript_id=trt1.feature_id JOIN transcript_release_tag_relationship ON trt1.transcript_release_id=transcript_release_tag_relationship.transcript_release_object_id JOIN transcript_release_tag trt2 ON transcript_release_tag_relationship.transcript_release_subject_id=trt2.transcript_release_id JOIN transcript t2 ON trt2.feature_id=t2.transcript_id JOIN relationship_type ON transcript_release_tag_relationship.relationship_type_id=relationship_type.relationship_type_id JOIN transcript_gene tg1 ON t1.transcript_id=tg1.transcript_id JOIN gene gene1 ON tg1.gene_id=gene1.gene_id JOIN gene_names gn1 ON gene1.name_id=gn1.external_id where gn1.primary_id=1 ORDER BY gn1.name; """ with connections['tark'].cursor() as cursor: cursor.execute(sql) results = ReleaseUtils.dictfetchall(cursor) return render( request, 'mane_list.html', context={ 'results': results } )
def feature_diff(request, feature, from_release, to_release, direction="changed", source="Ensembl"): """ Get the list of features that are different between two releases in the context (addition, deletion or change) Parameters ---------- feature : str version : int direction : str One of gained|removed|changed Returns ------- count : int """ # print("feature {} from_release {}, to_release {}, direction {}, source {} ".format(feature, from_release, to_release, direction, source)) sql = """ SELECT v0.stable_id as from_stable_id, v0.stable_id_version as from_stable_id_version, v1.stable_id as to_stable_id, v1.stable_id_version as to_stable_id_version FROM ( SELECT #FEATURE#.stable_id, #FEATURE#.stable_id_version, f_tag.feature_id, rs.shortname, rs.description, rs.assembly_id FROM #FEATURE# JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id) JOIN release_set AS rs ON (f_tag.release_id=rs.release_id) JOIN release_source AS rst ON (rs.source_id=rst.source_id) WHERE rs.shortname=%s AND rst.shortname=%s ) AS v0 #DIRECTION# JOIN ( SELECT #FEATURE#.stable_id, #FEATURE#.stable_id_version, f_tag.feature_id, rs.shortname, rs.description, rs.assembly_id FROM #FEATURE# JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id) JOIN release_set AS rs ON (f_tag.release_id=rs.release_id) JOIN release_source AS rst ON (rs.source_id=rst.source_id) WHERE rs.shortname=%s AND rst.shortname=%s ) AS v1 ON (v0.stable_id=v1.stable_id) WHERE #OUTER_WHERE#; """ sql = sql.replace('#FEATURE#', feature) if direction == 'removed': sql = sql.replace('#DIRECTION#', 'LEFT') sql = sql.replace('#OUTER_WHERE#', 'v1.stable_id IS NULL') elif direction == 'gained': sql = sql.replace('#DIRECTION#', 'RIGHT') sql = sql.replace('#OUTER_WHERE#', 'v0.stable_id IS NULL') else: sql = sql.replace('#DIRECTION#', '') sql = sql.replace('#OUTER_WHERE#', 'v0.stable_id_version!=v1.stable_id_version') # print(sql) with connections['tark'].cursor() as cursor: cursor.execute(sql, [ str(from_release), source, str(to_release), source, ]) results = ReleaseUtils.dictfetchall(cursor) return render(request, 'feature_diff_list.html', context={ 'feature': feature, 'from_release': from_release, 'to_release': to_release, 'source': source, 'direction': direction, 'results': results })
def feature_diff(request, feature, from_release, to_release, direction="changed", source="Ensembl"): """ Get the list of features that are different between two releases in the context (addition, deletion or change) Parameters ---------- feature : str version : int direction : str One of gained|removed|changed Returns ------- count : int """ # print("feature {} from_release {}, to_release {}, direction {}, source {} ".format(feature, from_release, to_release, direction, source)) # Change the original sql query to include biotypes from gene and transcript tables sql = """ SELECT v0.stable_id as from_stable_id, v0.stable_id_version as from_stable_id_version, v1.stable_id as to_stable_id, v1.stable_id_version as to_stable_id_version, v1.biotype as new_biotype, case when v1.biotype=v0.biotype then ' ' else v0.biotype end as previous_biotype FROM ( SELECT #FEATURE#.stable_id, #FEATURE#.stable_id_version, #FEATURE#.biotype, f_tag.feature_id, rs.shortname, rs.description, rs.assembly_id FROM #FEATURE# JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id) JOIN release_set AS rs ON (f_tag.release_id=rs.release_id) JOIN release_source AS rst ON (rs.source_id=rst.source_id) WHERE rs.shortname=%s AND rst.shortname=%s ) AS v0 #DIRECTION# JOIN ( SELECT #FEATURE#.stable_id, #FEATURE#.stable_id_version, #FEATURE#.biotype, f_tag.feature_id, rs.shortname, rs.description, rs.assembly_id FROM #FEATURE# JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id) JOIN release_set AS rs ON (f_tag.release_id=rs.release_id) JOIN release_source AS rst ON (rs.source_id=rst.source_id) WHERE rs.shortname=%s AND rst.shortname=%s ) AS v1 ON (v0.stable_id=v1.stable_id) WHERE #OUTER_WHERE#; """ # In Release stats gene count details page, display gene name along with stable_id, if gene name is present in database if feature == 'gene': sql = """ SELECT v0.gene_symbol as from_gene, v0.stable_id as from_stable_id, v0.stable_id_version as from_stable_id_version, v1.gene_symbol as to_gene, v1.stable_id as to_stable_id, v1.stable_id_version as to_stable_id_version,v1.biotype as new_biotype, case when v1.biotype=v0.biotype then ' ' else v0.biotype end as previous_biotype FROM ( SELECT IF(gn.name IS NULL, '', gn.name) as gene_symbol, #FEATURE#.stable_id, #FEATURE#.stable_id_version, #FEATURE#.biotype, f_tag.feature_id, rs.shortname, rs.description, rs.assembly_id FROM #FEATURE# JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id) JOIN release_set AS rs ON (f_tag.release_id=rs.release_id) JOIN release_source AS rst ON (rs.source_id=rst.source_id) LEFT JOIN gene_names AS gn ON (#FEATURE#.name_id = gn.external_id) AND (gn.primary_id = 1) WHERE rs.shortname=%s AND rst.shortname=%s ) AS v0 #DIRECTION# JOIN ( SELECT IF(gn.name IS NULL, '', gn.name) as gene_symbol, #FEATURE#.stable_id, #FEATURE#.stable_id_version, #FEATURE#.biotype, f_tag.feature_id, rs.shortname, rs.description, rs.assembly_id FROM #FEATURE# JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id) JOIN release_set AS rs ON (f_tag.release_id=rs.release_id) JOIN release_source AS rst ON (rs.source_id=rst.source_id) LEFT JOIN gene_names AS gn ON (#FEATURE#.name_id = gn.external_id) AND (gn.primary_id = 1) WHERE rs.shortname=%s AND rst.shortname=%s ) AS v1 ON (v0.stable_id=v1.stable_id) WHERE #OUTER_WHERE#; """ sql = sql.replace('#FEATURE#', feature) if direction == 'removed': sql = sql.replace('#DIRECTION#', 'LEFT') sql = sql.replace('#OUTER_WHERE#', 'v1.stable_id IS NULL') elif direction == 'gained': sql = sql.replace('#DIRECTION#', 'RIGHT') sql = sql.replace('#OUTER_WHERE#', 'v0.stable_id IS NULL') else: sql = sql.replace('#DIRECTION#', '') sql = sql.replace( '#OUTER_WHERE#', 'v0.stable_id_version!=v1.stable_id_version' ) # print(sql) with connections['tark'].cursor() as cursor: cursor.execute( sql, [ str(from_release), source, str(to_release), source, ] ) results = ReleaseUtils.dictfetchall(cursor) return render( request, 'feature_diff_list.html', context={ 'feature': feature, 'from_release': from_release, 'to_release': to_release, 'source': source, 'direction': direction, 'results': results } )