Parse XML to store in a database

Question

I'm working on a Python script to read xml data from a web server. I also want to store the data in an sqlite database, but I still have no idea how to parse the xml data prior to storing the data.

Here is the current code:

import xbmc
import xbmcgui
import xbmcaddon
import urllib2

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):
   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', ''))

   if os.path.exists(profilePath):
      profilePath = profilePath + 'source.db'
      con = lite.connect(profilePath)
      cur = con.cursor()
      cur.execute('CREATE TABLE IF NOT EXISTS data (channels, programme_title, programme_time, description, logo_url)')
   cur.close()

return data
cSetVisible(self,4201,True)

Here's the settings.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<settings>
    <category label="30101">
        <setting id="source" label="30101" type="labelenum" default="YouSee.tv"
                 values="YouSee.tv|XMLTV|ONTV.dk" />

        <setting id="youseetv.category" label="30102" type="labelenum" default="Danske"
                 values="Danske|Filmkanaler|Grundpakken|Mellempakken|Fuldpakken|Nordiske|Engelske|Tyske|Latinske|Slaviske|Tyrkiske|Kurdiske|Arabiske|Asiatiske"
                 visible="eq(-1,0)" />
        <setting id="program.background.enabled" label="30107" type="bool" default="true" visible="eq(-2,0)"/>
        <setting id="english.enabled" value="true"/>
        <setting id="french.enabled" value="false"/>
        <setting id="xmltv.file" label="30103" type="file" visible="eq(-3,1)" />
        <setting id="xmltv.logo.folder" label="30116" type="folder" visible="eq(-4,1)"/>
        <setting id="ontv.url" label="30117" type="text" visible="eq(-5,2)" default="http://ontv.dk/xmltv/c81e728d9d4c2f636f067f89cc14862c"/>
    </category>
</settings>

I'm reading the xml data from this website: http://ontv.dk/xmltv/c81e728d9d4c2f636f067f89cc14862c

I know how to read the xml data with response.read(), but I still have no idea how to parse the xml data to store in the database.

How I can parse the xml data and store it in the database?

Python comes with a number of XML-parsing modules [in the standard library](http://docs.python.org/2/library/xml.html). There are also [dozens of third-party alternatives](https://pypi.python.org/pypi?%3Aaction=search&term=XML&submit=search) like [`lxml`](http://lxml.de). StackOverflow cannot help with "library shopping", but you can skim through these, pick the one that looks friendliest to you, read its tutorials/docs, start parsing, and then, when you get stuck, post a more specific question. — abarnert, Feb 20 '14 at 00:07
See [What topics can I ask about here?](http://stackoverflow.com/help/on-topic) and other sections of the help for more information. — abarnert, Feb 20 '14 at 00:08
The XML parsing and the database storage are unrelated operations, unless you choose a datastore with native XML support (which sqlite isn't). You might as well ask "how do I run `ls` when my display is a HDTV?" -- just as your monitor doesn't change how you use `ls`, whether you're storing your extracted content in SQLite doesn't change how you parse it. — Charles Duffy, Feb 20 '14 at 00:20
@CharlesDuffy I know that my SQLite doesn't change because i did not know how to store it. I want to know how I can store it for xbmc and how i can output them in the xml when I really need it? — , Feb 20 '14 at 00:31
...so, there are two possibilities. One is that you don't want to parse the XML at all, but store it as literal character data. The disadvantage to this is that you'll get no support from your RDBMS for doing any kind of intelligent querying on it. The other is that you want to convert it into a native SQL database structure, and be able to convert the native structure back to XML -- and the answer to how someone does that is "with a lot of work". — Charles Duffy, Feb 20 '14 at 00:32
@CharlesDuffy Yeah that's what I really want to convert it into a native SQL database and then convert the native structure back to XML. Can you tell me how i can do this? I need it as i'm making my own tv guide addon. — , Feb 20 '14 at 00:40
There's no magic bullet to do that, and by its nature (given the difference between schema-free documents and tabular content) no way a third-party library can automate it in a generic fashion. As I said, it's work, and you have to do that work yourself. — Charles Duffy, Feb 20 '14 at 00:41

score 1 · Accepted Answer · answered Feb 20 '14 at 02:42

As other mentioned I also recommend lxml

If you want to look through the xml structure and make it in a very pythonic way look at
lxml objectify
or
untangle

With this libraries you should get the data out of the XML. Maybe keep in mind this is not the best and fastest approach for larger XML files.

Parse XML to store in a database

1 Answers1