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)