Section 11.1. Mapping Relationships

11.1. Mapping Relationships

The most common of the advanced SQLObject features you'll need to understand if you need to get at the "relational" power of your database are "mapping relationships," which allow your objects to know how they are associated with other database tables. For those of you who aren't familiar with SQL and relational databases in general, the TurboGearsBook.com website contains links to several tutorials that might help you understand how all the underlying "relational" parts of relational databases work.

As you've seen, querying a single table is useful and can often get you quite a way to the functionality you want. But, even in our toy examples, we ended up needing some objects that contain information that reside in multiple tables. SQLObject enables you define one-to-many and many-to-many relationships between your tables and execute queries against them.

11.1.1. One-to-Many Relationships (MultipleJoin)

One-to-many relationships means that object A has or contains multiple objects of type B. For example, a Python module may contain multiple functions, but a function belongs to a single module. Let's represent this relationship with SQLObject classes:

class PythonFunction(SQLObject):   name = StringCol()   module_id = ForeignKey('PythonModule') class PythonModule(SQLObject):   name = StringCol()   functions=MultipleJoin('PythonFunction', joinColumn='module_id') for table in [PythonFunction, PythonModule]:   table.dropTable(ifExists=True)   table.createTable()

The PythonFunction class has a module_id attribute, which is a foreign key ^[1] of the PythonModule it belongs to. The PythonModule class has a MultipleJoin "functions" attribute. In SQLObject, ForeignKey indicates that there will be a one-to-one or one-to-many relationship with the other table.

^[1] A foreign key is a field in the database that refers to the primary key of another table in the database. The primary key uniquely identifies a particular record in a table. So, a foreign key creates a one-way mapping between its record and the record in the other table that it references.

If both tables use ForeignKey, the resulting relationship will be one to one. On the other hand if, as in the PythonModule case above, the other table has a MultipleJoin attribute rather than a ForeignKey attribute, the relationship between the tables is one to many.

If that sounds abstract, perhaps an example will help. In the following example, we create a PythonModule object. After the PythonModule has been created, we can then check to make sure it has no functions (yet):

m = PythonModule(name='xxx') assert len(m.functions) == 0

Then we can create two PythonFunction objects for the module. The functions attribute of the module suddenly reflects the fact that it contains two functions:

f1 = PythonFunction(name='foo()', module_id=m.id) f2 = PythonFunction(name='bar()', module_id=m.id) assert len(m.functions) == 2 assert m.functions[0].name == 'foo()' assert m.functions[1].name == 'bar()'

In the preceding example, we used the ID field of the module object explicitly to create the mapping relationship we wanted. This makes sense because the foreign key field in the database contains that ID. But this takes extra typing and doesn't give us that nice object-oriented feeling. Fortunately, SQLObject provides a convenient feature that bypasses the need for all that. We can just pass a reference to the object itself, and SQLObject will figure out the right foreign key value to use for us. So, rather than using m.id when creating our new functions, we can write something like this:

f3 = PythonFunction(name='foo()', module_id=m)

As you can see, SQLObject makes using ForeignKey and MultipleJoin to add relationships between your tables easy.

MultipleJoin and ForeignKey create the necessary columns automatically, and they create attributes for the tables that make accessing the adjoining table data as easy and as object oriented as possible. So in the above example, the PythonModule object now has a functions attribute that contains a list of the functions in that module. This is accessible via standard Python list semantics, so you can get the first element of that list with a simple statement like this:

assert m.functions[0].name == 'foo()'

The default when creating joins using ForeignKey or MultipleJoin is to use the ID field of the adjoining table, but there are times when you want to join on another column, and SQLObject makes this easy by allowing you to override the default behavior easily by using the joinColumn="module_id" syntax. This gives you the flexibility to join on other key fields, or even to do what the SQL people call a recursive join against another column in the same table (as you saw in Chapter 6, "Exploring More Complex Models in WhatWhat Status").

11.1.2. Many-to-Many Relationships (RelatedJoin)

The one-to-many relationship can be used to describe a lot of the data you might have. Functions belong to one module. But sometimes you need to express relationships that don't have that "belong to" character. And that's where many-to-many relationships come in.

In dry academic terms, a many-to-many relationship means that object type A has or contains multiple objects of type B, and at the same time, object B has or contains multiple objects of type A. A real-life example is friends. You can have a whole bunch of friends, and all of those friends can have a whole bunch of other friends besides just you. Or, if you spend too much time programming to have friends, perhaps another example will work better: A Python module can contain multiple import statements, and a specific import statement may appear in any number of modules.

This many-to-many relationship can be represented by a set of SQLObject classes that looks like this:

class PythonImportStatement(SQLObject):   statement = StringCol()   modules = RelatedJoin('PythonModule') class PythonModule(SQLObject):   name = StringCol()   imports=RelatedJoin('PythonImportStatement') for table in [PythonImportStatement, PythonModule]:   table.dropTable(ifExists=True)   table.createTable()

SQLObject makes creating many-to-many relationships easy by providing the RelatedJoin column class. A RelatedJoin isn't like the other column classes, because it creates a whole intermediate table that contains a row for each pairing of the two objects. This table is created for you automatically, and is maintained by SQLObject using a number of useful methods.

The following code creates a bunch of PythonModule objects and PythonImportStatement objects. So far, they are not connected, so the imports attribute of each module is empty, and the modules attribute of each import statement is empty, too:

m1 = PythonModule(name='module_1') m2 = PythonModule(name='module_2') m3 = PythonModule(name='module_3') for m in [m1, m2, m3]:   assert len(m.imports) == 0 i1 = PythonImportStatement(statement='import os') i2 = PythonImportStatement(statement='import sys') i3 = PythonImportStatement(statement='import re') for i in [i1, i2, i3]:   assert len(i.modules) == 0

Here comes the magic … SQLObject automatically creates an addPythonImportStatement() method for the PythonModule class, and an addPythonModule() method to the PythonImportStatement class. You can use either of these methods to create a new relationship between items in either class.

The code below uses the addPythonImportStatement() method several times on several modules and addPythonModule() on the i2 PythonImportStatement. The result of each of these calls is a link in the database between the import statements and the modules. As you can see in the assertions, the imports attribute of each PythonModule contains all the PythonImportStatement objects that were added using addImportPythonStatement(), and the modules attribute of each PythonImportStatement contains all the modules it was added to.

Where did the addImportPythonStatement() and addPythonModule() methods come from? They were created automatically by SQLObject via the "magic" of Python metaclasses. Fortunately, metaclasses aren't dark magic. If you're new to Python, you proably don't even need to know anything about metaclasses other than that they can be used to create custom classes, with special behavior. In this case, the metaclass is dynamically creating methods for your SQLObject class based on the presence and contents of a RelatedJoin column type.

But you don't need to know any of that to use these custom methods.

m1.addPythonImportStatement(i1) m1.addPythonImportStatement(i2) m2.addPythonImportStatement(i1) m3.addPythonImportStatement(i1) m3.addPythonImportStatement(i3) i2.addPythonModule(m3)

After you've created the relationship, you get a list back whenever you access the imports attribute of a module or the module attribute of an import:

assert m1.imports == [i1, i2] assert m2.imports == [i1] assert m3.imports == [i1, i3, i2] assert i1.modules == [m1, m2, m3] assert i2.modules == [m1, m3] assert i3.modules == [m3]

Under the covers, this works by the creation of an intermediate table that contains the ID values of the the PythonModule and PythonImportStatements objects that have been associated. In standard SQL, this table is called a join table.