PyORQ - Python Object Relational binding with Queries

Tutorial

Download and Installation

You can download the latest version of PyORQ here.

To install PyORQ, unpack the tar file you downloaded, and run python setup.py install. I.e (on Linux):

tar -xvzf PyORQ-0.1.tar.gz
cd PyORQ-0.1
python setup.py install

This assumes that you have write privileges in the site-packages directory of your Python distribution. If you don't have the necessary permissions, or if you just want to test PyORQ, you can also just add the directory that was created when unpacking PyORQ to your PYTHONPATH environment variable.

PyORQ assumes that you have access to one of the supported databases ( PostgreSQL, MySQL or SQLite), and that you have installed the corresponding Python interface ( pyPgSQL, MySQL-Python, or PySQLite).

In the case of MySQL and PostgreSQL you will also need to know your username, password and the name of the database, and, if the server is not located on your machine, the name of the host.

Creating a persistent object

Before you can create persistent object, you have to create the database interface for our hierarchy (all classes in the hierarchy must use the same interface object). To do this you import the appropriate module (postgresql_db, mysql_db, sqlite_db) from the package pyorq.interface. To create an interface for PostgreSQL, use:

from pyorq.interface import postgresql_db
db = postgresql_db.postgresql_db(database='testdb')

This assumes that there exists a database on your machine with the name 'testdb' and that the server recognizes you as a user (i.e.: your username is your login name). The database may ask you for a password. You can pass more arguments to the constructor. For example:

db = postgresql_db.postgresql_db(host='host.network.domain:port',
                                 database='mydb', user='myname')

If you have a database, you can define persistent objects. A persistent object should derive from pobject. It should also have an attribute database that refers to the previously created interface object. Finally, it should define one or more persistent attributes. For example:

from pyorq import pobject, pstr, pdate
class Person(pobject):
    database = db
    name  = pstr()
    birthdate = pdate()

defines a persistent object Person with two persistent attributes name, birthdate. When the class definition is executed for the first time, PyORQ will create a new table in the database.

pstr() and pdate() create persistent property objects. The purpose of these objects is to provide accessor methods for the attributes. They also give the database the information it needs to create tables with appropriate columns. Finally, they provide the mechanism by which you can write queries (see below)

PyORQ currently supports the following types of persistent attributes:

You can create an instance of Person and assign some values to the attributes.

import datetime
p = Person()
p.name = 'Tim'
p.birthdate = datetime.date(1955, 7, 21)

To store this person in the database, use:

p.commit()

Persistent objects can refer to other persistent objects. To indicate a persistent reference you should use the property pref(cls), where cls is the persistent object that is being referred to. For example:

class Contact(pobject):
    database = db
    person = pref(Person)
    email = pstr()

c = Contact()
c.person = p
c.email = 'tim@wherever.com'
print c.person.name    # will print Tim, see previous code
c.commit()

Writing queries

I have shown how you can store objects in the database using the commit() method. To retrieve those objects, you write queries.

This is a simple query:

Person.name == 'Tim'

Note that the query is written as an expression with a class attribute as operant. We interpret this query as the set of all instances p for which isinstance(p, Person) and p.name=='Tim' is True. This means that the query may be satisfied by objects that are instances of subclasses of Person. Queries are polymorphic

A query in itself does not produce a result. In fact, queries are generators of instances (iterators). So, you can do something like:

for p in Person.name=='Tim':
    print p.name, p.birthdate

This may be a handy utility function:

def one(query):
    """Return the instance for a query where you expect to have one and 
    only one result"""
    result = list(query)
    if len(result) != 1:
        raise Exception, "Expected one and only one result"
    return result[0]

Given this function, we can illustrate an important property of queries (assuming that there is only one 'Tim' in our database):

p1 = one(Person.name=='Tim')
p2 = one(Person.name=='Tim')
print p1 is p2    # Should print True

that is: two variables that refer to the same row in the database, will in fact be referring to the same object.

Queries understand references. For example:

contacts_tims = list(Contact.person.name=='Tim')

is equivalent to (but faster):

contacts_tims = []
for p in Person.name=='Tim':
    contacts_tims.extend(Contact.person==p)

Queries use the bitwise operators ~, |, and & to represent the logical operators not, or, and and respectively. Note that bitwise operators have a higher precedence than comparison operators (contrary to logical operators). Make sure that you properly parenthesize the terms in your queries!

The most important rule in queries is that they should have only one 'free variable'. This is an illegal query:

(A.a == 1) & (B.b == 2)

because it is not clear if this query should return instances of type A or instances of type B.

Queries also understand the arithmetic operators +, -, /, and *. This is a valid query:

(A.x + A.b.y + 17) <= (A.y * A.b.x * 2)