Python standard library: sqlite3

Create a table then insert and select data

#!/usr/bin/python
import sqlite3
import os.path

if os.path.isfile('foo.db'):
   os.remove('foo.db')

db = sqlite3.connect('foo.db')

cur = db.cursor()

cur.execute('create table bar (a number, b varchar)')

cur.execute("insert into bar values (2, 'two')")

cur.execute('insert into bar values (?, ?)', (42, 'forty-two'))

cur.executemany('insert into bar values (?, ?)', [
  (4, 'four'),
  (5, 'five'),
  (7, 'seven'),
  (9, 'nine')
])

def generateData():
    for i in range(4):
        yield i, '*' * i
#
#  0:
#  1: *
#  2: two
#  2: **
#  3: ***
#  4: four
#  5: five
#  7: seven
#  9: nine
# 42: forty-two


cur.executemany('insert into bar values (?, ?)', generateData())

for row in cur.execute('select * from bar order by a'):
    print("{:2d}: {:s}".format(row[0], row[1]))

print('---')
# for row in cur.execute('select b from bar where a = ?',   42    ):     # ValueError: parameters are of unsupported type
# for row in cur.execute('select b from bar where a = ?', ( 42  ) ):     # ValueError: parameters are of unsupported type
# for row in cur.execute('select b from bar where a = ?',  '42'   ):     # sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.
# for row in cur.execute('select b from bar where a = ?', ('42' ) ):     # sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.
for   row in cur.execute('select b from bar where a = ?', ('42',) ):
    print(row[0])
    # forty-two

Non ascii characters

#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import sys
import sqlite3

db_filename = 'non_ascii.db'
if os.path.isfile(db_filename):
   os.remove(db_filename)

db = sqlite3.connect(db_filename)
db.text_factory = str

cur = db.cursor()

cur.execute('create table T (a text)')

cur.execute('insert into T values (?)' , ( 'Tasse'     ,) )
cur.execute('insert into T values (?)' , ( 'Märchen'   ,) )
cur.execute('insert into T values (?)' , ( 'Butterbrot',) )
cur.execute('insert into T values (?)' , ( 'Säbel'     ,) )

# two records returned
print('\nselect * from T where a like "%ä%"')
for r in cur.execute('select * from T where a like "%ä%"'):
    print("  " + r[0])

# Note: no records returned. (At least not on Windows with Python 2.7)
print('\nselect * from T where upper(a) like "%Ä%"')
for r in cur.execute('select * from T where upper(a) like "%Ä%"'):
    print("  " + r[0])

Nested loop - same cursor

#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import sys
import sqlite3

db_filename = 'nested_loop.db'
if os.path.isfile(db_filename):
   os.remove(db_filename)

db = sqlite3.connect(db_filename)
db.text_factory = str

cur = db.cursor()

cur.execute('create table O (o int       )')
cur.execute('create table I (o int, i int)')

cur.execute('insert into O values (?   )' , ( 1 ,   ) )
cur.execute('insert into O values (?   )' , ( 2 ,   ) )
cur.execute('insert into O values (?   )' , ( 3 ,   ) )

cur.execute('insert into I values (?, ?)' , ( 1 , 11) )
cur.execute('insert into I values (?, ?)' , ( 1 , 12) )
cur.execute('insert into I values (?, ?)' , ( 1 , 13) )
cur.execute('insert into I values (?, ?)' , ( 1 , 14) )

cur.execute('insert into I values (?, ?)' , ( 2 , 21) )

cur.execute('insert into I values (?, ?)' , ( 3 , 31) )
cur.execute('insert into I values (?, ?)' , ( 3 , 32) )


def I(o):

    print("o: {:d}".format(o))
    for r in cur.execute('select i from I where o = ?', (o ,)):
        print("  i: {:d}".format(r[0]))
       

def O():
    for r in cur.execute('select * from O'):
        I(r[0])

O()

Transactions

#!/usr/bin/python
import sqlite3
import os

if os.path.isfile('trx.db'):
   os.remove('trx.db')

db = sqlite3.connect('trx.db')

cur = db.cursor()

cur.execute('create table t (a number, b varchar)')

cur.execute("insert into t values (1, 'one')")
cur.execute("insert into t values (2, 'two')")

db.rollback()

cur.execute("insert into t values (3, 'three')")
cur.execute("insert into t values (4, 'four')")

db.commit()

for row in cur.execute('select * from t order by a'):  
    print("%2d: %s" % (row[0], row[1]))

printf

#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import sys
import sqlite3

db_filename = 'printf.db'
if os.path.isfile(db_filename):
   os.remove(db_filename)

db = sqlite3.connect(db_filename)
db.text_factory = str

cur = db.cursor()

# printf only added with sqlite version 3.8.3, compare http://stackoverflow.com/questions/26149879/why-cant-i-use-printf-with-sqlite-when-executed-in-python
cur.execute(r'create table foo as select printf("%5.2f", 42.424242) bar')

See also