SQLObject and Database Programming in Python

Ian Bicking

Imaginary Landscape

The Ongoing Example

Address book:

The DB API

Database programming in Python is based on the DB-API. Some supported databases:

DB API Example

Example:

import MySQLdb
conn = MySQLdb.connect(
    db='test', username='ianb')
cur = conn.cursor()
cur.execute(
    "SELECT id, first_name, last_name FROM person")
result = cur.fetchall()
for id, first_name, last_name in result:
    print "%2i %s, %s" % (id, last_name, first_name)

DB API

DB API problems

  1. Connections and cursors are tedious.
  2. The databases don't all work the same; multiple SQL dialects.
  3. Lots of time spent writing SQL.
  4. Database updates can effect all the SQL you've written.
  5. Unless you make abstractions...

Abstractions

Object-Relational Mappers

The table:

person
id first_name last_name
1 John Doe
2 Tom Jones
...

ORMs: Classes

The class:

person
id first_name last_name
1 John Doe
2 Tom Jones
...
class Person(SQLObject):
    # id is implicit
    first_name = StringCol()
    last_name = StringCol()

ORMs: Instances

An instance:

person
id first_name last_name
1 John Doe
2 Tom Jones
...
>>> john = Person.get(1)
>>> john.first_name
'John'

ORM: summary

Usage

__connection__ = 'postgres://pgsql@localhost/test'
class Person(SQLObject):
    first_name = StringCol()
    last_name = StringCol()
    emails = MultipleJoin('Email')

Usage (classes)

class Email(SQLObject):
    person = ForeignKey('Person')
    type = EnumCol(['home', 'work'])
    address = StringCol()

Usage (creating tables)

def createTables():
    for table in (Person, Email):
        table.createTable(ifNotExists=True)

Usage (instances)

Using your classes:
>>> john = Person(first_name='John', last_name='Doe')
>>> email = Email(person=john, type='home', 
...     address='john@work.com')
>>> john.emails
[]

Usage (instances)

>>> tom = Person(first_name='Tom', last_name='Jones')
>>> tom is Person.get(tom.id)
True
>>> list(Person.selectBy(first_name='John'))
[]

Defining Classes

Defining Classes...

You can add extra methods:

class Person(SQLObject):
    ...
    def _get_name(self):
        return self.first_name + ' ' + self.last_name
>>> tom.name
'Tom Jones'

Automatic properties

Defining classes...

You can override columns:

class Person(SQLObject):
    ...
    last_name_searchable = StringCol()
    def _set_last_name(self, value):
        self._SO_set_last_name(self, value)
        self.last_name_lower = re.sub(r'[^a-zA-Z]', '', value).lower()

Defining classes...

You can fiddle with the naming:

class Person(SQLObject):
    _table = "people"
    first_name = StringCol(dbName="fname")
    ...

Connecting classes

Foreign Keys:

class Email(SQLObject):
    person = ForeignKey('Person')
    ...
Note we use a string for 'Person'.

Backreferences

The other side of one-to-many:

class Person(SQLObject):
    ...
    emails = MultipleJoin('Email')

Many-to-many

Many to many relationships imply a "hidden" table:

class Address(SQLObject):
    people = RelatedJoin('Person')
    ...
class Person(SQLObject):
    addresses = RelatedJoin('Address')

Many-to-many...

The intermediate table created:

CREATE TABLE address_person (
  address_id INT NOT NULL,
  person_id INT NOT NULL
);

Lazy classes

SQLObject can use reflection to figure out what columns your table has:

class Person(SQLObject):
    _fromDatabase = True
You can start with _fromDatabase = True and add in explicit columns, overriding defaults.

Instances

Updating

Selecting

You can use Python expressions (kind of):

query = Person.q.first_name == "Joe"
Person.select(query)

Selecting...

Selecting...

Complicated joins are possible:

Person.select((Person.q.id == Email.q.personID)
              & (Email.q.address.startswith('joe')))
Becomes:
SELECT person.id, person.first_name, person.last_name
FROM person, email
WHERE person.id = email.person_id
      AND email.address LIKE 'joe%'

SelectResult

Select results are more than just lists:

Caching

Performance

The problem with ORMs

ORMs live in the world between Object Oriented programmers (and programs) and Relational programmers (and programs). Neither will be happy.

ORM Mismatch

ORM Mismatch: Classes

ORM Mismatch: Instances

ORM Mismatch: Relations

Solving the question by avoidance

Solving the Mismatch

SQLObject's answer: don't try too hard.

Some other alternatives

Several Python ORMs exist; contrasts:

Other alternatives...

See the Python Wiki at: python.org/moin
The page is HigherLevelDatabaseProgramming.