Decompressing a BLOB from SQLite with Python

Question

Using Python 2.7, I am storing web-pages in SQLite into a BLOB type column:

The compression is with zlib, and I'm inserting it as sqlite3.Binary type.

Without compression this works fine:

db_body = sqlite3.Binary(page_body)
dbc.execute("insert into table (content) values ?", db_body)

With compression also works fine:

db_body = sqlite3.Binary(zlib.compress(page_body))
dbc.execute("insert into table (content) values ?", db_body)

The problem comes when I try and retrieve the compressed data. I have tried the following:

dbc.execute("select content from table limit 1")
sql_result =  dbc.fetchone()

page_content = zlib.decompress(sql_result[0])

But the result (page_content) is a str type that's still compressed. There are no errors or exceptions. The content type of sql_result[0] is a Buffer, so the decompress function is changing the data type but not the content.

If I compress and recompress at the same time, without putting it through sqlite, the output is fine:

db_body = sqlite3.Binary(zlib.compress(page_body))
page_content = zlib.decompress(db_body)

So, how do I decompress data that I've inserted and then retrieved from SQLite?

Answer 1

Are you sure you need to use sqlite3.Binary?

db = sqlite3.connect('/tmp/mydb')
db.execute('CREATE TABLE tbl (bin_clmn BLOB)')
db.execute('INSERT INTO tbl  VALUES(?)', [buffer(zlib.compress(page_body))])
db.commit()
db.close()


db = sqlite3.connect('/tmp/mydb')
row = db.execute('SELECT * FROM tbl').fetchone()
page_body_decompressed = zlib.decompress(str(row[0]))
print page_body == page_body_decompressed