SQLAlchemy and Relationship

Mon 22 December 2014

Filed under Python

Moving along with SQLAlchemy and ... Relationships

Most of programming is not a simple flat table. Mostly, there are several tables, and there is something that links them - the tables have relationships.

So going back to the small membership program from the previous post, let's add a purchases table, where we can keep track of all purchases each member makes. The two tables are linked by the member_id. They have a relationship.

Each member can have several purchases. The one member can have many purchases. That is a one-to-many relationship.

Let's set that up in SQLAlchemy:

This is the Member class we set up last time:

class Member(Base):
    __tablename__ = 'members'

    member_id = Column(Integer, primary_key=True, autoincrement=True)
    inquiry_date = Column(DateTime, default=datetime.now())
    active__date = Column(DateTime, nullable=True)
    inactive__date = Column(DateTime, nullable=True)
    last_name = Column(String)
    first_name = Column(String)
    email_address = Column(String)
    password = Column(String)
    is_active = Column(Boolean, default=False)

And now we will set up the Purchase class.

class Purchase(Base):
    __tablename__ = 'purchases'

    purchase_id = Column(Integer, primary_key=True)
    member_id = Column(Integer, ForeignKey('members.member_id'))
    item_id = Column(String)
    purchase_date = Column(DateTime, default=datetime.now())
    purchase_amt = Column(Integer)

Let's go back to the Member class and add the relevant code to the bottom.

purchases = relationship("Purchase", backref="members")

And we have to add a bit to the imports - relationship, backref, ForeignKey

from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy import create_engine, Column, Integer, DateTime, String, ForeignKey

Explanations

The Purchase class is pretty straightforward. It is based on the Base class and it has a primary key. The interesting piece is in the args of member_id. Remember, the member_id is the one field that both tables share; it creates the relationship. So we have to state that somehow.(More about relationships)

The purchases table is a 'child' of the members table, which is its parent.

member_id = Column(Integer, ForeignKey('members.member_id'))

• Integer: The data type must be the same in both tables.
• ForeignKey('members.member_id') : This states that it is the 'child' of the members table, specifically linked to the member_id.
  • It is linked to the field in the members table, not the Member class.

purchases = relationship("Purchase", backref="members")

• This is the link to the 'child'. The relationship is on the 'parent' class.
• backref. This establishes the bi-directional relationship - from the many to the one, from the child to the parent.
• backref is a shortcut for creating the relationship instead of creating the relationships in both classes. Much more about backref
• This establishes Member.purchases; purchases is an attribute of Member class.
• And it also establishes Purchase.member; `member is an attribute of Purchase class.

So now the relationships between the 2 tables has been established.

Queries for tables with relationships

Let's say we want to know who purchased what. We need the name data from the members table and the purchase data from the purchases table. We need a query that somehow pulls from both tables. There are some query(args) for that.

Here's a simple query of 2 tables:

def items_purchased_by_purchaser():
    purchases = session.query(Member, Purchase).\
                        filter(Purchase.member_id == Member.member_id).\
                        order_by(Purchase.member_id).\
                        all()
    for  member, purchase in purchases:
        print purchase.member_id, purchase.purchase_date

• both classes are included in the args of the query.
• the filter is when member_id in the Member class is equal to the member_id in the Purchase class.
• in the for statement, the order of the 2 variables matters. It must be in the same order that the classes were listed in the args. (Member is first, so the object that will hold all Member's attributes must be first)
• it uses order_by
• and it returns all results

Here is another function that will have the same results - another way to write the query. Using a join.

def items_purchased_by_purchaser():
    purchases = session.query(Purchase, Member).\
                join(Member.purchases).\
                order_by(Purchase.member_id).\
                all()
    for purchase, member in purchases:
        print purchase.member_id, member.last_name, purchase.purchase_date

• the join works because the relationship has already been declared previously when we created the classes.
• include both classes in the query(paren)

And one more way to write the query.

def items_purchased_by_purchaser():
    purchases = session.query(Purchase, Member).\
                join(Purchase).\
                join(Member).\
                order_by(Purchase.member_id).\
                all()
    for purchase, member in purchases:
        print purchase.member_id, member.last_name, purchase.purchase_date