简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
首页
文章
问答
课程
专题
下载
手游
词典
最近更新

首页  >  文章  >  后端开发  >  python如何实现xml与数据库读取转换的示例代码分享

python如何实现xml与数据库读取转换的示例代码分享

黄舟
黄舟原创
2017-06-18 11:12:391718浏览

这篇文章主要给大家介绍了关于利用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 (&#39;192.168.1.1&#39;,&#39;X86-Windows2000&#39;,&#39;9 hours 42 minutes&#39;,&#39;zhangsan&#39;,&#39;computerZhang&#39;)
插入语句: insert into info values (&#39;192.168.1.3&#39;,&#39;router&#39;,&#39;24 hours&#39;,&#39;ruijie&#39;,&#39;Router2&#39;)
插入语句: insert into info values (&#39;192.168.2.1&#39;,&#39;router&#39;,&#39;89 hours&#39;,&#39;Cisco&#39;,&#39;Router3&#39;)
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=&#39;+00:00&#39; */;
/*!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=&#39;NO_AUTO_VALUE_ON_ZERO&#39; */;
/*!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 (&#39;192.168.1.1&#39;,&#39;X86-Windows2000&#39;,&#39;9 hours 42 minutes&#39;,&#39;zhangsan&#39;,&#39;computerZhang&#39;),(&#39;192.168.1.3&#39;,&#39;router&#39;,&#39;24 hours&#39;,&#39;ruijie&#39;,&#39;Router2&#39;),(&#39;192.168.2.1&#39;,&#39;router&#39;,&#39;89 hours&#39;,&#39;Cisco&#39;,&#39;Router3&#39;);
/*!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 
 
&#39;&#39;&#39;
一个xml作业，自己用python实现了从xml读取到数据库，和从数据库读取到xml的功能
&#39;&#39;&#39;
 
def buildNewsXmlFile(data):
 
 
 root = ET.Element(&#39;sys_info&#39;)#创建sys_info根元素
 # print help(ET)
 info = ET.SubElement(root, "info",attrib={&#39;ip&#39;:&#39;%s&#39;%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={&#39;ip&#39;:&#39;%s&#39;%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={&#39;ip&#39;:&#39;%s&#39;%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 &#39;write into sys.xml...&#39;
 tree = ET.ElementTree(root)
 tree.write("sys.xml")
 
 
def xml_parser():
 data = {}
 data_list = []
 tree = ET.parse(&#39;21.xml&#39;)
 root = tree.getroot()# 获取根元素
 for info in root.findall(&#39;info&#39;): #查找所有info元素
  for child in info: #对每个info元素遍历属性和子节点
   data [&#39;ip&#39;]= info.attrib[&#39;ip&#39;]
   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(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;sys_info2&#39;,charset=&#39;utf8&#39;)
 cursor = conn.cursor()
 cursor.execute(&#39;select * from info&#39;);
 result = cursor.fetchall()
 if not result:
  print &#39;please insert the database first&#39;
  sys.exit()
 
 
 x = PrettyTable([&#39;IP地址&#39;,&#39;sysDescr.0&#39;,&#39;sysUpTime.0&#39;,&#39;sysContact&#39;,&#39;sysName.0&#39;])
 for i in result:
  x.add_row(i)
 print x
 
 # print result
 return result
 
def set_Mysql(data):
 conn = MySQLdb.connect(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;sys_info2&#39;,charset=&#39;utf8&#39;)
 cursor = conn.cursor()
 for i in data:
  # print tuple(i)
  sysName,ip,sysUpTime,sysDescr,sysContact = tuple(i)
  sql = "insert into info values (&#39;%s&#39;,&#39;%s&#39;,&#39;%s&#39;,&#39;%s&#39;,&#39;%s&#39;)"%(ip,sysDescr,sysUpTime,sysContact,sysName)
  print &#39;插入语句:&#39;,sql
  try:
   cursor.execute(sql)
  except:
   print &#39;please clear the database&#39;
   sys.exit()
 print &#39;insert success!!!&#39;
 conn.commit()
 conn.close()
 
 
def clear_Mysql():
 conn = MySQLdb.connect(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;sys_info2&#39;,charset=&#39;utf8&#39;)
 cursor = conn.cursor()
 cursor.execute(&#39;delete from info&#39;)
 conn.commit()
 conn.close()
 
 
def main():
 parser = argparse.ArgumentParser()
 parser.add_argument(&#39;status&#39;,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 &#39;usage %s [0|1|2]&#39;%sys.argv[0]
 
if name == &#39;main&#39;:
 main()

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

总结

以上是python如何实现xml与数据库读取转换的示例代码分享的详细内容。更多信息请关注PHP中文网其他相关文章！

声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
上一篇：在Mac上如何切换Python多版本的详细介绍下一篇：如何使用python写个自动SSH登录远程服务器的小工具代码分享（推荐）

相关文章

查看更多