Python version 3.3.2 now ships with SQLite3 version 3.7.12 ...

[David]

Just download and install the latest Python 3.3.2 and then ...

you are all ready ...

to jump-start doing Data-Base Programming with Python 3
(and SQLite3 version 3.7.12 included.)

Update: FREE homework help NOW available ...

You can contact me via:
http://sites.google.com/site/andeveryeyeshallseehim/home/he-comes
http://developers-heaven.net/forum/index.php/board,9.0.html

Below are some example to help get you started:

Code: [Select]

import sqlite3

conn = sqlite3.connect( 'my_test1.db' )

print( "Opened database successfully" )

input( "Press 'Enter' to continue/exit ... " )

[David]

15 little snippets here found on the web ... edited some and updated to now run in Python 3.3.2 ...

Code: [Select]

# PythonSqlite3.py # # this version 2013-07-15 #

'''
    15 little demo's here ...
   
    found at:
    http://zetcode.com/db/sqlitepythontutorial/
   
    fixed/edited to run on Python 3.3 by dwz on 2013-07-15
'''


import sqlite3 as lite
import sys

con = None

try:
    con = lite.connect( 'test.db' )
   
    cur = con.cursor()   
    cur.execute( 'SELECT SQLITE_VERSION()' )
   
    data = cur.fetchone()
   
    print( "SQLite version: %s" % data )             
   
except(lite.Error, e):
   
    print( "Error %s:" % e.args[0] )
    sys.exit(1)
   
finally:
   
    if con:
        con.close()



input( "1. Press 'Enter' to continue ... " )



con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   
    cur.execute( 'SELECT SQLITE_VERSION()' )
   
    data = cur.fetchone()
   
    print( "SQLite version: %s" % data )


'''
input( "2. Press 'Enter' to continue ... " )



con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   
    cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
    cur.execute("INSERT INTO Cars VALUES(1,'Audi',52642)")

    cur.execute("INSERT INTO Cars VALUES(2,'Mercedes',57127)")
    cur.execute("INSERT INTO Cars VALUES(3,'Skoda',9000)")
    cur.execute("INSERT INTO Cars VALUES(4,'Volvo',29000)")
    cur.execute("INSERT INTO Cars VALUES(5,'Bentley',350000)")
    cur.execute("INSERT INTO Cars VALUES(6,'Citroen',21000)")
    cur.execute("INSERT INTO Cars VALUES(7,'Hummer',41400)")
    cur.execute("INSERT INTO Cars VALUES(8,'Volkswagen',21600)")
'''

'''
    These two lines insert two cars into the table.
    Using the with keyword, the changes are automatically committed.
    Otherwise, we would have to commit them manually.
'''



input( "3. Press 'Enter' to continue ... " )

#import sqlite3 as lite
#import sys

cars = (
    (1, 'Audi', 52642),
    (2, 'Mercedes', 57127),
    (3, 'Skoda', 9000),
    (4, 'Volvo', 29000),
    (5, 'Bentley', 350000),
    (6, 'Hummer', 41400),
    (7, 'Volkswagen', 21600)
)

#print cars


con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   
   
    cur.execute("DROP TABLE IF EXISTS Cars")
    cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
    cur.executemany("INSERT INTO Cars VALUES(?, ?, ?)", cars)



input( "4. Press 'Enter' to continue ... " )

#import sqlite3 as lite
#import sys

try:
    con = lite.connect('test.db')

    cur = con.cursor() 

    cur.executescript("""
        DROP TABLE IF EXISTS Cars;
        CREATE TABLE Cars(Id INT, Name TEXT, Price INT);
        INSERT INTO Cars VALUES(1,'Audi',52642);
        INSERT INTO Cars VALUES(2,'Mercedes',57127);
        INSERT INTO Cars VALUES(3,'Skoda',9000);
        INSERT INTO Cars VALUES(4,'Volvo',29000);
        INSERT INTO Cars VALUES(5,'Bentley',350000);
        INSERT INTO Cars VALUES(6,'Citroen',21000);
        INSERT INTO Cars VALUES(7,'Hummer',41400);
        INSERT INTO Cars VALUES(8,'Volkswagen',21600);
        """)

    con.commit()
   
except( lite.Error, e ):
   
    if con:
        con.rollback()
       
    print( "Error %s:" % e.args[0] )
    sys.exit(1)
   
finally:
   
    if con:
        con.close()



input( "5. Press 'Enter' to continue ... " )

#import sqlite3 as lite
#import sys

con = lite.connect(':memory:')

with con:
   
    cur = con.cursor()   
    cur.execute("CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);")
    cur.execute("INSERT INTO Friends(Name) VALUES ('Tom');")
    cur.execute("INSERT INTO Friends(Name) VALUES ('Rebecca');")
    cur.execute("INSERT INTO Friends(Name) VALUES ('Jim');")
    cur.execute("INSERT INTO Friends(Name) VALUES ('Robert');")
       
    lid = cur.lastrowid

print( "The last Id of the inserted row is %d" % lid )




input( "6. Press 'Enter' to continue ... " )

#import sqlite3 as lite
#import sys


con = lite.connect('test.db')

with con:   
   
    cur = con.cursor()   
    cur.execute("SELECT * FROM Cars")

    rows = cur.fetchall()

    for row in rows:
        print( row )



input( "7. Press 'Enter' to continue ... " )

#import sqlite3 as lite
#import sys

con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   
    cur.execute("SELECT * FROM Cars")

    print( "%s  %20s  %6s" % ("ID", "NAME", "PRICE" ) )

    while True:
     
        row = cur.fetchone()
       
        if row == None:
            break
           
        print( "%2i  %20s  %6i" % (row[0], row[1], row[2]) )



input( "8. Press 'Enter' to continue ... " )

#import sqlite3 as lite

con = lite.connect('test.db')   

with con:

    # We select a dictionary cursor.
    con.row_factory = lite.Row
       
    cur = con.cursor()
    cur.execute("SELECT * FROM Cars order by -Price") #descending order

    rows = cur.fetchall()

    print( "%s  %20s  %6s" % ("ID", "NAME", "PRICE" ) )

    for row in rows:
        # Now we can access records by the names of columns.
        print( "%2s  %20s  %6s" % (row["Id"], row["Name"], row["Price"]) )



input( "9. Press 'Enter' to continue ... " )

'''
Parameterized queries ...

Now we will concern ourselves with parameterized queries.
When we use parameterized queries, we use placeholders
instead of directly writing the values into the statements.
Parameterized queries increase security and performance.

The Python sqlite3 module supports two types of placeholders.
Question marks and named placeholders.
'''

#import sqlite3 as lite
#import sys

con = lite.connect('test.db')

uId = 1
uPrice = 62300

with con:

    cur = con.cursor()

    # We update a price of one car.
    # In this code example, we use the question mark placeholders.

    cur.execute("UPDATE Cars SET Price=? WHERE Id=?", (uPrice, uId))       
    con.commit()
   
    print( "Number of rows updated: %d" % cur.rowcount )


    cur.execute("SELECT * FROM Cars order by -Price")

    print( "%s  %20s  %6s" % ("ID", "NAME", "PRICE" ) )

    while True:
     
        row = cur.fetchone()
       
        if row == None:
            break
           
        print( "%2i  %20s  %6i" % (row[0], row[1], row[2]) )



input( "10. Press 'Enter' to continue ... " )

#import sqlite3 as lite
#import sys

uId = 4

con = lite.connect('test.db')

with con:

    cur = con.cursor()   
    # The second example uses parameterized statements
    # with named placeholders.
    # We select a name and a price of a car using named placeholders
    cur.execute("SELECT Name, Price FROM Cars WHERE Id=:Id", {"Id": uId})       
    con.commit()
   
    row = cur.fetchone()
    print( row[0], row[1] )



input( "11. Press 'Enter' to continue ... " )
'''
import sqlite3 as lite
import sys

In this script, we read an image from the current
working directory and write it into the Images table
of the SQLite test.db database.

'''

def readImage():

    try:
        fin = open("woman.jpeg", "rb")
        img = fin.read()
       
        print( "\nimg read ok ..." )
        return img
       
    except( IOError, e ):

        print( "Error %d: %s" % (e.args[0], e.args[1]) )
        sys.exit(1)

    finally:
       
        if fin:
            fin.close()



try:
    con = lite.connect('test.db')
   
    cur = con.cursor()
    data = readImage()
    binary = lite.Binary(data)
   
    cur.execute("DROP TABLE IF EXISTS Images")
    cur.execute("CREATE TABLE Images(Data BINARY)")
    #cur.execute("INSERT INTO Cars VALUES(8,'Volkswagen',21600)")
    cur.execute("INSERT INTO Images(Data) VALUES (?)", (binary,) )

    con.commit()   
   
except( lite.Error, e ):
   
    if con:
        con.rollback()
       
    print( "Error %s:" % e.args[0] )
    sys.exit(1)
   
finally:
   
    if con:
        con.close()



input( "12. Press 'Enter' to continue ... " )

'''
In this section, we are going to perform the reverse operation.
We will read an image from the database table.

import sqlite3 as lite
import sys
'''


def writeImage(data):
   
    try:
        fout = open('woman2.jpg','wb')
        fout.write(data)
   
    except( IOError, e ):   
        print( "Error %d: %s" % (e.args[0], e.args[1]) )
        sys.exit(1)
       
    finally:
       
        if fout:
            fout.close()       
   

try:
    con = lite.connect('test.db')
   
    cur = con.cursor()   
    cur.execute("SELECT Data FROM Images LIMIT 1")
    data = cur.fetchone()[0]

    # We read image data from the Images table
    # and write it to another file, which we call woman2.jpg.
    writeImage(data)

   
except( lite.Error, e ):
   
    print( "Error %s:" % e.args[0] )
    sys.exit(1)
   
finally:
   
    if con:
        con.close()     



input( "13. Press 'Enter' to continue ... " )
'''
import sqlite3 as lite
import sys

'''

con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   

    # In this example, we issue the PRAGMA table_info(tableName) command,
    # to get some metadata info about our Cars table.
    cur.execute('PRAGMA table_info(Cars)')
   
    data = cur.fetchall()
   
    for d in data:
        print( d[0], d[1], d[2] )



input( "14. Press 'Enter' to continue ... " )
'''
import sqlite3 as lite
import sys

Next we will print all rows from the Cars table with their column names.
'''

con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   
    cur.execute('SELECT * FROM Cars order by -Price') # ordered in descending order #
   
    col_names = [cn[0] for cn in cur.description] # make a Python list of col names #
   
    rows = cur.fetchall()
   
    # Now, we include the names of the columns too. 
    print( "%s   %-10s %8s" % (col_names[0], col_names[1], col_names[2]) )

    for row in rows:
        # We print the contents of the Cars table to the console.
        # The records are aligned with the column names.
        print( "%2s   %-10s %8s" % row )



input( "15. Press 'Enter' to continue ... " )
'''
In our last example related to the metadata,
we will list all tables in the test.db database.

import sqlite3 as lite
import sys
'''

con = lite.connect('test.db')

with con:
   
    cur = con.cursor()   
    cur.execute( "SELECT name FROM sqlite_master WHERE type = 'table'" )

    tables = cur.fetchall()

    # The code example prints all available tables in the current database to the console.

    for table in tables:
        print( table[0] )


input( "\nPress 'Enter' to continue / EXIT --- > )


[David]

Another example found on the web upgraded and modified to run in Python 3...

with date / time ... in microseconds

Code: [Select]

# test4_Sqlite3.py # # this version 2013-07-15 #

'''
    upgraded / modified for Python 3 from ...

    http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

    by dwz on 2013-07-15
'''

import sqlite3 as lite


from datetime import datetime

def getNow():
    daynow = datetime.now()
    return '{}{:>02d}{:>02d} {:>02}:{:>02}:{:>02}:{:>06}'.format(daynow.year, daynow.month, \
                                                   daynow.day, daynow.hour, \
                                                         daynow.minute, daynow.second, daynow.microsecond )

con = lite.connect( 'test4.db' )

with con: # data is here 'automatically commited' since using 'with ... '
   
    cur = con.cursor()   
    cur.execute( 'SELECT SQLITE_VERSION()' )
   
    data = cur.fetchone()
   
    print( "SQLite version: %s" % data )

    cur.execute( 'DROP TABLE IF EXISTS T1' ) # clear out old TABLE if exits
                                             # so can CREATE a new one below ...
   
    cur.execute( 'CREATE TABLE T1 (t1key INTEGER PRIMARY KEY, data TEXT, num DOUBLE, tn TEXT);' ) # tn is timeNow

    # cur.execute("SELECT Name, Price FROM Cars WHERE Id=:Id", {"Id": uId})
    cur.execute( "INSERT INTO T1 (data, num, tn) VALUES ( 'This is sample data', 333.33, ? )", (getNow(),) )
   
    for i in range( 5000000 ): pass
    cur.execute( "insert into T1 (data, num, tn) values ( 'Some later data', 666.6, ? )", (getNow(),) )
   
    for i in range( 5000000 ): pass
    cur.execute( "insert into T1 (data, num, tn) values ( 'A little later', 999.989, ? )", (getNow(),) )

    cur.execute( "SELECT * FROM t1" )

    print( "{:3s}  {:>20s}  {:>6s}  {:>8s} {:>15s}".format("KEY", "DATA DESCRIPTION", "VALUE", "DATE", "TIME" ) )

    while True:
     
        row = cur.fetchone()
       
        if row == None:
            break
           
        print( "%3i  %20s  %0.2f  %6s" % (row) )

    cur.execute( "SELECT * FROM sqlite_master" )

    items = cur.fetchall()

    print( "\nShowing all stuff in db ... " )

    for item in items:
        for subItem in item:
            print( subItem )

input( "\n\nPress 'Enter' to continue/exit ... " )



[David]

A little head-start to your Customer Data Base ...

Code: [Select]

# customerDB_Sqlite3.py #  # this version 2013-07-15 #

import sqlite3

#GLOBAL VARIABLES#
con = sqlite3.connect( ':memory:' ) # just keeping this in temporary memory #
cur = con.cursor() # get an iterator 'cur' to the start of the data base


# NOTE: here the PRIMARY KEY 'id' ... is 'auto generated/incremented'
def createTable():
    cur.execute( '''CREATE TABLE customers
    ( id INTEGER PRIMARY KEY, name TEXT, city TEXT, street TEXT,
    province TEXT, balance REAL )''' )

def addCust( name, street, city, province, balance ):
    cur.execute( '''INSERT INTO customers ( name, street, city, province, balance )
    VALUES ( ?, ?, ?, ?, ? )''', ( name, street, city, province, balance ) )

def showAllRows( table ):
    listTitle = [ 'Id Num', 'Name', 'Street', 'City', 'Province', 'Balance' ]
    for row in table:
        print()
        t = 0
        for item in row:
            print( "{:<10s}".format( listTitle[t] +':'), end = ' ' )
            if t < 5: print( item )
            else: print( "{:.2f}".format( item ))
            if t < 5: t += 1
            else: t = 0

def grandTotal( table ):
    tot = 0.0
    for row in table:
        print( row[5] )
        tot += row[5]
    return tot

def getIdOfMaxBalance( table ):
    maxBal = -1.00
    count = 0
    id = 0
    for row in table:
        count += 1
        if( count == 1 ):
            id = row[0]
            maxBal = row[5]
        else:
            if row[5] > maxBal:
                maxBal = row[5]
                id = row[0]
    return id

   

def main():
    createTable()
   
    addCust( 'Sam', '3 Consession 2', 'RR#1 Someville', 'ON', 1234.56 )
    addCust( 'Joe', '4 Consession 9', 'RR#7 Newville', 'ON',     5.5 )
    addCust( 'Ann', '5 Consession 1', 'RR#5 Serville', 'ON',  333.33 )
    addCust( 'Bob', '6 Consession 6', 'RR#9 Paville', 'ON', 9999.99 )

    con.commit() # make changes to data base

    print( '\nShowing all customers sorted by balance in descending order ...' )
           
    cur.execute( '''SELECT * FROM customers ORDER BY -balance''' )
    showAllRows( cur )


    print( '\nShowing all customers sorted by name in ascending order ...' )
    input( "Press 'Enter' to continue ... " )
           
    cur.execute( '''SELECT * FROM customers ORDER BY name''' )
    showAllRows( cur )

    print( '\nShowing all customers with balance >= 500.00 ...' )
    input( "Press 'Enter' to continue ... " )
           
    cur.execute( '''SELECT * FROM customers WHERE balance >= 500.00''' )
    showAllRows( cur )

    cur.execute( '''SELECT * FROM customers''' )
    print( "\nThe outstanding balance is", grandTotal( cur ) )

    cur.execute( '''SELECT * FROM customers''' )
    idMaxBal = getIdOfMaxBalance( cur )
    print( "\nThe id of largest balance is", idMaxBal )

    # cur.execute("SELECT Name, Price FROM Cars WHERE Id=:Id", {"Id": uId})

    cur.execute( "SELECT * FROM customers WHERE id=:id", {"id": idMaxBal})
    print( "\nThe customer with max balance of", idMaxBal, "is" )
    showAllRows( cur )
   
    input( "\n\nPress 'Enter' to continue/exit ... " )
           

if __name__ == '__main__' : main()