CODEWITHSUNDEEP
pythonsqlalchemy
PizzAzzra
PizzAzzra

Reputation: 715

Use a foreign key mapping to get data from the other table using Python and SQLAlchemy

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(Order, 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?

Updated based on my comment

I know this seems a little useless, but just for the sake of knowing: what would I have to do to keep the relationship uni-directional?

Also, if I had another foreign key into another table? How would I update the mapper?

Upvotes: 4

Views: 6243

Answers (1)

van
van

Reputation: 76992

First of all, if you do pass customer to the Order constructur, then at least use it. I suggest, use a default value, but still allow assigning to a customer on creation as below:

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

In order for your relationship to work, you have to initialize relationship between objects, FK is not enough. I suggest you change your code as following (extracts):

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

# for bi-directional relationship use:
mapper(Customer, customers_table, properties={
    'orders': relationship(Orders, backref='customer')
})
mapper(Orders, orders_table)
# for uni-directional relationship use:
mapper(Customer, customers_table)
mapper(Orders, orders_table, properties={
    'customer': relationship(Customer)
})
#...

Then you can navigate from order to customer, and back:

print mycustomer.orders # in case of bi-directional
print order.customer

Upvotes: 5

Related Questions