The json Module

The standard library’s json module supplies four key functions:

import collections, fileinput, json, dbm
word_pos = collections.defaultdict(list)
for line in fileinput.input():
    pos = fileinput.filename(), fileinput.filelineno()
    for word in line.split():
        word_pos[word].append(pos)
dbm_out = dbm.open('indexfilem', 'n')
for word in word_pos:
    dbm_out[word] = json.dumps(word_pos[word])
dbm_out.close()

import sys, json, dbm, linecache
dbm_in = dbm.open('indexfilem')
for word in sys.argv[1:]:
    if word not in dbm_in:
         print('Word {!r} not found in index file'.format(word),
               file=sys.stderr)
         continue
    places = json.loads(dbm_in[word])
    for fname, lineno in places:
        print('Word {!r} occurs in line {} of file {}:'.format(
               word,lineno,fname))
        print(linecache.getline(fname, lineno), end='') 

The pickle and cPickle Modules

The pickle and, in v2, cPickle modules supply factory functions, named Pickler and Unpickler, to generate objects that wrap file-like objects and supply Python-specific serialization mechanisms. Serializing and deserializing via these modules is also known as pickling and unpickling.

In v2 only, the difference between the modules is that, in pickle, Pickler and Unpickler are classes, so you can inherit from these classes to create customized serializer objects, overriding methods as needed. In cPickle, on the other hand, Pickler and Unpickler are factory functions that generate instances of non-subclassable types, not classes. Performance is much better with cPickle, but inheritance is not feasible. In the rest of this section, we’ll be talking about the module pickle, but everything applies to cPickle too. v3 only supplies pickle, which is quite fast and supplies Pickler and Unpickler as classes.

Serialization shares some of the issues of deep copying, covered in “The copy Module”. The pickle module deals with these issues in much the same way as the copy module does. Serialization, like deep copying, implies a recursive walk over a directed graph of references. pickle preserves the graph’s shape: when the same object is encountered more than once, the object is serialized only the first time, and other occurrences of the same object serialize references to that single value. pickle also correctly serializes graphs with reference cycles. However, this means that if a mutable object o is serialized more than once to the same Pickler instance p, any changes to o after the first serialization of o to p are not saved.

pickle can serialize with a legacy ASCII protocol or with one of a few compact binary ones. In v2, the ASCII protocol 0 is the default, for backward compatibility, but you should normally explicitly request binary protocol 2, the v2-supported protocol that’s most parsimonious of time and storage space. In v3, protocols range from 0 to 4, included; the default is 3, which is usually a reasonable choice, but you may explicitly specify protocol 2 (to ensure that your saved pickles can be loaded by v2 programs), or protocol 4, incompatible with earlier versions but with performance advantages for very large objects.

When you reload objects, pickle transparently recognizes and uses any protocol that the Python version you’re currently using supports.

pickle serializes classes and functions by name, not by value. pickle can therefore deserialize a class or function only by importing it from the same module where the class or function was found when pickle serialized it. In particular, pickle can normally serialize and deserialize classes and functions only if they are top-level names for their module (i.e., attributes of their module). For example, consider the following:

def adder(augend):
    def inner(addend, augend=augend): return addend+augend
    return inner
plus5 = adder(5)

This code binds a closure to name plus5 (as covered in “Nested functions and nested scopes”)—a nested function inner plus an appropriate outer scope. Therefore, trying to pickle plus5 raises a pickle.PicklingError exception (in v2; just AttributeError in v3): a function can be pickled only when it is top-level, and the function inner, whose closure is bound to the name plus5 in this code, is not top-level but rather nested inside the function adder. Similar issues apply to pickling nested functions and nested classes (i.e., classes that are not top-level).

import collections, fileinput, pickle, dbm
word_pos = collections.defaultdict(list)
for line in fileinput.input():
    pos = fileinput.filename(), fileinput.filelineno()
    for word in line.split():
        word_pos[word].append(pos)
dbm_out = dbm.open('indexfilep','n')
for word in word_pos:
    dbm_out[word] = pickle.dumps(word_pos[word],2)
dbm_out.close()

import sys, pickle, dbm, linecache
dbm_in = dbm.open('indexfilep')
for word in sys.argv[1:]:
    if word not in dbm_in:
         print('Word {!r} not found in index file'.format(word),
               file=sys.stderr)
         continue
    places = pickle.loads(dbm_in[word])
    for fname, lineno in places:
        print('Word {!r} occurs in line {} of file {}:'.format(
               word,lineno,fname))
        print(linecache.getline(fname, lineno), end='') 

>>> import pickle, copy_reg, marshal
>>> def marsh(x): return marshal.loads, (marshal.dumps(x),)
...
>>> c=compile('2+2','','eval')
>>> copy_reg.pickle(type(c), marsh)
>>> s=pickle.dumps(c, 2)
>>> cc=pickle.loads(s)
>>> print eval(cc)
4

The shelve Module

The shelve module orchestrates the modules pickle (or cPickle, in v2, when available), io (in v3; in v2, cStringIO when available, and otherwise StringIO), and dbm (and its underlying modules for access to DBM-like archive files, as discussed in “DBM Modules”; that’s in v3—anydbm in v2) in order to provide a simple, lightweight persistence mechanism.

shelve supplies a function open that is polymorphic to anydbm.open. The mapping s returned by shelve.open is less limited than the mapping a returned by anydbm.open. a’s keys and values must be strings. s’s keys must also be strings, but s’s values may be of any pickleable types. pickle customizations (copy_reg, __getnewargs__, __getstate__, and __setstate__) also apply to shelve, as shelve delegates serialization to pickle.

Beware of a subtle trap when you use shelve with mutable objects: when you operate on a mutable object held in a shelf, the changes don’t “take” unless you assign the changed object back to the same index. For example:

import shelve
s = shelve.open('data')
s['akey'] = list(range(4))
print(s['akey'])           # prints: [0, 1, 2, 3]
s['akey'].append(9)        # trying direct mutation
print(s['akey'])           # doesn't "take"; prints: [0, 1, 2, 3]

x = s['akey']              # fetch the object
x.append(9)                # perform mutation
s['akey'] = x              # key step: store the object back!
print(s['akey'])           # now it "takes", prints: [0, 1, 2, 3, 9]

You can finesse this issue by passing the named argument writeback=True when you call shelve.open, but beware: if you do pass that argument, you may seriously impair the performance of your program.

import collections, fileinput, shelve
word_pos = collections.defaultdict(list)
for line in fileinput.input():
    pos = fileinput.filename(), fileinput.filelineno()
    for word in line.split():
        word_pos[word].append(pos)
sh_out = shelve.open('indexfiles','n')
sh_out.update(word_pos)
sh_out.close()

import sys, shelve, linecache
sh_in = shelve.open('indexfiles')

for word in sys.argv[1:]:
    if word not in sh_in:
         print('Word {!r} not found in index file'.format(word),
               file=sys.stderr)
         continue
    places = sh_in[word]
    for fname, lineno in places:
        print('Word {!r} occurs in line {} of file {}:'.format(
               word,lineno,fname))
        print(linecache.getline(fname, lineno), end='')

The v3 dbm Package

v3’s dbm package supplies the following two top-level functions:

In addition to these two top-level functions, the v3 package dbm contains specific modules, such as ndbm, gnu, and dumb, which provide various implementations of DBM functionality, which you normally access only via the two top-level functions of the package dbm. Third-party packages can install further implementation modules in dbm.

The only implementation module of the dbm package that’s guaranteed to exist on all platforms is dumb. The dumb submodule of dbm has minimal DBM functionality and mediocre performance. dumb’s only advantage is that you can use it anywhere, since dumb does not rely on any library. You don’t normally import dbm.dumb: rather, import dbm, and let dbm.open supply the best DBM module available, defaulting to dumb if nothing else is available on the current Python installation. The only case in which you import dumb directly is the rare one in which you need to create a DBM-like file guaranteed to be readable from any Python installation. The dumb module supplies an open function polymorphic to dbm’s.

v2’s dbm modules

v2’s anydbm module is a generic interface to any other DBM module. anydbm supplies a single factory function open, equivalent to v3’s dbm.open.

v2’s whichdb module supplies a single function, whichdb, equivalent to v3’s dbm.whichdb.

DBM implementation modules, in v2, are top-level ones, named dbm, gdbm, dumbdbm, and so forth, and otherwise paralleling v3 dbm submodules.

Examples of DBM-Like File Use

DBM’s keyed access is suitable when your program needs to record persistently the equivalent of a Python dictionary, with strings as both keys and values. For example, suppose you need to analyze several text files, whose names are given as your program’s arguments, and record where each word appears in those files. In this case, the keys are words and, therefore, intrinsically strings. The data you need to record for each word is a list of (filename, linenumber) pairs. However, you can encode the data as a string in several ways—for example, by exploiting the fact that the path separator string os.pathsep (covered in “Path-String Attributes of the os Module”) does not normally appear in filenames. (More solid, general, and reliable approaches to the issue of encoding data as strings are covered with the same example in “Serialization”.) With this simplification, the program that records word positions in files might be as follows, in v3:

import collections, fileinput, os, dbm
word_pos = collections.defaultdict(list)
for line in fileinput.input():
    pos = '{}{}{}'.format(
        fileinput.filename(), os.pathsep, fileinput.filelineno())
    for word in line.split():
        word_pos[word].append(pos)
sep2 = os.pathsep * 2
with dbm.open('indexfile','n') as dbm_out:
    for word in word_pos:
        dbm_out[word] = sep2.join(word_pos[word])

We can read back the data stored to the DBM-like file indexfile in several ways. The following example accepts words as command-line arguments and prints the lines where the requested words appear, in v3:

import sys, os, dbm, linecache
dbm_in = dbm.open('indexfile')
sep = os.pathsep
sep2 = sep * 2
for word in sys.argv[1:]:
    if word not in dbm_in:
         print('Word {!r} not found in index file'.format(word),
               file=sys.stderr)
         continue
    places = dbm_in[word].split(sep2)
    for place in places:
        fname, lineno = place.split(sep)
        print('Word {!r} occurs in line {} of file {}:'.format(
               word,lineno,fname))
        print(linecache.getline(fname, lineno), end='')

In v2, in both examples, import and use the module anydbm instead of the package dbm.

Exception Classes

A DBAPI-compliant module supplies the exception classes Warning, Error, and several subclasses of Error. Warning indicates anomalies such as data truncation on insertion. Error’s subclasses indicate various kinds of errors that your program can encounter when dealing with the DB and the DBAPI-compliant module that interfaces to it. Generally, your code uses a statement of the form:

try:
    ...
except module.Error as err:
    ...

to trap all DB-related errors that you need to handle without terminating.

Thread Safety

When a DBAPI-compliant module has a threadsafety attribute greater than 0, the module is asserting some level of thread safety for DB interfacing. Rather than relying on this, it’s usually safer, and always more portable, to ensure that a single thread has exclusive access to any given external resource, such as a DB, as outlined in “Threaded Program Architecture”.

Parameter Style

A DBAPI-compliant module has an attribute called paramstyle to identify the style of markers used as placeholders for parameters. Insert such markers in SQL statement strings that you pass to methods of Cursor instances, such as the method execute, to use runtime-determined parameter values. Say, for example, that you need to fetch the rows of DB table ATABLE where field AFIELD equals the current value of Python variable x. Assuming the cursor instance is named c, you could theoretically (but ill-advisedly) perform this task with Python’s string formatting:

c.execute('SELECT * FROM ATABLE WHERE AFIELD={!r}'.format(x))

For example, when a module’s paramstyle attribute (described next) is 'qmark', express the preceding query as:

c.execute('SELECT * FROM ATABLE WHERE AFIELD=?', (some_value,))

The read-only string attribute paramstyle tells your program how it should use parameter substitution with that module. The possible values of paramstyle are:

format

The marker is %s, as in old-style string formatting (always with s: never use other type indicator letters, whatever the data’s type). A query looks like:

c.execute('SELECT * FROM ATABLE WHERE AFIELD=%s', (some_value,))

named

The marker is :name, and parameters are named. A query looks like:

c.execute('SELECT * FROM ATABLE WHERE AFIELD=:x', {'x':some_value})

numeric

The marker is :n, giving the parameter’s number, 1 and up. A query looks like:

c.execute('SELECT * FROM ATABLE WHERE AFIELD=:1', (some_value,))

pyformat

The marker is %(name)s, and parameters are named. Always use s: never use other type indicator letters, whatever the data’s type. A query looks like:

c.execute('SELECT * FROM ATABLE WHERE AFIELD=%(x)s', {'x':some_value})

qmark

The marker is ?. A query looks like:

c.execute('SELECT * FROM ATABLE WHERE AFIELD=?', (x,))

When parameters are named (i.e., when paramstyle is 'pyformat' or 'named'), the second argument of the execute method is a mapping. Otherwise, the second argument is a sequence.

Factory Functions

Parameters passed to the DB via placeholders must typically be of the right type: this means Python numbers (integers or floating-point values), strings (bytes or Unicode), and None to represent SQL NULL. There is no type universally used to represent dates, times, and binary large objects (BLOBs). A DBAPI-compliant module supplies factory functions to build such objects. The types used for this purpose by most DBAPI-compliant modules are those supplied by the modules datetime or mxDateTime (covered in Chapter 12), and strings or buffer types for BLOBs. The factory functions specified by the DBAPI are as follows:

Type Description Attributes

A Cursor instance’s attribute description describes the types and other characteristics of each column of the SELECT query you last executed on that cursor. Each column’s type (the second item of the tuple describing the column) equals one of the following attributes of the DBAPI-compliant module:

A cursor’s description, and in particular each column’s type, is mostly useful for introspection about the DB your program is working with. Such introspection can help you write general modules and work with tables using different schemas, including schemas that may not be known at the time you are writing your code.

The connect Function

A DBAPI-compliant module’s connect function accepts arguments that depend on the kind of DB and the specific module involved. The DBAPI standard recommends that connect accept named arguments. In particular, connect should at least accept optional arguments with the following names:

Connection Objects

A DBAPI-compliant module’s connect function returns an object x that is an instance of the class Connection. x supplies the following methods:

Cursor Objects

A Connection instance provides a cursor method that returns an object c that is an instance of the class Cursor. A SQL cursor represents the set of results of a query and lets you work with the records in that set, in sequence, one at a time. A cursor as modeled by the DBAPI is a richer concept, since it’s the only way your program executes SQL queries in the first place. On the other hand, a DBAPI cursor allows you only to advance in the sequence of results (some relational DBs, but not all, also provide higher-functionality cursors that are able to go backward as well as forward), and does not support the SQL clause WHERE CURRENT OF CURSOR. These limitations of DBAPI cursors enable DBAPI-compliant modules to easily provide DBAPI cursors even on RDBMSes that provide no real SQL cursors at all. An instance of the class Cursor c supplies many attributes and methods; the most frequently used ones are the following:

name, typecode, displaysize, internalsize, precision, 
scale, nullable

c.executemany('UPDATE atable SET x=? '
              'WHERE y=?',(12,23),(23,34))

c.execute('UPDATE atable SET x=12 WHERE y=23')

c.execute('UPDATE atable SET x=23 WHERE y=34')

DBAPI-Compliant Modules

Whatever relational DB you want to use, there’s at least one (often more than one) Python DBAPI-compliant module downloadable from the Internet. There are so many DBs and modules, and the set of possibilities is so constantly shifting, that we couldn’t possibly list them all, nor (importantly) could we maintain the list over time. Rather, we recommend you start from the community-maintained wiki page, which has at least a fighting chance to be complete and up-to-date at any time.

What follows is therefore only a very short, time-specific list of a very few DBAPI-compliant modules that, at the time of writing, are very popular themselves, and interface to very popular open source DBs.

ODBC

Open DataBase Connectivity (ODBC) is a standard way to connect to many different DBs, including a few not supported by other DBAPI-compliant modules. For an ODBC-compliant DBAPI-compliant module with a liberal open source license, use pyodbc; for a commercially supported one, mxODBC.

MySQL

MySQL is a popular open source RDBMS, currently owned by Oracle. Oracle’s own “official” DBAPI-compliant interface to it is MySQL Connector/Python.

PostgreSQL

PostgreSQL is an excellent open source RDBMS. The most popular DBAPI-compliant interface to it is psycopg2.

SQLite

SQLite is “a self-contained, server-less, zero-configuration, transactional SQL database engine,” which is the most widely deployed DB engine in the world—it’s a C-coded library that implements a DB within a single file, or even in memory for sufficiently small and transient cases. Python’s standard library supplies the package sqlite3, which is a DBAPI-compliant interface to SQLite.

SQLite has rich advanced functionality, with many options you can choose; sqlite3 offers access to much of that functionality, plus further possibilities to make interoperation between your Python code and the underlying DB even smoother and more natural. In this book, we don’t cover every nook and cranny of these two powerful software systems; we focus on the subset that is most commonly used and most useful. For a great level of detail, including examples and tips about best practices, see SQLite’s documentation and sqlite3’s online documentation. If you want a book on the subject, we recommend O’Reilly’s Using SQLite.

Package sqlite3 supplies the following functions:

In addition, sqlite3 supplies the classes Connection, Cursor, and Row. Each can be subclassed for further customization; however, this is an advanced issue that we do not cover further in this book. The Cursor class is a standard DBAPI cursor class, except for an extra convenience method executescript accepting a single argument, a string of multiple statements separated by ; (no parameters). The other two classes are covered in the following sections.

class sqlite3.Connection

In addition to the methods common to all Connection classes of DBAPI-compliant modules, covered in “Connection Objects”, sqlite3.Connection supplies the following methods and other attributes:

class sqlite3.Row

sqlite3 supplies the class Row, which is mostly like a tuple but also supplies the method keys(), returning a list of column names, and supports indexing by a column name as an extra alternative to indexing by column number.

import fileinput, sqlite3
connect = sqlite3.connect('database.db')
cursor = connect.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS Words '
               '(Word TEXT, File TEXT, Line INT)')
for line in fileinput.input():
    f, l = fileinput.filename(), fileinput.filelineno()
    cursor.executemany('INSERT INTO Words VALUES (:w, :f, :l)',
        [{'w':w, 'f':f, 'l':l} for w in line.split()])
connect.commit()
connect.close()

import sys, sqlite3, linecache
connect = sqlite3.connect('database.db')
cursor = connect.cursor()
for word in sys.argv[1:]:
    cursor.execute('SELECT File, Line FROM Words '
                   'WHERE Word=?', [word])
    places = cursor.fetchall()
    if not places:
         print('Word {!r} not found in index file'.format(word),
               file=sys.stderr)
         continue
    for fname, lineno in places:
        print('Word {!r} occurs in line {} of file {}:'.format(
               word,lineno,fname))
        print(linecache.getline(fname, lineno), end='')