In [1]:

import sqlite3
import os.path

In [2]:

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

In [3]:

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.

In [4]:

# 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.

In [5]:

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"

In [6]:

create_tbl_artists(conn)

Created Artists table successfully

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

In [7]:

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

Out[7]:

<sqlite3.Cursor at 0x52905a0>

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

In [8]:

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

Out[8]:

<sqlite3.Cursor at 0x5290620>

Passing values from a dictionary into named placeholders.

In [9]:

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

Out[9]:

<sqlite3.Cursor at 0x5290720>

In [10]:

conn.commit()

In [11]:

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

In [12]:

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.

In [13]:

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"

In [14]:

create_tbl_CDs(conn)

Created CDs table successfully

In [15]:

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

In [16]:

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.

In [17]:

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

In [18]:

# 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¶

In [19]:

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)

In [20]:

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

In [21]:

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

In [22]:

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

In [23]:

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

In []:

SQLite with Python¶

Creation / Opening Database¶

Creation of tables¶

Backing up and restoring the database¶