Programming Python (175 page)

Concurrent updates

You don’t need to manually lock files to avoid data corruption
if there are potentially many concurrent writers, the way you would
for shelves.

Transaction commit and rollback

If your program crashes, your changes are not retained unless
you explicitly commit them to the database.

Automatic updates for some types of in-memory object
changes

Objects in ZODB derived from a persistence superclass are
smart enough to know the database must be updated when an attribute
is assigned.

Automatic caching of objects

Objects are cached in memory for efficiency and are
automatically removed from the cache when they haven’t been
used.

Platform-independent storage

Because ZODB stores your database in a single flat file with
large-file support, it is immune to the potential size constraints
and DBM filesystem format differences of shelves. As we saw earlier
in this chapter, a shelve created on Windows using
bsddb
may not be accessible to a script
running with
gdbm
on
Linux.

• Accessing the database requires a small amount of extra
  boilerplate code to interface with ZODB—it’s not a simple open
  call.
  

• Classes are derived from a persistence superclass if you want
  them to take advantage of automatic updates on changes—persistent
  classes are generally not as
  completely
  independent of the database
  as in shelves, though they can be.
  

Unfortunately, as I write
this edition in June 2010, ZODB is not yet available for
Python 3.X, the version used in this book. Because of that, the prior
edition’s Python 2.X examples and material have been removed from this
section. However, in deference to Python 2.X users, as well as 3.X
readers of some bright future where a 3.X-base ZODB has materialized,
I’ve made the prior edition’s ZODB materials and examples available in
this edition’s examples package.

See the
Preface
for details on the examples
package, and see these locations within it for more on ZODB:

C:\...\Dbase\Zodb-2.x                   # ZODB examples code third edition
C:\...\Dbase\Zodb-2.x\Documentaion      # The 3rd Edition's ZODB tutorial

Although I cannot predict the future, ZODB will likely become
available for Python 3.X eventually. In the absence of this, other
Python-based OODBs may offer additional 3.X options.

To give you a brief sample of ZODB’s flavor, though, here’s a
quick spin through its operation in Python 2.X. Once we’ve installed a
compatible ZODB, we begin by first creating a database:

...\PP4E\Dbase\Zodb-2.x>
python
>>>
from ZODB import FileStorage, DB
>>>
storage = FileStorage.FileStorage(r'C:\temp\mydb.fs')
>>>
db = DB(storage)
>>>
connection = db.open()
>>>
root = connection.root()

This is mostly standard “boilerplate” code for connecting to a
ZODB database: we import its tools, create a
FileStorage
and a
DB
from it, and then open the database and
create the
root object
. The root object is the
persistent dictionary in which objects are stored.
FileStorage
is an object that maps the
database to a flat file. Other storage interface options, such as
relational database-based storage, are also possible.

Adding objects to a ZODB database is as simple as in shelves.
Almost any Python object will do, including tuples, lists, dictionaries,
class instances, and nested combinations thereof. As for
shelve
, simply assign your objects to a key in
the database root object to make them persistent:

>>>
object1 = (1, 'spam', 4, 'YOU')
>>>
object2 = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
>>>
object3 = {'name': ['Bob', 'Doe'],
'age':  42,
'job':  ('dev', 'mgr')}
>>>
root['mystr']   = 'spam' * 3
>>>
root['mytuple'] = object1
>>>
root['mylist']  = object2
>>>
root['mydict']  = object3
>>>
root['mylist']
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]

Because ZODB supports transaction rollbacks, you must commit your
changes to the database to make them permanent. Ultimately, this
transfers the pickled representation of your objects to the underlying
file storage medium—here, three files that include the name of the file
we gave when opening:

>>>
import transaction
>>>
transaction.commit()
>>>
storage.close()
...\PP4E\Dbase\Zodb-2.x>
dir /B c:\temp\mydb*
mydb.fs
mydb.fs.index
mydb.fs.tmp

Without the final commit in this session, none of the changes we
made would be saved. This is what we want in general—if a program aborts
in the middle of an update task, none of the partially complete work it
has done is retained. In fact, ZODB supports general database undo
operations.

Pulling persistent objects back from ZODB in another session or
program is just as straightforward: reopen the database as before and
index the root to fetch objects back into memory. Like shelves, the
database root supports dictionary interfaces—it may be indexed, has
dictionary methods and a length, and so on:

...\PP4E\Dbase\Zodb-2.x>
python
>>>
from ZODB import FileStorage, DB
>>>
storage = FileStorage.FileStorage(r'C:\temp\mydb.fs')
>>>
db = DB(storage)
>>>
connection = db.open()
>>>
root = connection.root()
# connect
>>>
len(root), root.keys()
# size, index
(4 ['mylist', 'mystr', 'mytuple', 'mydict'])
>>>
root['mylist']
# fetch objects
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]
>>>
root['mydict']
{'job': ('dev', 'mgr'), 'age': 42, 'name': ['Bob', 'Doe']}
>>>
root['mydict']['name'][-1]
# Bob's last name
'Doe'

Because the database root looks just like a dictionary, we can
process it with normal dictionary code—stepping through the keys list to
scan record by record, for instance:

>>>
for key in root.keys():
print('%s => %s' % (key.ljust(10), root[key]))
mylist     => [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
mystr      => spamspamspam
mytuple    => (1, 'spam', 4, 'YOU')
mydict     => {'job': ('dev', 'mgr'), 'age': 42, 'name': ['Bob', 'Doe']}

Also like pickling and shelves, ZODB supports storage and
retrieval of class instance objects, though they must inherit from a
superclass which provides required protocol and intercepts attribute
changes in order to flush them to disk automatically:

from persistent import Persistent
class Person(Persistent):
def __init__(self, name, job=None, rate=0):
self.name = name
self.job  = job
self.rate = rate
def changeRate(self, newrate):
self.rate = newrate                   # automatically updates database

When changing ZODB persistent class instances, in-memory attribute
changes are
automatically
written
back to the database. Other types of changes, such as in-place
appends
and key assignments, still require
reassignment to the original key as in shelves to force the change to be
written to disk (built-in list and dictionary objects do not know that
they are persistent).

Because ZODB does not yet work with Python 3.X, that’s as much as
we can say about it in this book. For more details, search for ZODB and
Zope resources on the Web, and see the examples package resources listed
earlier. Here, let’s move on to see how Python programs can make use of
a very different sort of database interface—relational databases and
SQL.

• They store data in related tables of columns (rather than in
  persistent dictionaries of arbitrarily structured persistent Python
  objects).
  

• They support the SQL query language for accessing data and
  exploiting relationships among it (instead of Python object
  traversals).
  

Like ZODB, and
unlike the
pickle
and
shelve
persistence modules presented
earlier, most SQL databases are optional extensions that are not part of
Python itself. SQLite is the only relational database package that comes
with Python. Moreover, you need to know SQL to fully understand their
interfaces. Because we don’t have space to teach SQL in this text, this
section gives a brief overview of the API; please consult other SQL
references and the database API resources mentioned in the next section
for more details that we’ll skip here.

The good news is that you can access SQL databases from Python,
through a straightforward and portable model. The Python database API
specification defines an interface for communicating with underlying
database systems from Python scripts. Vendor-specific database
interfaces for Python may or may not conform to this API completely, but
all database extensions for Python in common use are minor variations on
a theme. Under the database API, SQL databases in Python are grounded on
three core
concepts
:

Beyond this, the API defines a standard set of database exception
types, special database type object constructors, and informational
top-level calls including thread safety and replacement style
checks.

For instance, to establish a database connection under the Python
API-compliant Oracle interface, install the commonly used Python Oracle
extension module as well as Oracle itself, and then run a statement of
this form:

connobj = connect("user/[email protected]")

This call’s arguments may vary per database and vendor (e.g., some
may require network details or a local file’s name), but they generally
contain what you provide to log in to your database system. Once you
have a connection object, there a variety of things you can do with it,
including:

connobj.close()
close connection now (not at object __del__ time)
connobj.commit()
commit any pending transactions to the database
connobj.rollback()
roll database back to start of pending transactions

But one of the most useful things to do with a connection object
is to generate a cursor object:

cursobj = connobj.cursor()
return a new cursor object for running SQL

Cursor objects have a set of methods, too (e.g.,
close
to close the cursor before its
destructor runs, and
callproc
to call
a stored procedure), but the most important may be this one:

cursobj.execute(
sqlstring
[,
parameters
])
run SQL query or command string

Parameters are passed in as a sequence or mapping of values, and
are substituted into the SQL statement string according to the interface
module’s replacement target conventions. The
execute
method can be used to run a variety of
SQL statement strings:

After running an SQL statement, the cursor’s
rowcount
attribute gives the number of rows
changed (for DML changes) or fetched (for DQL queries), and the cursor’s
description
attribute gives column
names and types after a query;
execute
also returns the number of rows
affected or fetched in the most vendor interfaces. For DQL query
statements, you must call one of the
fetch
methods to complete the
operation:

tuple       = cursobj.fetchone()
fetch next row of a query result
listoftuple = cursobj.fetchmany([
size
])
fetch next set of rows of query result
listoftuple = cursobj.fetchall()
fetch all remaining rows of the result

And once you’ve received fetch method results, table information
is processed using normal Python sequence operations; for example, you
can step through the tuples in a
fetchall
result list with a simple
for
loop or comprehension expression. Most
Python database interfaces also allow you to provide values to be passed
to SQL statement strings, by providing targets and a tuple of
parameters. For instance:

query = 'SELECT name, shoesize FROM spam WHERE job = ? AND age = ?'
cursobj.execute(query, (value1, value2))
results = cursobj.fetchall()
for row in results:
...

In this event, the database interface utilizes prepared statements
(an optimization and convenience) and correctly passes the parameters to
the database regardless of their Python types. The notation used to code
targets in the query string may vary in some database interfaces (e.g.,
:p1
and
:p2
or two
%s
, rather than the two
?
s used by the Oracle interface); in any
event, this is not the same as Python’s
%
string formatting operator, as it sidesteps
security issues along the way.

Finally, if your database supports stored procedures, you can call
them with the
callproc
method or by
passing an SQL
CALL
or
EXEC
statement string to the
execute
method.
callproc
may generate a result table retrieved
with a
fetch
variant, and returns a
modified copy of the input sequence—input parameters are left untouched,
and output and input/output parameters are replaced with possibly new
values. Additional API features, including support for database blobs
(roughly, with sized results), is described in the API’s documentation.
For now, let’s move on to do some real SQL processing in
Python.

We don’t have
space to provide an exhaustive reference for the database
API in this book. To sample the flavor of the interface, though, let’s
step through a few simple examples. We’ll use the SQLite database system
for this tutorial. SQLite is a standard part of Python itself, which you
can reasonably expect to be available in all Python installations.
Although SQLite implements a complete relational database system, it
takes the form of an in-process library instead of a server. This
generally makes it better suited for program storage than for
enterprise-level data needs.

Thanks to Python’s portable database API, though, other popular
database packages such as PostgreSQL, MySQL, and Oracle are used almost
identically; the initial call to log in to the database will be all that
normally requires different argument values for scripts that use
standard SQL code. Because of this, we can use the SQLite system both as
a prototyping tool in applications development and as an easy way to get
started with the Python SQL database API in this book.

C:\...\PP4E\Dbase\Sql>
python
>>>
import sqlite3
>>>
conn = sqlite3.connect('dbase1')
# use a full path for files elsewhere

>>>
curs = conn.cursor()
>>>>
>>>
tblcmd = 'create table people (name char(30), job char(10), pay int(4))'
>>>
curs.execute(tblcmd)

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'

>>>
curs.executemany('insert into people values (?, ?, ?)',
...
[ ('Sue', 'mus', '70000'),
...
('Ann', 'mus', '60000')])
>>>
curs.rowcount
2

>>>
rows = [['Tom', 'mgr', 100000],
...
['Kim', 'adm', 30000],
...
['pat', 'dev', 90000]]
>>>
for row in rows:
...
curs.execute('insert into people values (? , ?, ?)', row)
...
>>>
conn.commit()

>>>
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)]

>>>
curs.execute('select * from people')
>>>
for row in curs.fetchall():
...
print(row)
...
('Bob', 'dev', 5000)
('Sue', 'mus', 70000)
('Ann', 'mus', 60000)
('Tom', 'mgr', 100000)
('Kim', 'adm', 30000)
('pat', 'dev', 90000)

>>>
curs.execute('select * from people')
>>>
for (name, job, pay) in curs.fetchall():
...
print(name, ':', pay)
...
Bob : 5000
Sue : 70000
Ann : 60000
Tom : 100000
Kim : 30000
pat : 90000

>>>
curs.execute('select name from people')
>>>
names = curs.fetchall()
>>>
names
[('Bob',), ('Sue',), ('Ann',), ('Tom',), ('Kim',), ('pat',)]

>>>
curs.execute('select * from people')
>>>
names = [rec[0] for rec in curs.fetchall()]
>>>
names
['Bob', 'Sue', 'Ann', 'Tom', 'Kim', 'pat']

>>>
curs.execute('select * from people')
>>>
while True:
...
row = curs.fetchone()
...
if not row: break
...
print(row)
...
('Bob', 'dev', 5000)
('Sue', 'mus', 70000)
('Ann', 'mus', 60000)
('Tom', 'mgr', 100000)
('Kim', 'adm', 30000)
('pat', 'dev', 90000)

>>>
curs.execute('select * from people')
>>>
while True:
...
rows = curs.fetchmany()
# size=N optional argument
...
if not rows: break
...
for row in rows:
...
print(row)
...
('Bob', 'dev', 5000)
('Sue', 'mus', 70000)
('Ann', 'mus', 60000)
('Tom', 'mgr', 100000)
('Kim', 'adm', 30000)
('pat', 'dev', 90000)

>>>
curs.fetchone()
>>>
curs.fetchmany()
[]
>>>
curs.fetchall()
[]

>>>
curs.execute('select name, job from people where pay > 60000')
>>>
curs.fetchall()
[('Sue', 'mus'), ('Tom', 'mgr'), ('pat', 'dev')]

>>>
query = 'select name, job from people where pay >= ? order by name'
>>>
curs.execute(query, [60000])
>>>
for row in curs.fetchall(): print(row)
...
('Ann', 'mus')
('Sue', 'mus')
('Tom', 'mgr')
('pat', 'dev')