# Creates/opens a db
db = sqlite3.connect('data.db')
c = db.cursor()

#
# CREATE and INSERT

c.execute('DROP TABLE IF EXISTS philosopher; ')

c.execute('''
 CREATE TABLE philosopher (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 city TEXT
 )
''')
db.commit()

c.execute('''
 INSERT INTO philosopher (name, city)
 VALUES (?, ?) ''', ('Immanuel Kant', 'Konigsberg'))

# get the last row id;
# returns None with executemany though
print c.lastrowid

db.commit()
guys = [('Plato', 'Athens'),
('Aristoteles', 'Stagira'),
('Spinoza', 'Amsterdam')]

c.executemany('INSERT INTO philosopher (name, city) VALUES (?, ?)', guys)
db.commit()

#
# SELECT

c.execute('SELECT * FROM philosopher')
kant = c.fetchone()
print kant # this is tuple (1, 'Immanuel Kant', 'K')
print kant[0]

everybody = c.fetchall() # a list of tuples
for row in everybody:
print('ID: {0}, Name: {1}, City: {2}' .format(row[0], row[1], row[2]))

c.execute('SELECT * FROM philosopher WHERE city= (?)', ('Athens',)) # tuple here
athenians = c.fetchall()
print athenians