# 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