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>

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 MySQLdbtry: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 = 0for column in columns:data = rows[columnNumber]if data == None:data = ''xmlFile.write('<%s>%s</%s>' % (column,data,column))columnNumber += 1xmlFile.write('</ROW>')
xmlFile.write('</DATA>')
xmlFile.close()

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 buildEclass 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.datadef 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 elementdef 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 docdoc = fetchXML(DummyCursor(['name','description']))print etree.tostring(doc,pretty_print=True)

Yields:

<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>