利用python实现xml与数据库读取转换的方法

2024-02-19 13:07:03 55

前言

xml课的第三第四个作业都是用java编程来实现xmldom的一些转换,因为自己没怎么学过java，因此和老师说了下想用python来实现第三第四个作业，下面就直接贴代码了

xml文档










]>



X86-Windows2000
9hours42minutes
zhangsan
computerZhang



router
24hours
ruijie
Router2


router
89hours
Cisco
Router3

解析xml文档用的是python自带的xml库ElementTree,读取mysql可以安装MySQLdb模块

apt-getinstallpython-MySQLdb

程序运行如下

root@lj/h/s/x/3#python21.py-h
usage:21.py[-h]status

positionalarguments:
status0clar,1read,2insert

读取xml保存到数据库

root@lj/h/s/x/3#python21.py2
插入语句:insertintoinfovalues('192.168.1.1','X86-Windows2000','9hours42minutes','zhangsan','computerZhang')
插入语句:insertintoinfovalues('192.168.1.3','router','24hours','ruijie','Router2')
插入语句:insertintoinfovalues('192.168.2.1','router','89hours','Cisco','Router3')
insertsuccess!!!

读取数据库保存到xml文档

root@lj/h/s/x/3#python21.py1
+-------------+-----------------+--------------------+------------+---------------+
|IP地址|sysDescr.0|sysUpTime.0|sysContact|sysName.0|
+-------------+-----------------+--------------------+------------+---------------+
|192.168.1.1|X86-Windows2000|9hours42minutes|zhangsan|computerZhang|
|192.168.1.3|router|24hours|ruijie|Router2|
|192.168.2.1|router|89hours|Cisco|Router3|
+-------------+-----------------+--------------------+------------+---------------+
writeintosys.xml...

建立数据库的sql文件:

--MySQLdump10.16Distrib10.1.21-MariaDB,fordebian-linux-gnu(x86_64)
--
--Host:localhostDatabase:localhost
--------------------------------------------------------
--Serverversion10.1.21-MariaDB-5

/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;
/*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;
/*!40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;
/*!40101SETNAMESutf8mb4*/;
/*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;
/*!40103SETTIME_ZONE='+00:00'*/;
/*!40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*!40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;
/*!40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;
/*!40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/;

--
--Tablestructurefortable`info`
--

DROPTABLEIFEXISTS`info`;
/*!40101SET@saved_cs_client=@@character_set_client*/;
/*!40101SETcharacter_set_client=utf8*/;
CREATETABLE`info`(
`ip`char(15)NOTNULL,
`sysDescr`varchar(20)DEFAULTNULL,
`sysUpTime`varchar(40)DEFAULTNULL,
`sysContract`varchar(20)DEFAULTNULL,
`sysName`varchar(20)DEFAULTNULL,
PRIMARYKEY(`ip`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
/*!40101SETcharacter_set_client=@saved_cs_client*/;

--
--Dumpingdatafortable`info`
--

LOCKTABLES`info`WRITE;
/*!40000ALTERTABLE`info`DISABLEKEYS*/;
INSERTINTO`info`VALUES('192.168.1.1','X86-Windows2000','9hours42minutes','zhangsan','computerZhang'),('192.168.1.3','router','24hours','ruijie','Router2'),('192.168.2.1','router','89hours','Cisco','Router3');
/*!40000ALTERTABLE`info`ENABLEKEYS*/;
UNLOCKTABLES;
/*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/;

/*!40101SETSQL_MODE=@OLD_SQL_MODE*/;
/*!40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;
/*!40014SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;
/*!40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;
/*!40101SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;
/*!40101SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/;
/*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/;

--Dumpcompletedon2017-03-2315:36:31

下面是主要代码

#!/usr/bin/envpython
#-*-coding:utf-8-*-
#@Date:2017-03-2314:47:39
#@Author:江sir(2461805286@qq.com)
#@Link:http://www.blogsir.com.cn
#@Version:$1.1

importsys
importxml.etree.ElementTreeasET
importMySQLdb
importargparse
fromprettytableimportPrettyTable

'''
一个xml作业，自己用python实现了从xml读取到数据库，和从数据库读取到xml的功能
'''

defbuildNewsXmlFile(data):


root=ET.Element('sys_info')#创建sys_info根元素
#printhelp(ET)
info=ET.SubElement(root,"info",attrib={'ip':'%s'%data[0][0]})#创建四个二级元素
sysDescr=ET.SubElement(info,"sysDescr")
sysUpTime=ET.SubElement(info,"sysUpTime")
sysContact=ET.SubElement(info,"sysContact")
sysName=ET.SubElement(info,"sysName")
sysDescr.text=data[0][1]
sysUpTime.text=data[0][2]
sysContact.text=data[0][3]
sysName.text=data[0][4]

info=ET.SubElement(root,"info",attrib={'ip':'%s'%data[1][0]})
sysDescr=ET.SubElement(info,"sysDescr")
sysUpTime=ET.SubElement(info,"sysUpTime")
sysContact=ET.SubElement(info,"sysContact")
sysName=ET.SubElement(info,"sysName")
sysDescr.text=data[1][1]
sysUpTime.text=data[1][2]
sysContact.text=data[1][3]
sysName.text=data[1][4]

info=ET.SubElement(root,"info",attrib={'ip':'%s'%data[2][0]})
sysDescr=ET.SubElement(info,"sysDescr")
sysUpTime=ET.SubElement(info,"sysUpTime")
sysContact=ET.SubElement(info,"sysContact")
sysName=ET.SubElement(info,"sysName")
sysDescr.text=data[2][1]
sysUpTime.text=data[2][2]
sysContact.text=data[2][3]
sysName.text=data[2][4]

print'writeintosys.xml...'
tree=ET.ElementTree(root)
tree.write("sys.xml")


defxml_parser():
data={}
data_list=[]
tree=ET.parse('21.xml')
root=tree.getroot()#获取根元素
forinfoinroot.findall('info'):#查找所有info元素
forchildininfo:#对每个info元素遍历属性和子节点
data['ip']=info.attrib['ip']
data[child.tag]=child.text

#printdata.values()
data_list.append(data.values())


#printdata_list
returndata_list



defget_Mysql():
conn=MySQLdb.connect('localhost','root','root','sys_info2',charset='utf8')
cursor=conn.cursor()
cursor.execute('select*frominfo');
result=cursor.fetchall()
ifnotresult:
print'pleaseinsertthedatabasefirst'
sys.exit()


x=PrettyTable(['IP地址','sysDescr.0','sysUpTime.0','sysContact','sysName.0'])
foriinresult:
x.add_row(i)
printx

#printresult
returnresult

defset_Mysql(data):
conn=MySQLdb.connect('localhost','root','root','sys_info2',charset='utf8')
cursor=conn.cursor()
foriindata:
#printtuple(i)
sysName,ip,sysUpTime,sysDescr,sysContact=tuple(i)
sql="insertintoinfovalues('%s','%s','%s','%s','%s')"%(ip,sysDescr,sysUpTime,sysContact,sysName)
print'插入语句:',sql
try:
cursor.execute(sql)
except:
print'pleaseclearthedatabase'
sys.exit()
print'insertsuccess!!!'
conn.commit()
conn.close()


defclear_Mysql():
conn=MySQLdb.connect('localhost','root','root','sys_info2',charset='utf8')
cursor=conn.cursor()
cursor.execute('deletefrominfo')
conn.commit()
conn.close()


defmain():
parser=argparse.ArgumentParser()
parser.add_argument('status',type=int,help="0clar,1read,2insert")
arg=parser.parse_args()
#printarg
status=arg.status
ifstatus==1:
data=get_Mysql()
buildNewsXmlFile(data)
elifstatus==2:
data=xml_parser()
set_Mysql(data)
elifstatus==0:
clear_Mysql()
else:
print'usage%s[0|1|2]'%sys.argv[0]

if__name__=='__main__':
main()

第四个作业是web编程，用python的flask框架即可快速实现一个xml文档的显示，文件过多，就不贴了

总结

以上就是这文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如有疑问大家可以留言交流，谢谢大家对毛票票的支持。

利用python实现xml与数据库读取转换的方法

热门推荐

随机推荐