Advanced SQLite with Python: Storing and retrieving Blob and DateTime

Numerical experiments, Tips, Tricks and Gotchas

Advanced SQLite with Python: Blob, Date, Time, etc.

Introduction

As usual, these notes are primarily for myself. I just collected in one place the excerpt from available documentation. I also used the information I found on the internet. Some links are presented in the Reference section.

Storing and retrieving BLOBs

Import of DB, os, image

import sqlite3
import os.path
from os import listdir, getcwd
from IPython.core.display import Image

List files

def get_picture_list(rel_path):
    abs_path = os.path.join(os.getcwd(),rel_path)
    print 'abs_path =', abs_path
    dir_files = os.listdir(abs_path)
    #print dir_files
    return dir_files

picture_list = get_picture_list('pictures')
print picture_list

abs_path = E:\Projects\Numerical_Notes\SQLite\pictures
['Chrysanthemum50.jpg', 'Desert50.jpg', 'Hydrangeas50.jpg', 'Jellyfish50.jpg', 'Koala50.jpg', 'Lighthouse50.jpg', 'Penguins50.jpg', 'Thumbs.db', 'Tulips50.jpg']

Image(filename='.\pictures\Chrysanthemum50.jpg')

Create picture database

def create_or_open_db(db_file):
    db_is_new = not os.path.exists(db_file)
    conn = sqlite3.connect(db_file)
    if db_is_new:
        print 'Creating schema'
        sql = '''create table if not exists PICTURES(
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        PICTURE BLOB,
        TYPE TEXT,
        FILE_NAME TEXT);'''
        conn.execute(sql) # shortcut for conn.cursor().execute(sql)
    else:
        print 'Schema exists\n'
    return conn

def insert_picture(conn, picture_file):
    with open(picture_file, 'rb') as input_file:
        ablob = input_file.read()
        base=os.path.basename(picture_file)
        afile, ext = os.path.splitext(base)
        sql = '''INSERT INTO PICTURES
        (PICTURE, TYPE, FILE_NAME)
        VALUES(?, ?, ?);'''
        conn.execute(sql,[sqlite3.Binary(ablob), ext, afile]) 
        conn.commit()

conn = create_or_open_db('picture_db.sqlite')

Creating schema

picture_file = "./pictures/Chrysanthemum50.jpg"
insert_picture(conn, picture_file)
conn.close()

def extract_picture(cursor, picture_id):
    sql = "SELECT PICTURE, TYPE, FILE_NAME FROM PICTURES WHERE id = :id"
    param = {'id': picture_id}
    cursor.execute(sql, param)
    ablob, ext, afile = cursor.fetchone()
    filename = afile + ext
    with open(filename, 'wb') as output_file:
        output_file.write(ablob)
    return filename

conn = create_or_open_db('picture_db.sqlite')
cur = conn.cursor()
filename = extract_picture(cur, 1)
cur.close()
conn.close()
Image(filename='./'+filename)

Bulk insert

conn = create_or_open_db('picture_db.sqlite')
conn.execute("DELETE FROM PICTURES")
for fn in picture_list:
    picture_file = "./pictures/"+fn
    insert_picture(conn, picture_file)
    
for r in conn.execute("SELECT FILE_NAME FROM PICTURES"):
    print r[0]

conn.close()

Schema exists

Chrysanthemum50
Desert50
Hydrangeas50
Jellyfish50
Koala50
Lighthouse50
Penguins50
Thumbs
Tulips50

Storing and retrieving Datetime

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If you want to use other types you must add support for them yourself.

This includes the following steps:

Define and register a callable to convert the custom Python type type into one of SQLite's supported types (adapter).
Define and registers a callable to convert a bytestring from the database into a custom Python type (converter).
Eneble inference of types from column names and/or from column types.

SQLite3 comes with support of DATE and TIMESTAMP (datetime) types (see dbapi2.py).

The DATE field accepts a sring in ISO 8601 format 'YYYY-MM-DD' or datetime.date object:

from datetime import datetime, date
conn = sqlite3.connect("person_db.sqlite",detect_types=sqlite3.PARSE_DECLTYPES) 
conn.execute("CREATE TABLE person (id integer primary key, firstname text, lastname text, dob date)")

 conn.execute("INSERT INTO person(firstname, lastname, dob) values (?, ?, ?)", ("Joe","Doe","2003-06-25"))
#d = date.strptime("06/05/1723", "%m/%d/%Y")
d = date(1723,06,05)
conn.execute("INSERT INTO person(firstname, lastname, dob) values (?, ?, ?)", ("Adam","Smith", d))
conn.commit()
cur = conn.cursor()
cur.execute("SELECT * FROM person")
print cur.fetchone()
print cur.fetchone()
conn.close()

(1, u'Joe', u'Doe', datetime.date(2003, 6, 25))
(2, u'Adam', u'Smith', datetime.date(1723, 6, 5))

The TIMESTAMP field accepts a sring in ISO 8601 format 'YYYY-MM-DD HH:MM:SS.mmmmmm' or datetime.datetime object:

conn = sqlite3.connect("log_db.sqlite",detect_types=sqlite3.PARSE_DECLTYPES) 
conn.execute("CREATE TABLE logs (id integer primary key, message text, [timestamp] timestamp)")

conn.execute("INSERT INTO logs(message, timestamp) values (?, ?)", ("message: error",'2012-12-25 23:59:59'))
#d = date.strptime("06/05/1723", "%m/%d/%Y")
#dt = datetime(1723,06,05)
conn.execute("INSERT INTO logs(message, timestamp) values (?, ?)", ("message: ok", datetime.now()))
conn.commit()
cur = conn.cursor()
cur.execute("SELECT * FROM logs")
print cur.fetchone()
print cur.fetchone()
conn.close()

(1, u'message: error', datetime.datetime(2002, 12, 25, 23, 59, 59))
(2, u'message: ok', datetime.datetime(2014, 1, 5, 10, 37, 50, 51000))

SQLite3 can also detect the type from a column name

detect_types=sqlite3.PARSE_COLNAMES

or both from a declaration or name

detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES

Storing and retrieving custom types

As an example let's implement storing of decimal.Decimal.

The adapter accepts as single parameter the Python value, and must return a value of the following types: int, long, float, str or buffer.

from decimal import Decimal

def decimal2float(val):
    return float(str(val))
	
sqlite3.register_adapter(Decimal, decimal2float)

Now we can insert the Decimal type without getting an error ("unsupported type"). However a number is retrieved as a float.

conn = sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_DECLTYPES) 
conn.execute("CREATE TABLE numbers (number decimal)")
conn.execute("INSERT INTO numbers(number) values (?)", (Decimal('123.456789'),))
conn.commit()
cur = conn.cursor()
cur.execute("SELECT * FROM numbers")
print cur.fetchone()
conn.close()

(123.456789,)

To retrieve numbers as a Decimal, we have to implement and register a converter.

def float2decimal(val):
    return Decimal(str(val))
sqlite3.register_converter("decimal", float2decimal)

Here "decimal" is a typename of a field.

conn = sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_DECLTYPES) 
conn.execute("CREATE TABLE numbers (number decimal)")
conn.execute("INSERT INTO numbers(number) values (?)", (Decimal('123.456789'),))
conn.commit()
cur = conn.cursor()
cur.execute("SELECT * FROM numbers")
print cur.fetchone()
conn.close()

(Decimal('123.456789'),)

Note that the case of typename and the name of the type in queries must match!

Accessing columns by name

One useful feature of the sqlite3 module is the built-in sqlite3.Row class designed to be used as a row factory. Rows wrapped with this class can be accessed both by index (like tuples) and case-insensitively by name:

conn = sqlite3.connect("person_db.sqlite",detect_types=sqlite3.PARSE_DECLTYPES)
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT * FROM person where id=2")
for row in cur:
    print row['firstname']
    print 'DoB:', row['dob']

Adam
DoB: 1723-06-05

con = sqlite3.connect(":memory:") con.row_factory = sqlite3.Row

Using the connection as a context manager

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

''' example from http://docs.python.org/2.7/library/sqlite3.html '''
con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"

couldn't add Joe twice

Numerical experiments

The IPython HTML notebook experiments are available at the links below.

The IPython HTML notebooks can be:

viewed: sqlite_blob.html, sqlite_time_etc.html.
downloaded: sqlite_blob.ipynb, sqlite_time_etc.ipynb .

The images used in this tutorial are also available for download (zip).