CODEWITHSUNDEEP
pythonxmlpostgresql
Navi
Navi

Reputation: 41

Python script to generate XML file from PSQL database table

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

Upvotes: 1

Views: 1294

Answers (2)

Navi
Navi

Reputation: 41

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

Upvotes: 2

A. Scherbaum
A. Scherbaum

Reputation: 855

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

Upvotes: 1

Related Questions