1   Introduction

PyDO is an ORM (Object-Relational Mapper) database access library for Python. This document describes PyDO version 2.0 and later only.

2   Acknowledgements

PyDO was originally written by Drew Csillag in 2000, and released under the GPL license in 2001 as part of SkunkWeb. Several developers have contributed to the codebase since then (see ACKNOWLEDGEMENTS in the source distribution). Jacob Smullyan is responsible for work on the 2.0 series, but while errors and misfeatures are attributable to him, the fundamental design remains Csillag's.

3   Overview

from pydo import *

PyDO's basic strategy is to let you define a PyDO subclass for every table in a database that you wish to represent. Each PyDO instance contains the data for one row in a database table. As PyDO is a dict subclass, you can access this data by key, and, if the class attribute use_attributes is true (the default) also by attribute. In either case, the key or attribute name is the name of the database column:

>>> MyPyDOInstance.title
'Fustian Wonders'
>>> MyPyDOInstance['title']
'Fustian Wonders'

If you have column names that are Python keywords (such as "pass", "class", etc.) a warning will be raised when the class is defined and an attempt at attribute access of that field will give rise to a SyntaxError, but you'll still be able to access it dictionary-style.

Instances are normally obtained, not by directly invoking the PyDO class's constructor, but by calling one of various class methods, discussed below, that return single instances or lists thereof.

PyDO is not an attempt to model all of SQL or its relational model. Its focus is on modelling those relations that tend to be both selectable and updateable, namely, tables. Since PyDO does not provide a complete abstraction layer over SQL, it is entirely appropriate for PyDO-generated queries to be mixed with raw SQL queries in real applications (although that may be hidden in your own abstraction layer).

PyDO 2 requires Python 2.4 or later.

4   Defining Table Classes

To model a database table, you define a subclass of PyDO and set some class attributes that describe the table:

from pydo import PyDO, Sequence, Unique

class Article(PyDO):
    """PyDO class for the Article table"""

    # define a connection alias so that PyDO knows how to 
    # connect to the database
    connectionAlias='my_db'

    # the schema name, if applicable, e.g.:
    # schema = 'myschema'

    # the table name.  
    table='article'

    # whether we are allowed to update instances of this class;
    # this defaults to True anyway.
    mutable=True

    # whether, after creating a new instance (i.e., performing
    # an insert) the instance should be refreshed to get any
    # default (or other automatic) values.
    refetch=True

    # declare the fields
    fields=(Sequence('id'),
            Unique('title'),
            'slug',
            'author',
            'created',
            'body')

The connectionAlias attribute must correspond to an alias initialized elsewhere (with the initAlias() function) that tells PyDO how to create a database connection.

If the database supports schemas, like later versions of PostgreSQL, the schema name can be specified by setting the schema attribute. When PyDO then generates SQL referring to this table, it will qualify it with the schema name. By default, schema is None and there will be no such qualification. (The method that returns the actual qualified tablename is getTable(cls, withSchema=True); the withSchema parameter determines whether the returned value is schema qualified.)

The table attribute is simply the name of the table, view, or table-like entity (set function, for instance). By default, you can leave this out if the name of the class is the name of the table; in this case, cls.table will be a name made by coercing the class name to lowercase. If you don't want to allow this behavior, you can suppress it by setting the class attribute guess_tablename to False. (Note that this feature is of limited utility for database systems like mysql whose table/column names may be case-sensitive.)

The fields attribute should be a tuple or list of either Field instances (of which Sequence and Unique are subclasses), or data -- strings (which should be column names), dicts, or tuples -- that can be passed to a Field constructor (e.g., Field(*fieldTuple)). You can use your own Field subclasses if you wish to store additional information about fields (e.g., data type, validators, etc.), and if you want to customize how strings, tuples, or dicts are turned into Fields for you, you can shadow the static factory method PyDO.create_field() to do so.

A Sequence field is used to represent either an auto-increment column, for databases like MySQL that use that mechanism, or a sequence column, as used in PostgreSQL. These columns are implicitly unique and not null. For PostgreSQL, you can explicitly declare the name of the sequence with the sequence parameter of the Sequence constructor:

Sequence('id', 'chimpanzee_id_seq')

If you do not, PyDO will infer the name of the sequence from the name of the table and field, i.e.:

$table_$field_seq

A Unique field is used to represent a column that has a single-column uniqueness constraint and is not null. Multiple-column not-null uniqueness constraints can also be indicated, with the unique class attribute:

from pydo import PyDO

class ArticleKeywordJunction(PyDO):
"""PyDO class for junction table between Article and Keyword"""
    connectionAlias="my_db"
    table="article_keyword_junction"
    fields=('article_id',
            'keyword_id')
    unique=(('article_id', 'keyword_id'),)

The unique attribute can be thought of as analogous to how, in SQL, you may declare uniqueness constraints in a separate clause after you have declared the fields; it is optional to do so for single-column uniqueness constraints, which are more conveniently declared inline with the field, but necessary for the multi-column case.

It is not necessary to declare any uniqueness constraints in a PyDO class at all, either implicitly with the Unique field class, or via the unique class attribute. However, if you do not, instances of the corresponding PyDO class won't be able to identify the unique rows in the database table to which they correspond, and hence the instances will not be mutable. (If the class is mutable, however, it will still be possible to perform inserts and mass updates and deletes.)

The inherited fields, uniqueness constraints, and sequences of a class may be read, but not changed, through the class methods getFields(), getUniquenessConstraints(), and getSequences(), respectively.

If you omit the fields and unique declarations completely and declare the class attribute guess_columns, PyDO will attempt to introspect into the database and build the table description itself at class creation time. The declaration only affects the class in which it is declared; classes that inherit the attribute will not themselves attempt to guess columns. By default, column guessing will require querying the database when the class is initialized for every process in which the class is imported; to mitigate this potential performance hit, the data can be cached to disk if you set PyDO.guesscache to an instance of GuessCache or a compatible object, or to 1 or True, in which case a default GuessCache will be used. GuessCache stores pickles associated with classes in a cache directory, by default one created with the name $USERNAME_pydoguesscache inside tempfile.gettempdir(), where $USERNAME is the login of the current user; if the schema of one of your objects has changed and you want to refresh the cache, simply delete the corresponding cache object and restart your application.

Finally, if you are writing a quick script and want basic, uncustomized PyDO classes for every table in a schema, the function autoschema will generate them for you, and return them to you in a dictionary keyed by class name:

globals().update(autoschema(alias='myDBAlias',
                            schema='public',
                            guesscache=True,
                            module=someModule)

By default, it will use the default GuessCache, and specify no schema; you must give an alias (which should be initialized first with initAlias). The module parameter has the same effect as the module parameter to PyDO.project(): it causes the new classes to be associated with the provided module, so they can be pickled and unpickled. While convenient for scripts, using autoschema gives you no way of adding methods to your PyDO objects or customizing their attributes, so isn't well suited for PyDO's main purpose, namely, crafting an application's data access layer.

myProjection=MyBaseClass.project('id', 'title')

class myBaseClass(PyDO):
    fields=(Sequenced('id'),
            Unique('title'),
            'author'
            'ISBN',
            'first_chapter')

5   Making Queries: getSome() and getUnique()

There are two class methods provided for performing SELECTs. getSome returns a list of rows of PyDO instances:

 >>> myFungi.getSome()
[{'id' : 1, 'species' : 'Agaricus lilaceps', 'comment' : 'nice shroom!'}, 
 {'id' : 2, 'species' :  'Agaricus micromegathus', 'comment' : ''}]

getUnique returns a single instance. You must provide enough information to getUnique to satisfy one declared uniqueness constraint; this is accomplished by passing in keyword parameters where the keywords are column names corresponding to the columns of a uniqueness constraint declared for the object, and the values are what you are asserting those columns equal for the unique row:

>>> myFungi.getUnique(id=2)
{'id' : 2, 'species' :  'Agaricus micromegathus', 'comment' : ''}
>>> myFungi.getUnique(id=55) is None
True 

getSome is similar, but admits a much wider range of query options, and returns a list of PyDO instances. Assuming that comment is not a unique field above, you could not add selection criteria based on comment to getUnique(), but could to getSome:

>>> myFungi.getSome(comment=None)
[{'id' : 2, 'species' :  'Agaricus micromegathus', 'comment' :  ''}]
>>> myFungi.getSome(comment='better than asparagus', id=55)
[]

>>> myFungi.getSome(IN(FIELD('comment'), 
...                    SET('nice shroom!', 'has pincers')))

6   Inserts, Updates, and Deletes

To insert a new record in the database and create the corresponding PyDO object, use the class method new():

>>> subscription=Subscriptions.new(email='alvin@krinst.org',
                                   magazine='NYRB')
>>> subscription
{'email' : 'alvin@krinst.org', 'magazine' : 'NYRB'}

If the object has a field which will acquire a default non-null value even though you haven't specified a value for it, PyDO will automatically refetch it for you if you have set cls.refetch to a true value:

>>> Sonnet.refetch
True
>>> poem=Sonnet.new(title='Anguished Parsnips',
                    body='\n'.join(' '.join(['oy veh!' * 5]) * 14))
>>> poem.created
datetime.datetime(2005, 5, 9, 11, 6, 25, 221004)

This is equivalent to calling refresh() after new(), and also requires that a uniqueness constraint be been declared for the class. You can also explicitly set the refetch behavior on a per-call basis by using the methods newfetch() and newnofetch(), or (for backwards compatibility with PyDO1) by using a deprecated keyword parameter, refetch, to new():

>>> dud=Failure.newfetch(name="Charlie Brown")
>>> dud2=Failure.new(refetch=1, name="Oblomov")

Usually this isn't necessary, as whether you need to refetch is primarily determined by the characteristics of the table, but sometimes it useful -- for instance, if refetch is true class-wide, but you don't plan on doing anything with the object you are creating, it will be more efficient to use newnofetch.

If you don't specify the value of a column when calling new(), and there is no refetch, PyDO will assume that the default value is null and store None for that column.

If a class is declared mutable and has a uniqueness constraint, it is possible to mutate an undeleted instance of it by calling:

>>> poem['title']='Sayings of the Robo-Rabbi'

or, equivalently, if use_attributes is true for the class:

>>> poem.title='Sayings of the Robo-Rabbi'

Multiple updates can be done together via update():

MyInstance.update(dict(fieldname=newValue,
                       otherFieldname=otherValue))

Each mutation will cause an UPDATE statement to be executed on the underlying database. If you attempt to mutate an immutable PyDO instance, a PyDOError will be raised. A PyDOError will also be raised if the number of rows affected, as returned by the database driver, is not equal to 1. If the driver returns something other than 1 for a successful update in a particular case (for instance, for an updateable view), set the class attribute _ignore_update_rowcount to True.

It is also possible to update potentially many rows at once with the class method updateSome():

>>> Article.updateSome(dict(slug="nonsense"), 
...                    LT(FIELD("created"),
...                       CONSTANT("CURRENT_TIMESTAMP")),
...                    author='Smullyan')
6

The first argument to updateSome() is a dictionary of values to set for affected rows; remains positional and keyword args accept the same arguments as getSome() (with the exception of order, limit, and offset which wouldn't make sense in this context). The return value is the number of affected rows.

To delete an instance, call the instance method delete():

>>> Article.getUnique(id=44).delete()

The method returns nothing; the instance in question is marked as immutable.

To delete many rows at once, use the class method deleteSome():

>>> Article.deleteSome(LT(FIELD("created"), 
...                       CONSTANT("CURRENT_TIMESTAMP")),
...                    author="Grisby Holloway")

The parameters accepted are again the same as for getSome()), except for order, limit, and offset, and the return value is the number of affected rows.

7   Joins

def getB(self):
    return B.getUnique(id=self.b_id)

def setB(self, item):
    if item is None:
        self.b_id=None
    else:
        self.b_id=item.id

B=property(getB, setB)

B=ForeignKey('b_id', 'id', B)

B=ForeignKey('b_id', 'id', 'B')

B=ForeignKey('b_id', 'id', 'somePackage.someModule.B')

def getBs(self, *args, **kwargs):
    return B.getSome(a_id=self.id, *args, **kwargs)

getBs=OneToMany('id', 'a_id', B)

def getBs(self, *args, **kwargs):
    return self.joinTable('id', 'J', 'a_id', 'b_id', 
                          B, 'id', *args, **kwargs)

getBs=ManyToMany('id', 'J', 'a_id', 'b_id', B, 'id')

def fetch(resultSpec, sqlTemplate, *values, **kwargs)

>>> tmpl='''SELECT $COLUMNS FROM $TABLES WHERE art.creator=auth.id 
...         AND art.id=%s'''
>>> res=fetch([(Article.project('title'), 'art'),
...            (Author.project('lastname'), 'auth'),
...            '3-2'], tmpl, 4)
(({'title': 'My Woodchuck Smarts'}, {'lastname' : 'Pydong'}, 1),)

8   Managing Database Connections

All that a PyDO class knows about its database connection is its connectionAlias attribute. Before you use the class, you must call initAlias() to associate that alias with the data needed to make an actual database connection:

initAlias(alias, driver, connectArgs, pool=False, verbose=False)

driver must be the name of a driver registered with PyDO; the built-in ones are currently "mysql", "psycopg", "sqlite", "mssql", and "oracle". connectArgs are arguments to pass to the underlying DBAPI driver's connect() function; you can pass a tuple of positional args, a dictionary of keyword args, or a single object that will be treated like a tuple of length 1. pool is an optional connection pool; if you want one, you can either pass a ConnectionPool instance or something with a compatible connect() method, or a true value, in which case a default ConnectionPool will be created. By default no pool is used. verbose is whether or not to log the generated SQL; by default no logging is done.

The class method PyDO.getDBI() returns a database interface object (an instance of a driver-specific pydo.dbi.DBIBase subclass), which in turn uses an underlying DBAPI database connection. The DBAPI connection is stored in thread-local storage and created lazily when an attempt is made to access it, so transactions in different threads will transparently use different connections. By default the connection will live as long as the current thread. If you use a pool, every time a transaction is completed, the connection will be released by the DBI object and returned to the pool. If you aren't using a pool and are using multiple threads, when the thread is finished, its connection will go out of scope and will get closed during garbage collection.

If you want to manage connections outside of PyDO, you can, by using the DBI object's swapConnection() method:

oldConnection=myDBI.swapConnection(newConnection)
# do something with PyDO

Because the connections are stored thread-locally, this is thread-safe. Using this technique, one could juggle multiple transactions in the same process without using multiple threads.

The dbapi module that underlies a given DBI object is available as DBIobj.dbapiModule; the standard dbapi-mandated exceptions defined in that module are available in a dictionary, DBIobj.exceptions, keyed by name.

pool=ConnectionPool(max_poolsize=0, 
                    keep_poolsize=1, 
                    delay=0.2,
                    retries=10)

9   A Complete Example

Consider the following toy sqlite database:

CREATE TABLE contact (
  id integer not null primary key,
  first_name text,
  last_name text not null,
  address_id1 integer references address,
  address_id2 integer references address,
  email1 text,
  email2 text,
  work_phone text,
  home_phone text,
  mobile_phone text
);

CREATE TABLE address (
  id integer not null primary key,
  line1 text,
  line2 text,
  town text,
  state text,
  country text,
  postal_code text
);

CREATE TABLE note (
  id integer not null primary key,
  title text,
  body text not null,
  created timestamp
);

CREATE TABLE contact_note (
  contact_id integer references contact,
  note_id integer references note,
  primary key (contact_id, note_id)
);
  

The following Python module wraps its tables in an api:

"""
a small example PIM application, using sqlite.

The schema is actually rather ridiculous (why are contact -> notes
many to many?)  but you get the idea.

"""
import os
from mx.DateTime import now
from pydo import *

class Note(PyDO):
    connectionAlias="pim"
    # if created had a default value (which
    # it would if the version of sqlite this was
    # tested on supported anything like CURRENT_TIMESTAMP)
    # I'd set:
    # refetch=True
    fields=(Sequence('id'),
            'title',
            'body',
            'created')

class Address(PyDO):
    connectionAlias='pim'
    fields=(Sequence('id'),
            'line1',
            'line2',
            'town',
            'state',
            'country',
            'postal_code')

    def getContacts(self):
        return Contact.getSome(OR(EQ(FIELD('address_id1'), self.id),
                                  EQ(FIELD('address_id2'), self.id)))


class Contact(PyDO):
    connectionAlias='pim'
    fields=(Sequence('id'),
            'first_name',
            'last_name',
            'address_id1',
            'address_id2',
            'email1',
            'email2',
            'work_phone',
            'home_phone',
            'mobile_phone')

    Address1=ForeignKey('address_id1', 'id', Address)
    Address2=ForeignKey('address_id2', 'id', Address)

    def addNote(self, title, body):
        n=Note.new(title=title, body=body, created=now())
        junction=ContactNote.new(contact_id=self.id,
                                 note_id=n.id)

    getNotes=ManyToMany('id',
                        'contact_note',
                        'contact_id',
                        'note_id',
                        Note,
                        'id')


class ContactNote(PyDO):
    connectionAlias='pim'
    # table is specified here, because the class name
    # is not the same as the table name
    table="contact_note"
    fields=('contact_id',
            'note_id')
    unique=(('contact_id',
             'note_id'),)

DB=os.environ.get('PIMDB', 'pim.db')
initAlias('pim', 'sqlite', DB)

def initDB():
    joseAddress=Address.new(line1="43 Chestnut Place",
                            line2="Fourth Floor",
                            town="Princeton",
                            state="NJ",
                            country="USA",
                            postal_code="06540")
    jose=Contact.new(first_name='Jose',
                     last_name='Gutenberg',
                     email1='jgutenberg@example.com',
                     address_id1=joseAddress.id,
                     home_phone='609/555-1234')
    jose.addNote('French Tutor',
                 'meet every other Thursday at 7pm in the Annex')
    jose.commit()
    
                     

10   Differences From PyDO 1

This version of PyDO differs in several ways from PyDO version 1.x, most notably:

1. PyDO1 defines fields as a tuple of tuples (fieldname, dbtype); type is required, as PyDO1 drivers take much of the responsibility for marshalling Python data types to database types, and does so as a function of column type. PyDO2 does not need to know about what the database type is of the underlying columns, because DBAPI drivers now largely take care of this themselves.
2. You cannot define uniqueness constraints in the field list in PyDO1.
3. Sequences and auto-increment fields are handled separately in PyDO1, and both have to be declared in separate class attributes, which have been dropped in PyDO2.
4. The SQLOperator syntax is now more flexible, and is accepted by getSome(). In PyDO1, there are three additional methods that accept different query syntaxes: getSomeWhere(), getTupleWhere(), and getSQLWhere(). These have been dropped. joinTable() now accepts this query syntax as well, so it is no longer necessary to override a protected method to add additional criteria to a join query.
5. PyDO1 is not thread-safe, and has no connection pool facilities for multi-threaded use. What connection management facilities it does have are tied in with SkunkWeb. PyDO2 is entirely separate from SkunkWeb.
6. PyDO1 uses a different package structure; the new version does not have the same sub-modules. However, everything you would normally need is available in the top-level namespace for both versions.
7. PyDO1 does not have projections, and the inheritance semantics, while similar, are not exactly the same.
8. PyDO1 does not use new-style classes (as it predates them), so the metaclass functionality is more elaborate, including its own implementation of class methods (which it calls "static" methods).
9. PyDO1 supports more databases than PyDO2 does at the time of writing.
10. PyDO2 does not implement PyDO1's original scatterFetch() method, which returns multiple PyDO objects of different types in a single query, but has a new function, fetch which has a superset of the same functionality.
11. PyDO1 has a variable SYSDATE that means the current datetime, regardless of the underlying db. PYDO2 does not abstract this, as it seems unnecessary now; you can use something database-dependent like CONSTANT('CURRENT_TIMESTAMP') or mx.DateTime.now().
12. The package name of PyDO in this version is pydo, not PyDO. Both versions can be installed simultaneously without any fancy footwork on case-sensitive operating systems; on case-insensitive OSes, it is still possible to install and use both, by putting PyDO1 into a zip file, in which paths are always case-sensitive. (Thanks to Hamish Lawson for suggesting this workaround.)
13. The newfetch() and newnofetch() methods and the refetch class attribute of PyDO objects are new in PyDO2; in PyDO1, the refetch keyword argument to new() was used instead, but was broken for the unlikely case of a column named "refetch".
14. The support of schema-qualified table names and optional guessing of table name from class name is new in PyDO2.
15. The optional guessing of field information at runtime, controlled by the guess_columns attribute, is a new feature in PyDO2.
16. autoschema is new in PyDO2.
17. ForeignKey, OneToMany and ManyToMany are new in PyDO2.