Storing binary data in SQLite

10 Jan 2007

Storing images into a database is not common, usually you store only the file name, but sometimes it can be useful. Think you must log the position of several objects on a map. You can store the map and the logs in different files, but if your users have to share map and logs and they are not geek people, it can be a serious problem.

Remember that, of course, store files into a database makes the storage space more expensive. However, if you have to store one or few files it's not a big problem. :-)

Consider the following database schema:

    name varchar(20) NOT NULL PRIMARY KEY,
    image_file blob NOT NULL

    -- <your logs schema here>

Let's start to code:

import Image
import StringIO
from pysqlite2 import dbapi2 as sqlite

Storing an image into the database is very simple:

def storeInDatabase(cur, image_name, data):

    cur.execute("INSERT INTO map (name, image_file) values (?, ?)",
                (image_name, sqlite.Binary(data))

N.B. As said by Fredrik Lundh, SQLite <3.0 has a limitation of 1 MB for each row of data, and the database uses NUL bytes to separate columns in the storage.

Retrieve the previously image stored is simple too:

def retrieveFromDatabase(cur, image):

    cur.execute("SELECT image_file FROM map WHERE name = ?", (image,))
    img = cur.fetchone()[0]
    return StringIO.StringIO(img)

I use StringIO.StringIO because cur.fetchone()[0] returns a buffer-object and I want a file-object instead. ;)

You can test with:

if __name__ == '__main__':

    IMAGE = 'Surf.jpg'

    i = open(IMAGE, 'rb')
    idata =

    con = sqlite.connect('blob.db')
    cur = con.cursor()

        storeInDatabase(cur, image_name=IMAGE, data=idata)
    except sqlite.IntegrityError:
        print 'You have stored this map already'

    img = retrieveFromDatabase(cur, IMAGE)


The whole example is here: