SQLAlchemy and Relationship
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 themembers
table, specifically linked to themember_id
.- It is linked to the field in the
members
table, not theMember
class.
- It is linked to the field in the
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 theMember
class is equal to themember_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