python解析xml入库_在python中解析XML并将其添加到数据库

importpandasaspdimportxml.etree.ElementTreeasETimportcx_Oracleimportcsv

doc=ET.parse(events)nodes=doc.findall('.//Row')#RANGE(num) - COLUMN COUNTS(SysName,ServerDt,UIDS,EmplID,....T)=(list()foriinrange(18))fornodeinnodes:foreleminnode.findall("*"):try:ifelem.tag=="SysName":SysName.append(elem.text)ifelem.tag=="UID":UIDS.append(elem.text)ifelem.tag=="ServerDt":ServerDt.append(elem.text)ifelem.tag=="PersID":PersID.append(elem.text)...exceptAttributeError:print(elem.tag)#ER nonetype' object has no attribute 'text' python ER arrays must all be same lengths1=pd.Series(SysName)s2=pd.Series(ServerDt)s3=pd.Series(UIDS).....#TEMPORY CSV. DONT KNOW WHY, BUT COLUMNS ARE MIXED WITH THIS ITERATIONdf=pd.DataFrame({"SysName":s1,"ServerDt":s2,"UIDS":s3,"PersID":s4,"Emp_name":s5,"EVENTID":s6,...})file_name='events.csv'df.to_csv(file_name,sep='\t')print("File name: ",my_file.name,"created")ResultSet_Py_List=[]ora_conn=cx_Oracle.connect('login/pass@TNSNAME')ora_cursor=ora_conn.cursor()my_file=open(file_name,'r',newline='')#PANDA ER: 'utf8' codec can't decode byte 0xe9 in position 10: invalid continuation bytereader=csv.reader(my_file,dialect='excel',delimiter='\t')forindex,rowinenumerate(reader):#Without header CSVifindex>0:try:ResultSet_Py_List.append(row)exceptAttributeError:passprint(str(len(ResultSet_Py_List))+' Records from Source')sql_del="""delete from HR.EVENTS_TST"""ora_cursor.execute(sql_del)ora_cursor.execute("commit")print("Table is clean")#COLUMNS ARE MIXED IN CSV ITERATIONsql_insert="""

INSERT INTO HR.EVENTS_TST (ROW_NUM

,ACCRUALSNAME

, ACCRUALSPROZ

, ACCRUALSSUM

, AWART

, CAUSEEVENTID

...)

VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14

,:15,:16,:17,:18, :19)

"""ora_cursor.prepare(sql_insert)ora_cursor.executemany(None,ResultSet_Py_List)ora_conn.commit()ora_cursor.execute("commit")print("Data imported")