python xml to string, insert into postgres

Question

Here is my code:

#!/usr/bin/python

import psycopg2
import sys
from lxml import etree
def main():

    #Define our connection string
    conn_string = ("host=host dbname=lal user=user password=pass")


    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object
    cursor = conn.cursor()
    print "Connected!\n"

    # Open file


    parser = etree.parse("XML/epg.xml")
    for row in parser:
        print row

        postgres = ('INSERT INTO epg_live (channel_id, program, start, duration) VALUES (%s, %s, %s, %s)', (row, row, row, row))
        cursor.execute(parser,postgres)
        cursor.commit()
        print "Gotovo!"

if __name__ == "__main__":
    main()

Can you help me with parsing XML file to string and insert into table in posgresql. When I run script i get errors like:

File "./xml.py", line 32, in <module>
    main()
  File "./xml.py", line 22, in main
    parser = etree.parse("XML/epg.xml")
  File "lxml.etree.pyx", line 2953, in lxml.etree.parse (src/lxml/lxml.etree.c:56204)
  File "parser.pxi", line 1533, in lxml.etree._parseDocument (src/lxml/lxml.etree.c:82287)
  File "parser.pxi", line 1562, in lxml.etree._parseDocumentFromURL (src/lxml/lxml.etree.c:82580)
  File "parser.pxi", line 1462, in lxml.etree._parseDocFromFile (src/lxml/lxml.etree.c:81619)
  File "parser.pxi", line 1002, in lxml.etree._BaseParser._parseDocFromFile (src/lxml/lxml.etree.c:78528)
  File "parser.pxi", line 569, in lxml.etree._ParserContext._handleParseResultDoc (src/lxml/lxml.etree.c:74472)
  File "parser.pxi", line 650, in lxml.etree._handleParseResult (src/lxml/lxml.etree.c:75363)
  File "parser.pxi", line 590, in lxml.etree._raiseParseError (src/lxml/lxml.etree.c:74696)
lxml.etree.XMLSyntaxError: Opening and ending tag mismatch: epg line 2 and item, line 26, column 10

My XML is fine it looks like:

<item><program>        Program 3   
</program><start>            Start   20130918 15:00:00 
</start><duration>            Duration   04:30:00 
</duration><title>                  Title Nujna seja Odbora za finance in monetarno politiko   
</title></item>

Can you help me with some solution for python, thx guys for reading this post.

Answer 1

You can read xml into parameters and send to PostgreSQL like this:

root = etree.parse("XML/epg.xml")
for i in root.findall("item"):
    p = [i.find(n).text for n in ("program", "start", "duration")]
    # now you get list with values of parameters

    postgres = ('INSERT INTO epg_live (program, start, duration) VALUES (%s, %s, %s)', p)
    cursor.execute(parser,postgres)
    cursor.commit()

don't know where to get channel_id parameter