def get_descriptions(self, code, with_pdb):
subquery = scoped_db.query(PfamA)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(),
PfamA.pfamA_id.ilike(code))).distinct().subquery()
query1 = scoped_db.query(PfamARegFullSignificant.pfamseq_acc,
PfamARegFullSignificant.seq_start,
PfamARegFullSignificant.seq_end)
query1 = query1.filter(
PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc,
PfamARegFullSignificant.in_full)
query1 = query1.options(
Load(PfamARegFullSignificant).load_only("seq_start", "seq_end"))
query1 = query1.distinct().subquery()
# query2 = scoped_db.query(Pfamseq.pfamseq_id)
# query2 = query2.filter(Pfamseq.pfamA_acc == subquery.c.pfamA_acc).distinct().subquery()
query = scoped_db.query(
concat(Pfamseq.pfamseq_id, '/',
cast(query1.c.seq_start, types.Unicode), '-',
cast(query1.c.seq_end, types.Unicode)))
query = query.filter(Pfamseq.pfamseq_acc == query1.c.pfamseq_acc)
if with_pdb:
subquery2 = scoped_db.query(PdbPfamAReg)
subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c.
pfamA_acc).distinct().subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
query = query.order_by(Pfamseq.pfamseq_id.asc())
return query.distinct().all()
def get_descriptions(self, code, with_pdb):
#icode = "%{:}%".format(code)
subquery = scoped_db.query(PfamA.pfamA_acc)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(),
PfamA.pfamA_id.ilike(code))).distinct().subquery()
query = scoped_db.query(
concat(Pfamseq.pfamseq_id, '/',
cast(PfamARegFullSignificant.seq_start, types.Unicode), '-',
cast(PfamARegFullSignificant.seq_end, types.Unicode)))
query = query.outerjoin(
PfamARegFullSignificant,
and_(Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc,
PfamARegFullSignificant.in_full == 1))
query = query.filter(
PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc)
if with_pdb:
subquery2 = scoped_db.query(PdbPfamAReg)
subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c.
pfamA_acc).distinct().subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
query = query.options(
Load(Pfamseq).load_only('pfamseq_id'),
Load(PfamARegFullSignificant).load_only("seq_start", "seq_end"))
query = query.order_by(Pfamseq.pfamseq_id.asc())
return query.distinct().all()
def get_descriptions(self, code, with_pdb):
subquery = scoped_db.query(PfamA)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(),
PfamA.pfamA_id.ilike(code))).distinct().subquery()
query = scoped_db.query(Pfamjoinpfamseq.pfamseq_id,
Pfamjoinpfamseq.pfamA_acc)
query = query.filter(Pfamjoinpfamseq.pfamA_acc == subquery.c.pfamA_acc)
if with_pdb:
query = query.filter(Pfamjoinpfamseq.has_pdb == 1)
query = query.order_by(Pfamjoinpfamseq.pfamseq_id.asc())
return query.distinct().all()
def query(self, query):
join = (scoped_db.query(Uniprot, UniprotRegFull, PfamA).
filter(or_(Uniprot.uniprot_id == query, Uniprot.uniprot_acc == query)).
filter(UniprotRegFull.uniprot_acc == Uniprot.uniprot_acc).
filter(PfamA.pfamA_acc == UniprotRegFull.pfamA_acc).
order_by(UniprotRegFull.seq_start)).all()
return join
def query(self, pfamA_acc):
join = (scoped_db.query(Uniprot, UniprotRegFull, PdbPfamAReg).
filter(UniprotRegFull.pfamA_acc == pfamA_acc).
filter(UniprotRegFull.uniprot_acc == Uniprot.uniprot_acc).
filter(UniprotRegFull.auto_uniprot_reg_full ==
PdbPfamAReg.auto_uniprot_reg_full)).all()
return join
def query(self, query):
join = (scoped_db.query(Uniprot, UniprotRegFull, PfamA).filter(
or_(Uniprot.uniprot_id == query,
Uniprot.uniprot_acc == query)).filter(
UniprotRegFull.uniprot_acc == Uniprot.uniprot_acc).filter(
PfamA.pfamA_acc == UniprotRegFull.pfamA_acc).order_by(
UniprotRegFull.seq_start)).all()
return join
def to_pfam_acc(self, code):
subquery = scoped_db.query(PfamA)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(), PfamA.pfamA_id.ilike(code)))
subquery = subquery.options(Load(PfamA).load_only("pfamA_acc"))
try:
return subquery.one().pfamA_acc
except NoResultFound as e:
return None
def query(self, uniprot_id, seq_start, seq_end):
join = (scoped_db.query(Uniprot, UniprotRegFull, PdbPfamAReg, Pdb).
filter(Uniprot.uniprot_id == uniprot_id).
filter(UniprotRegFull.seq_start == seq_start).
filter(UniprotRegFull.seq_end == seq_end).
filter(UniprotRegFull.uniprot_acc == Uniprot.uniprot_acc).
filter(UniprotRegFull.auto_uniprot_reg_full ==
PdbPfamAReg.auto_uniprot_reg_full).
filter(PdbPfamAReg.pdb_id == Pdb.pdb_id).
order_by(PdbPfamAReg.pdb_id).
order_by(PdbPfamAReg.chain)
).all()
return join
def get_descriptions(self, code, with_pdb):
#icode = "%{:}%".format(code)
subquery = scoped_db.query(PfamA)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(),
PfamA.pfamA_id.ilike(code))).distinct().subquery()
#query = scoped_db.query(UniprotRegFull, Uniprot, PdbPfamAReg)
#query = query.filter(UniprotRegFull.pfamA_acc == subquery.c.pfamA_acc)
#query = query.filter(UniprotRegFull.auto_uniprot_reg_full == PdbPfamAReg.auto_uniprot_reg_full)
#query = query.filter(UniprotRegFull.uniprot_acc == Uniprot.uniprot_acc)
query = scoped_db.query(
PfamARegFullSignificant.pfamseq_acc,
concat('/', cast(PfamARegFullSignificant.seq_start, types.Unicode),
'-', cast(PfamARegFullSignificant.seq_end, types.Unicode)))
#query = query.join(PfamARegFullSignificant, Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc)
query = query.filter(
PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc)
if with_pdb:
subquery2 = scoped_db.query(PdbPfamAReg)
subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c.
pfamA_acc).distinct().subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
query = query.filter(PfamARegFullSignificant.in_full)
query = query.options(
Load(Pfamseq).load_only('pfamseq_id'),
Load(PfamARegFullSignificant).load_only("seq_start", "seq_end"))
#query = query.order_by(Pfamseq.pfamseq_id.asc())
results = query.distinct().all()
results = [pfamseq_map[prefix] + suffix for prefix, suffix in results]
return results
def query(self, uniprot_id, seq_start, seq_end):
query = scoped_db.query(Uniprot, UniprotRegFull, PdbPfamAReg, Pdb)
query = query.filter(
Uniprot.uniprot_id == uniprot_id,
UniprotRegFull.seq_start == seq_start,
UniprotRegFull.seq_end == seq_end,
UniprotRegFull.uniprot_acc == Uniprot.uniprot_acc,
UniprotRegFull.auto_uniprot_reg_full ==
PdbPfamAReg.auto_uniprot_reg_full,
PdbPfamAReg.pdb_id == Pdb.pdb_id)
query = query.order_by(PdbPfamAReg.pdb_id)
query = query.order_by(PdbPfamAReg.chain)
query = query.options(
Load(PdbPfamAReg).load_only("pdb_id", "chain", "pdb_res_start",
"pdb_res_end"),
Load(UniprotRegFull).load_only("pfamA_acc"),
Load(Pdb).load_only("title", "resolution", "method", "date",
"author"))
return query.all()
def get_pfam_from_pfamacc(self, pfam_acc):
query = scoped_db.query(PfamA.num_full, PfamA.description)
query = query.filter(PfamA.pfamA_acc == pfam_acc)
return query.one()
def preload_pfamseq_map():
query = scoped_db.query(Pfamseq.pfamseq_id, Pfamseq.pfamseq_acc).all()
return {acc: id for id, acc in query}
def query(self, query):
return (scoped_db.query(PdbImage).filter(
PdbImage.pdb_id == query.upper())).all()
def query(self, query):
return (scoped_db.query(PdbImage).filter(PdbImage.pdb_id == query.upper())).all()
def to_pfam_acc(self, pfam_id):
quest = scoped_db.query(PfamA).filter(PfamA.pfamA_id == pfam_id).all()
return quest[0].pfamA_acc if len(quest) else pfam_id
def create_aux_pfamA_pfamseqid():
from sqlalchemy.orm import aliased
tablename = "pfamjoinpfamseq"
m = MetaData(engine)
if not engine.dialect.has_table(engine, tablename):
# t.drop(engine) # to delete/drop table
t = Table(tablename, m,
Column('pfamseq_id', String(40), primary_key=True),
Column('pfamA_acc', String(7), primary_key=True, index=True),
Column('pfamseq_acc', String(40)),
Column('has_pdb', Boolean, unique=False, default=False))
t.create(engine)
pfams = db.query(PfamA.pfamA_acc).all()
for pf in pfams:
# for pf in ["PF00451", "PF00452", "PF00453"]:
print pf[0]
pfam = pf[0]
# pfam = pf
query = db.query(
concat(Pfamseq.pfamseq_id, '/',
cast(PfamARegFullSignificant.seq_start,
types.Unicode), '-',
cast(PfamARegFullSignificant.seq_end,
types.Unicode)), PfamARegFullSignificant.pfamA_acc,
PfamARegFullSignificant.pfamseq_acc)
query = query.join(
PfamARegFullSignificant,
and_(
PfamARegFullSignificant.in_full == 1,
Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc,
PfamARegFullSignificant.pfamA_acc == pfam))
### add column with pdb
subquery2 = db.query(PdbPfamAReg.pfamseq_acc)
subquery2 = subquery2.filter(
PdbPfamAReg.pfamA_acc == pfam).distinct().subquery()
query_pdb = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
subquery_pdb = query_pdb.subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc.notin_(subquery2))
# print query.add_columns(literal_column("0").label("has_pdb")).distinct().all()
# print "################"
# print "################"
# print query_pdb.add_columns(literal_column("1").label("has_pdb")).distinct().all()
query = query.add_columns(
literal_column("0").label("has_pdb")).distinct()
query_pdb = query_pdb.add_columns(
literal_column("1").label("has_pdb")).distinct()
query_union = query.union(query_pdb)
if query_union.first():
engine.execute(t.insert().values(tuple(query_union.all())))
return ["Succesfully created pfamjoinpfamseq"]
else:
pfamjoinpfamseq = Table(tablename, m, autoload=True)
mapper(Pfamjoinpfamseq, pfamjoinpfamseq)
return ["pfamjoinpfamseq table exists, creating mapper"]