def Register(request):
from configparser import ConfigParser
import os
conn = ConfigParser()
file_path = os.path.join(os.path.abspath('.'), 'config.ini')
if not os.path.exists(file_path):
raise FileNotFoundError("文件不存在")
conn.read(file_path)
pghost = conn.get('api', 'pghost')
pgport = conn.get('api', 'pgport')
pguser = conn.get('api', 'pguser')
pgpassword = conn.get('api', 'pgpassword')
pgdatabase = conn.get('api', 'pgdatabase')
iotdbIp = conn.get('api', 'iotdbIp')
iotdbUser = conn.get('api', 'iotdbUser')
iotdbPassword = conn.get('api', 'iotdbPassword')
#iotdb_conn = JDBC.connect('org.apache.iotdb.jdbc.IoTDBDriver', "jdbc:iotdb://192.168.3.31:6667/", ['root', 'root'], 'iotdb-jdbc-0.9.0-SNAPSHOT-jar-with-dependencies.jar')
iotdb_conn = get_jdbc_connection(iotdbIp, iotdbUser, iotdbPassword)
iotdb_curs = iotdb_conn.cursor()
# conn = psycopg2.connect(host = '172.16.50.7', port = 5432, user = '******', password = '******', database='protodw')
conn = psycopg2.connect(host=pghost,
port=pgport,
user=pguser,
password=pgpassword,
database=pgdatabase)
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
x = print(request.POST)
print(request.body)
print("1")
body = json.loads(str(request.body, encoding='utf8'))
if not body['roidList']:
print('No roidList!')
iotdb_curs.close()
iotdb_conn.close()
cursor.close()
conn.close()
return HttpResponse('No roidList!')
roidList = body['roidList']
print(roidList)
errors = []
for roid in roidList:
sql = "SELECT * FROM plt_tsm_railline WHERE plt_oid = '" + roid + "'"
cursor.execute(sql)
qr = cursor.fetchall()
line_id = qr[0]["plt_lineid"]
sql = "SELECT * FROM plt_cus_car WHERE plt_railline = '" + roid + "'"
cursor.execute(sql)
car_qr = cursor.fetchall()
sql = "SELECT * FROM plt_tsm_protocol WHERE plt_oid = '" + qr[0][
'plt_protoid'] + "'"
cursor.execute(sql)
protocol_qr = cursor.fetchall()
work_condition = []
for protocol in protocol_qr:
poid = protocol["plt_oid"]
sql = "SELECT plt_tsm_template.* FROM ((plt_tsm_protocol INNER JOIN plt_tsm_r_pro2temp ON plt_tsm_protocol.plt_oid = plt_tsm_r_pro2temp.plt_leftoid) INNER JOIN plt_tsm_template ON plt_tsm_r_pro2temp.plt_rightoid = plt_tsm_template.plt_oid) WHERE plt_tsm_protocol.plt_oid = '" + poid + "'"
cursor.execute(sql)
template_qr = cursor.fetchall()
for template in template_qr:
toid = template["plt_oid"]
sql = "SELECT plt_tsm_templatepara.* FROM ((plt_tsm_template INNER JOIN plt_tsm_r_tem2tempara ON plt_tsm_template.plt_oid = plt_tsm_r_tem2tempara.plt_leftoid) INNER JOIN plt_tsm_templatepara ON plt_tsm_r_tem2tempara.plt_rightoid = plt_tsm_templatepara.plt_oid) WHERE plt_tsm_template.plt_oid = '" + toid + "'"
cursor.execute(sql)
tempara_qr = cursor.fetchall()
for tempara in tempara_qr:
if (tempara["plt_paratype"] != "工况参数"):
continue
name = tempara["plt_paraid"]
type = tempara["plt_datatype"]
iotdb_type = ""
iotdb_encoding = ""
if type == "Int":
iotdb_type = "INT32"
iotdb_encoding = "RLE"
elif type == "Long":
iotdb_type = "INT64"
iotdb_encoding = "RLE"
elif type == "Float":
iotdb_type = "FLOAT"
iotdb_encoding = "GORILLA"
elif type == "Double":
iotdb_type = "DOUBLE"
iotdb_encoding = "GORILLA"
elif type == "String":
iotdb_type = "TEXT"
iotdb_encoding = "PLAIN"
elif type == "Boolean":
iotdb_type = "BOOLEAN"
iotdb_encoding = "RLE"
work_condition.append((name, iotdb_type, iotdb_encoding))
for car in car_qr:
car_id = car["plt_carid"]
coid = car["plt_oid"]
sql = "SELECT * FROM plt_cus_terminal WHERE plt_carid = '" + coid + "'"
cursor.execute(sql)
terminal_qr = cursor.fetchall()
for terminal in terminal_qr:
position = terminal["plt_position"]
terminal_id = terminal["plt_terminalid"]
if position == "车头":
terminal_id = "Head"
elif position == "车尾":
terminal_id = "Tail"
storage_group = "root." + line_id + "." + car_id + "." + terminal_id
iotdb_sql = "set storage group to " + storage_group
try:
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
try:
iotdb_sql = "create timeseries " + storage_group + ".OriginalPackage with datatype=TEXT,encoding=PLAIN"
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for i in range(1, 7):
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage" + str(
i) + "." + wc[0] + " with datatype=" + wc[
1] + ",encoding=" + wc[2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(
e
) != 'java.sql.SQLException: Method not supported'
):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage11." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage12." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage21." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage22." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage31." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage32." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage41." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage42." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage51." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage52." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage61." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
for wc in work_condition:
try:
iotdb_sql = "create timeseries " + storage_group + "." + "Carriage62." + wc[
0] + " with datatype=" + wc[1] + ",encoding=" + wc[
2]
iotdb_curs.execute(iotdb_sql)
except Exception as e:
if (str(e) !=
'java.sql.SQLException: Method not supported'):
errors.append(str(e))
iotdb_curs.close()
iotdb_conn.close()
cursor.close()
conn.close()
return HttpResponse(errors)
def Query(request):
from configparser import ConfigParser
import os
conn = ConfigParser()
file_path = os.path.join(os.path.abspath('.'), 'config.ini')
if not os.path.exists(file_path):
raise FileNotFoundError("文件不存在")
conn.read(file_path)
pghost = conn.get('api', 'pghost')
pgport = conn.get('api', 'pgport')
pguser = conn.get('api', 'pguser')
pgpassword = conn.get('api', 'pgpassword')
pgdatabase = conn.get('api', 'pgdatabase')
iotdbIp = conn.get('api', 'iotdbIp')
iotdbUser = conn.get('api', 'iotdbUser')
iotdbPassword = conn.get('api', 'iotdbPassword')
print(request.body)
body = json.loads(str(request.body, encoding='utf8'))
if not body['railLineOid']:
print('No railLineOid!')
return JsonResponse({'state': 'Error', 'value': '没有选择线路！'})
roid = body['railLineOid']
if not body['carList']:
print('No carList!')
return JsonResponse({'state': 'Error', 'value': '没有选择列车！'})
carList = body['carList']
if not body['terminalList']:
print('No terminalList!')
return JsonResponse({'state': 'Error', 'value': '没有选择终端！'})
tmp = body['terminalList'].split(',')
terminalList = []
for i in tmp:
if i == '车头':
terminalList.append('Head')
elif i == '车尾':
terminalList.append('Tail')
if not body['CarriageList']:
print('No CarriageList!')
return JsonResponse({'state': 'Error', 'value': '没有选择车厢！'})
carriageList = body['CarriageList'].split(',')
if not body['paraList']:
print('No paraList!')
return JsonResponse({'state': 'Error', 'value': '没有选择工况！'})
paraList = body['paraList']
if not body['startTime']:
print('No startTime!')
return JsonResponse({'state': 'Error', 'value': '没有选择开始时间！'})
startTime = body['startTime'].split('.')[0]
if not body['endTime']:
print('No endTime!')
return JsonResponse({'state': 'Error', 'value': '没有选择结束时间！'})
endTime = body['endTime'].split('.')[0]
if startTime > endTime:
print('startTime > endTime!')
return JsonResponse({'state': 'Error', 'value': '开始时间晚于结束时间！'})
#iotdb_conn = JDBC.connect('org.apache.iotdb.jdbc.IoTDBDriver', "jdbc:iotdb://192.168.3.31:6667/", ['root', 'root'], 'iotdb-jdbc-0.9.0-SNAPSHOT-jar-with-dependencies.jar')
iotdb_conn = get_jdbc_connection(iotdbIp, iotdbUser, iotdbPassword)
iotdb_curs = iotdb_conn.cursor()
# conn = psycopg2.connect(host = '172.16.50.7', port = 5432, user = '******', password = '******', database='protodw')
conn = psycopg2.connect(host=pghost,
port=pgport,
user=pguser,
password=pgpassword,
database=pgdatabase)
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
sql = "SELECT * FROM plt_tsm_railline WHERE plt_oid = '" + roid + "'"
cursor.execute(sql)
qr = cursor.fetchall()
line_id = qr[0]["plt_lineid"]
ret = []
for i in carList:
for j in terminalList:
for k in carriageList:
for l in paraList:
#time_series = "root." + "BJ8T00" + "." + i + "." + j + ".Carriage" + k
time_series = "root." + line_id + "." + i + "." + j + ".Carriage" + k
if l == "OriginalPackage":
time_series = "root." + line_id + "." + i + "." + j
#time_series = "root." + "BJ8T00.H411.Head"+".Carriage" + k
# logging.warning(time_series)
sql = "SELECT " + l + " FROM " + time_series + " where time >= " + startTime + " && time <= " + endTime
#sql = "SELECT " + l + " FROM " + "root.BJ8T00.H411.Head.Carriage2" + " where time <= " + endTime
# logging.warning(sql)
try:
iotdb_curs.execute(sql)
qr = iotdb_curs.fetchall()
# print(qr)
for r in qr:
ret.append([time_series + "." + l, r[0], r[1]])
except Exception as e:
print(e)
cursor.close()
conn.close()
iotdb_curs.close()
iotdb_conn.close()
return JsonResponse({'state': 'OK', 'value': ret})
def XMLexport(request):
from configparser import ConfigParser
import os
conn = ConfigParser()
file_path = os.path.join(os.path.abspath('.'), 'config.ini')
if not os.path.exists(file_path):
raise FileNotFoundError("文件不存在")
conn.read(file_path)
pghost = conn.get('api', 'pghost')
pgport = conn.get('api', 'pgport')
pguser = conn.get('api', 'pguser')
pgpassword = conn.get('api', 'pgpassword')
pgdatabase = conn.get('api', 'pgdatabase')
iotdbIp = conn.get('api', 'iotdbIp')
iotdbUser = conn.get('api', 'iotdbUser')
iotdbPassword = conn.get('api', 'iotdbPassword')
'''print(request.POST)
if not request.POST.get('pOidList'):
print('No poidList!')
return 'No pOidList!'
poidList = request.POST.get('pOidList')
print(poidList)
'''
print(request.GET)
if not request.GET.get('poidList'):
print('No poidList!')
return HttpResponse('No poidList!')
poidList = [request.GET.get('poidList')]
print(poidList)
#ElementTree 构建XML元素框架
protName = ""
protocol = ET.Element('protocol')
tree = ET.ElementTree(protocol)
protList = ET.SubElement(protocol, 'protList')
protSolnList = ET.SubElement(protocol, 'protSolnList')
templtList = ET.SubElement(protocol, 'templtList')
tmpSolnList = ET.SubElement(protocol, 'tmpSolnList')
paraList = ET.SubElement(protocol, 'paraList')
paraSolnList = ET.SubElement(protocol, 'paraSolnList')
canIDList = ET.SubElement(protocol, 'canIDList')
#psycopg2数据库连接 To-Do: 改为从配置文件读取
# conn = psycopg2.connect(host = '172.16.50.7', port = 5432, user = '******', password = '******', database='protodw')
conn = psycopg2.connect(host=pghost,
port=pgport,
user=pguser,
password=pgpassword,
database=pgdatabase)
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
#协议处理方案集合
ptsOidSet = set([])
for poid in poidList:
tpQue = []
#查询协议
sql = "SELECT * FROM plt_tsm_protocol WHERE plt_oid = '" + poid + "'"
cursor.execute(sql)
qr = cursor.fetchall()
if (len(qr) >= 1):
protName = qr[0]["plt_protname"]
prot = ET.SubElement(protList, 'prot')
addContent(prot, qr[0])
#查询协议处理方案，去重
sql = "SELECT * FROM plt_tsm_protreatclass WHERE plt_oid = '" + qr[
0]["plt_treatclass"] + "'"
cursor.execute(sql)
qr = cursor.fetchall()
if (len(qr) >= 1):
psOid = qr[0]['plt_oid']
#判断是否重复
if (psOid not in ptsOidSet):
protSoln = ET.SubElement(protSolnList, 'protSoln')
addContent(protSoln, qr[0])
ptsOidSet.add(psOid)
tempOidList = ET.SubElement(prot, 'tempOidList')
#查询写入协议关联的模版的oid到协议标签下，写入模版信息到templtList
sql = "SELECT plt_tsm_template.* FROM ((plt_tsm_protocol INNER JOIN plt_tsm_r_pro2temp ON plt_tsm_protocol.plt_oid = plt_tsm_r_pro2temp.plt_leftoid) INNER JOIN plt_tsm_template ON plt_tsm_r_pro2temp.plt_rightoid = plt_tsm_template.plt_oid) WHERE plt_tsm_protocol.plt_oid = '" + poid + "'"
cursor.execute(sql)
qr = cursor.fetchall()
#每个循环处理一个模版
#模板参数集合
pOidSet = set([])
psOidSet = set([])
tOidSet = set([])
tsOidSet = set([])
sql = "SELECT plt_tsm_templatepara.* FROM ((plt_tsm_protocol INNER JOIN plt_tsm_r_pro2para ON plt_tsm_protocol.plt_oid = plt_tsm_r_pro2para.plt_leftoid) INNER JOIN plt_tsm_templatepara ON plt_tsm_r_pro2para.plt_rightoid = plt_tsm_templatepara.plt_oid) WHERE plt_tsm_protocol.plt_oid = '" + poid + "'"
cursor.execute(sql)
poro_head_qr = cursor.fetchall()
poro_head_oid_list = ET.SubElement(prot, 'paraOidList')
for i in range(len(poro_head_qr)):
paraType = str(poro_head_qr[i]['plt_paratype'])
paraId = str(poro_head_qr[i]['plt_paraid'])
#1. 把模板参数oid写在模板标签下
#！！！！改，加一个List，如果该模版参数已经保存过则不再保存, 但是模版参数的oid加入unique的即可
pOidText = str(poro_head_qr[i]['plt_oid'])
pOid = ET.SubElement(poro_head_oid_list, 'pOid')
pOid.text = pOidText
sql = "SELECT * FROM plt_tsm_r_pro2para WHERE plt_leftoid = '" + poid + "' AND plt_rightoid = '" + pOidText + "'"
cursor.execute(sql)
qrR = cursor.fetchall()
if (len(qrR) >= 1):
offset = str(qrR[0]['plt_offset'])
order = str(qrR[0]['plt_order'])
pOid.set('offset', offset)
pOid.set('order', order)
if pOidText not in pOidSet:
pOidSet.add(pOidText)
#2. 把对应的模版参数记录写在paraList的para标签下
para = ET.SubElement(paraList, 'para')
addContent(para, poro_head_qr[i])
#把模版参数解决方案写在paraSolnList对应的pSoln对应的标签下
sql = "SELECT plt_tsm_paratreatclass.* FROM plt_tsm_templatepara INNER JOIN plt_tsm_paratreatclass ON plt_tsm_templatepara.plt_treatclass = plt_tsm_paratreatclass.plt_oid WHERE plt_tsm_templatepara.plt_oid = '" + pOidText + "'"
cursor.execute(sql)
pSolnQr = cursor.fetchall()
#3. 模板参数解决方案oid写入para的solnOid标签下
#注意：模版参数解决方案可能为空
solnOid = ET.SubElement(para, 'solnOid')
if (len(pSolnQr) >= 1):
pSolnOid = str(pSolnQr[0]['plt_oid'])
#参数oid
solnOid.text = pSolnOid
#参数解决方案的Oid肯定是要写在para里的，但是如果重复不用重复在paraSolution里写
if (pSolnOid not in psOidSet):
pSoln = ET.SubElement(paraSolnList, 'pSoln')
addContent(pSoln, pSolnQr[0])
psOidSet.add(pSolnOid)
for i in range(len(qr)):
#1. 模板oid写在协议tempOidList中
tOid = ET.SubElement(tempOidList, 'tOid')
tOidText = qr[i]['plt_oid']
tOid.text = tOidText
#2. 每个模板的记录写在templtList下的每个template标签下
template = ET.SubElement(templtList, 'template')
addContent(template, qr[i])
#每个template加paraOidList标签
paraOidList = ET.SubElement(template, 'paraOidList')
#查询模版对应的模板参数
#注意模版的模版参数可能为空
sql = "SELECT plt_tsm_templatepara.* FROM ((plt_tsm_template INNER JOIN plt_tsm_r_tem2tempara ON plt_tsm_template.plt_oid = plt_tsm_r_tem2tempara.plt_leftoid) INNER JOIN plt_tsm_templatepara ON plt_tsm_r_tem2tempara.plt_rightoid = plt_tsm_templatepara.plt_oid) WHERE plt_tsm_template.plt_oid = '" + tOidText + "'"
cursor.execute(sql)
tpltParaQr = cursor.fetchall()
#每次循环处理一个模版参数
for j in range(len(tpltParaQr)):
paraType = str(tpltParaQr[j]['plt_paratype'])
paraId = str(tpltParaQr[j]['plt_paraid'])
if (paraType == "子模板参数"):
tpQue.append(paraId)
#1. 把模板参数oid写在模板标签下
#！！！！改，加一个List，如果该模版参数已经保存过则不再保存, 但是模版参数的oid加入unique的即可
pOidText = str(tpltParaQr[j]['plt_oid'])
pOid = ET.SubElement(paraOidList, 'pOid')
pOid.text = pOidText
sql = "SELECT * FROM plt_tsm_r_tem2tempara WHERE plt_leftoid = '" + tOidText + "' AND plt_rightoid = '" + pOidText + "'"
cursor.execute(sql)
qrR = cursor.fetchall()
if (len(qrR) >= 1):
offset = str(qrR[0]['plt_offset'])
order = str(qrR[0]['plt_order'])
pOid.set('offset', offset)
pOid.set('order', order)
if pOidText not in pOidSet:
pOidSet.add(pOidText)
#2. 把对应的模版参数记录写在paraList的para标签下
para = ET.SubElement(paraList, 'para')
addContent(para, tpltParaQr[j])
#把模版参数解决方案写在paraSolnList对应的pSoln对应的标签下
sql = "SELECT plt_tsm_paratreatclass.* FROM plt_tsm_templatepara INNER JOIN plt_tsm_paratreatclass ON plt_tsm_templatepara.plt_treatclass = plt_tsm_paratreatclass.plt_oid WHERE plt_tsm_templatepara.plt_oid = '" + pOidText + "'"
cursor.execute(sql)
pSolnQr = cursor.fetchall()
#3. 模板参数解决方案oid写入para的solnOid标签下
#注意：模版参数解决方案可能为空
solnOid = ET.SubElement(para, 'solnOid')
if (len(pSolnQr) >= 1):
pSolnOid = str(pSolnQr[0]['plt_oid'])
#参数oid
solnOid.text = pSolnOid
#参数解决方案的Oid肯定是要写在para里的，但是如果重复不用重复在paraSolution里写
if (pSolnOid not in psOidSet):
pSoln = ET.SubElement(paraSolnList, 'pSoln')
addContent(pSoln, pSolnQr[0])
psOidSet.add(pSolnOid)
#3. 把模板对应的解决方案写入tmpSolnList各自的tmpSoln标签下
sql = "SELECT plt_tsm_temptreclass.* FROM plt_tsm_template INNER JOIN plt_tsm_temptreclass ON plt_tsm_template.plt_treatclass = plt_tsm_temptreclass.plt_oid WHERE plt_tsm_template.plt_oid = '" + tOidText + "'"
cursor.execute(sql)
tQr = cursor.fetchall()
if (len(tQr) >= 1):
tsOid = str(tQr[0]['plt_oid'])
if tsOid not in tsOidSet:
tsOidSet.add(tsOid)
tmpSoln = ET.SubElement(tmpSolnList, 'tmpSoln')
addContent(tmpSoln, tQr[0])
sql = "SELECT * FROM plt_tsm_tempid WHERE plt_tempid = '" + tOidText + "'"
cursor.execute(sql)
canIDs = cursor.fetchall()
for canID in canIDs:
canIDElement = ET.SubElement(canIDList, 'canID')
addContent(canIDElement, canID)
#子模板
for stp in tpQue:
if (stp not in tOidSet):
tOidSet.add(stp)
#找到template表中的子模版
sql = "SELECT * FROM plt_tsm_template WHERE plt_templateid = '" + stp + "'"
cursor.execute(sql)
qr = cursor.fetchall()
if (len(qr) > 0):
tOidText = qr[0]['plt_oid']
#每个子模版的记录写在templtList下的每个template标签下
template = ET.SubElement(templtList, 'template')
addContent(template, qr[0])
#每个template加paraOidList标签
paraOidList = ET.SubElement(template, 'paraOidList')
#查询模板对应的模板参数
#注意模板的模板参数可能为空
sql = "SELECT plt_tsm_templatepara.* FROM ((plt_tsm_template INNER JOIN plt_tsm_r_tem2tempara ON plt_tsm_template.plt_oid = plt_tsm_r_tem2tempara.plt_leftoid) INNER JOIN plt_tsm_templatepara ON plt_tsm_r_tem2tempara.plt_rightoid = plt_tsm_templatepara.plt_oid) WHERE plt_tsm_template.plt_oid = '" + tOidText + "'"
cursor.execute(sql)
tpltParaQr = cursor.fetchall()
#每次循环处理一个模版参数
for i in range(len(tpltParaQr)):
paraType = str(tpltParaQr[i]['plt_paratype'])
paraId = str(tpltParaQr[i]['plt_paraid'])
if (paraType == "子模板参数"):
tpQue.append(paraId)
#1. 把模板参数oid写在模板标签下
#改，加一个List，如果该模版参数已经保存过则不再保存，但是模板参数的oid加入unique的即可
pOidText = str(tpltParaQr[i]['plt_oid'])
pOid = ET.SubElement(paraOidList, 'pOid')
pOid.text = pOidText
sql = "SELECT * FROM plt_tsm_r_tem2tempara WHERE plt_leftoid = '" + tOidText + "' AND plt_rightoid = '" + pOidText + "'"
cursor.execute(sql)
qrR = cursor.fetchall()
if (len(qrR) > 0):
offset = str(qrR[0]['plt_offset'])
order = str(qrR[0]['plt_order'])
pOid.set('offset', offset)
pOid.set('order', order)
if (pOidText not in pOidSet):
pOidSet.add(pOidText)
#2. 把对应的模板参数记录写在paraList的para标签下
para = ET.SubElement(paraList, 'para')
addContent(para, tpltParaQr[i])
#把模板参数解决方案写在paraSolnList对应的pSoln对应的标签下
sql = "SELECT plt_tsm_paratreatclass.* FROM plt_tsm_templatepara INNER JOIN plt_tsm_paratreatclass ON plt_tsm_templatepara.plt_treatclass = plt_tsm_paratreatclass.plt_oid WHERE plt_tsm_templatepara.plt_oid = '" + pOidText + "'"
cursor.execute(sql)
pSolnQr = cursor.fetchall()
#3. 模板参数解决方案oid写入para的solnOid标签下
#注意：模版参数解决方案可能为空
solnOid = ET.SubElement(para, 'solnOid')
if (len(pSolnQr) > 0):
pSolnOid = str(pSolnQr[0]['plt_oid'])
#参数oid
solnOid.text = pSolnOid
#参数解决方案的Oid肯定是要写在para里的，但是如果重复不用重复在paraSolution里写
if (pSolnOid not in psOidSet):
pSoln = ET.SubElement(
paraSolnList, 'pSoln')
addContent(pSoln, pSolnQr[0])
#如果oid存在，在hashset中是否可重复
psOidSet.add(pSolnOid)
#3. 把子模板对应的解决方案写入tmpSolnList各自的tmpSoln标签下
sql = "SELECT plt_tsm_temptreclass.* FROM plt_tsm_template INNER JOIN plt_tsm_temptreclass ON plt_tsm_template.plt_treatclass = plt_tsm_temptreclass.plt_oid WHERE plt_tsm_template.plt_oid = '" + tOidText + "'"
cursor.execute(sql)
tQr = cursor.fetchall()
if len(tQr) > 0:
tsOid = str(tQr[0]['plt_oid'])
if (tsOid not in tsOidSet):
tsOidSet.add(tsOid)
tmpSoln = ET.SubElement(tmpSolnList, 'tmpSoln')
addContent(tmpSoln, tQr[0])
#写入文件
if (len(poidList) > 1):
protName = 'protocol'
indent(protocol)
tree.write(protName + '.xml',
xml_declaration=True,
encoding='UTF-8',
method='xml')
file = open(protName + '.xml', 'rb')
response = HttpResponse(file)
response['Content-Type'] = 'application/octet-stream'
response[
'Content-Disposition'] = 'attachment;filename="' + protName + '.xml"'
os.remove(protName + '.xml')
return response
import psycopg2.extras
if __name__ == '__main__':
from configparser import ConfigParser
import os
conn = ConfigParser()
file_path = os.path.join(os.path.abspath('.'), 'config.ini')
if not os.path.exists(file_path):
raise FileNotFoundError("文件不存在")
conn.read(file_path)
pghost = conn.get('api', 'pghost')
pgport = conn.get('api', 'pgport')
pguser = conn.get('api', 'pguser')
pgpassword = conn.get('api', 'pgpassword')
pgdatabase = conn.get('api', 'pgdatabase')
iotdbIp = conn.get('api', 'iotdbIp')
iotdbUser = conn.get('api', 'iotdbUser')
iotdbPassword = conn.get('api', 'iotdbPassword')
# conn = psycopg2.connect(host='223.99.13.54', port=5098, user='******', password='******', database='dataway')
conn = psycopg2.connect(host=pghost,
port=pgport,
user=pguser,
password=pgpassword,
database=pgdatabase)
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
print(cursor)
