is_ and isnot in conjunction with boolean values / MySQL

MySQL does not have native support for boolean types. Instead they are mimicked using tinyints. From the docs (http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html):

''BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true''

Probably wrong-footed by the integer type, SA converts Python boolean values into integers in queries. This works for filters based on ==, but not for filters using the is_ or isnot operators:

query = DBSession.query(Test).filter(Test.flag.isnot(False))
result = query.all()

yields the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 3") 'SELECT test.id AS 
test_id, test.flag AS test_flag \nFROM test \nWHERE test.flag IS NOT %s' (0,)

The same result is obtained from the MySQL prompt:

mysql> SELECT test.id AS test_id, test.flag AS test_flag FROM test WHERE test.flag IS NOT 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near '0' at line 1

But using boolean syntax, the query passes:

mysql> SELECT test.id AS test_id, test.flag AS test_flag FROM test WHERE test.flag IS NOT False;
Empty set (0.00 sec)

Complete script to reproduce:

from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import Boolean


DeclarativeBase = declarative_base()
Session = sessionmaker()

class Test(DeclarativeBase):
    __tablename__ = 'test'
    id = Column('id', Integer, primary_key=True)
    flag = Column('flag', Boolean, default=None)

    def __repr__(self):
        return "<Test({}, {})>".format(self.id, self.flag)


def configure_session(database_uri, echo=True):
    engine = create_engine(database_uri, echo=True)
    DeclarativeBase.metadata.create_all(engine)
    Session.configure(bind=engine)


if __name__ == "__main__":
    configure_session(...)
    DBSession = Session()

    DBSession.add_all([Test(flag=False), Test(flag=True](Test(),))

    query = DBSession.query(Test).filter(Test.flag == True)
    query = DBSession.query(Test).filter(Test.flag.is_(True))
    query = DBSession.query(Test).filter(Test.flag.isnot(False))
    r = query.all()

Comments (9)