def gene_hsa_form():
result = {}
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select trim(fid) from gene t
""")
rows = cursor.fetchall()
gene_data = []
for row in rows:
gene_data.append(row[0])
cursor.execute("""
select hsa_id from hsa t
""")
rows = cursor.fetchall()
hsa_data = []
for row in rows:
hsa_data.append(row[0])
result['gene'] = gene_data
result['hsa'] = hsa_data
return render_template('ghdn/gene/gene_hsa_form.html',
result=result,
type='gene')
def get_dict():
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select
(select GROUP_CONCAT(ke) from (
select t.ke from QYZTXX t group by t.ke) t ) kes,
(select group_concat(mu) from (
select t.mu from QYZTXX t group by t.mu) t) mus,
(select group_concat(chongzhong) from (
select t.chongzhong from QYZTXX t group by t.chongzhong) t) chongzhongs,
(select group_concat(clstq) from (
select t.clstq from QYZTXX t group by t.clstq) t ) clstqs
from dual
""")
rows = cursor.fetchall()
dict_obj = {}
for row in rows:
dict_obj['kes'] = [k for k in row[0].split(',')]
dict_obj['mus'] = [k for k in row[1].split(',')]
dict_obj['chongzhongs'] = [k for k in row[2].split(',')]
dict_obj['clstqs'] = [k for k in row[3].split(',')]
return dict_obj
def detail():
req = request.args
id = req.get("id")
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select fid,drug,type,description,indication,pharmacodynamics,machanismOfAction,stucture,synonyms,SafetyAndHazards from drug where id=%s
""" % (id))
rows = cursor.fetchall()
data = {}
for row in rows:
i = 0
data['fid'] = row[i]
i += 1
data['drug'] = row[i]
i += 1
data['type'] = row[i]
i += 1
data['description'] = row[i]
i += 1
data['indication'] = row[i]
i += 1
data['pharmacodynamics'] = row[i]
i += 1
data['machanismOfAction'] = row[i]
i += 1
data['stucture'] = row[i]
i += 1
data['synonyms'] = row[i]
i += 1
data['SafetyAndHazards'] = row[i]
return render_template('ghdn/drug/detail.html', data=data, type='drug')
def gene_network_form():
result = {}
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute(
"""
select fid from network t
"""
)
rows = cursor.fetchall()
network = []
for row in rows:
network.append(row[0])
cursor.execute(
"""
select fid from gene t
"""
)
rows = cursor.fetchall()
gene_data = []
for row in rows:
gene_data.append(row[0])
result['network'] = network
result['Gene'] = gene_data
return render_template('ghdn/network/gene_network_form.html',result=result, type='network')
def network_hsa_form():
result={}
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute(
"""
select fid from network t
"""
)
rows = cursor.fetchall()
network = []
for row in rows:
network.append(row[0])
cursor.execute(
"""
select hsa_id from hsa t
"""
)
rows = cursor.fetchall()
hsa_data = []
for row in rows:
hsa_data.append(row[0])
result['network']=network
result['hsa'] = hsa_data
return render_template('ghdn/network/network_hsa_form.html',result=result, type='network')
def getData(para):
conn = db.get_connection()
cursor = conn.cursor()
sql = "select BAIDU_GEOCODES,UNISCID,ENTNAME,DOM,MU,KE,XUEMING,MEN from QYZTXX t where BAIDU_GEOCODES is not null "
if para:
if para.get('mu', ''):
sql += " and mu='%s'" % (para.get('mu', ''))
if para.get('ke', ''):
sql += " and ke='%s'" % (para.get('ke', ''))
if para.get('chongzhong', ''):
sql += " and chongzhong='%s'" % (para.get('chongzhong', ''))
if para.get('clstq', ''):
sql += " and clstq='%s'" % (para.get('clstq', ''))
print(sql)
cursor.execute(sql)
rows = cursor.fetchall()
locationList = []
for row in rows:
data = eval(row[0])
data['result']['location']["uniscid"] = row[1]
data['result']['location']["entname"] = row[2]
data['result']['location']["dom"] = row[3]
data['result']['location']["mu"] = row[4]
data['result']['location']["ke"] = row[5]
data['result']['location']["xueming"] = row[6]
data['result']['location']["men"] = row[7]
locationList.append(data['result']['location'])
return locationList
def hsa_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req = request.form
hsa_id = req.get('hsa_id')
disease = req.get('disease')
para = (hsa_id, disease)
sql = "insert into hsa(hsa_id,disease) values(%s,%s)"
cursor.execute(sql, para)
return "1"
def gene_hsa_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req = request.form
gene = req.get('gene')
hsa = req.get('hsa')
para = (gene, hsa)
sql = "insert into gene_hsa(gene,hsa) values(%s,%s)"
cursor.execute(sql, para)
return "1"
def gene_network_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req=request.form
network=req.get('network')
Gene=req.get('Gene')
para = (Gene,network)
sql="insert into gene_network(Gene,network) values(%s,%s)"
cursor.execute(sql,para)
return "1"
def network_hsa_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req=request.form
network=req.get('network')
hsa=req.get('hsa')
para = (network, hsa)
sql="insert into network_hsa(network,hsa) values(%s,%s)"
cursor.execute(sql,para)
return "1"
def gene_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req = request.form
fid = req.get('fid')
Gene = req.get('Gene')
Definition = req.get('Definition')
Organism = req.get('Organism')
para = (fid, Gene, Definition, Organism)
sql = "insert into gene(fid,Gene,Definition,Organism) values(%s,%s,%s,%s)"
cursor.execute(sql, para)
return "1"
def network_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req=request.form
fid=req.get('fid')
name=req.get('name')
definitionExpanded=req.get('definitionExpanded')
type=req.get('type')
para = (fid, name, definitionExpanded, type)
sql="insert into network(fid, name, definitionExpanded, type) values(%s,%s,%s,%s)"
cursor.execute(sql,para)
return "1"
def register_submit():
result = "0"
conn = db.get_connection()
cursor = conn.cursor()
req = request.form
username = req.get('username')
password = req.get('password')
confPassword = req.get('confPassword')
if password == confPassword:
option = (username, password, 1)
sql = "insert into tb_user(username,password,roleid) values(%s,%s,%s)"
cursor.execute(sql, option)
result = "1"
else:
result = "2"
return result
def login_submit():
result = "0"
conn = db.get_connection()
cursor = conn.cursor()
req = request.form
username = req.get('username')
password = req.get('password')
option = (username, password)
sql = "select username,password,roleid from tb_user t where username=%s and password=%s"
cursor.execute(sql, option)
rows = cursor.fetchall()
if len(rows) == 1:
session['username'] = rows[0][1]
session['roleid'] = rows[0][2]
result = "1"
return result
def index():
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select id,fid,drug from drug where fid!=''
""")
rows = cursor.fetchall()
dict_result = []
for row in rows:
dict_obj = {}
dict_obj['id'] = row[0]
dict_obj['fid'] = row[1]
dict_obj['drug'] = row[2]
dict_result.append(dict_obj)
return render_template('ghdn/drug/drug.html',
data=dict_result,
type='drug')
def drug_form_submit():
conn = db.get_connection()
cursor = conn.cursor()
req = request.form
fid = req.get('fid')
drug = req.get('drug')
type = req.get('type')
description = req.get('description')
indication = req.get('indication')
pharmacodynamics = req.get('pharmacodynamics')
machanismOfAction = req.get('machanismOfAction')
para = (fid, drug, type, description, indication, pharmacodynamics,
machanismOfAction)
sql = "insert into drug(fid, drug, type, description,indication,pharmacodynamics,machanismOfAction) values(%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(sql, para)
return "1"
def detail():
result = {}
req = request.args
id = req.get("id")
fid = req.get("fid")
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select Gene,Definition,Organism from gene t where t.id=%s
""" % (id))
rows = cursor.fetchall()
data = {}
for row in rows:
i = 0
data['Gene'] = row[i]
i += 1
data['Definition'] = row[i]
i += 1
data['Organism'] = row[i]
#网络
cursor.execute("""
select t2.* from gene_network t1
inner join network t2
on t1.network=t2.fid
where trim(t1.Gene)='%s'
""" % (fid))
rows = cursor.fetchall()
network_arr = []
for row in rows:
dict_obj = {}
i = 0
dict_obj['fid'] = row[i]
i += 1
dict_obj['name'] = row[i]
i += 1
dict_obj['definitionExpanded'] = row[i]
i += 1
dict_obj['type'] = row[i]
network_arr.append(dict_obj)
result['data'] = data
result['network'] = network_arr
return render_template('ghdn/gene/detail.html', result=result, type='gene')
def index():
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute(
"""
select id,fid,name,type from network where fid!=''
"""
)
rows = cursor.fetchall()
dict_result= []
for row in rows:
dict_obj={}
dict_obj['id'] = row[0]
dict_obj['fid'] = row[1]
dict_obj['name'] =row[2]
dict_obj['type'] = row[3]
dict_result.append(dict_obj)
return render_template('ghdn/network/network.html', data=dict_result,type='network')
def index():
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select id,fid,Gene,Definition from gene
""")
rows = cursor.fetchall()
dict_result = []
for row in rows:
dict_obj = {}
dict_obj['id'] = row[0]
dict_obj['fid'] = row[1]
dict_obj['gene'] = row[2]
dict_obj['Definition'] = row[3]
dict_result.append(dict_obj)
return render_template('ghdn/gene/gene.html',
data=dict_result,
type='gene')
def getData():
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute("""
select gene,count(0) total from gene_hsa group by gene
""")
rows = cursor.fetchall()
dict_result = {}
data_arr = []
title_arr = []
for index, row in enumerate(rows):
dict_obj = {}
dict_obj['name'] = row[0]
dict_obj['value'] = row[1]
title_arr.append(row[0])
data_arr.append(dict_obj)
dict_result['data'] = data_arr
dict_result['title'] = title_arr
return json_response(dict_result)
def tongjitu1(para):
conn = db.get_connection()
cursor = conn.cursor()
sql = "select t.clstq letter,(count(0)/(select count(0) from QYZTXX where type=3))*100 frequency from QYZTXX t where 1=1"
if para:
if para.get('mu', ''):
sql += " and mu='%s'" % (para.get('mu', ''))
if para.get('ke', ''):
sql += " and ke='%s'" % (para.get('ke', ''))
if para.get('chongzhong', ''):
sql += " and chongzhong='%s'" % (para.get('chongzhong', ''))
if para.get('clstq', ''):
sql += " and clstq='%s'" % (para.get('clstq', ''))
sql += " group by t.clstq"
print(sql)
cursor.execute(sql)
rows = cursor.fetchall()
list = []
for row in rows:
list.append({'letter': row[0], 'frequency': int(row[1])})
return list
def detail():
result={}
req=request.args
id=req.get("id")
fid = req.get("fid")
conn = db.get_connection()
cursor = conn.cursor()
cursor.execute(
"""
select name,definitionExpanded,type from network t where trim(t.id)=%s
""" % (id)
)
rows = cursor.fetchall()
data = {}
for row in rows:
i=0
data['name'] = row[i]
i+=1
data['definitionExpanded'] = row[i]
i += 1
data['type'] = row[i]
sql="""
select t3.* from network_hsa t2
inner join hsa t3
on trim(t3.hsa_id)=trim(t2.hsa)
where trim(t2.network)='%s' """ % (fid)
#路通信息
cursor.execute(sql)
rows = cursor.fetchall()
print(sql)
hsa_data = []
for row in rows:
dict_json={}
i = 0
dict_json['hsa_id'] = row[i]
i += 1
dict_json['disease'] = row[i]
hsa_data.append(dict_json)
#基因信息
cursor.execute(
"""
select t5.* from gene_network t4
inner join gene t5
on trim(t5.fid)=trim(t4.Gene)
where trim(t4.network)='%s'
""" % (fid)
)
rows = cursor.fetchall()
gene_data = []
for row in rows:
dict_json = {}
i = 0
dict_json['fid'] = row[i]
i += 1
dict_json['Gene'] = row[i]
i += 1
dict_json['Definition'] = row[i]
i += 1
dict_json['Organism'] = row[i]
gene_data.append(dict_json)
result['network']=data
result['hsa'] = hsa_data
result['gene'] = gene_data
return render_template('ghdn/network/detail.html', result=result,type='network')
