利用python实现xml与数据库读取转换的方法
作者:daisy 发布时间:2024-01-23 06:27:51
标签: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文档的显示,文件过多,就不贴了
来源:http://www.blogsir.com.cn/safe/354.html
0
投稿
猜你喜欢
- 前言段(segment)是一种在数据库中消耗物理存储空间的任何实体(一个段可能存在于多个数据文件中,因为物理的数据文件是组成逻辑表空间的基本
- asp之家注:为什么要防止访客频繁刷新页面呢?也许你会说他想刷新就让他刷新吧,没什么关系,而且还增加了网页的PV,呵呵。但是有的页面我们可能
- 代码如下:<% Randomize Do While Len(pass)<12 
- 可以任意转载,但转载时必须标明原作者charlee、原始链接http://tech.idv2.com/2008/11/03/python-m
- Python 使用tf-idf算法计算文档关键字权重,并生成词云1. 根据tf-idf计算一个文档的关键词或者短语:代码如下:注意需要安装p
- pygame实现代码雨动画如视频所示 利用pygame库实现了一个代码呈雨状下落的视觉效果部分代码如下import sysimport ra
- 前几天在一本书上看到一篇可以利用字典破解zip文件密码的文章,觉得比较有意思于是研究了一番,在这里分享一下原理主要是利用python里自带的
- pandas中的agg函数python中的agg函数通常用于调用groupby()函数之后,对数据做一些聚合操作,包括sum,min,max
- 方法一:#导入math包import math#定义点的函数class Point: def __init__(self,x=0
- 比如,若要将某个String对象s从gbk内码转换为UTF-8,可以如下操作 s.decode('gbk').encode(
- 目录urllib库urllib.request模块Request对象1 . 请求头添加2. 操作cookie3. 设置代理urllib.pa
- 在具体数据的选取上,我爬取的是各省份降水量实时数据话不多说,开始实操正文 1.爬取数据使用python爬虫,爬取中国天气网各省份2
- 自动签到的python脚本源码新建一个python文件,checkin.py,保存到电脑上某个位置,我这里保存到的是E:\pyproject
- 1.语法及用法(1)语法:str.endswith(suffix[,start][,end])str:字符串,待判断字符串suffix:后缀
- Beautiful Soup 库一般被称为bs4库,支持Python3,是我们写爬虫非常好的第三方库。因用起来十分的简便流畅。所以也被人叫做
- 在填写表单的时候为了让用户有更好的体验,有时需要根据用户的输入出现提示共用户选择,我这个就是根据输入的内容,从数据库取出相关内容以供选择,这
- kNN算法是k-近邻算法的简称,主要用来进行分类实践,主要思路如下:1.存在一个训练数据集,每个数据都有对应的标签,也就是说,我们知道样本集
- 因为很多时候要涉及到url的编码和解码工作,所以自己制作了一个类,废话不多说 码上见!# coding:utf-8import urllib
- 问题:如何把具有相同字段的记录删除,只留下一条。 例如:表test里有id,name字段,如果有name相同的记录只留下一条,
- pandas in 和 not in 的用法经常在处理数据中从一个总数据中清洗出数据, 但是有时候需要把没有处理的数据也统计出来.这时候就需