python提取xml属性导入Mysql
xml文档来自ganglia-gmond端telnet localhost 8649产生出来的文档,由于ganglia每隔一段时间就更新数据,为了永久保存数据到MySQL中,就用python写了最开始的代码,后续的数据库操作应该是update 某些属性的值。
1.使用MySQL建表
mysql>create database ganglia;
mysql>show databases;
mysql>use ganglia;
mysql> create table class (NAME char(20), VAL char(40), TYPE char(10), UNITS char(15), TN int, TMAX int, DMAX int, SLOPE char(10));
mysql> describe class2;
mysql>insert into class values('load_one','0.00','float',' ',7043,70,0,'both');
mysql> select * from class;
2.将数据保存至数据库
import xml.dom.minidom as minidom import MySQLdb dom = minidom.parse("181.xml") root = dom.getElementsByTagName("GANGLIA_XML") conn=MySQLdb.connect(host='localhost',user='root',passwd='123',port=3306) cur=conn.cursor() conn.select_db('ganglia') for node in root: gangliaxml = node.getElementsByTagName("CLUSTER") for cluster in gangliaxml: host = cluster.getElementsByTagName("HOST") for metric in host: #print(metric.getAttribute("NAME")) for metrics in metric.getElementsByTagName("METRIC"): value=[metrics.attributes["NAME"].value, metrics.attributes["VAL"].value, metrics.attributes["TYPE"].value, metrics.attributes["UNITS"].value, metrics.attributes["TN"].value, metrics.attributes["TMAX"].value, metrics.attributes["DMAX"].value, metrics.attributes["SLOPE"].value,] if metric.getAttribute("NAME") == '172.19.0.181': cur.execute('insert into class values(%s,%s,%s,%s,%s,%s,%s,%s)',value) conn.commit() elif metric.getAttribute("NAME") == '172.19.0.186': cur.execute('insert into class2 values(%s,%s,%s,%s,%s,%s,%s,%s)',value) conn.commit() else: cur.execute('insert into class3 values(%s,%s,%s,%s,%s,%s,%s,%s)',value) conn.commit() cur.close() conn.close() print 'Complete!'
xml文档是:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?> <!DOCTYPE GANGLIA_XML [ <!ELEMENT GANGLIA_XML (GRID|CLUSTER|HOST)*> <!ATTLIST GANGLIA_XML VERSION CDATA #REQUIRED> <!ATTLIST GANGLIA_XML SOURCE CDATA #REQUIRED> <!ELEMENT GRID (CLUSTER | GRID | HOSTS | METRICS)*> <!ATTLIST GRID NAME CDATA #REQUIRED> <!ATTLIST GRID AUTHORITY CDATA #REQUIRED> <!ATTLIST GRID LOCALTIME CDATA #IMPLIED> <!ELEMENT CLUSTER (HOST | HOSTS | METRICS)*> <!ATTLIST CLUSTER NAME CDATA #REQUIRED> <!ATTLIST CLUSTER OWNER CDATA #IMPLIED> <!ATTLIST CLUSTER LATLONG CDATA #IMPLIED> <!ATTLIST CLUSTER URL CDATA #IMPLIED> <!ATTLIST CLUSTER LOCALTIME CDATA #REQUIRED> <!ELEMENT HOST (METRIC)*> <!ATTLIST HOST NAME CDATA #REQUIRED> <!ATTLIST HOST IP CDATA #REQUIRED> <!ATTLIST HOST LOCATION CDATA #IMPLIED> <!ATTLIST HOST TAGS CDATA #IMPLIED> <!ATTLIST HOST REPORTED CDATA #REQUIRED> <!ATTLIST HOST TN CDATA #IMPLIED> <!ATTLIST HOST TMAX CDATA #IMPLIED> <!ATTLIST HOST DMAX CDATA #IMPLIED> <!ATTLIST HOST GMOND_STARTED CDATA #IMPLIED> <!ELEMENT METRIC (EXTRA_DATA*)> <!ATTLIST METRIC NAME CDATA #REQUIRED> <!ATTLIST METRIC VAL CDATA #REQUIRED> <!ATTLIST METRIC TYPE (string | int8 | uint8 | int16 | uint16 | int32 | uint32 | float | double | timestamp) #REQUIRED> <!ATTLIST METRIC UNITS CDATA #IMPLIED> <!ATTLIST METRIC TN CDATA #IMPLIED> <!ATTLIST METRIC TMAX CDATA #IMPLIED> <!ATTLIST METRIC DMAX CDATA #IMPLIED> <!ATTLIST METRIC SLOPE (zero | positive | negative | both | unspecified) #IMPLIED> <!ATTLIST METRIC SOURCE (gmond) 'gmond'> <!ELEMENT EXTRA_DATA (EXTRA_ELEMENT*)> <!ELEMENT EXTRA_ELEMENT EMPTY> <!ATTLIST EXTRA_ELEMENT NAME CDATA #REQUIRED> <!ATTLIST EXTRA_ELEMENT VAL CDATA #REQUIRED> <!ELEMENT HOSTS EMPTY> <!ATTLIST HOSTS UP CDATA #REQUIRED> <!ATTLIST HOSTS DOWN CDATA #REQUIRED> <!ATTLIST HOSTS SOURCE (gmond | gmetad) #REQUIRED> <!ELEMENT METRICS (EXTRA_DATA*)> <!ATTLIST METRICS NAME CDATA #REQUIRED> <!ATTLIST METRICS SUM CDATA #REQUIRED> <!ATTLIST METRICS NUM CDATA #REQUIRED> <!ATTLIST METRICS TYPE (string | int8 | uint8 | int16 | uint16 | int32 | uint32 | float | double | timestamp) #REQUIRED> <!ATTLIST METRICS UNITS CDATA #IMPLIED> <!ATTLIST METRICS SLOPE (zero | positive | negative | both | unspecified) #IMPLIED> <!ATTLIST METRICS SOURCE (gmond) 'gmond'> ]> <GANGLIA_XML VERSION="3.6.1" SOURCE="gmond"> <CLUSTER NAME="sg620g" LOCALTIME="1432625962" OWNER="unspecified" LATLONG="unspecified" URL="unspecified"> <HOST NAME="172.19.0.181" IP="172.19.0.181" TAGS="" REPORTED="1432625957" TN="4" TMAX="20" DMAX="0" LOCATION="unspecified" GMOND_STARTED="1432609944"> <METRIC NAME="load_one" VAL="0.01" TYPE="float" UNITS=" " TN="33" TMAX="70" DMAX="0" SLOPE="both"> <EXTRA_DATA> <EXTRA_ELEMENT NAME="GROUP" VAL="load"/> <EXTRA_ELEMENT NAME="DESC" VAL="One minute load average"/> <EXTRA_ELEMENT NAME="TITLE" VAL="One Minute Load Average"/> </EXTRA_DATA> </METRIC> ............... </HOST> <HOST NAME="172.19.0.186" IP="172.19.0.186" TAGS="" REPORTED="1432625960" TN="2" TMAX="20" DMAX="0" LOCATION="unspecified" GMOND_STARTED="1432366857"> <METRIC NAME="load_one" VAL="0.53" TYPE="float" UNITS=" " TN="33" TMAX="70" DMAX="0" SLOPE="both"> <EXTRA_DATA> <EXTRA_ELEMENT NAME="GROUP" VAL="load"/> <EXTRA_ELEMENT NAME="DESC" VAL="One minute load average"/> <EXTRA_ELEMENT NAME="TITLE" VAL="One Minute Load Average"/> </EXTRA_DATA> </METRIC> ................... </HOST> <HOST NAME="172.19.0.177" IP="172.19.0.177" TAGS="" REPORTED="1432625960" TN="2" TMAX="20" DMAX="0" LOCATION="unspecified" GMOND_STARTED="1432395875"> <METRIC NAME="load_one" VAL="0.39" TYPE="float" UNITS=" " TN="33" TMAX="70" DMAX="0" SLOPE="both"> <EXTRA_DATA> <EXTRA_ELEMENT NAME="GROUP" VAL="load"/> <EXTRA_ELEMENT NAME="DESC" VAL="One minute load average"/> <EXTRA_ELEMENT NAME="TITLE" VAL="One Minute Load Average"/> </EXTRA_DATA> </METRIC> ......................... </HOST> </CLUSTER> </GANGLIA_XML>