How do I correctly map a Python "list of lists" into an SQL table (built with SQLite3)?

Question

I have data coming out of an XML parser that contains instrument readings. Samples of the data are reproduced below:

cimReading = [['2012-08-15 10:05:13.101485', ['0x46'], ['0x32'], ['1.234'], ['5.678'], ['9.123'],
 ['4.567'], ['0x98'], ['0x97']], 
['2012-08-15 10:05:13.101979', ['0x47'], ['0x33'], ['8.901'], ['2.345'], ['6.789'], 
['0.123'], ['0x96'], ['0x95']]]

I'm trying to loop over the list items and put them away in a table, cim76_dmt that was previously defined in Python with sqlite3. Here is the code I'm using to try to do this:

for devList in cimReading:
    print devList
    cim76TabPopulate.execute('INSERT INTO cim76_dmt VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)', 
            devList)
BenchDB.commit()
cim76TabPopulate.close()

Here is the error message I receive upon execution:

['2012-08-15 10:40:21.110140', ['0x46'], ['0x32'], ['1.234'], ['5.678'], ['9.123'], ['4.567'], ['0x98'], ['0x97']]
EXCEPTION...
(<class 'sqlite3.InterfaceError'>, InterfaceError('Error binding parameter 1 - probably unsupported type.',), <traceback object at 0x1007a7518>)

So, I'm having a problem mapping a list item to a SQL table field. I think there should be a way to do this, but another possibility may be to tweak the XML parsing so I generate a single list of multiple strings for each record. I'd welcome any suggestions or advice on this.

Thanks in advance!

JosefAssad · Accepted Answer · 2012-08-15 17:08:08Z

2

Your problem is that all the parameters after the first one are lists, not strings or integers. The part win the error message that is complaining about unsupported type is complaining about the data element ['0x46'].

You need to massage your data a little bit more; you want to unpack ['0x46'] to '0x46'. This needs to be done to the other data elements in single length lists in your example data.

edited Aug 15, 2012 at 17:08

answered Aug 15, 2012 at 17:00

JosefAssad

4,12730 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Red Spanner Over a year ago

Hi Josef, Thanks for the reply. Yes, I thought the problem was tied to the data presentation as a 'list of lists'. I was looking for advice about whether I should re-write the XML parse code to deliver strings, or if there was a fast way to convert the list items to a sequence of strings. Which approach would you recommend?

JosefAssad Over a year ago

Hm. Well, let the structure of the data suit its nature. If each element can only consist of 1 element then get rid of the list. So if your data is guaranteed to always only look like [['2012-08-15 10:05:13.101485', ['0x46'] ... and never like [['2012-08-15 10:05:13.101485', ['0x46', 0x45] .... If you use the facilities in the way they were intended, then Everything Works Better(tm) so consider whether you might not also want an Integer field for values like 0x46 instead of the string representation.

Red Spanner Over a year ago

It is a simple list, and I planned to leave the data in string format for storage in the SQL table, converting it only when I extract it to do calculations, etc. The numbers are coming "raw" from the instrument server in hex and floating point, and I add the timestamp when it is acquired via a function call. I think we both know the answer -- I need to go back to the XML parser and extract the data in this fashion.

Collectives™ on Stack Overflow

How do I correctly map a Python "list of lists" into an SQL table (built with SQLite3)?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related