Python 2.3.5 (#1, Nov 26 2007, 09:16:55) 
[GCC 4.0.1 (Apple Computer, Inc. build 5363) (+4864187)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from pysqlite2 import dbapi2 as sqlite

Standard Operation

Keep DB in a file on the hard drive.

>>> con = sqlite.connect('sample.db')
>>> cur = con.cursor()
>>> ex = cur.execute

Create a table.

>>> ex('create table users (username text, password text, email text)')
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

List all table names.

>>> ex("select name from sqlite_master where type = 'table'").fetchall()
[(u'users',)]

Put in some data.

>>> t = ('gary', 'bcaba2cab740d20f', 'gary@someplace.com',)
>>> ex('insert into users values(?,?,?)', t)
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

>>> t = ('frank', 'fce15d1d', 'frank@somewhere.com',)
>>> ex('insert into users values(?,?,?)', t)
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

Get some data.

>>> ex('select * from users').fetchall() 
[(u'gary', u'bcaba2cab740d20f', u'gary@someplace.com'), \
 (u'frank', u'fce15d1d', u'frank@somewhere.com')]

>>> lookfor = ('frank',)                  
>>> ex('select * from users where username=?', lookfor).fetchall()
[(u'frank', u'fce15d1d', u'frank@somewhere.com')]

>>> lookfor = ('gary',)              
>>> ex('select email from users where username=?', lookfor).fetchall()
[(u'gary@someplace.com',)]

Change data in-place.

>>> newpassfor = ('helloworld', 'frank',)
>>> ex('update users set password=? where username=?', newpassfor)
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

>>> lookfor = ('frank',)
>>> ex('select * from users where username=?', lookfor).fetchall()
[(u'frank', u'helloworld', u'frank@somewhere.com')]

Delete some data.

>>> lookfor = ('frank',)
>>> ex('delete from users where username=?', lookfor)
<pysqlite2.dbapi2.Cursor object at 0x66ec0>
>>> ex('select * from users where username=?', lookfor).fetchall()
[]

Kill a table.

>>> ex('drop table users')
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

>>> ex('select * from users').fetchall()
Traceback (most recent call last):
  File "", line 1, in ?
pysqlite2.dbapi2.OperationalError: no such table: users

Save changes.

>>> con.commit()
>>> 

Dates & Times

>>> from datetime import datetime

Run DB in memory only.

>>> con = sqlite.connect(':memory:')
>>> cur = con.cursor()
>>> ex = cur.execute

Create the table named "schedule".

>>> ex('create table schedule (team text, opponent text, date datetime)')

Populate the table.

>>> g = ('wsh', 'fla', datetime(2008,12,3,19,0))
>>> ex('insert into schedule values(?,?,?)', g)
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

>>> g = ('wsh', 'tor', datetime(2008,11,30,19,0))
>>> ex('insert into schedule values(?,?,?)', g)
<pysqlite2.dbapi2.Cursor object at 0x66ec0>

Review table contents.

>>> ex('select * from schedule').fetchall()
[(u'wsh', u'fla', u'2008-12-03 19:00:00'), (u'wsh', u'tor', u'2008-11-30 19:00:00')]

What time is it?

>>> now = (datetime.now(),)
>>> now
(datetime.datetime(2008, 12, 3, 14, 39, 18, 516583),)

Find today's games.

>>> ex('select * from schedule where date(date) = date(?)', now).fetchall()
[(u'wsh', u'fla', u'2008-12-03 19:00:00')]

Find all future games.

>>> ex('select * from schedule where date(date) > date(?)', now).fetchall()
[]

Find all past games.

>>> ex('select * from schedule where date(date) < date(?)', now).fetchall()
[(u'wsh', u'tor', u'2008-11-30 19:00:00')]