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).
Enable 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:

In [1]:

import sqlite3
from datetime import datetime, date

In [2]:

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

Out[2]:

<sqlite3.Cursor at 0x54351a0>

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

In [3]:

conn.execute("INSERT INTO person(firstname, lastname, dob) values (?, ?, ?)", ("Joe","Doe","2003-06-25"))
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))

In [4]:

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

Out[4]:

<sqlite3.Cursor at 0x54353e0>

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

In [5]:

conn.execute("INSERT INTO logs(message, timestamp) values (?, ?)", ("message: error",'2012-12-25 23:59:59'))
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(2012, 12, 25, 23, 59, 59))
(2, u'message: ok', datetime.datetime(2014, 1, 5, 16, 52, 57, 523000))

Storing and retrieving custom types¶

In [6]:

from decimal import 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.

In [7]:

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

In [8]:

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.

In [9]:

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

In [10]:

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

Accessing columns by name¶

In [11]:

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=6")
for row in cur:
    print row['firstname']
    print 'DoB:', row['dob']
conn.close()

Using the connection as a context manager¶

In [12]:

# 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

Storing and retrieving Datetime¶

Storing and retrieving custom types¶

Accessing columns by name¶

Using the connection as a context manager¶