Saturday, May 2, 2009

Python and SQLite

Python and SQLite work together seamlessly.   

#!/usr/bin/python

from pysqlite2 import dbapi2 as sqlite 
database_name = "newdb.db"

conn = sqlite.connect(database_name)

cursor=conn.cursor() 

cursor.execute('drop table if exists FOODS') 

cursor.execute('''create table FOODS 
   ( id INTEGER PRIMARY KEY AUTOINCREMENT
   , name VARCHAR(50) NOT NULL
   , serving_size_oz INT NOT NULL
   , calories_per_serving INT NOT NULL
   , unique (name))''')

cursor.execute('''insert into FOODS 
  (name, serving_size_oz, calories_per_serving) 
  values("Gummy Bears", 5, 80)''')

cursor.execute('''insert into FOODS 
  (name, serving_size_oz, calories_per_serving) 
  values("Potato Chips", 10, 100)''')

cursor.execute('''insert into FOODS 
  (name, serving_size_oz, calories_per_serving) 
  values("Ice Cream", 8, 170)''')

cursor.execute('''insert into FOODS 
  (name, serving_size_oz, calories_per_serving) 
  values("Barbeque", 12, 255)''')

conn.commit() 
cursor.execute('SELECT * FROM foods LIMIT 2')
row = cursor.fetchone ()
while row != None:
  print row
  row = cursor.fetchone ()

conn.close()



~/dev/python/wx $ ./fooddb.py

at