Querying a hybrid property in SQLAlchemy

Question

I'm storing file paths as relative paths in the database, but I'm then using hybrid properties to turn in into an absolute path when its mapped. When I query using this property it throws an error. Here's the model:

class File(Base):
    __tablename__ = 'files'
    ...

    _f_path = Column(Unicode(30))

    ...

    @hybrid_property
    def f_path(self):
        env = shelve.open('environment')
        return os.path.join(env['project_dir'], self._f_path)

    @f_path.setter
    def f_path(self, _f_path):
        self._f_path = _f_path

When I run this query (where ref is a unicode string):

session.query(File).filter_by(f_path=ref).first()

It gives me this error:

File "/Users/Ben/Dropbox/Giraffe/giraffe_server/giraffe/file_handlers/maya.py", line 135, in process_file
    rf = session.query(File).filter_by(f_path=str(ref)).first()
  File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py", line 1211, in filter_by
    for key, value in kwargs.iteritems()]
  File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/util.py", line 597, in _entity_descriptor
    return getattr(entity, key)
  File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/ext/hybrid.py", line 681, in __get__
    return self.expr(owner)
  File "/Users/Ben/Dropbox/Giraffe/giraffe_server/giraffe/model.py", line 133, in f_path
    print "\n\n\n[model.py:File@f_path hybrid_property] returning: ", os.path.join(env['project_dir'], self._f_path)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/posixpath.py", line 66, in join
    if b.startswith('/'):
  File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/sql/expression.py", line 3426, in __nonzero__
    raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

Answer 1

Your hybrid property must return a sql expression; yours does not, it returns a python string instead.

To resolve that for this case, don't do the path join in python but in a SQL expression instead:

return env['project_dir'] + os.path.sep + self._f_path

which will resolve to self._f_path.__radd__(result_of_project_dir_plus_os_path_sep), which can be used both in queries and as a return value.