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

　　xml文档?

<?Stream Vera Sans Mono', 'Courier New', Courier, monospace !important; FLOAT: none !important; BORDER-TOP-WIDTH: 0px !important; BORDER-BOTTOM-WIDTH: 0px !important; HEIGHT: auto !important; COLOR: rgb(0,102,153) !important; CLEAR: both; VERTICAL-ALIGN: baseline !important; OVERFLOW: visible !important; TOP: auto !important; RIGHT: auto !important; FONT-WEIGHT: bold !important; PADDING-TOP: 0px; LEFT: auto !important; border-image-source: initial; border-radius: 0px; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; background-size: initial; background-origin: initial; background-clip: initial" class="xml keyword">xmlversion="1.0"encoding="UTF-8"?>

<?xml-stylesheettype="text/xsl"href="1.xslt"rel="external nofollow"?>

<!DOCTYPE sys_info [

 <!ELEMENT sys_info (info+)>

 <!ELEMENT info (sysDescr,sysUpTIMe,sysContact,sysName)>

 <!ELEMENT sysDescr (#PCDATA)>

 <!ELEMENT sysUpTime (#PCDATA)>

 <!ELEMENT sysContact (#PCDATA)>

 <!ELEMENT sysName (#PCDATA)>

 <!ATTLIST info ip CDATA #REQUIRED>

]>

<sys_infoxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="1.xsd">

 <infoip="192.168.1.1">

  <sysDescr>X86-windows2000</sysDescr>

  <sysUpTime>9 hours 42 minutes</sysUpTime>

  <sysContact>zhangsan</sysContact>

  <sysName>computerZhang</sysName>

 </info>

 <infoip="192.168.1.3">

  <sysDescr>router</sysDescr>

  <sysUpTime>24 hours</sysUpTime>

  <sysContact>ruijie</sysContact>

  <sysName>Router2</sysName>

 </info>

 <infoip="192.168.2.1">

  <sysDescr>router</sysDescr>

  <sysUpTime>89 hours</sysUpTime>

  <sysContact>Cisco</sysContact>

  <sysName>Router3</sysName>

 </info>

</sys_info>

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

1	`apt-get install python-MySQLdb`

程序运行如下?

1

2

3

4

5

root@lj /h/s/x/3# python 21.py -h

usage: 21.py[-h] status

positional arguments:

 status  0clar,1read,2insert

读取xml保存到数据库?

1

2

3

4

5

root@lj /h/s/x/3# python 21.py 2

插入语句: insert into info values ('192.168.1.1','X86-Windows2000','9 hours 42 minutes','zhangsan','computerZhang')

插入语句: insert into info values ('192.168.1.3','router','24 hours','ruijie','Router2')

插入语句: insert into info values ('192.168.2.1','router','89 hours','Cisco','Router3')

insert success!!!

读取数据库保存到xml文档?

root@lj /h/s/x/3# python 21.py 1

+-------------+-----------------+--------------------+------------+---------------+

| IP地址 | sysDescr.0| sysUpTime.0  | sysContact | sysName.0|

+-------------+-----------------+--------------------+------------+---------------+

| 192.168.1.1| X86-Windows2000 | 9hours 42minutes | zhangsan | computerZhang |

| 192.168.1.3|  router  |  24hours  | ruijie | Router2 |

| 192.168.2.1|  router  |  89hours  | Cisco | Router3 |

+-------------+-----------------+--------------------+------------+---------------+

write into sys.xml...

建立数据库的sql文件:?

--MySQL dump 10.16Distrib 10.1.21-MariaDB, fordebian-linux-gnu (x86_64)

--

--host: localhost Database: localhost

--------------------------------------------------------

--Server version 10.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 */;

/*!40101SETNAMES utf8mb4 */;

/*!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*/;

--

--Table structure fortable `info`

--

DROP TABLE IF EXISTS `info`;

/*!40101SET@saved_cs_client  =@@character_set_client */;

/*!40101SETcharacter_set_client =utf8 */;

CREATE TABLE `info` (

 `ip` char(15) NOT NULL,

 `sysDescr` varchar(20) DEFAULT NULL,

 `sysUpTime` varchar(40) DEFAULT NULL,

 `sysContract` varchar(20) DEFAULT NULL,

 `sysName` varchar(20) DEFAULT NULL,

 PRIMARY KEY (`ip`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101SETcharacter_set_client =@saved_cs_client */;

--

--Dumping data fortable `info`

--

LOCK TABLES `info` WRITE;

/*!40000ALTER TABLE `info` DISABLE KEYS */;

INSERT INTO `info` VALUES ('192.168.1.1','X86-Windows2000','9 hours 42 minutes','zhangsan','computerZhang'),('192.168.1.3','router','24 hours','ruijie','Router2'),('192.168.2.1','router','89 hours','Cisco','Router3');

/*!40000ALTER TABLE `info` ENABLE KEYS */;

UNLOCK TABLES;

/*!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 */;

--Dump completed on 2017-03-2315:36:31

　　下面是主要代码

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Date : 2017-03-23 14:47:39

# @Author : 江sir (2461805286@QQ.com)

# @Link : 

# @Version : $1.1

importsys

importxml.etree.ElementTree as ET

importMySQLdb

importargparse

fromprettytable importPrettyTable 

'''

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

'''

defbuildNewsXmlFile(data):

 root =ET.Element('sys_info')#创建sys_info根元素

 # print help(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'write into sys.xml...'

 tree =ET.ElementTree(root)

 tree.write("sys.xml")

defxml_parser():

 data ={}

 data_list =[]

 tree =ET.parse('21.xml')

 root =tree.getroot()# 获取根元素

 forinfo inroot.findall('info'): #查找所有info元素

  forchild ininfo: #对每个info元素遍历属性和子节点

   data ['ip']=info.attrib['ip']

   data[child.tag] =child.text

  # print data.values()

  data_list.append(data.values())

 # print data_list

 returndata_list

defget_Mysql():

 conn =MySQLdb.connect('localhost','root','root','sys_info2',charset='utf8')

 cursor =conn.cursor()

 cursor.execute('select * from info');

 result =cursor.fetchall()

 ifnotresult:

  print'please insert the database first'

  sys.exit()

 x =PrettyTable(['IP地址','sysDescr.0','sysUpTime.0','sysContact','sysName.0'])

 fori inresult:

  x.add_row(i)

 printx

 # print result

 returnresult

defset_Mysql(data):

 conn =MySQLdb.connect('localhost','root','root','sys_info2',charset='utf8')

 cursor =conn.cursor()

 fori indata:

  # print tuple(i)

  sysName,ip,sysUpTime,sysDescr,sysContact =tuple(i)

  sql ="insert into info values ('%s','%s','%s','%s','%s')"%(ip,sysDescr,sysUpTime,sysContact,sysName)

  print'插入语句:',sql

  try:

   cursor.execute(sql)

  except:

   print'please clear the database'

   sys.exit()

 print'insert success!!!'

 conn.commit()

 conn.close()

defclear_Mysql():

 conn =MySQLdb.connect('localhost','root','root','sys_info2',charset='utf8')

 cursor =conn.cursor()

 cursor.execute('delete from info')

 conn.commit()

 conn.close()

defmain():

 parser =argparse.ArgumentParser()

 parser.add_argument('status',type=int,help="0clar,1read,2insert")

 arg =parser.parse_args()

 # print arg

 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()

阅读全文

扫码关注“ 多特资源库 ”

上一篇：Python使用Flask框架获取当前查询参数

下一篇：旅行青蛙蜗牛出现时间规律一览

更多更全的软件资源下载

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

相关攻略