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

前言

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

xml文档

    <?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模块

    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

读取xml保存到数据库

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

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

总结

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