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
– Parfait, Commented Jan 8, 2017 at 0:49

Navi · Accepted Answer · 2017-01-09 05:18:24Z

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

Community · Accepted Answer · 2017-05-23 12:00:28Z

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, 2017 at 12:00

CommunityBot

11 silver badge

answered Jan 7, 2017 at 23:04

A. Scherbaum

8555 silver badges9 bronze badges

Collectives™ on Stack Overflow

Python script to generate XML file from PSQL database table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related