>>> 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, as-
suming 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.
Building Record Dictionaries
Now that we’ve seen the basics in action, let’s move on and apply them to a few larger
tasks. The SQL API defines query results to be sequences of sequences. One of the more
common features that people seem to miss from the API is the ability to get records
back as something more structured—a dictionary or class instance, for example, with
keys or attributes giving column names. The ORMs we’ll meet at the end of this chapter
map to class instances, but because this is Python, it’s easy to code this kind of trans-
formation in other ways. Moreover, the API already gives us the tools we need.
Using table descriptions
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):
SQL Database Interfaces| 1339