What is the correct syntax to search an HSTORE column with SQLAlchemy ORM (0.8)?

Question

I am using flask w/ flask-sqlachemy extension.

Am attempting to search for all records that have an hstore key with a certain value.

Here is what the column is set up as:

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict

db = SQLAlchemy()

class BookDB(db.Model):

    attributes = db.Column(MutableDict.as_mutable(HSTORE), nullable=False, default={ 'disabled' : '0'}, index=True)

and here is the query I'm running:

results = BookDB.query.filter_by(attributes={ 'disabled' : '0' }).all()

This goes through without error but finds no results.

If I do:

results = BookDB.query.filter_by(attributes['disabled']='0').all()

I get the error: 'SyntaxError: keyword can't be an expression'

If I use filter() instead of filter_by(), I can do

results = BookDB.query.filter(BookDB.attributes['disabled']=='0').all()

and this works fine and produces correct results.

But what is the syntax for it to work with filter_by()?

Answer 1

filter_by is just a convenient shortcut when comparing simple field equality. It only accepts keyword arguments, and therefore only valid Python names are accepted. In this case, it is correct to use filter instead.