Python 2.7, sqlite3, ValueError: could not convert BLOB to buffer

Question

I am trying to save a BLOB created from an integer array (that is, a packed array of integers) in an SQLite DB. The script shown below gives the following traceback. As far as I can see from the Python 2.7 sqlite3 documentation, it should be possible to insert a buffer object into a table, where it is supposed to be saved as a BLOB. However, I am unable to make this work. (FWIW, isinstance(b,buffer) prints True if inserted into the script, so I'm indeed creating a buffer object.)

Any suggestions?

Thanks, -P.

Traceback (most recent call last):
  File "example.py", line 13, in <module>
    conn.execute( 'insert into foo values (?)', (b,) ) # <=== line 14
ValueError: could not convert BLOB to buffer

---

import sqlite3
import sys
import array

ar = array.array( 'I' )
ar.extend( [1,0,3,11,43] )
b = buffer( ar )

conn = sqlite3.connect( ':memory:' )
conn.execute( 'create table foo( bar BLOB )' )
conn.commit()

conn.execute( 'insert into foo values (?)', (b,) ) # <=== line 14
conn.commit()

Answer 1

Cristian Ciupitu's note about the bug is correct, but bytes(ar) will give you the __str__ representation instead of a serialized output. Therefore, use ar.tostring().

Use array.fromstring to unserialize the array again - you have to create an array object with the same type and then call .fromstring(...).