sqlalchemy_filtering_issue.py

"""
Demonstration of a filtering issue that was introduced in sqlalchemy r6748.

In the post-r6748 behavior, the filter param False is not processed before being
passed to the DBAPI. E.g. this is echoed:

SELECT foos.id AS foos_id, foos.data AS foos_data FROM foos WHERE foos.data = ?
(False,)

Instead of this (pre-r6748):

SELECT foos.id AS foos_id, foos.data AS foos_data 
FROM foos 
WHERE foos.data = ? 
 LIMIT 2 OFFSET 0
['false']
"""

import json
from sqlalchemy import *
from sqlalchemy.types import TypeDecorator
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base


metadata = MetaData('sqlite://')
metadata.bind.echo = True
Base = declarative_base(metadata=metadata)


class JSON(TypeDecorator):
    "Store JSON-serialized objects."
    impl = String

    def process_bind_param(self, value, dialect):
        "Convert value from python object to JSON for storage."
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        "Parse JSON into object."
        return json.loads(value)


class Foo(Base):
    __tablename__ = 'foos'
    id = Column(Integer, primary_key=True)
    data = Column(JSON)

    def __init__(self, data):
        self.data = data

    def __repr__(self):
        return '<Foo: %r>' % self.data


metadata.drop_all()
metadata.create_all()

foos = [Foo('one'),
        Foo(False),
        Foo(123)]

sess = create_session()
sess.add_all(foos)
sess.flush()
sess.expunge_all()

print sess.query(Foo).filter_by(data='one').one()
print sess.query(Foo).filter_by(data=123).one()
print sess.query(Foo).filter_by(data=False).one()