Ranga Sarin Ranga Sarin - 1 year ago 114
Python Question

python parse XML with multiple elements and insert into sqlite

I'm very new to python so please excuse me.. I have parsed an XML file and am inserting into sqlite just fine however I now need to get text value from multiple elements with the same name(channel-category) and insert into one sql column like

somecategory1, somecategory2

here is what I'm doing right now

elif elem.tag == "channel":
cid = elem.get("id").replace("'", "")
title = elem.findtext("display-name")
chncategory = elem.findtext("channel-category")
result = Channel(cid, title, chncategory, logo, streamUrl, visible)

I then call this

c.execute('INSERT OR IGNORE INTO channels(id, title, chncategory, logo, stream_url, visible, weight, source) VALUES(?, ?, ?, ?, ?, ?, (CASE ? WHEN -1 THEN (SELECT COALESCE(MAX(weight)+1, 0) FROM channels WHERE source=?) ELSE ? END), ?)',
[channel.id, channel.title, channel.chncategory, channel.logo, channel.streamUrl, channel.visible, channel.weight,
self.source.KEY, channel.weight, self.source.KEY])

which works fine.. however it only grabs the FIRST channel-category element and I need to grab them all and add into that column so it displays like

somecategory1, somecategory2, etc

my xml looks like this

<tv info="blahblah">
<channel id="channel1">
<display-name lang="en">channel1</display-name>
<icon src="somewhere.png" />

How would I make this work so instead of just grabbing the first value and putting it into the column, have it grab them ALL and insert to that one column?

Answer Source

How about, instead of

chncategory = elem.findtext("channel-category")

You use:

In [5]: chncategory = ', '.join(map(lambda x: x.text, elem.findall("channel-category")))

In [6]: chncategory
Out[6]: 'somecategory1, somecategory2'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download