Example 17-5. PP4E\Dbase\Sql\testdb.py
from sqlite3 import connect
conn = connect('dbase1')
curs = conn.cursor()
try:
curs.execute('drop table people')
except:
pass # did not exist
curs.execute('create table people (name char(30), job char(10), pay int(4))')
curs.execute('insert into people values (?, ?, ?)', ('Bob', 'dev', 50000))
curs.execute('insert into people values (?, ?, ?)', ('Sue', 'dev', 60000))
curs.execute('select * from people')
for row in curs.fetchall():
print(row)
curs.execute('select from people')
colnames = [desc[0] for desc in curs.description]
while True:
print('-' 30)
row = curs.fetchone()
if not row: break
for (name, value) in zip(colnames, row):
print('%s => %s' % (name, value))
conn.commit() # save inserted records
Refer to prior sections in this tutorial if any of the code in this script is unclear. When
run, it creates a two-record database and lists its content to the standard output stream:
C:\...\PP4E\Dbase\Sql> testdb.py
('Bob', 'dev', 50000)
('Sue', 'dev', 60000)
------------------------------
name => Bob
job => dev
pay => 50000
------------------------------
name => Sue
job => dev
pay => 60000
------------------------------As is, this example is really just meant to demonstrate the database API. It hardcodes
database names, and it re-creates the database from scratch each time. We could turn
this code into generally useful tools by refactoring it into reusable parts, as we’ll see
later in this section. First, though, let’s explore techniques for getting data into our
databases.
SQL Database Interfaces| 1343