def save_configuration(pageType, pagePart, language="en"):
config = db.helpers.legacy_query_user_config(language=language, entity=pageType)
application.logger.debug(pageType)
application.logger.debug(pagePart)
if pageType == "my_patients":
pageType = "hpo"
application.logger.debug(config)
for col in config[0][pagePart]["colNames"]:
if col["key"] in request.form.getlist("colNames[]"):
application.logger.debug(col["key"], True)
col["default"] = True
else:
application.logger.debug(col["key"], False)
col["default"] = False
c = postgres_cursor()
try:
c.execute(
"UPDATE user_config SET config=%s WHERE user_name=%s AND language=%s AND page=%s",
(json.dumps(config), session[USER], language, pageType),
)
get_db().commit()
c.close()
except (Exception, psycopg2.DatabaseError) as error:
application.logger.exception(error)
get_db().rollback()
return jsonify("save configuration failed"), 500
finally:
c.close()
return jsonify(success=""), 200
def get_all_genes():
with session_scope() as db_session:
try:
limit, offset = _get_pagination_parameters()
if limit > MAX_PAGE_SIZE:
return (
jsonify(message="The maximum page size for genes is {}".
format(MAX_PAGE_SIZE)),
400,
)
sqlq_end = sql.SQL("""
and exists (
select 1 from public.users_individuals ui
join phenopolis.individual i on i.phenopolis_id = ui.internal_id
join phenopolis.individual_gene ig on i.id = ig.individual_id and ig.gene_id = g.identifier
where ui."user" = %(user)s)
""")
sqlq = sqlq_main + sqlq_end + sql.SQL("limit {} offset {}".format(
limit, offset))
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, {
"user": session[USER],
"hga": HG_ASSEMBLY
})
genes = cursor2dict(cur)
process_for_display(db_session, genes)
except PhenopolisException as e:
return jsonify(success=False, message=str(e)), e.http_status
return jsonify(genes), 200
def _get_hpos(features: List[str]):
with get_db() as conn:
with conn.cursor() as cur:
cur.execute("select * from hpo.term t where t.name = any(%s);",
[features])
res = cur.fetchall()
return res
def _search_phenotypes(db_session: Session, query, limit):
r"""
A user may search for things like 'Abnormality of body height' or for an HPO id as HP:1234567 (ie: HP:\d{7})
or just a seq of numbers like '1234'
"""
if HPO_REGEX.match(query) or NUMERIC_REGEX.match(query):
sqlq = sql.SQL("""
select t.hpo_id, t."name" from hpo.term t where t.hpo_id ~ %(query)s order by t.id limit %(limit)s
""")
else:
# TODO: search also over synonyms
# TODO: return the distance so the frontend have greater flexibility
# NOTE: order results by similarity and then by hpo_name (case insensitive)
sqlq = sql.SQL("""
select
t.hpo_id,
t."name" ,
t."name" <-> %(query)s as distance
from
hpo.term t
where
t."name" %% %(query)s
order by
distance,
lower(t."name")
limit %(limit)s
""")
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, {"query": query, "limit": limit})
phenotypes = cursor2dict(cur)
return [f"hpo::{x.get('name')}::{x.get('hpo_id')}" for x in phenotypes]
def process_for_display(db_session: Session, data):
with get_db() as conn:
with conn.cursor() as cur:
cur.execute('select ui.internal_id from public.users_individuals ui where ui."user" = %s', [session[USER]])
my_patients = [x[0] for x in cur.fetchall()]
# TODO: avoid this transformation to dict and use the objects themselves
for x2 in data:
if "CHROM" in x2 and "POS" in x2 and "REF" in x2 and "ALT" in x2:
variant_id = f'{x2["CHROM"]}-{x2["POS"]}-{x2["REF"]}-{x2["ALT"]}'
x2["variant_id"] = [{"end_href": variant_id, "display": variant_id[:60]}]
if "gene_symbol" in x2:
x2["gene_symbol"] = [{"display": x3} for x3 in x2["gene_symbol"].split(",") if x3]
if x2.get("HET"):
x2["HET"] = [
{"display": "my:" + x3, "end_href": x3}
if x3 in my_patients
else {}
if session[USER] == DEMO_USER
else {"display": x3, "end_href": x3}
for x3 in x2["HET"]
]
if x2.get("HOM"):
x2["HOM"] = [
{"display": "my:" + x3, "end_href": x3}
if x3 in my_patients
else {}
if session[USER] == DEMO_USER
else {"display": x3, "end_href": x3}
for x3 in x2["HOM"]
]
def _update_individual(consanguinity, gender: Sex, genes, hpos: List[tuple],
individual: Individual):
"""
Updates tables:
phenopolis.individual: col(gender)
phenopolis.individual_feature: col(feature_id) # hpo_ids
phenopolis.individual_gene: col(gene_id) # maps hgnc_symbol -> gene.identifier
given hgnc_symbol MUST exactly match hgnc_symbols in ensembl.gene table otherwise returns []
"""
individual.sex = gender
individual.consanguinity = consanguinity
hpo_ids = [h[0] for h in hpos]
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""
delete from phenopolis.individual_feature where individual_id = %(id)s
and "type" = any('{observed,simplified}');
insert into phenopolis.individual_feature (individual_id, feature_id, type) select %(id)s as individual_id,
unnest(%(hpo_ids)s::int[]) as feature_id, 'observed' as type;
insert into phenopolis.individual_feature (individual_id, feature_id, type) select %(id)s as individual_id,
unnest(%(hpo_ids)s::int[]) as feature_id, 'simplified' as type;
delete from phenopolis.individual_gene where individual_id = %(id)s;
insert into phenopolis.individual_gene (individual_id, gene_id) select %(id)s as individual_id,
identifier from ensembl.gene where hgnc_symbol = any(%(genes)s::text[]) and assembly = %(hga)s;
""",
{
"id": individual.id,
"hpo_ids": hpo_ids,
"genes": genes,
"hga": HG_ASSEMBLY
},
)
def get_all_hpos():
with session_scope() as db_session:
individuals = _get_authorized_individuals(db_session)
sqlq_all_hpos = sql.SQL("""
select distinct t.hpo_id, t."name" from phenopolis.individual_feature ife
join hpo.term t on t.id = ife.feature_id
where ife.individual_id = any(%s) and ife.type in ('observed')
""")
try:
limit, offset = _get_pagination_parameters()
if limit > MAX_PAGE_SIZE:
return (
jsonify(message="The maximum page size for variants is {}".
format(MAX_PAGE_SIZE)),
400,
)
sqlq = sqlq_all_hpos + sql.SQL("limit {} offset {}".format(
limit, offset))
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, [[x.id for x in individuals]])
hpos = cursor2dict(cur)
process_for_display(db_session, hpos)
except PhenopolisException as e:
return jsonify(success=False, message=str(e)), e.http_status
return jsonify(hpos), 200
def count_hpos(individuals: List[Individual]):
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""select ife.type, count(distinct ife.feature_id) from phenopolis.individual_feature ife
where ife.individual_id = any(%s) and ife.type in ('observed','unobserved') group by ife.type""",
[[x.id for x in individuals]],
)
res = dict(cur.fetchall())
return res.get("observed", 0), res.get("unobserved", 0)
def _count_all_individuals_by_sex(sex: Sex) -> int:
"""
For admin users it counts all individuals and all users having access to them.
But for other than admin it counts only individuals which this user has access, other users having access are
not counted
"""
query = _query_all_individuals(sex)
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(query, (session[USER], sex.name))
return cur.rowcount
def count_genes(individuals: List[Individual]):
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""
select distinct gene_id from phenopolis.individual_gene ig
join ensembl.gene g on g.identifier = ig.gene_id
where g.assembly = %s and g.chromosome ~ '^X|^Y|^[0-9]{1,2}'
and ig.individual_id = any(%s)
""",
[HG_ASSEMBLY, [x.id for x in individuals]],
)
return cur.rowcount
def _insert_genes(individual, genes):
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""
insert into phenopolis.individual_gene (individual_id, gene_id) select %(id)s as individual_id,
identifier from ensembl.gene where hgnc_symbol = any(%(genes)s::text[]) and assembly = %(hga)s;
""",
{
"id": individual.id,
"genes": genes,
"hga": HG_ASSEMBLY
},
)
def _insert_feats(individual, hpo_ids):
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""
insert into phenopolis.individual_feature (individual_id, feature_id, type)
select %(id)s as individual_id, t.id as feature_id, unnest('{observed,simplified}'::text[]) as type
from hpo.term t where t.hpo_id = any(%(hpo_ids)s::text[]) order by type, feature_id;
""",
{
"id": individual.id,
"hpo_ids": hpo_ids
},
)
def _fetch_all_individuals(db_session: Session, offset, limit) -> List[Dict]:
"""
For admin user it returns all individuals and all users having access to them.
But for others than admin it returns only individuals which this user has access,
other users having access are not returned
"""
query = _query_all_individuals() + sql.SQL("limit {} offset {}".format(
limit, offset))
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(query, [session[USER]])
individuals = sorted(cursor2dict(cur), key=lambda i: i["id"])
if session[USER] != ADMIN_USER:
for dd in individuals:
dd["users"] = [session[USER]]
return individuals
def _get_gene(db_session: Session, gene_id):
g_id = gene_id.upper()
sqlq_end = sql.SQL(
"and (g.ensembl_gene_id = %(g_id)s or upper(g.hgnc_symbol) = %(g_id)s)"
)
sqlq = sqlq_main + sqlq_end
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, {"g_id": g_id, "hga": HG_ASSEMBLY})
gene = cursor2dict(cur)
if not gene:
application.logger.info("Using gene_synonym")
sqlq_end = sql.SQL("and upper(gs.external_synonym) = %(g_id)s")
sqlq = sqlq_main + sqlq_end
cur.execute(sqlq, {"g_id": g_id, "hga": HG_ASSEMBLY})
gene = cursor2dict(cur)
return gene
def _get_genes_for_individual(
individual: Union[Individual, dict]) -> List[Tuple[str]]:
"""returns e.g. [('TTLL5',)]"""
if isinstance(individual, Individual):
ind_id = individual.id
elif isinstance(individual, dict):
ind_id = individual.get("id")
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""
select distinct g.hgnc_symbol from phenopolis.individual i
join phenopolis.individual_gene ig on i.id = ig.individual_id
join ensembl.gene g on g.identifier = ig.gene_id
and i.id = %s""",
[ind_id],
)
genes = cur.fetchall()
return genes
def get_all_variants():
with session_scope() as db_session:
try:
limit, offset = _get_pagination_parameters()
if limit > MAX_PAGE_SIZE:
return (
jsonify(message="The maximum page size for variants is {}".
format(MAX_PAGE_SIZE)),
400,
)
sqlq = sqlq_all_variants + sql.SQL("limit {} offset {}".format(
limit, offset))
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, [session[USER]])
variants = cursor2dict(cur)
process_for_display(db_session, variants)
except PhenopolisException as e:
return jsonify(success=False, message=str(e)), e.http_status
return jsonify(variants), 200
def _individual_preview(config, individual: Individual):
sql_zig = """select iv.zygosity,count(*) from phenopolis.variant v
join phenopolis.individual_variant iv on iv.variant_id = v.id
where iv.individual_id = %s
group by iv.zygosity"""
sql_comp = """select sum(c)::int as total from(
select count(v.*) as c
from phenopolis.variant_gene vg
join phenopolis.individual_variant iv on iv.variant_id = vg.variant_id
join phenopolis.variant v on v.id = iv.variant_id
where iv.individual_id = %s and iv.zygosity = 'HET'
group by vg.gene_id having count(v.*) > 1
) as com"""
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sql_zig, [individual.id])
hh = dict(cur.fetchall())
cur.execute(sql_comp, [individual.id])
hc = cur.fetchone()[0] or 0
hom_count = hh.get("HOM", 0)
het_count = hh.get("HET", 0)
comp_het_count = hc
external_id = individual.external_id
if session[USER] == DEMO_USER:
external_id = "_demo_"
# TODO: make a dict of this and not a list of lists
config[0]["preview"] = [
["External_id", external_id],
["Sex", individual.sex.name],
["Genes", [g[0] for g in _get_genes_for_individual(individual)]],
["Features", [f[1] for f in _get_feature_for_individual(individual)]],
["Number of hom variants", hom_count],
["Number of compound hets", comp_het_count],
["Number of het variants", het_count],
]
return config
def _get_feature_for_individual(
individual: Union[Individual, dict],
atype: str = "simplified") -> List[Tuple[str, str]]:
"""
returns observed_features for a given individual
options are: simplified (default), observed, unobserved
e.g. [('HP:0000007', 'Autosomal recessive inheritance'), ('HP:0000505', 'Visual impairment')]
"""
if isinstance(individual, Individual):
ind_id = individual.id
elif isinstance(individual, dict):
ind_id = individual.get("id")
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(
"""
select distinct t.hpo_id, t."name" from phenopolis.individual i
join phenopolis.individual_feature if2 on (i.id = if2.individual_id)
join hpo.term t on (t.id = if2.feature_id) and if2."type" = %s
and i.id = %s""",
(atype, ind_id),
)
res = cur.fetchall()
return res
def _search_genes(db_session: Session, query, limit):
"""
Either search for:
- a gene id like 'ENSG000...'
- a transcript id like 'ENST000...' not only canonical
- a protein id like 'ENSP000...' not only canonical
- a numeric id without any qualifier like '12345'
- a gene name like 'TTLL...'
- a gene synonym like 'asd...'
The order of results is sorted by gene identifier for the 3 searches by identifier; and it is sorted by similarity
for gene name and gene synonym searches
"""
is_identifier_query = (ENSEMBL_GENE_REGEX.match(query)
or ENSEMBL_TRANSCRIPT_REGEX.match(query)
or NUMERIC_REGEX.match(query)
or ENSEMBL_PROTEIN_REGEX.match(query))
if is_identifier_query:
query = remove_version_from_id(query)
sqlq = sql.SQL("""
select distinct g.hgnc_symbol, g.ensembl_gene_id
from ensembl.gene g
left outer join ensembl.transcript t on g.ensembl_gene_id = t.ensembl_gene_id
where g.assembly = %(hga)s
and g.chromosome ~ '^X|^Y|^[0-9]{1,2}'
and (
g.ensembl_gene_id ~* %(query)s or
t.ensembl_transcript_id ~* %(query)s or
t.ensembl_peptide_id ~* %(query)s
)
order by g.ensembl_gene_id
limit %(limit)s
""")
else:
sqlq = sql.SQL("""
select
g.hgnc_symbol,
g.ensembl_gene_id ,
g.hgnc_symbol <-> %(query)s as distance
from
ensembl.gene g
where g.assembly = %(hga)s
and g.chromosome ~ '^X|^Y|^[0-9]{1,2}'
and g.hgnc_symbol %% %(query)s
order by
distance,
lower(g.hgnc_symbol)
limit %(limit)s
""")
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, {
"query": query,
"limit": limit,
"hga": HG_ASSEMBLY
})
genes = cursor2dict(cur)
if not genes:
sqlq = sql.SQL("""
select
g.hgnc_symbol,
g.ensembl_gene_id ,
gs.external_synonym <-> %(query)s as distance
from
ensembl.gene g
join ensembl.gene_synonym gs on gs.gene = g.identifier
where g.assembly = %(hga)s
and g.chromosome ~ '^X|^Y|^[0-9]{1,2}'
and gs.external_synonym %% %(query)s
order by
distance,
lower(gs.external_synonym)
limit %(limit)s
""")
cur.execute(sqlq, {
"query": query,
"limit": limit,
"hga": HG_ASSEMBLY
})
genes = cursor2dict(cur)
return [
f"gene::{x.get('hgnc_symbol')}::{x.get('ensembl_gene_id')}"
for x in genes
]
def _get_variants(target: str):
"""Returns a list of dict variants
Args:
target (str):
* variant_id (e.g '14-76156575-A-G') - and it will return the variant(s) dict for it
'12-7241974-C-T', e.g., returns 2 dicts because of 'phenopolis.individual_variant'
* gene_id (e.g. 'ENSG00000144285') - and it will return all variants linked to that gene
* phenopolis_id (e.g. 'PH00008256') - and it will return all variants linked to that patient
input 'target' must obey its respective string format.
Returns:
List[dict variant]: empty ([]), one or more variants depending on input target
"""
if CHROMOSOME_POS_REF_ALT_REGEX.match(target):
c, p, r, a = target.split("-")
filter = sql.SQL(
f"""where v.chrom = '{c}' and v.pos = {p} and v."ref" = '{r}' and v.alt = '{a}'"""
)
elif ENSEMBL_GENE_REGEX.match(target):
filter = sql.SQL(f"where vg.gene_id = '{target}'")
elif PATIENT_REGEX.match(target):
filter = sql.SQL(f"where i2.phenopolis_id = '{target}'")
else:
return []
sqlq_main = sql.SQL("""
select
array_agg(distinct iv.zygosity order by iv.zygosity) as zigosity,
array_agg(distinct concat(g.hgnc_symbol,'@',g.ensembl_gene_id)) as genes, -- to split
v.chrom as "CHROM", v.pos as "POS", v."ref" as "REF", v.alt as "ALT", v.cadd_phred, v.dann,
v.fathmm_score, v.revel, -- new added
-- removed: v.id
vg.most_severe_consequence, string_agg(distinct vg.hgvs_c,',' order by vg.hgvs_c) as hgvsc,
string_agg(distinct vg.hgvs_p,',' order by vg.hgvs_p) as hgvsp, -- via variant_gene
iv.dp as "DP", iv."fs" as "FS", iv.mq as "MQ", iv."filter" as "FILTER", -- via individual_variant
(
select array_agg(i.phenopolis_id order by i.id)
from phenopolis.individual i
join phenopolis.individual_variant iv2 on iv2.individual_id = i.id and iv2.zygosity = 'HOM'
where v.id = iv2.variant_id
) as "HOM",
(
select array_agg(i.phenopolis_id order by i.id)
from phenopolis.individual i
join phenopolis.individual_variant iv2 on iv2.individual_id = i.id and iv2.zygosity = 'HET'
where v.id = iv2.variant_id
) as "HET",
(
select distinct on (ah.chrom,ah.pos,ah."ref",ah.alt) ah.af from kaviar.annotation_hg19 ah
where ah.chrom = v.chrom and ah.pos = v.pos and ah."ref" = v."ref" and ah.alt = v.alt
order by ah.chrom,ah.pos,ah."ref",ah.alt,ah.ac desc
) as af_kaviar,
av.af as af_gnomad_genomes -- gnomad # NOTE: missing strand?
-- deprecated: MLEAF, MLEAC
-- need to be added (by Daniele): af_converge, af_hgvd, af_jirdc, af_krgdb, af_tommo,
from phenopolis.variant v
join phenopolis.individual_variant iv on iv.variant_id = v.id
join phenopolis.individual i2 on i2.id = iv.individual_id
left outer join phenopolis.variant_gene vg on vg.variant_id = v.id -- variant_gene not complete?
left outer join ensembl.gene g on vg.gene_id = g.ensembl_gene_id
and g.assembly = %(hga)s and g.chromosome ~ '^X|^Y|^[0-9]{1,2}'
left outer join gnomad.annotation_v3 av
on av.chrom = v.chrom and av.pos = v.pos and av."ref" = v."ref" and av.alt = v.alt
--where v.chrom = '12' and v.pos = 7241974 and v."ref" = 'C' and v.alt = 'T' -- 2 rows
--where v.chrom = '7' and v.pos = 2303057 and v."ref" = 'G' and v.alt = 'A' -- 1 row
--where i2.phenopolis_id = 'PH00008256'
--where vg.gene_id = 'ENSG00000144285'
""")
sqlq_end = sql.SQL("""
group by "CHROM","POS","REF","ALT",cadd_phred,dann,fathmm_score,revel,most_severe_consequence,
"DP","FS","MQ","FILTER", -- need for array_agg but disambiguates depending on individual_variant
"HOM","HET",af_kaviar,af_gnomad_genomes
order by
substring(v.chrom FROM '([0-9]+)')::int,
v.pos, v."ref", v.alt, iv.dp desc
""")
sqlq = sqlq_main + filter + sqlq_end
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, {"hga": HG_ASSEMBLY})
variants = cursor2dict(cur)
for v in variants:
gs, gi = zip(*[x.split("@") for x in sorted(v["genes"])])
v["gene_symbol"] = ",".join([x for x in gs if x])
v["gene_id"] = ",".join([x for x in gi if x])
if not v["HET"]:
v["HET"] = []
if not v["HOM"]:
v["HOM"] = []
v["HET_COUNT"] = len(v["HET"])
v["HOM_COUNT"] = len(v["HOM"])
v["AC"] = v["HET_COUNT"] + 2 * v["HOM_COUNT"]
v["AN"] = (v["HET_COUNT"] + v["HOM_COUNT"]) * 2
v["AF"] = v["AC"] / v["AN"]
v["af_hgvd"] = "TBA" # to be added
v["af_converge"] = "TBA" # to be added
v["af_jirdc"] = "TBA" # to be added
v["af_krgdb"] = "TBA" # to be added
v["af_tommo"] = "TBA" # to be added
# -------
v["ID"] = "TBR" # to be removed
v["MLEAC"] = "TBR" # to be removed
v["MLEAF"] = "TBR" # to be removed
for x, y in v.items():
if y is None:
v[x] = "NA"
return variants
def count_variants():
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq_all_variants, [session[USER]])
return cur.rowcount
def hpo(hpo_id="HP:0000001", subset="all", language="en"):
with session_scope() as db_session:
config = query_user_config(db_session=db_session,
language=language,
entity="hpo")
field = "hpo_id"
if not hpo_id.startswith("HP:"):
field = "name"
sql_query = sql.SQL(rf"""
select
t.id, t.hpo_id, t.name
from
hpo.term t
where
t.id in (
select
regexp_split_to_table(path::text, '\.')::int as ancestor
from
hpo.is_a_path tpath
join hpo.term ht on
tpath.term_id = ht.id
where
ht.{field} = %s )
order by
t.id""")
sqlq = sql_query
with get_db() as conn:
with conn.cursor() as cur:
if subset == "preview":
ni = _preview(cur, session[USER], hpo_id)
config[0]["preview"] = [["Number of Individuals", ni]]
return jsonify([{subset: y["preview"]} for y in config])
cur.execute(sqlq, [hpo_id])
res = cursor2dict(cur)
application.logger.debug(res)
data = [x for x in res if x[field] == hpo_id]
if not data:
response = jsonify(message="HPO not found")
response.status_code = 404
return response
d_hpo = data[0]
h_id = d_hpo["id"]
hpo_id = d_hpo["hpo_id"]
hpo_name = d_hpo["name"]
parent_phenotypes = [{
"display": i,
"end_href": j
} for j, i in [(h, n) for _i, h, n in [ii.values() for ii in res]]]
# query to give the ancestors for a given hpo for a given user for all patients this user has access
sqlq = sql.SQL("""
select distinct i.id, i.external_id, i.phenopolis_id, i.sex, i.consanguinity,
(select array_agg(distinct g.hgnc_symbol order by g.hgnc_symbol)
from phenopolis.individual_gene ig
join ensembl.gene g on g.identifier = ig.gene_id
where ig.individual_id = i.id
) AS genes,
(
select array_agg(distinct concat(t.hpo_id,'@', t."name"))
from hpo.term t
join phenopolis.individual_feature if2 on t.id = if2.feature_id
where i.id = if2.individual_id
and if2."type" ='observed'
) AS simplified_observed_features_names
from phenopolis.individual i
join public.users_individuals ui on ui.internal_id = i.phenopolis_id
join phenopolis.individual_feature if3 on i.id = if3.individual_id and if3."type" = 'observed'
join hpo.term t2 on t2.id = if3.feature_id
join hpo.is_a_path p on p.term_id = t2.id
where ui."user" = %s
and p.path ~ %s
order by i.id
""")
with get_db() as conn:
with conn.cursor() as cur:
cur.execute(sqlq, (session[USER], f"*.{h_id}.*"))
individuals = cursor2dict(cur)
if hpo_id != "HP:0000001":
cur.execute("select * from phenogenon where hpo_id=%s",
[hpo_id])
config[0]["phenogenon_recessive"]["data"] = [{
"gene_id": [{
"display": gene_id,
"end_href": gene_id
}],
"hpo_id":
hpo_id,
"hgf_score":
hgf,
"moi_score":
moi_score,
} for gene_id, hpo_id, hgf, moi_score, in cur.fetchall()]
# NOTE: redundant line below commented
# cur.execute("select * from phenogenon where hpo_id=%s", [hpo_id])
config[0]["phenogenon_dominant"]["data"] = config[0][
"phenogenon_recessive"]["data"]
# Chr,Start,End,HPO,Symbol,ENSEMBL,FisherPvalue,SKATO,variants,CompoundHetPvalue,HWEp,min_depth,nb_alleles_cases,case_maf,nb_ctrl_homs,nb_case_homs,MaxMissRate,nb_alleles_ctrls,nb_snps,nb_cases,minCadd,MeanCallRateCtrls,MeanCallRateCases,OddsRatio,MinSNPs,nb_ctrl_hets,total_maf,MaxCtrlMAF,ctrl_maf,nb_ctrls,nb_case_hets,maxExac
cur.execute(
"select Symbol,FisherPvalue,SKATO,OddsRatio,variants from skat where HPO= %s",
[hpo_id])
config[0]["skat"]["data"] = [{
"gene_id": [{
"display": gene_id,
"end_href": gene_id
}],
"fisher_p_value":
fisher_p_value,
"skato":
skato,
"odds_ratio":
odds_ratio,
"variants": [],
} for gene_id, fisher_p_value, skato, odds_ratio, _variants
in cur.fetchall()[:100]]
application.logger.debug(len(individuals))
config[0]["preview"] = [["Number of Individuals", len(individuals)]]
for ind in individuals[:]:
ind["internal_id"] = [{"display": ind["phenopolis_id"]}]
if ind["genes"]:
ind["genes"] = [{"display": i} for i in ind["genes"]]
else:
ind["genes"] = []
ind["simplified_observed_features_names"] = [{
"display": j,
"end_href": i
} for i, j, in [
x.split("@")
for x in sorted(ind["simplified_observed_features_names"],
key=lambda x: x.split("@")[1])
]]
config[0]["individuals"]["data"] = individuals
config[0]["metadata"]["data"] = [{
"name": hpo_name,
"id": hpo_id,
"count": len(individuals),
"parent_phenotypes": parent_phenotypes
}]
process_for_display(db_session, config[0]["metadata"]["data"])
if subset == "all":
return jsonify(config)
else:
return jsonify([{subset: y[subset]} for y in config])
