def get_songs_from_db(from_date=None):
if from_date is None:
# get all songs
songs = db.session.query(Song).filter(Song.is_valid).all()
else:
# get songs that were added after last playlist update
songs = db.session.query(Song).filter(sqlalchemy._and(
Song.is_valid,
Song.date_added > from_date
)).all()
return songs
def search(self,
module,
title_query,
tags_query=None,
fields=None,
limit=None,
or_=True):
if fields is None:
fields = module.__searchable__
filter_title = []
filter_tags = []
keywords = title_query.split(' ')
for field in fields:
if field == "title":
query = [
getattr(module, field).contains(keyword)
for keyword in keywords if keyword
]
if not or_:
filter_title.append(_and(*query))
else:
filter_title.append(_or(*query))
if field == "tags":
query = [
getattr(module, field).contains(tag) for tag in tags_query
if tag
]
filter_tags.append(_and(*query))
results = module.query.filter(_or(*filter_title))
results = results.filter(_and(*filter_tags))
if limit is not None:
results = results.limit(limit)
return results
def msearch(self, m, query, fields=None, limit=None, or_=True):
if fields is None:
fields = m.__searchable__
f = []
if self.analyzer is not None:
keywords = self.analyzer(query)
else:
keywords = query.split(' ')
for field in fields:
query = [getattr(m, field).contains(keyword)
for keyword in keywords if keyword]
if not or_:
f.append(_and(*query))
else:
f.append(_or(*query))
results = m.query.filter(_or(*f))
if limit is not None:
results = results.limit(limit)
return results
def msearch(self, m, query, fields=None, limit=None, or_=False):
if fields is None:
fields = m.__searchable__
f = []
if self.analyzer is not None:
keywords = self.analyzer(query)
else:
keywords = query.split(' ')
for field in fields:
query = [getattr(m, field).contains(keyword)
for keyword in keywords if keyword]
if not or_:
f.append(_and(*query))
else:
f.append(_or(*query))
results = m.query.filter(_or(*f))
if limit is not None:
results = results.limit(limit)
return results
def get_dataframe(self,
pval=None,
rsid=None,
trait=None,
population=None,
position=None,
limit=None,
sig_only=False,
extra=False):
"""Filter database and return results as a dataframe.
All arguments are optional and additive, no arguments will return the
whole database.
Note that using multiple arguments at the same time can make the
operation very, very slow, try to use as few as possible.
Args:
pval (float): Filter by pvalue less than this number.
rsid (str/list): Filter by rsid, can be a list of rsids.
trait (str/list): Filter by trait name, can be a list.
population (str/list): Filter by population, can be a list.
position (str, list): Filter by location, syntax is important,
must be:
chr:start-end
End is optional, both numbers are base-0.
You can provide a list of these, but each
must match the syntax requirements.
limit (int): Number of results to limit to.
sig_only (bool): Only return results in the significant table.
extra (bool): Add additional information columns to output.
Returns:
DataFrame: A pandas dataframe of the results.
"""
# Choose columns
rtable = T.SigRiskAllele if sig_only else T.RiskAllele
columns = self.basic_columns_sig if sig_only else self.basic_columns
if extra:
columns += self.extra_columns_sig if sig_only else self.extra_columns
# Get the initial query object
query = self.query(*columns)
# Run filters
if pval:
assert isinstance(pval, (float, int))
query = query.filter(rtable.P < pval)
if rsid:
if isinstance(rsid, str):
query = query.filter(rtable.rsID == rsid)
else:
query = query.filter(rtable.rsID.in_(listify(rsid)))
if trait:
if isinstance(trait, int):
query = query.filter(rtable.trait_id == trait)
elif isinstance(trait, str):
query = query.filter(T.Trait.trait == trait)
query = query.filter(rtable.trait_id == T.Trait.id)
else:
trait = listify(trait)
if isinstance(trait, int):
query = query.filter(rtable.trait.in_(trait))
else:
query = query.filter(T.Trait.trait.in_(trait))
query = query.filter(rtable.trait_id.in_(T.Trait.id))
if population:
if isinstance(population, str):
query = query.filter(rtable.population == population)
else:
query = query.filter(rtable.population.in_(
listify(population)))
if position:
if isinstance(position, str):
chrom, start, end = parse_position(position)
query = query.filter(rtable.chrom == chrom)
if start:
if end:
query = query.filter(
rtable.position.between(start, end))
else:
query = query.filter(rtable.position == start)
else:
positions = listify(position)
or_list = []
for pos in positions:
chrom, start, end = parse_position(pos)
if start:
if end:
or_list.append(
_and(rtable.chrom == chrom,
rtable.position.between(start, end)))
else:
or_list.append(
_and(rtable.chrom == chrom,
rtable.position == start))
else:
or_list.append(rtable.chrom == chrom)
query = query.filter(_or(*or_list))
# Set limits
if limit:
query = query.limit(int(limit))
# Get DataFrame
df = _pd.read_sql_query(query.statement, self.engine, index_col='id')
df.index.name = None
return df