SO.py

from sqlalchemy import Column, BigInteger, DateTime, String, SmallInteger
from sqlalchemy import create_engine, func, select, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


# model definitions
Base = declarative_base()
metadata = MetaData()


class Foo(Base):
     __tablename__ = 'foo'
     __table_args__ = {'schema': 'public'}

     id = Column('id', BigInteger, primary_key=True)
     time = Column('time', DateTime(timezone=True))
     version = Column('version', String)
     revision = Column('revision', SmallInteger)


# connect to the database
engine = create_engine('postgresql://postgres:root@localhost/db')

session = sessionmaker()
session.configure(bind=engine)
session = session()

# construct the query
foo_max_time_q = select([func.max(Foo.time).label('foo_max_time'), Foo.id.label('foo_id')]).group_by(Foo.id).alias("foo_max_time_q")

foo_q = session.query(
          Foo.id.label('foo_id'),
          Foo.version.label('foo_version'),
          Foo.revision.label('foo_revision'),
          foo_max_time_q.c.foo_max_time.label('foo_max_time')).join(foo_max_time_q, foo_max_time_q.c.foo_id == Foo.id)

print(foo_q.__str__())