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

   <!DOCTYPE sys_info [  ]>   X86-Windows20009 hours 42 minuteszhangsancomputerZhang router24 hoursruijieRouter2 router89 hoursCiscoRouter3

 apt-get install python-MySQLdb

 root@lj /h/s/x/3# python 21.py -h usage: 21.py [-h] status positional arguments: status  0clar,1read,2insert

 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!!!

 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...

 -- 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 ([email protected]) # @Link : http://www.blogsir.com.cn # @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 <i style="color:transparent">来源gaodai$ma#com搞$$代**码网</i>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()