Welcome, guest | Sign In | My Account | Store | Cart

Storing binary data in SQLite (Python recipe) by John Barham
ActiveState Code (http://code.activestate.com/recipes/252531/)

This script demonstrates how to store binary data (aka BLOB or BINARY fields) in SQLite using the PySQLite extension.

Python, 21 lines
Download
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
import os
import sqlite

class Blob:
    """Automatically encode a binary string."""
    def __init__(self, s):
        self.s = s

    def _quote(self):
        return "'%s'" % sqlite.encode(self.s)

db = sqlite.connect("test.db")
cursor = db.cursor()
cursor.execute("CREATE TABLE t (b BLOB);")
s = "\0"*50 + "'"*50
cursor.execute("INSERT INTO t VALUES(%s);", Blob(s))
cursor.execute("SELECT b FROM t;")
b = cursor.fetchone()[0]
assert b == s # b is automatically decoded
db.close()
os.remove("test.db")

SQLite cannot store binary data (i.e., strings containing NUL or ' characters) unless it is first encoded to remove these characters. The sqlite.encode() function compactly encodes binary strings so that they can be safely stored in an SQLite database. PySQLite detects when a retrieved field is a binary field (if its column type is BLOB or BINARY) and automatically decodes it using the sqlite.decode() function.

The sqlite.encode() function wraps a C function included with the SQLite source distribution. It imposes at worst a 1.2% size penalty for encoded strings, vs. a 33% size increase incurred every time by using base-64 encoding.

You must be using PySQLite 0.5 or above for this recipe to work.

References:

The PySQLite homepage is at http://pysqlite.sourceforge.net.

For more information on storing binary data in SQLite see the SQLite FAQ, "Does SQLite support a BLOB type?", at http://www.hwaci.com/sw/sqlite/faq.html#q12.

Tags: database

2 comments

Gerhard Häring 19 years, 6 months ago  # | flag

Use the DB-API method. Just use sqlite.Binary(). This is is the Right Way(tm).

Sergiusnick Sergiusnick 14 years, 8 months ago  # | flag

I've founded that the next code works: If you want to check it, try

-- coding: utf-8 --

import sqlite3 as db c = db.connect(database="files") cu = c.cursor()

cu.execute("create table files (FileName varchar(80), FileContent blob)")

query="insert into files (FileName, FileContent) values ('1', ?)"

f=file(u'ТЗ (Документооборот).doc', 'rb')

k=f.read()

f.close()

cu.execute(query, [db.Binary(k)])

c.commit()

cu.execute("select FileContent from files") k=0 for i in cu.fetchall(): k+=1 f=file(str(k)+'.doc', 'wb') f.write(i[0]) f.close()