Archive

Posts Tagged ‘SQLAlchemy’

ON DELETE CASCADE and LEFT JOIN in SQLAlchemy

October 10th, 2011 Comments off

Here’s another one in the hoping-it-saves-someone-at-least-half-an-hour-worth-of-searching-around category :-) which shows nothing but how to define an ON DELETE CASCADE constraint and then how to issue a LEFT JOIN in SQLAlchemy.

# -*- coding: utf-8 -*-
 
# SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, sessionmaker
 
Base = declarative_base()
 
# The definitions, note the use of both 'ondelete' and 'cascade'.
 
class Mom(Base):
    __tablename__ = 'mom'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(60))
 
class Daughter(Base):
    __tablename__ = 'daughter'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(60))
 
    mom_id = Column(Integer, ForeignKey('mom.id', ondelete='CASCADE'), 
                nullable=False)
    mom = relationship(Mom, 
        backref=backref('daughters', cascade='all, delete, delete-orphan'))
 
class Son(Base):
    __tablename__ = 'son'
 
    id = Column(Integer, primary_key=True)    
    name = Column(String(60))
 
    mom_id = Column(Integer, ForeignKey('mom.id', ondelete='CASCADE'), 
                nullable=False)
    mom = relationship(Mom, 
                backref=backref('sons', cascade='all, delete, delete-orphan'))
 
# Create an in-memory database.
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
 
# Let's find out how LEFT JOIN works, note the usage of .outerjoin
 
mom1 = Mom()
mom1.name = 'Molly'
 
mom2 = Mom()
mom2.name = 'Sarah'
 
mom3 = Mom()
mom3.name = 'Martha'
 
daughter = Daughter()
daughter.name = 'Matilda'
daughter.mom = mom1
 
son1 = Son()
son1.name = 'Robert'
son1.mom = mom1
 
son2 = Son()
son2.name = 'Tom'
son2.mom = mom2
 
session.add_all([mom1, mom2, mom3])
session.commit()
 
session.delete(mom2)
session.commit()
 
# All moms and their children.
rows1 = session.query(Mom.id, Mom.name, 
            Daughter.name.label('daughter_name'),
            Son.name.label('son_name')).\
              outerjoin(Daughter, Mom.id==Daughter.mom_id).\
              outerjoin(Son, Mom.id==Son.mom_id).\
              order_by('mom.name').\
              all()
 
# Sarah doesn't like us anymore so Tom should be gone as well.
rows2 = session.query(Son.id, Son.name).all()
 
for row in rows1:
    print(row)
 
print('')    
 
for row in rows2:
    print(row)

As expected, the result is

(3, u'Martha', None, None)
(1, u'Molly', u'Matilda', u'Robert')

(1, u'Robert')

We can also confirm the SQL code that’s being generated – notice the ON DELETE CASCADE clause ..

CREATE TABLE mom (
	id INTEGER NOT NULL, 
	name VARCHAR(60), 
	PRIMARY KEY (id)
)
 
CREATE TABLE daughter (
	id INTEGER NOT NULL, 
	name VARCHAR(60), 
	mom_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(mom_id) REFERENCES mom (id) ON DELETE CASCADE
)
 
CREATE TABLE son (
	id INTEGER NOT NULL, 
	name VARCHAR(60), 
	mom_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(mom_id) REFERENCES mom (id) ON DELETE CASCADE
)

.. and the LEFT JOIN is here indeed.

SELECT mom.id AS mom_id, mom.name AS mom_name, 
    daughter.name AS daughter_name, son.name AS son_name 
FROM mom 
    LEFT OUTER JOIN daughter ON mom.id = daughter.mom_id 
    LEFT OUTER JOIN son ON mom.id = son.mom_id
ORDER BY mom.name

@fourthrealm

Share
Categories: Software Tags: , , ,