Q:

How to fetch pairs from join where second value can be none, left join

I have two models in database like 
class Base(object):
    def __tablename__(self):
        return self.__name__.lower()

    id = Column(Integer, primary_key=True, nullable=False)
    utc_time = Column(BigInteger, default=utc_time, onupdate=utc_time)

class EntityModel(Base):
    __tablename__ = 'entities'
    town_id = Column(Integer, ForeignKey('towns.id', ondelete='CASCADE'), nullable=False)
    type = Column(Integer, nullable=False)
    level = Column(Integer, nullable=False, default=Level.LEVEL_ONE)
    energy = Column(Float, nullable=False, default=0)
    x = Column(Integer, nullable=False)
    y = Column(Integer, nullable=False)

class CommandModel(Base):
    __tablename__ = 'commands'
    entity_id = Column(Integer, ForeignKey('entities.id', ondelete='CASCADE'), nullable=False)
    command = Column(Integer, nullable=False)
    started_at = Column(BigInteger, nullable=False)
    ends_at = Column(BigInteger, nullable=False)
    type = Column(Integer, default=None)

Entity can have one or none command. How to fetch pairs entity, command (entity can exists without command)
I tried like but it doesn't work 
for e, c in session.query(EntityModel, CommandModel).join(CommandModel).filter(EntityModel.town_id == 100).all():

A:

You can use outerjoin for a left outer join, which seems to be what you want.
http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.outerjoin

