Programming Python (176 page)

Cursor objects
also are used to submit SQL update statements to the
database
server—
updates,
deletes, and inserts. We’ve already seen the
insert
statement at work. Let’s start a new
session to perform some other kinds of updates; we begin with the same
data we had in the prior session:

C:\...\PP4E\Dbase\Sql>
python
>>>
import sqlite3
>>>
conn = sqlite3.connect('dbase1')
>>>
curs = conn.cursor()
>>>
curs.execute('select * from people')
>>>
curs.fetchall()
[('Bob', 'dev', 5000), ('Sue', 'mus', 70000), ('Ann', 'mus', 60000), ('Tom', 'mgr',
100000), ('Kim', 'adm', 30000), ('pat', 'dev', 90000)]

The SQL
update
statement
changes records—the following changes three records’ pay column values
to
65000
(Bob, Ann, and Kim),
because their pay was no more than $60,000. As usual, the cursor’s
rowcount
gives the number of
records changed:

>>>
curs.execute('update people set pay=? where pay <= ?', [65000, 60000])
>>>
curs.rowcount
3
>>>
curs.execute('select * from people')
>>>
curs.fetchall()
[('Bob', 'dev', 65000), ('Sue', 'mus', 70000), ('Ann', 'mus', 65000), ('Tom', 'mgr',
100000), ('Kim', 'adm', 65000), ('pat', 'dev', 90000)]

The SQL
delete
statement
removes records, optionally according to a condition (to
delete all records, omit the condition). In the following, we delete
Bob’s record, as well as any record with a pay that is at least
$90,000:

>>>
curs.execute('delete from people where name = ?', ['Bob'])
>>>
curs.execute('delete from people where pay >= ?',(90000,))
>>>
curs.execute('select * from people')
>>>
curs.fetchall()
[('Sue', 'mus', 70000), ('Ann', 'mus', 65000), ('Kim', 'adm', 65000)]
>>>
conn.commit()

Finally, remember to commit your changes to the database before
exiting Python, assuming you wish to keep them. Without a commit, a
connection rollback or close call, as well as the connection’s
__del__
deletion method, will back
out uncommitted changes. Connection objects are automatically closed
if they are still open when they are garbage collected, which in turn
triggers a
__del__
and a rollback;
garbage collection happens automatically on program exit, if not
sooner.

For example, after a
query
execute
call,
the DB API specifies that the cursor’s
description
attribute gives the names
and (for some databases) types of the columns in the result table. To
see how, let’s continue with the database in the state in which we
left it in the prior section:

>>>
curs.execute('select * from people')
>>>
curs.description
(('name', None, None, None, None, None, None), ('job', None, None, None, None, None,
None), ('pay', None, None, None, None, None, None))
>>>
curs.fetchall()
[('Sue', 'mus', 70000), ('Ann', 'mus', 65000), ('Kim', 'adm', 65000)]

Formally, the description is a sequence of column-description
sequences, each of the following form. See the DB API for more on the
meaning of the type code slot—it maps to objects at the top level of
the database interface module, but the
sqlite3
module implements only the name
component:

(name, type_code, display_size, internal_size, precision, scale, null_ok)

Now, we can use this metadata anytime we want to label the
columns—for instance, in a formatted records display (be sure to
regenerate a query result first, since the prior result has been
fetched):

>>>
curs.execute('select * from people')
>>>
colnames = [desc[0] for desc in curs.description]
>>>
colnames
['name', 'job', 'pay']
>>>
for row in curs.fetchall():
...
for name, value in zip(colnames, row):
...
print(name, '\t=>', value)
...
print()
...
name    => Sue
job     => mus
pay     => 70000
name    => Ann
job     => mus
pay     => 65000
name    => Kim
job     => adm
pay     => 65000

Notice how a tab character is used to try to make this output
align; a better approach might be to determine the maximum field name
length (we’ll see how in a later
example
).

It’s a minor extension of our formatted display code to create a
dictionary for each record, with field names for keys—we just need to
fill in the dictionary as we go:

>>>
curs.execute('select * from people')
>>>
colnames = [desc[0] for desc in curs.description]
>>>
rowdicts = []
>>>
for row in curs.fetchall():
...
newdict = {}
...
for name, val in zip(colnames, row):
...
newdict[name] = val
...
rowdicts.append(newdict)
...
>>>
for row in rowdicts: print(row)
...
{'pay': 70000, 'job': 'mus', 'name': 'Sue'}
{'pay': 65000, 'job': 'mus', 'name': 'Ann'}
{'pay': 65000, 'job': 'adm', 'name': 'Kim'}

Because this is Python, though, there are more powerful ways to
build up these record dictionaries. For instance, the dictionary
constructor call accepts the zipped name/value pairs to fill out the
dictionaries for us:

>>>
curs.execute('select * from people')
>>>
colnames = [desc[0] for desc in curs.description]
>>>
rowdicts = []
>>>
for row in curs.fetchall():
...
rowdicts.append( dict(zip(colnames, row)) )
...
>>>
rowdicts[0]
{'pay': 70000, 'job': 'mus', 'name': 'Sue'}

And finally, a list comprehension will do the job of collecting
the dictionaries into a
list—
not
only is this less to type, but it probably runs quicker than the
original version:

>>>
curs.execute('select * from people')
>>>
colnames = [desc[0] for desc in curs.description]
>>>
rowdicts = [dict(zip(colnames, row)) for row in curs.fetchall()]
>>>
rowdicts[0]
{'pay': 70000, 'job': 'mus', 'name': 'Sue'}

One of the things we lose when moving to dictionaries is record
field order—if you look back at the raw result of
fetchall
, you’ll notice that record fields
are in the name, job, and pay order in which they were stored. Our
dictionary’s fields come back in the pseudorandom order of Python
mappings. As long as we fetch fields by key, this is irrelevant to our
script. Tables still maintain their order, and dictionary construction
works fine because the description result tuple is in the same order
as the fields in row tuples returned by queries.

We’ll leave the task of translating record tuples into class
instances as a suggested exercise, except for two hints: Python’s
standard library
collections
module
implements more exotic data types, such as named tuples and ordered
dictionaries; and we can access fields as attributes rather than as
keys, by simply creating an empty class instance and assigning to
attributes with the Python
setattr
function. Classes would also provide a natural place to code
inheritable tools such as standard display methods. In fact, this is
part of the utility that the upcoming ORMs can provide for us.

Up to this point, we’ve
essentially used Python as a command-line SQL client—our
queries have been typed and run interactively. All the kinds of code
we’ve run, though, can be used as the basis of database access in
script files. Working interactively requires retyping things such as
multiline loops, which can become tedious. With scripts, we can
automate our work.

To demonstrate, let’s make the last section’s prior example into
a utility module—
Example 17-4
is a reusable
module that knows how to translate the result of a query from row
tuples to row dictionaries.

"""
convert list of row tuples to list of row dicts with field name keys
this is not a command-line utility: hardcoded self-test if run
"""
def makedicts(cursor, query, params=()):
cursor.execute(query, params)
colnames = [desc[0] for desc in cursor.description]
rowdicts = [dict(zip(colnames, row)) for row in cursor.fetchall()]
return rowdicts
if __name__ == '__main__':   # self test
import sqlite3
conn = sqlite3.connect('dbase1')
cursor = conn.cursor()
query  = 'select name, pay from people where pay < ?'
lowpay = makedicts(cursor, query, [70000])
for rec in lowpay: print(rec)

As usual, we can run this file from the system command line as a
script to invoke its self-test code:

...\PP4E\Dbase\Sql>
makedicts.py
{'pay': 65000, 'name': 'Ann'}
{'pay': 65000, 'name': 'Kim'}

Or we can import it as a module and call its function from
another context, like the interactive prompt. Because it is a module,
it has become a reusable database tool:

...\PP4E\Dbase\Sql>
python
>>>
from makedicts import makedicts
>>>
from sqlite3 import connect
>>>
conn = connect('dbase1')
>>>
curs = conn.cursor()
>>>
curs.execute('select * from people')
>>>
curs.fetchall()
[('Sue', 'mus', 70000), ('Ann', 'mus', 65000), ('Kim', 'adm', 65000)]
>>>
rows = makedicts(curs, "select name from people where job = 'mus'")
>>>
rows
[{'name': 'Sue'}, {'name': 'Ann'}]

Our utility handles arbitrarily complex queries—they are simply
passed through the DB API to the database server or library. The
order by
clause here sorts the
result on the name
field:

>>>
query = 'select name, pay from people where job = ? order by name'
>>>
musicians = makedicts(curs, query, ['mus'])
>>>
for row in musicians: print(row)
...
{'pay': 65000, 'name': 'Ann'}
{'pay': 70000, 'name': 'Sue'}

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