import sqlite3
conn = sqlite3.connect( 'my_test1.db' )
print( "Opened database successfully" )
input( "Press 'Enter' to continue/exit ... " )
# 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 --- > )
# 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 ... " )
# 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()