1.5.3 One-To-Many Mappings

One of the features that distinguishes this module from many others is its ability to deal with more complex datastructures than just simple tables. As an example of a one-to-many mapping we will consider an address book.

In our address book each person can have many addresses but each address is only associated with one person. The data structure looks like this:

            +--  Address 1
Person 1 ---|
            +--  Address 2

To create a database to describe this struture we need two tables, a Person table and an Address table.

import web.database, web.database.object
connection = web.database.connect(type="sqlite", database="object-multiple.db")
cursor = connection.cursor() 

person = web.database.object.Table("Person")
person.addColumn(web.database.object.String(name="firstName"))
person.addColumn(web.database.object.String(name="surname"))
person.addMultiple(name="addresses", foreignTable="Address")

address = web.database.object.Table("Address")
address.addColumn(web.database.object.String(name="firstLine"))
address.addColumn(web.database.object.String(name="postcode"))
address.addSingle(name="person", foreignTable="Person")

database = web.database.object.Database()
database.addTable(person)
database.addTable(address)
database.init(cursor)

As in the introductory example we use the addColumn() method to add Column objects to the Address table. This time however we also use the addSingle() method to add a column named person to the table. We have also used addMultiple() method to add a multiple join called addresses from the Person foreign table to the Person table. The final change is that we have added the Address table to the database.

Note: We in the addSingle() and addMultiple() methods we refer to the foreignTable by the string representing its name and not the object itself.

When we access a person's addresses key, we will get back a list of all the Address objects associated with that person. Continuing the example above:

>>> john = database['Person'].insert(firstName='John', surname='Smith')
>>> print john['surname']
Smith
>>> print john['addresses']
{}
>>> database['Address'].insert(person=john, firstLine='12 Friendly Place', postcode='OX4 1AB')
>>> database['Address'].insert(person=john, firstLine='3a Crazy Gardens', postcode='OX1 2ZX')
>>> for address in john['addresses'].values:
...    print address['firstLine']
...
12 Friendly Place
3a Crazy Gardens

Note how we specify the person to add the address to using person=john. We could alternatively have specified the rowid of the person to add the address to. Just like the database, tables and rows, the value returned by john['addresses'] behaves like a dictionary. In this example we use the values() method to return a list of the Row objects.

It should be noted that you cannot set the values of multiple columns like the 'addresses' column directly. Instead you should set the values of each object induvidually.

>>> john['addresses'] = something # XXX Doesn't work!

Just for interest here is how the tables look in the live database. You can see that the person column in the Address table contains the rowid in the Person table of the person to associate the address with.

# Tables in the 'test' database
+----------------+
| Tables_in_test |
+----------------+
| Address        |
| Person         |
+----------------+

# The Person table
+-------+-----------+---------+
| rowid | firstName | surname |
+-------+-----------+---------+
| 1     | John      | Smith   |
+-------+-----------+---------+

# The Address table
+-------+-----------------------+----------+--------+
| rowid | firstLine             | postcode | person |
+-------+-----------------------+----------+--------+
| 1     | 12 Friendly Place     | OX4 1AB  | 1      |
+-------+-----------------------+----------+--------+
| 2     | 3a Crazy Gardens      | OX1 2ZX  | 1      |
+-------+-----------------------+----------+--------+