使用Python和lxml从MySQL查询创建xml [英] Creating xml from MySQL query with Python and lxml

问题描述

我正在尝试使用Python和LXML从Mysql查询结果创建XML文件.这是我想要的格式.

I am trying to use Python and LXML to create an XML file from a Mysql query result. Here is the format I want.

     <DATA>
         <ROW>
             <FIELD1>content</FIELD1>
             <FIELD2>content</FIELD2>
         </ROW>
    </DATA>

由于某种原因，代码的格式不正确，XML无法验证.这是代码

For some reason the code isn't formatting right and the XML will not validate. Here is that code

from lxml import etree
from lxml.etree import tostring
from lxml.builder import E
import MySQLdb


try:
       conn = MySQLdb.connect(host = 'host',user = 'user',passwd = 'pass',db = 'db')
       cursor = conn.cursor()
except:
       sys.exit(1)

cursor.execute("SELECT * FROM db.table")
columns = [i[0] for i in cursor.description]
allRows = cursor.fetchall()
xmlFile = open("mysqlxml.xml","w")
xmlFile.write('<DATA>')
for rows in allRows:
       xmlFile.write('<ROW>')
       columnNumber = 0
       for column in columns:
              data = rows[columnNumber]
              if data == None:
                     data = ''
              xmlFile.write('<%s>%s</%s>' % (column,data,column))
              columnNumber += 1
       xmlFile.write('</ROW>')
xmlFile.write('</DATA>')
xmlFile.close()

推荐答案

这是一个如何使用lxml构建xml的小例子.

Here's a little example of how you can build xml using lxml.

为元素创建创建辅助函数非常有用，这是一个简单的函数.我已经创建了一个虚拟的游标对象用于演示.

It's useful to create a helper function for element creation, here's a simple one. I've created a dummy cursor object for demo purposes.

from lxml import etree
from lxml.builder import E as buildE

class DummyCursor(object):
  def __init__(self,fields,rows=5):
    self.description = [[f] for f in fields]
    self.data = [ ["%s%02d" % (f,i) for f in fields] for i in range(rows) ]
  def fetchall(self):
    return self.data

def E(tag,parent=None,content=None):
  """Simple E helper"""
  element = buildE(tag)
  if content is not None:
    element.text = unicode(content)
  if parent is not None:
    parent.append(element)
  return element

def fetchXML(cursor):
  fields = [x[0] for x in cursor.description ]
  doc = E('data')
  for record in cursor.fetchall():
    r = E('row',parent=doc)
    for (k,v) in zip(fields,record):
      E(k,content=v,parent=r)
  return doc

doc = fetchXML(DummyCursor(['name','description']))

print etree.tostring(doc,pretty_print=True)

收益:

<data>
  <row>
    <name>name00</name>
    <description>description00</description>
  </row>
  <row>
    <name>name01</name>
    <description>description01</description>
  </row>
  <row>
    <name>name02</name>
    <description>description02</description>
  </row>
  <row>
    <name>name03</name>
    <description>description03</description>
  </row>
  <row>
    <name>name04</name>
    <description>description04</description>
  </row>
</data>

这篇关于使用Python和lxml从MySQL查询创建xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！