Use a foreign key mapping to get data from the other table using Python and SQLAlchemy.
- by Az
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?
