python - sqlalchemy 中的外连接

Question

我是数据库新手，正在练习基本操作。我已经在我的机器上设置了 mysql、python 和 sqlalchemy(巨大的胜利)。

我已经用 Departments 和 Employees 编写了这个基本模式(我知道，这很无聊)。我已经想出如何进行内部连接(下面包含代码，请随意复制)，但我似乎无法获得外部连接。

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

这是错误的打印输出:

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'

Answer 1

首先，您的内部联接很可能甚至不是 INNER JOIN，而是导致相同最终结果的 WHERE 子句。你可以检查生成的SQL来验证

现在，内部/外部联接如下所示:

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

可选:此外，为了获得完整的模型，您还应该让 SA 模型了解这种关系:

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

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

在这种情况下，您可以执行以下操作:

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