Hmm, the title was harder to formulate than I thought.

Basically, I've got these simple classes mapped to tables, using SQLAlchemy. I know they're missing a few items but those aren't essential for highlighting the problem.

class Customer(object):
    def __init__(self, uid, name, email):
        self.uid = uid
        self.name = name
        self.email = email

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Cust: %s, Name: %s (Email: %s)" %(self.uid, self.name, self.email)  

The above is basically a simple customer with an id, name and an email address.

class Order(object):
    def __init__(self, item_id, item_name, customer):
        self.item_id = item_id
        self.item_name = item_name
        self.customer = None

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Item ID %s: %s, has been ordered by customer no. %s" %(self.item_id, self.item_name, self.customer)  

This is the Orders class that just holds the order information: an id, a name and a reference to a customer. It's initialised to None to indicate that this item doesn't have a customer yet. The code's job will assign the item a customer.

The following code maps these classes to respective database tables.

# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

customers_table = Table('customers', metadata,
    Column('uid', Integer, primary_key=True),
    Column('name', String),
    Column('email', String)
)


orders_table = Table('orders', metadata,
    Column('item_id', Integer, primary_key=True),
    Column('item_name', String),
    Column('customer', Integer, ForeignKey('customers.uid'))
)

metadata.create_all(engine)
mapper(Customer, customers_table)
mapper(Orders, orders_table)  

Now if I do something like:

for order in session.query(Order):
    print order  

I can get a list of orders in this form:

Item ID 1001: MX4000 Laser Mouse, has been ordered by customer no. 12  

What I want to do is find out customer 12's name and email address (which is why I used the ForeignKey into the Customer table). How would I go about it?