Take the 2-minute tour ×

Using SQLite3 in Python, I am trying to store a compressed version of a snippet of UTF-8 HTML code.

Code looks like this:

c = connection.cursor()
c.execute('create table blah (cid integer primary key,html blob)')
c.execute('insert or ignore into blah values (?, ?)',(cid, zlib.compress(html)))

At which point at get the error:


If I use 'text' rather than 'blob' and don't compress the HTML snippet, it works all fine (db is to large though). When I use 'blob' and compress via Python zlib library, I get the above error message. I looked around but couldn't find a simple answer for this one.

share|improve this question
add comment

3 Answers

If you want to use 8-bit strings instead of unicode string in sqlite3, set approptiate text_factory for sqlite connection:

connection = sqlite3.connect(...)
connection.text_factory = str
share|improve this answer
add comment

Found the solution, I should have spent just a little more time searching.

Solution is to 'cast' the value as a Python 'buffer', like so:

c.execute('insert or ignore into blah values (?, ?)',(cid, buffer(zlib.compress(html))))

Hopefully this will help somebody else.

share|improve this answer
Could you explain why this works? –  Moshe Apr 8 '13 at 14:58
When I did this, my database was full of base36 text, which would make the database larger than storing the blob directly. –  Brian Minton Mar 6 at 19:04
add comment

You could store the value using repr(html) instead of the raw output and then use eval(html) when retrieving the value for use.

c.execute('insert or ignore into blah values (?, ?)',(1, repr(zlib.compress(html))))
share|improve this answer
Using eval and repr like this is very dirty. No matter how much you trust a data-source. –  Jason Fried Jan 27 at 18:50
add comment

Your Answer


