SQLite with Python

Numerical experiments, Tips, Tricks and Gotchas

SQLite with Python

Introduction

This article extends my SQLite SQL Tutorial. It covers the basics of SQLite programming with the Python language.

I used the contents of sample tables from an excellent series of tutorials at http://www.linuxdevcenter.com/pub/ct/19.

I ran the examples from the IPython HTML Notebook. The whole project is available at the links below.

Creation / Opening Database

import sqlite3
import os.path

def create_or_open_db(filename):
    file_exists = os.path.isfile(filename)
    conn = sqlite3.connect(filename)
    if file_exists:
        print ''' "{}" database successfully opened '''.format(filename)
    else:
        print ''' "{}" database successfully created '''.format(filename)
    return conn

conn = create_or_open_db('mycds.sqlite')
cur = conn.cursor()
cur.execute('SELECT SQLITE_VERSION()')
print 'version:', cur.fetchone()

 "mycds.sqlite" database successfully created 
version: (u'3.6.21',)

Using the nonstandard execute(), executemany() and executescript() methods of the Connection object, the code can be written more compactly without creating the Cursor objects explicitly.

# using shortcut
for row in conn.execute('SELECT SQLITE_VERSION()'):
    print row[0]

3.6.21

Creation of tables

In SQLite, every row of every table has an 64-bit signed unique integer ROWID (|ROWID| ≤ 9223372036854775807). The ROWID of an SQLite table can be accessed using one the special column names ROWID, ROWID, or OID. If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID.

def create_tbl_artists(conn):
    sql = '''create table if not exists Artists(
            ArtistID INTEGER PRIMARY KEY,
            ArtistName TEXT);'''
    conn.execute(sql) # shortcut for conn.cursor().execute(sql)
    print "Created Artists table successfully"

create_tbl_artists(conn)

Created Artists table successfully

Inserting NULL into ArtistID or skipping this column generates the new ID.

conn.execute("insert into Artists (ArtistID, ArtistName) values (NULL,'Peter Gabriel');")
conn.execute("insert into Artists (ArtistName) values ('Bruce Hornsby');")

Passing values of Python variables from a list or a tuple into the "?" placeholders

conn.execute("insert into Artists (ArtistID, ArtistName) values (?, ?);",(3, 'Lyle Lovett'))

Passing values from a dictionary into named placeholders.

names = [{'name':'Beach Boys'},{'name':'Santana'}]
conn.executemany("insert into Artists (ArtistName) values (:name);", names)

Save changes.

conn.commit()

def print_tbl(conn, tablename):
    #cur = conn.cursor()
    #cur.execute("select * from Artists;")
    #for row in cur:
    for row in conn.execute("select * from {0};".format(tablename)):
        print row

print_tbl(conn, "Artists")

(1, u'Peter Gabriel')
(2, u'Bruce Hornsby')
(3, u'Lyle Lovett')
(4, u'Beach Boys')
(5, u'Santana')

With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database.

def create_tbl_CDs(conn):
    sql = '''create table if not exists CDs(
            CDID INTEGER PRIMARY KEY AUTOINCREMENT,
            ArtistID INTEGER NOT NULL,
            Title TEXT NOT NULL,
            Date TEXT);'''
    conn.execute(sql) # shortcut for conn.cursor().execute(sql)
    print "Created CDs table successfully"

create_tbl_CDs(conn)

Created CDs table successfully

cd = (1,'So','1984')
sql = "insert into CDs (ArtistID,Title,Date) values (?,?,?);"
cur = conn.cursor()
cur.execute(sql, cd)
print(cur.lastrowid)

Note that lastrowid returns None when you insert more than one row at a time with executemany.

lst = [(1,'Us','1992'),
       (2,'The Way It Is','1986'),
       (2,'Scenes from the Southside','1990')]
cur.executemany("insert into CDs (ArtistID,Title,Date) values (?,?,?);", lst)
print(cur.lastrowid)

None

Note that a dictionary does not guarantee the order of items.

sql = "insert into CDs (ArtistID,Title,Date) values (:id,:title,:date);"
dict = [{'id':1,'title':'Security','date':'1990'},
        {'id':3,'title':'Joshua Judges Ruth','date':'1992'},
        {'id':4,'title':'Pet Sounds','date':'1966'}]
cur.executemany(sql, dict)
conn.commit()

# save into the DB and print
conn.commit()
print_tbl(conn, "CDs")

(1, 1, u'So', u'1984')
(2, 1, u'Us', u'1992')
(3, 2, u'The Way It Is', u'1986')
(4, 2, u'Scenes from the Southside', u'1990')
(5, 1, u'Security', u'1990')
(6, 3, u'Joshua Judges Ruth', u'1992')
(7, 4, u'Pet Sounds', u'1966')

Backing up and restoring the database

def dump_to_file(conn, filename = 'dump.sql'):
    '''  Creates DDL and DML SQL script file
    '''
    with open(filename, 'w') as f:
        for line in conn.iterdump():
            f.write('%s\n' % line)

conn = sqlite3.connect('mycds.sqlite')

dump_to_file(conn, filename = 'dump.sql')

def restore_db(conn, db_file, filename = 'dump.sql'):
    ''' Using DDL and DML SQL script creates a new database
    '''
    with open(filename, 'r') as f:
        sql = f.read()
    conn.executescript(sql)
    conn.commit()

conn.close()
conn = sqlite3.connect('new_mycds.sqlite')
restore_db(conn, db_file = 'new_mycds.sqlite', filename = 'dump.sql')
conn.close()

Numerical experiments

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

The IPython HTML notebook can be:

viewed (html).
downloaded (ipynb).