Boolean type column as a ClauseElement in SQLAlchemy

Question

Why is it not possible in SQLAlchemy to use a Boolean type column as a ClauseElement in itself?

session.query(Table).filter(Table.name == 'huszar', Table.valid)

Of course Table.valid == True would work, but looks a little bit ugly to me...

Answer 1

I think maybe you're on 0.7 and the ORM didn't yet support multiple criteria in a single filter() call, that was added in 0.8, also 0.7 seems to need the table-bound column if standalone. everything works in 0.8:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(String(20))
    boolean = Column(Boolean)

# works
e = create_engine("sqlite://", echo=True)

# works
#e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

# works
#e = create_engine("mysql://scott:tiger@localhost/test", echo=True)

Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(data='a1', boolean=True),
    A(data='a2', boolean=False),
    A(data='a3', boolean=True),
])

# works
print s.query(A).filter(A.data > 'a1', A.boolean).all()

# works
print s.query(A).filter(A.boolean).all()

# if before 0.8, need to use and_() or table-bound column
# print s.query(A).filter(and_(A.data > 'a1', A.boolean)).all()
# print s.query(A).filter(A.__table__.c.boolean).all()