Outerjoin in sqlalchemy

Question

I'm new to databases and I'm practicing basic operations. I've set up mysql, python and sqlalchemy on my machine (huge victory).

I've coded up this basic schema with Departments and Employees (I know, pretty boring). I've figured out how to do an inner join (code included below, feel free to copy), but I can't seem to get the outerjoin.

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Table, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref, sessionmaker
from datetime import datetime

engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))

    def __init__(self, id, name):
        self.id = id
        self.name = name

class Emp(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    mgr_id = Column(Integer, ForeignKey('employee.id'))
    dept_id = Column(Integer, ForeignKey('department.id'))
    name = Column(String(100))
    sal = Column(Integer)
    doj = Column(Date)

    def __init__(self,id,mgr_id,dept_id,name,sal,doj):
        self.id = id
        self.mgr_id = mgr_id
        self.dept_id = dept_id
        self.name = name
        self.sal = sal
        self.doj = doj

Base.metadata.create_all(engine)

session.add_all([Department(1,'HR'), \
                Department(2,'Engineering'), \
                Department(3,'Marketing'), \
                Department(4,'Sales'), \
                Department(5,'Logistics')])


session.add_all([Emp(1, None, 2,'Hash', 100, datetime(2012,01,01)), \
                Emp(3, 2, 1, 'Privy', 50, datetime(2012,05,01)), \
                Emp(4, 1, 1, 'Inno', 50, datetime(2012,05,01)), \
                Emp(5, 2, 2, 'Anno', 80, datetime(2012,02,01)), \
                Emp(6, 1, 2, 'Darl', 80, datetime(2012,02,11)), \
                Emp(7, 1, 3, 'Pete', 70, datetime(2012,04,16)), \
                Emp(8, 7, 3, 'Meme', 60, datetime(2012,07,26)), \
                Emp(9, 2, 4, 'Tomiti', 70, datetime(2012,07,07)), \
                Emp(10, 9, 4, 'Bhuti', 60, datetime(2012,8,24)), \
                Emp(2, 1, 2, 'Robo', 100, datetime(2012,01,01))])

print '\nDepartment names'
for instance in session.query(Department).order_by(Department.id): 
    print instance.id,instance.name

print '\nEmployee names'
for instance in session.query(Emp).order_by(Emp.id):
    print instance.id,instance.name,instance.doj

print '\nInner Join'
for d, e in session.query(Department, Emp).\
                     filter(Department.id==Emp.dept_id).\
                     all(): 
    print d.name, e.name

print '\nOuter Join' # fails in next line
for d, e in session.query(Department, Emp).\
                     outerjoin(Department.id).\
                     all(): 
    print d.name, e.name

Here's the print-out with error:

nb-meagain:sqlalchemy me$ python joinsEtc.py 

Department names
1 HR
2 Engineering
3 Marketing
4 Sales
5 Logistics

Employee names
1 Hash 2012-01-01
2 Robo 2012-01-01
3 Privy 2012-05-01
4 Inno 2012-05-01
5 Anno 2012-02-01
6 Darl 2012-02-11
7 Pete 2012-04-16
8 Meme 2012-07-26
9 Tomiti 2012-07-07
10 Bhuti 2012-08-24

Inner Join
Engineering Hash
Engineering Robo
HR Privy
HR Inno
Engineering Anno
Engineering Darl
Marketing Pete
Marketing Meme
Sales Tomiti
Sales Bhuti

Outer Join
Traceback (most recent call last):
  File "joinsEtc.py", line 85, in <module>
    outerjoin(Department.id).\
  File "/Users/me/Library/Python/2.7/lib/python/site-packages/sqlalchemy/orm/query.py", line 1688, in outerjoin
    from_joinpoint=from_joinpoint)
  File "<string>", line 1, in <lambda>
  File "/Users/me/Library/Python/2.7/lib/python/site-packages/sqlalchemy/orm/query.py", line 51, in generate
    fn(self, *args[1:], **kw)
  File "/Users/me/Library/Python/2.7/lib/python/site-packages/sqlalchemy/orm/query.py", line 1768, in _join
    right_entity = onclause.property.mapper
AttributeError: 'ColumnProperty' object has no attribute 'mapper'

Can you edit your question to include the actual error message in its entirety? — VooDooNOFX
– VooDooNOFX, Commented Nov 21, 2013 at 1:34

van · Accepted Answer · 2013-11-22 09:06:09Z

5

First of all, your inner join most probably will not even be an INNER JOIN, but rather a WHERE clause leading to the same end result. You can check the generated SQL to verify

Now, inner/outer joins would look as follows:

print '\nInner Join2'
for d, e in session.query(Department, Emp).join(Emp):
    print d.name, e.name

print '\nOuter Join' # from Dep -> Emp
for d, e in session.query(Department, Emp).outerjoin(Emp):
    print d.name, e and e.name # NOTE: e Might be None because of the OUTER JOIN

print '\nOuter Join2' # from Emp -> Dep
for e, d in session.query(Emp, Department).outerjoin(Department):
    print e.name, d and d.name # NOTE: d Might be None because of the OUTER JOIN

Optional: In addition, in order to get a complete model you should make SA models aware of the relationship as well:

class Department(Base):
    ...
    employees = relationship("Emp", backref="department")
    ...

class Emp(Base):
    ...
    manager = relationship("Emp", backref="team", remote_side=[id])
    ...

In this case you can perform things like:

the_boss = session.query(Emp).get(1)
print the_boss.name
print the_boss.team
assert the_boss.team[0].manager == the_boss
print the_boss.department

edited Nov 22, 2013 at 9:06

answered Nov 21, 2013 at 9:58

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2852381 Over a year ago

Hi Van, nice answer, but there were actually some exceptions in your answer. They are pretty easy to fix, so its not really a big deal. But for the sake of not publishing buggy answers, do you mind editing the last part of your answer, i.e. where you print out info related to the_boss. E.g. the_boss.employees needs to be the_boss.team. Thanks!

van Over a year ago

will do, however you probably could do it as well :)

user2852381 Over a year ago

true! but then i couldn't accept it as an answer. thanks for following up!

Collectives™ on Stack Overflow

Outerjoin in sqlalchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related