def searchExpressionLevelsInDb(self,
rnaQuantId,
names=[],
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(names) > 0:
sql += "AND name in ("
sql += ",".join(['?' for name in names])
sql += ") "
for name in names:
sql_args += (name, )
sql += sqlite_backend.limitsSql(startIndex=startIndex,
maxResults=maxResults)
query = self._dbconn.execute(sql, sql_args)
return sqlite_backend.iterativeFetch(query)
def searchFeaturesInDb(
self, startIndex=0, maxResults=None,
referenceName=None, start=None, end=None,
parentId=None, featureTypes=None,
name=None, geneSymbol=None):
"""
Perform a full features query in database.
:param startIndex: int representing first record to return
:param maxResults: 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(
startIndex=startIndex, maxResults=maxResults,
referenceName=referenceName, start=start, end=end,
parentId=parentId, featureTypes=featureTypes,
name=name, geneSymbol=geneSymbol)
sql += sqlite_backend.limitsSql(startIndex, maxResults)
query = self._dbconn.execute(sql, sql_args)
return sqlite_backend.sqliteRowsToDicts(query.fetchall())
def testLimitClause(self):
noArgs = sqlite_backend.limitsSql()
zeroArgs = sqlite_backend.limitsSql(0, 0)
self.assertEqual(noArgs, zeroArgs)
with self.assertRaises(Exception):
sqlite_backend.limitsSql(startIndex=5)
limit = sqlite_backend.limitsSql(startIndex=1, maxResults=2)
self.assertEqual(limit, " LIMIT 1, 2")
limit = sqlite_backend.limitsSql(maxResults=3)
self.assertEqual(limit, " LIMIT 3")
def testLimitClause(self):
noArgs = sqlite_backend.limitsSql()
zeroArgs = sqlite_backend.limitsSql(0, 0)
self.assertEqual(noArgs, zeroArgs)
with self.assertRaises(Exception):
sqlite_backend.limitsSql(startIndex=5)
limit = sqlite_backend.limitsSql(startIndex=1, maxResults=2)
self.assertEqual(limit, " LIMIT 1, 2")
limit = sqlite_backend.limitsSql(maxResults=3)
self.assertEqual(limit, " LIMIT 3")
def searchExpressionLevelsInDb(
self, rnaQuantId, names=[], 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(names) > 0:
sql += "AND name in ("
sql += ",".join(['?' for name in names])
sql += ") "
for name in names:
sql_args += (name,)
sql += sqlite_backend.limitsSql(
startIndex=startIndex, maxResults=maxResults)
query = self._dbconn.execute(sql, sql_args)
return sqlite_backend.iterativeFetch(query)
def searchFeaturesInDb(self,
startIndex=0,
maxResults=None,
referenceName=None,
start=None,
end=None,
parentId=None,
featureTypes=None,
name=None,
geneSymbol=None):
"""
Perform a full features query in database.
:param startIndex: int representing first record to return
:param maxResults: 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(startIndex=startIndex,
maxResults=maxResults,
referenceName=referenceName,
start=start,
end=end,
parentId=parentId,
featureTypes=featureTypes,
name=name,
geneSymbol=geneSymbol)
sql += sqlite_backend.limitsSql(startIndex, maxResults)
query = self._dbconn.execute(sql, sql_args)
return sqlite_backend.sqliteRowsToDicts(query.fetchall())