How to fetch xml data to write in a database

Question

I'm working on a Python script to read the xml data from a server and storing the xml data in the database. When I create the database, I can see that it will write the list of xml in a database without fetching for each data and it did not create the database table which it looks like this: http://imageshack.com/a/img401/4210/ofa5.jpg

The xml i got from the server link: http://ontv.dk/xmltv/c81e728d9d4c2f636f067f89cc14862c

Here is the current code:

import xbmc
import xbmcgui
import xbmcaddon
import os
import urllib
import urllib2
import StringIO
import sqlite3
import datetime
import time
from xml.etree import ElementTree

ADDON = xbmcaddon.Addon(id = 'script.myaddon')

class MyScript(xbmcgui.WindowXML):

def __new__(cls):
         return super(MyScript, cls).__new__(cls, 'script-menu.xml', ADDON.getAddonInfo('path'))

def onInit(self):

   #DOWNLOAD THE XML SOURCE HERE
   url = ADDON.getSetting('ontv.url')
   req = urllib2.Request(url)
   response = urllib2.urlopen(req)
   data = response.read()
   response.close()
   profilePath = xbmc.translatePath(os.path.join('special://userdata/addon_data/script.tvguide', ''))
   io = StringIO.StringIO(req)
   context = ElementTree.iterparse(io)


   if os.path.exists(profilePath):
      profilePath = profilePath + 'source.db'
      con = sqlite3.connect(profilePath)
      cur = con.cursor()
      cur.execute('CREATE TABLE programs(channel TEXT, title TEXT, start_date TIMESTAMP, end_date TIMESTAMP, description TEXT, image_large TEXT, image_small TEXT, source TEXT, updates_id INTEGER, FOREIGN KEY(channel, source) REFERENCES channels(id, source) ON DELETE CASCADE, FOREIGN KEY(updates_id) REFERENCES updates(id) ON DELETE CASCADE)')
      cur.close()
      fc = open(profilePath, 'w')
      fc.write(data)
      fc.close

I want to fetch for each xml data to write in a database after when I creating the database table. I want to know how do you write the source for xbmc to fetch for each xml data to store in a database after when I create the database table?

There's no commit statement (`con.commit()`) after executing the create table statement, so your changes won't be persisted. — Talvalin, Feb 25 '14 at 17:15
@Talvalin I don't have the commit statement `con.commit()` so what I need to do? and what about how I can fetch the xml data? — , Feb 25 '14 at 17:26
Have a read of the ElementTree documentation: http://docs.python.org/2/library/xml.etree.elementtree.html and try to see if you can get the data you need from the XML file and then try writing that to your table. — Talvalin, Feb 25 '14 at 17:30
I have had a look but I have no idea how I can get the data I need from XML file using ElementTree compare to my code, can you help? — , Feb 25 '14 at 17:39

score 0 · Answer 1 · answered Feb 25 '14 at 18:45

I haven't got the xbmc module installed, so this code is based on loading the XML from a file and then parsing through it.

I couldn't see any references to image_large, image_small or updates_id in the XML, so I left those commented out. There is probably a better way of doing this, but this should get you started and hopefully from here you should be able to work out how to loop through the list to write each programme to your database table.

import xml.etree.ElementTree as ET

tree = ET.parse('epg.xml')
root = tree.getroot()
programmes = []

for item in root.findall('programme'):
    programme = {}
    programme["channel"] = item.attrib['channel']
    programme["title"] = item.find('title').text
    programme["start_date"] = item.attrib['start']
    programme["end_date"] = item.attrib['stop']
    programme["description"] = item.find('desc').text
    #programme["image_large"] = 
    #programme["image_small"] = 
    programme["source"] = item.find('icon').attrib['src']
    #programme["updates_id"] = 
    programmes.append(programme)

How to fetch xml data to write in a database

1 Answers1