def find_actrep_with_promoter(self, promoter, link_type, cor_ind_type, regulator_set):
if link_type == "Constitutive":
return None
if cor_ind_type == "Inducer":
cor_ind_type = "Induced"
if cor_ind_type == "Corepressor":
cor_ind_type = "Corepressed"
idx = len(regulator_set) + 1
if cor_ind_type not in {"Induced", "Corepressed"}:
sql_cmd = (
'SELECT DISTINCT ActRreNumber FROM relation WHERE\
PromoterNumber = "%s" AND ActRreType = "%s" AND IncCorType IS NULL\
ORDER BY ActRreNumber DESC LIMIT 0, %s'
% (promoter, link_type, idx)
)
else:
sql_cmd = (
'SELECT DISTINCT ActRreNumber FROM relation WHERE\
PromoterNumber = "%s" AND ActRreType = "%s" AND IncCorType = "%s"\
ORDER BY ActRreNumber DESC LIMIT 0, %s'
% (promoter, link_type, cor_ind_type, idx)
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
actrep = decodejson[0]["ActRreNumber"]
if link_type == "Positive":
actrep_table = "activator"
elif link_type == "Negative":
actrep_table = "repressor"
self.__cursor.execute('SELECT * FROM %s WHERE Number = "%s"' % (actrep_table, actrep))
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]
def getSelfPromoterOption(self, actrep, link_type, cor_ind_type):
if cor_ind_type == "Inducer":
cor_ind_type = "Induced"
if cor_ind_type == "Corepressor":
cor_ind_type = "Corepressed"
if cor_ind_type not in {"Induced", "Corepressed"}:
sql_cmd = """
SELECT promoter.* FROM promoter INNER JOIN relation
ON promoter.Number = relation.PromoterNumber WHERE relation.ActRreType = '%s'
AND relation.ActRreNumber = '%s' AND relation.IncCorType IS NULL
""" % (
link_type,
actrep,
)
else:
sql_cmd = """
SELECT promoter.* FROM promoter INNER JOIN relation
ON promoter.Number = relation.PromoterNumber WHERE relation.ActRreType = '%s'
AND relation.ActRreNumber = '%s' AND relation.IncCorType = '%s'
""" % (
link_type,
actrep,
cor_ind_type,
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson
def getUserNameById(self,id):
excuteString='select * from user_list where id = %d' %id
self.__cursor.execute(excuteString)
self.logger.debug('get user name by id: %d'%id)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description,self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]['name']
def selectAllOfTable(self,tableName='part_list'):
excuteString='select * from '+str(tableName)
self.__cursor.execute(excuteString)
self.logger.debug('selectAllOfTable: %s'%excuteString)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description,self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson
def getSelfRegulatorOption(self, RCluster, link_type, cor_ind_type):
if cor_ind_type == "Inducer":
cor_ind_type = "Induced"
if cor_ind_type == "Corepressor":
cor_ind_type = "Corepressed"
if cor_ind_type not in {"Induced", "Corepressed"}:
sql_cmd = """
SELECT * FROM relation
WHERE ActRreType = '%s' AND Cluster = '%s'
AND IncCorType IS NULL
""" % (
link_type,
RCluster,
)
else:
sql_cmd = """
SELECT * FROM relation
WHERE ActRreType = '%s' AND Cluster = '%s'
AND relation.IncCorType = '%s'
""" % (
link_type,
RCluster,
cor_ind_type,
)
print sql_cmd
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson
def select_with_name(self, table, name):
self.__cursor.execute('SELECT * FROM %s WHERE Number = "%s"' % (table, name))
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if decodejson != []:
return decodejson[0]
else:
return None
def getUserAnswer(self, userName):
self.__cursor.execute('select user_list.answer from user_list where name="%s"' % (userName))
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if len(decodejson) == 0:
return "no such a user"
else:
return decodejson[0]["answer"]
def getPlasmidBackboneNearValue(self, idealValue):
self.__cursor.execute(
"select * from plasmid_backbone order by\
abs(CopyNumber-%e) limit 0,1"
% idealValue
)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]
def getUserPartByLoginuser(self):
excuteString = (
"SELECT part_id,part_name AS Name,part_type as Type,part_author as Author,uploadUser as username FROM userPart WHERE uploadUser = '******'"
% self.getUserNameById(self.userId)
)
self.__cursor.execute(excuteString)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson
def getUserPasswordById(self,name): excuteString='select * from user_list where name = "%s"' %name self.__cursor.execute(excuteString) jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description,self.__cursor.fetchall()) decodejson = json.loads(jsonEncoded) if len(decodejson)==0: return None else: return decodejson[0]['password_MD5']
def select_row(self, tableName, idx):
excuteString = "SELECT * FROM %s ORDER BY Number DESC LIMIT %s,1" % (tableName, idx)
self.__cursor.execute(excuteString)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if len(decodejson) == 0:
return None
else:
return decodejson[0]["Number"]
def getUserPart(self, part_id):
excuteString = "SELECT * FROM userPart WHERE part_id = '%s'" % part_id
self.__cursor.execute(excuteString)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if len(decodejson) == 0:
return None
else:
return decodejson[0]
def find_promoter_with_activator(self, promoter_set, activator=None):
self.__cursor.execute(
'SELECT PromoterNumber FROM relation WHERE\
ActRreNumber = "%s" AND ActRreType = "Positive"'
% activator
)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
promoter = decodejson[0]["PromoterNumber"]
self.__cursor.execute('SELECT * FROM promoter WHERE Number = "%s"' % promoter)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if decodejson != []:
for item in decodejson:
if item["Cluster"] not in promoter_set:
promoter_set.add(item["Cluster"])
return item
else:
return None
def getUserFileNameList(self):
if self.userId == -1:
self.logger.error("not login but want to get the user fileList")
return "getUserFileNameList failed"
sql_cmd = "select fileName,fileType from user_save WHERE user_id=%d" % (self.userId)
self.__cursor.execute(sql_cmd)
self.logger.debug("select fileName from user_save: %s" % sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson
def getRegulatorCluster(self, regulator):
sql_cmd = (
"""
SELECT Cluster FROM relation WHERE ActRreNumber = '%s'
"""
% regulator
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]["Cluster"]
def getPromoterCluster(self, promoter):
sql_cmd = (
"""
SELECT Cluster FROM promoter WHERE Number = '%s'
"""
% promoter
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]["Cluster"]
def find_inducer_with_activator(self, activator, corep_ind_type):
if corep_ind_type == "Corepressor":
self.__cursor.execute("SELECT * FROM Corepressor ORDER BY random() LIMIT 1")
elif corep_ind_type == "Inducer":
self.__cursor.execute("SELECT * FROM Inducer ORDER BY random() LIMIT 1")
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if decodejson != []:
return decodejson[0]
else:
return None
def find_promoter_in_cluster(self, part_name, p_type, value):
sql_cmd = """
SELECT * FROM promoter WHERE Number = '%s' AND %s = '%s'
""" % (
part_name,
p_type,
value,
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]
def __getBestExpressionValueRecord(self,idealValue):
#data= self.db.getExpressionValue()
#minDiff=abs(data[0]['ExpressionValue']-idealValue)
#minIndex=0
#for i in xrange(1,len(data)):
# if(abs(data[i]['ExpressionValue']-idealValue)<minDiff):
# minIndex=i
# minDiff=abs(data[i]['ExpressionValue']-idealValue)
#return data[minIndex]
self.db._SqliteDatabase__cursor.execute('select * from expression_value order by abs(expression_value.ExpressionValue-%f) limit 0,1' %idealValue)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.db._SqliteDatabase__cursor.description,self.db._SqliteDatabase__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]
def getUserGroup(self, username):
excuteString = (
"SELECT [user_group].[id],[user_group].[name], [user_group].[canReadPartList],[user_group].[canWritePartList] FROM user_group,user_list WHERE user_list.name = '%s' AND user_list.group_id = user_group.id"
% (username)
)
self.logger.debug(excuteString)
self.__cursor.execute(excuteString)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if len(decodejson) == 0:
return "No such a user!"
else:
for item in decodejson[0]:
if decodejson[0][item] is None:
decodejson[0][item] = 0
return decodejson[0]
def getRepressorNearValue(self, idealValue, cor_ind_type, regulator_set, promoter_set):
idx = len(regulator_set) + 1
idx = 999999
if cor_ind_type == "Inducer":
cor_ind_type = "Induced"
elif cor_ind_type == "Corepressor":
cor_ind_type = "Corepressed"
if cor_ind_type not in {"Induced", "Corepressed"}:
sql_cmd = """
SELECT relation.ActRreNumber AS Number, relation.Cluster,
relation.K1, relation.HillCoeff1, relation.IncCorType,
relation.K2, relation.HillCoeff2,
relation.PromoterNumber, promoter.Cluster AS PCluster
FROM relation INNER JOIN promoter
ON promoter.Number = relation.PromoterNumber WHERE
relation.IncCorType IS NULL AND relation.ActRreType = "Negative"
ORDER BY abs(relation.K1 - %e)
""" % (
idealValue
)
else:
sql_cmd = """
SELECT relation.ActRreNumber AS Number, relation.Cluster,
relation.K1, relation.HillCoeff1, relation.IncCorType,
relation.K2, relation.HillCoeff2,
relation.PromoterNumber, promoter.Cluster AS PCluster
FROM relation INNER JOIN promoter
ON promoter.Number = relation.PromoterNumber WHERE
relation.IncCorType = "%s" AND relation.ActRreType = "Negative"
ORDER BY abs(relation.K1 - %e)
""" % (
cor_ind_type,
idealValue,
)
print sql_cmd
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
print decodejson
for item in decodejson:
regulator = item["Cluster"]
promoter = item["PCluster"]
if regulator not in regulator_set and promoter not in promoter_set:
regulator_set.add(regulator)
promoter_set.add(promoter)
return item
def getPromoterNearValue(self, idealValue, regulator_set, promoter_set, link_type, p_type, cor_ind_type):
if cor_ind_type == "Inducer":
cor_ind_type = "Induced"
if cor_ind_type == "Corepressor":
cor_ind_type = "Corepressed"
if cor_ind_type not in {"Induced", "Corepressed"}:
sql_cmd = """
SELECT promoter.*, relation.ActRreNumber, relation.PromoterNumber,
relation.ActRreType, relation.Cluster AS RCluster
FROM promoter INNER JOIN relation
ON promoter.Number = relation.PromoterNumber WHERE relation.ActRreType = "%s"
AND relation.IncCorType IS NULL ORDER BY abs(promoter.%s - %e)
""" % (
link_type,
p_type,
idealValue,
)
else:
sql_cmd = """
SELECT promoter.*, relation.ActRreNumber, relation.PromoterNumber,
relation.ActRreType, relation.Cluster AS RCluster
FROM promoter INNER JOIN relation
ON promoter.Number = relation.PromoterNumber WHERE relation.ActRreType = '%s'
AND relation.IncCorType = "%s" ORDER BY abs(promoter.%s - %e)
""" % (
link_type,
cor_ind_type,
p_type,
idealValue,
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if p_type != "PoPS":
for item in decodejson:
regulator = item["RCluster"]
promoter = item["Cluster"]
if regulator not in regulator_set and promoter not in promoter_set:
regulator_set.add(regulator)
promoter_set.add(promoter)
return item
else:
return decodejson[0]
def find_cor_ind(self, cor_ind_type, regulator, promoter):
if cor_ind_type == "Inducer":
cor_ind_type = "Induced"
if cor_ind_type == "Corepressor":
cor_ind_type = "Corepressed"
print cor_ind_type, regulator, promoter
sql_cmd = """SELECT IncCorName, HillCoeff2, K2 FROM relation WHERE
ActRreNumber = '%s' AND PromoterNumber = '%s' AND IncCorType = '%s'
""" % (
regulator,
promoter,
cor_ind_type,
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
if decodejson != []:
return decodejson[0]
else:
return None
def find_promoter(self, promoter_set, act_rep, act_rep_type):
sql_cmd = """
SELECT promoter.* FROM promoter INNER JOIN relation
ON promoter.Number = relation.PromoterNumber
WHERE ActRreNumber = "%s" AND ActRreType = "%s"
""" % (
act_rep,
act_rep_type,
)
print sql_cmd
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
print promoter_set
if decodejson != []:
for item in decodejson:
if item["Cluster"] not in promoter_set:
promoter_set.add(item["Cluster"])
return item
return None
else:
return None
def find_actrep(self, link, regulator_set, promoter_set):
idx = len(regulator_set) + 1
if link["type"] == "Repressor":
act_rep_type = "Negative"
else:
act_rep_type = "Positive"
if link["inducer"] not in {"Positive", "Negative"}:
sql_cmd = """
SELECT relation.ActRreNumber, relation.Cluster AS RCluster,
promoter.Cluster FROM promoter INNER JOIN relation
WHERE ActRreType = "%s" AND IncCorType IS NULL
ORDER BY ActRreNumber DESC
""" % (
act_rep_type
)
else:
if link["inducer"] == "Positive":
inc_cor_type = "Induced"
if link["inducer"] == "Negative":
inc_cor_type = "Corepressed"
sql_cmd = """
SELECT relation.ActRreNumber, relation.Cluster AS RCluster,
promoter.Cluster FROM promoter INNER JOIN relation
WHERE ActRreType = "%s" AND IncCorType = "%s"
ORDER BY ActRreNumber DESC
""" % (
act_rep_type,
inc_cor_type,
)
self.__cursor.execute(sql_cmd)
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.__cursor.description, self.__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
for item in decodejson:
if item["RCluster"] not in regulator_set and item["Cluster"] not in promoter_set:
regulator_set.add(item["RCluster"])
promoter_set.add(item["Cluster"])
return item["ActRreNumber"]
def getFileByExtractCode(self,code):
self.__cursor.execute('SELECT [user_save].[fileType], [user_save].[fileName], [user_list].[name] AS user_name FROM user_save,user_list WHERE user_list.id=user_save.user_id AND user_save.[extractCode]="%s"'%(code))
return json.loads(jsonUtil.turnSelectionResultToJson(self.__cursor.description,self.__cursor.fetchall()))[0]
def getSharedTypePart(self,type):
self.__cursor.execute('SELECT * FROM userPart where part_type="%s"'%type)
return json.loads(jsonUtil.turnSelectionResultToJson(self.__cursor.description,self.__cursor.fetchall()))
def getSharedFileList(self):
self.__cursor.execute('SELECT user_save.fileName,user_save.fileType,user_list.name FROM user_save,user_list where user_save.shared=1 AND user_list.id=user_save.user_id')
return json.loads(jsonUtil.turnSelectionResultToJson(self.__cursor.description,self.__cursor.fetchall()))
def __getRBSByName(self,rbsName):
self.db._SqliteDatabase__cursor.execute('select * from RBS where Number="%s"' %(rbsName))
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.db._SqliteDatabase__cursor.description,self.db._SqliteDatabase__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]
def __getPlasmidBackboneByExpressionValueRecord(self,ExpressionValueRecord):
self.db._SqliteDatabase__cursor.execute('select * from plasmid_backbone where Number="%s"' %(ExpressionValueRecord['PlasmidBackbone']))
jsonEncoded = jsonUtil.turnSelectionResultToJson(self.db._SqliteDatabase__cursor.description,self.db._SqliteDatabase__cursor.fetchall())
decodejson = json.loads(jsonEncoded)
return decodejson[0]
