Python - Reading BLOB type from SQLite3 DB

Question

This is a follow on from: Python - Converting Hex to INT/CHAR

I now have a working solution for converting the stored hex value of an IP from an sqlite3 db into a readable and usable format. However up until now I have been testing by copying and pasting the values directly from a sqlite3 db viewer.

I have been trying to poll the db with a script to lookup that information however I have discovered that it apparantly stores blob data into a buffer which isn't instantly human readable.

For example, the IP 192.168.0.1 is stored as blob type under the field ip as X'C0A80001'

Since I worked from a copied and pasted example into my code I have constructed code to strip the X' and ' from the hex to be able to then convert it. What I cannot get is that value from the database (X'C0A80001' <- this value that I can view with a db manager). Searching I found http://eli.thegreenplace.net/2009/05/29/storing-blobs-in-a-sqlite-db-with-pythonpysqlite/ which showed an example of reading blobs to and from a sqlite db but their methods didn't work. They displayed the error;

print row[0], str(row[1]).encode('hex')
IndexError: tuple index out of range

I am fairly new to Python so apologies if I am missing something basic but any pointers or code examples anyone has to help me get my head around this would be appreciated.

EDIT: Doh, didn't paste the code from the above example;

c = conn.cursor()
c.execute('select ip from item where name = ' + "\'" + host + "\'")
for row in c:
        print row[0], str(row[1]).encode('hex')

It may be my understanding thats really off here as to why I can't read it back the way I want to

UPDATE: Using the below answer I modified my code to;

rows = c.execute('select ip from item where name= ' + "\"" + host + "\"") 
   for row in rows:
       print str(row[0]).encode("hex")

Solution

After checking the link, it seems the most likely explanation is an empty row. On line 29 you set up a for loop to go over the results of a query. In python this means you have a list: [item,item2,item3] and each time you go through the loop, your row variable points to the next item.

Within the loop, you are checking the contents of the current item. Each of these items is a tuple which suppposedly has at least two entries. But if any of those items don't have an entry for row[0] or row[1] then you will get an index out of range exception.

You haven't posted enough code to make determining why this occurred feasible, but to get the code running I'd suggest this:

for row in cur.execute("select * from frames"):
    try:
        print row[0], str(row[1]).encode('hex') 
    except IndexError, e:
        print "IndexError: {0}".format(e)

That will continue across your entire query result, even if some of them are bad.

Edit: I just saw your update, and your problem is that c does not hold the contents of your query. c.execute('select ip from item where name = ' + "\'" + host + "\'") returns a list, which you ignore.

The original example works because you get the list in the for loop, and so it's an anonymous variable only within the loop context. To fix your code:

c = conn.cursor()
rows = c.execute('select ip from item where name = ' + "\'" + host + "\'")
for row in rows:
        print row[0], str(row[1]).encode('hex')

Comments

[Draineh]

Thank you! I was being very simple. So the IP field is the third field in the table and so what I needed to do was access row[2] using your modification of getting the list. I will edit my above question to include my final working solution for completeness. Thank you again.

[Spencer Rathbun]

@Draineh no problem. I'd suggest going through the python tutorial or at least skimming your way up to chapter 9. That will explain various python constructs, such as lists and looping. It doesn't take too long, and you'll save yourself a lot of headache.

[Draineh]

Just had "Python - Essential Reference" arrive through the post so hopefully I should be able to get some decent learning done.

[glglgl]

c.execute('select ip from item where name = ' + "\'" + host + "\'") is bad style. Better would be c.execute('select ip from item where name = ?', host) where I don't know if sqlite wants a ? or a %s as MySQL wants.

[Draineh]

@glglgl Thanks, I had read somewhere that it was good practice to use ? for sqlite interaction in Python but through my debugging I'd just been trying to get the basics working - I will change it over