As long as a script sticks to using standard SQL code, the connect call’s arguments are
usually the only thing that can vary across different database systems. For example, in
the MySQL interface this call accepts a network host’s domain name, user name, and
password, passed as keyword arguments instead, and the Oracle example sketched
earlier expects a more specific sting syntax. Once we’ve gotten past this platform-
specific call, though, the rest of the API is largely database neutral.
Making databases and tables
Next, let’s make a cursor for submitting SQL statements to the database server, and
submit one to create a first table:
>>> curs = conn.cursor()
>>>>
>>> tblcmd = 'create table people (name char(30), job char(10), pay int(4))'
>>> curs.execute(tblcmd)The last command here creates the table called “people” within the database; the name,
job, and pay information specifies the columns in this table, as well as their datatypes,
using a “type(size)” syntax—two strings and an integer. Datatypes can be more so-
phisticated than ours, but we’ll ignore such details here (see SQL references). In SQLite,
the file is the database, so there’s no notion of creating or using a specific database
within it, as there is in some systems. At this point, there is a simple flat file in the
current working directory named data1, which contains binary data and contains our
people database table.
Adding records
So far, we’ve logged in (which just means opening a local file in SQLite) and created a
table. Next let’s start a new Python session and create some records. There are three
basic statement-based approaches we can use here: inserting one row at a time or in-
serting multiple rows with a single call statement or a Python loop. Here is the simple
case (I’m omitting some call return values here if they are irrelevant to the story):
C:\...\PP4E\Dbase\Sql> python
>>> import sqlite3
>>> conn = sqlite3.connect('dbase1')
>>> curs = conn.cursor()
>>> curs.execute('insert into people values (?, ?, ?)', ('Bob', 'dev', 5000))
>>> curs.rowcount
1
>>> sqlite3.paramstyle
'qmark'Create a cursor object to submit SQL statements to the database server as before. The
SQL insert command adds a single row to the table. After an execute call, the cursor’s
rowcount attribute gives the number of rows produced or affected by the last statement
run. This is also available as the return value of an execute call in some database inter-
face modules, but this is not defined in the database API specification, and isn’t
1334 | Chapter 17: Databases and Persistence