Python script to generate XML file from PSQL database table

Question

I am trying to generate XML file from PostgreSQL database table. I was able to generate using below code but it is not formatted and displaying in single line. While getting row from rows it displays complete file in single row. How can I fix this problem?

def tableToXML(message):
    conn = None
    try:
        conn=psycopg2.connect("dbname='DB' user='user' password='i123456'")
    except:
        print "I am unable to connect to the database."

    cur = conn.cursor()
    try:
        cur.execute("SELECT table_to_xml('usapp_sipconf', true, false, '')")
    except:
        print "I can't SELECT from sipconf"

    rows = cur.fetchall()
    with open('sipconf.xml', 'w') as f:
        for row in rows:
            print row
            f.write("%s" % (str(row)))
    if conn:
        conn.close()
    return True

There's no problem. XML is still valid and well-formed but just doesn't have line breaks or indentation for human readability. Look for Python modules to pretty print output. — Parfait, Jan 08 '17 at 00:49

score 2 · Accepted Answer · answered Jan 09 '17 at 05:18

I have generated XML file from PostgreSQL database table by making thses modification to pre-existing code.

def tableToXML(tableName):
    conn       = None
    columnList = []
    fileName   = 'usappxml/'+tableName+'.xml'
    message    = '<'+tableName+'>\n'

    try:
        conn = psycopg2.connect("dbname='db' user='dbuser' password='123456'")
    except:
        print " ***  Can't able to connect database. *** "
        return False

    outfile = file(fileName, 'w')
    cursor  = conn.cursor()

    cursor.execute("SELECT column_name from information_schema.columns where table_name = '%s'" % tableName)
    columns = cursor.fetchall()

    for column in columns:
        columnList.append(column[0])

    cursor.execute("select * from  %s" % tableName)
    rows = cursor.fetchall()

    outfile.write('<?xml version="1.0" ?>\n')
    outfile.write(message)
    for row in rows:
        outfile.write('  <row>\n')
        for i in range(len(columnList)):
            outfile.write('    <%s>%s</%s>\n' % (columnList[i], row[i], columnList[i]))
             outfile.write('    <%s>%s</%s>\n' % (columnList[i], row[i], columnList[i]))
        outfile.write('  </row>\n')
     outfile.write(message)

    outfile.close()
    return True

score 1 · Answer 2 · edited May 23 '17 at 12:00

1

This question has been answered multiple times:

Pretty printing XML in Python

How do I get Python's ElementTree to pretty print to an XML file?

Python pretty XML printer for XML string

edited May 23 '17 at 12:00

Community

1
1

answered Jan 07 '17 at 23:04

A. Scherbaum

809
4
8

Python script to generate XML file from PSQL database table

2 Answers2