def searchFeaturesInDb(
self, pageToken=0, pageSize=None,
referenceName=None, start=0, end=0,
parentId=None, featureTypes=None):
"""
Perform a full features query in database.
:param pageToken: int representing first record to return
:param pageSize: int representing number of records to return
:param referenceName: string representing reference name, ex 'chr1'
:param start: int position on reference to start search
:param end: int position on reference to end search >= start
:param parentId: string restrict search by id of parent node.
:return an array of dictionaries, representing the returned data.
"""
# TODO: Refactor out common bits of this and the above count query.
sql = (
"SELECT * FROM FEATURE WHERE "
"reference_name = ? "
"AND end > ? " # compare this to query start
"AND start < ? ") # and this to query end
# TODO: Optimize by refactoring out string concatenation
sql_args = (referenceName, start, end)
if parentId is not None:
sql += "AND parent_id = ? "
sql_args += (parentId,)
if featureTypes is not None and len(featureTypes) > 0:
sql += "AND type IN ("
sql += ", ".join(["?", ] * len(featureTypes))
sql += ") "
sql_args += tuple(featureTypes)
sql += "ORDER BY reference_name, start, end ASC "
sql += sqliteBackend.limitsSql(pageToken, pageSize)
query = self._dbconn.execute(sql, sql_args)
return sqliteBackend.sqliteRows2dicts(query.fetchall())
def searchFeaturesInDb(
self, pageToken=0, pageSize=None,
referenceName=None, start=None, end=None,
parentId=None, featureTypes=None,
name=None, geneSymbol=None):
"""
Perform a full features query in database.
:param pageToken: int representing first record to return
:param pageSize: int representing number of records to return
:param referenceName: string representing reference name, ex 'chr1'
:param start: int position on reference to start search
:param end: int position on reference to end search >= start
:param parentId: string restrict search by id of parent node.
:param name: match features by name
:param geneSymbol: match features by gene symbol
:return an array of dictionaries, representing the returned data.
"""
# TODO: Refactor out common bits of this and the above count query.
sql, _, sql_args = self.featuresQuery(
pageToken=pageToken, pageSize=pageSize,
referenceName=referenceName, start=start, end=end,
parentId=parentId, featureTypes=featureTypes,
name=name, geneSymbol=geneSymbol)
sql += sqliteBackend.limitsSql(pageToken, pageSize)
query = self._dbconn.execute(sql, sql_args)
return sqliteBackend.sqliteRowsToDicts(query.fetchall())
def searchFeaturesInDb(
self, pageToken=0, pageSize=None,
referenceName=None, start=0, end=0,
parentId=None, featureTypes=None):
"""
Perform a full features query in database.
:param pageToken: int representing first record to return
:param pageSize: int representing number of records to return
:param referenceName: string representing reference name, ex 'chr1'
:param start: int position on reference to start search
:param end: int position on reference to end search >= start
:param parentId: string restrict search by id of parent node.
:return an array of dictionaries, representing the returned data.
"""
# TODO: Refactor out common bits of this and the above count query.
sql = (
"SELECT * FROM FEATURE WHERE "
"reference_name = ? "
"AND end > ? " # compare this to query start
"AND start < ? ") # and this to query end
# TODO: Optimize by refactoring out string concatenation
sql_args = (referenceName, start, end)
if parentId is not None:
sql += "AND parent_id = ? "
sql_args += (parentId,)
if featureTypes is not None and len(featureTypes) > 0:
sql += "AND type IN ("
sql += ", ".join(["?", ] * len(featureTypes))
sql += ") "
sql_args += tuple(featureTypes)
sql += "ORDER BY reference_name, start, end ASC "
sql += sqliteBackend.limitsSql(pageToken, pageSize)
query = self._dbconn.execute(sql, sql_args)
return sqliteBackend.sqliteRows2dicts(query.fetchall())
def searchExpressionLevelsInDb(self,
rnaQuantId,
featureIds=[],
threshold=0.0,
startIndex=0,
maxResults=0):
"""
:param rnaQuantId: string restrict search by quantification id
:param threshold: float minimum expression values to return
:return an array of dictionaries, representing the returned data.
"""
sql = ("SELECT * FROM Expression WHERE "
"rna_quantification_id = ? "
"AND expression > ? ")
sql_args = (rnaQuantId, threshold)
if len(featureIds) > 0:
sql += "AND feature_id in ("
sql += ",".join(['?' for featureId in featureIds])
sql += ") "
for featureId in featureIds:
sql_args += (featureId, )
sql += sqliteBackend.limitsSql(startIndex=startIndex,
maxResults=maxResults)
query = self._dbconn.execute(sql, sql_args)
return sqliteBackend.iterativeFetch(query)
def testLimitClause(self):
noArgs = sqliteBackend.limitsSql()
zeroArgs = sqliteBackend.limitsSql(0, 0)
self.assertEqual(noArgs, zeroArgs)
with self.assertRaises(Exception):
sqliteBackend.limitsSql(startIndex=5)
limit = sqliteBackend.limitsSql(startIndex=1, maxResults=2)
self.assertEqual(limit, " LIMIT 1, 2")
limit = sqliteBackend.limitsSql(maxResults=3)
self.assertEqual(limit, " LIMIT 3")
def searchExpressionLevelsInDb(
self, rnaQuantId, featureIds=[], threshold=0.0, startIndex=0,
maxResults=0):
"""
:param rnaQuantId: string restrict search by quantification id
:param threshold: float minimum expression values to return
:return an array of dictionaries, representing the returned data.
"""
sql = ("SELECT * FROM Expression WHERE "
"rna_quantification_id = ? "
"AND expression > ? ")
sql_args = (rnaQuantId, threshold)
if len(featureIds) > 0:
sql += "AND feature_id in ("
sql += ",".join(['?' for featureId in featureIds])
sql += ") "
for featureId in featureIds:
sql_args += (featureId,)
sql += sqliteBackend.limitsSql(
startIndex=startIndex, maxResults=maxResults)
query = self._dbconn.execute(sql, sql_args)
return sqliteBackend.iterativeFetch(query)