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

来源：网络时间：2018-02-07 16:04:34

　　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">xml version="1.0" encoding="UTF-8"?>

<?xml-stylesheet type="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_info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="1.xsd">

 <info ip="192.168.1.1">

  <sysDescr>X86-windows2000</sysDescr>

  <sysUpTime>9 hours 
42 minutes</sysUpTime>

  <sysContact>zhangsan</sysContact>

  <sysName>computerZhang</sysName>

 </info>

 <info ip="192.168.1.3">

  <sysDescr>router</sysDescr>

  <sysUpTime>24 
hours</sysUpTime>

  <sysContact>ruijie</sysContact>

  <sysName>Router2</sysName>

 </info>

 <info ip="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 | 
9 hours 
42 minutes | 
zhangsan | computerZhang |

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

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

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

write into 
sys.xml...

建立数据库的sql文件:?

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

--

-- host: 
localhost Database: localhost

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

-- Server version 
10.1.21-MariaDB-5

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 
*/;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE 
*/;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, 
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, 
SQL_NOTES=0 */;

--

-- Table 
structure for table 
`info`

--

DROP TABLE IF 
EXISTS `info`;

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

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

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

--

-- Dumping data 
for table 
`info`

--

LOCK TABLES 
`info` WRITE;

/*!40000 ALTER 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');

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

UNLOCK 
TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE 
*/;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE 
*/;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES 
*/;

-- Dump completed 
on 2017-03-23 15: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

import sys

import xml.etree.ElementTree as ET

import MySQLdb

import argparse

from prettytable 
import PrettyTable 

'''

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

'''

def buildNewsXmlFile(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")

def xml_parser():

 data 
= {}

 data_list 
= []

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

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

 for info 
in root.findall('info'): 
#查找所有info元素

  for child 
in info: 
#对每个info元素遍历属性和子节点

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

   data[child.tag] = child.text

  # 
print data.values()

  data_list.append(data.values())

 # 
print data_list

 return data_list

def get_Mysql():

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

 cursor 
= conn.cursor()

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

 result 
= cursor.fetchall()

 if not result:

  print 'please insert 
the database first'

  sys.exit()

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

 for i in result:

  x.add_row(i)

 print x

 # 
print result

 return result

def set_Mysql(data):

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

 cursor 
= conn.cursor()

 for i in data:

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

def clear_Mysql():

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

 cursor 
= conn.cursor()

 cursor.execute('delete from 
info')

 conn.commit()

 conn.close()

def main():

 parser 
= argparse.ArgumentParser()

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

 arg 
= parser.parse_args()

 # 
print arg

 status 
= arg.status

 if status 
== 1:

  data 
= get_Mysql()

  buildNewsXmlFile(data)

 elif status 
== 2:

  data 
= xml_parser()

  set_Mysql(data)

 elif status 
== 0:

  clear_Mysql()

 else:

  print 'usage %s 
[0|1|2]'%sys.argv[0]

if __name__ 
== '__main__':

 main()

<上一篇：Python使用Flask框架获取当前查询参下一篇：旅行青蛙蜗牛出现时间规律一览 >

文章内容来源于网络，不代表本站立场，若侵犯到您的权益，可联系我们删除。（本站为非盈利性质网站）联系邮箱：9145908@qq.com

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

XML转换Xsl工具

XML转换Excel工具(XML2XLS)

XML转换为EXCLE工具

XML转换EXCEL格式工具

XML To JSON Converter(XML转JSON转换器)

Total XML Converter(万能XML转换器)

Java to Python Converter(Java到Python转换器)

Analyzer网站数据转换XML工具

Python

python

利用捍卫者

C++ to Python Converter(C++到Python转换器)