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?

how do you obtain squl_result ? Please provide a Minimal, Complete, and Verifiable example : stackoverflow.com/help/mcve — Setop
– Setop, Commented Jan 1, 2017 at 20:49

Dekel · Accepted Answer · 2017-01-01 21:04:19Z

3

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

edited Jan 1, 2017 at 21:04

answered Jan 1, 2017 at 20:54

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

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

1 Comment

GIS-Jonathan Over a year ago

Thanks, that seems to work. Seems like odd behaviour though; the sqlite3.Binary type is the one I read somewhere as being designed for storing binary data.

Collectives™ on Stack Overflow

Decompressing a BLOB from SQLite with Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related