Return sql query in xml format in python

Question

When I first started working in the company that I am currently working on, I created a Java application that launched batches of jasper reports. To determine which parameters to use for each report in the report set, I run a sql query (on sqlserver). I wrote an application to take an XML file with a set of parameters for each report that will be generated in the set. so my process was essentially three steps:

• run the sql query and return the results in XML format (using "to use XML automatically")
• run the results of the sql query using the XSLT transform so that the xml is formatted so that it is friendly with the java application that I wrote.
• run java application with this final xml file

As you can imagine, what I would like to do is follow these steps in python, but I'm not quite sure how to get started. I know how to run a SQL query in Python. I see a lot of documentation on how to write my own XML document using Python. I even see documentation for xsl transforms in python.

The big question is how to get sql query results in XML via python. Any and all pointers would be very valuable. Thanks, _Ramy

Answer 1

, sql, "FOR XML AUTO", (xml). .

pyodbc:

cursor.execute("select user_name from users where user_id=? for xml auto", userid)
xml = cursor.fetchone()
if xml:
    # do your xsl transformation and other processing here...

Edit

( ) , .

, :

cursor.execute("select user_name from users where user_id=? for xml auto", userid)
rows = cursor.fetchall()
xml = ''.join(row[0] for row in rows)