Home

Recent
Archive

Numerical experiments, Tips, Tricks and Gotchas

Numerically speaking

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')
Chrysanthemum

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)
Chrysanthemum

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:

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:

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

 

References

  1. DB-API 2.0 interface for SQLite databases.
  2. Datatypes In SQLite Version 3.
  3. SQLite - Data Types.
  4. How to get Top 5 records in SqLite?.
  5. Using Sqlite3 to store Blob data.
  6. Insert binary file in SQLite database with Python.
  7. pysqlite - how to save images.
  8. Python, Sqlite3 - How to convert a list to a BLOB cell.
  9. Writing blob from SQLite to file using Python.
  10. Storing BLOBs in a SQLite DB with Python/pysqlite.
  11. y_serial - warehouse compressed Python objects with SQLite.
  12. Read datetime back from sqlite as a datetime in Python.
  13. SQLite date storage and conversion.
  14. Pylons tip #4 - SQLite3, datetime.date and datetime.datetime handling.
  15. Python by Example.

 

© Nikolai Shokhirev, 2012-2017

email: nikolai(dot)shokhirev(at)gmail(dot)com

Count: