SQLAlchemy: query custom property based on table field

Question

I'm using SQLAlchemy declarative base to define my model. I defined a property name that is computed from one the columns (title):

class Entry(Base):
    __tablename__ = "blog_entry"
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(255))
    ...

    @property
    def name(self):
        return re.sub(r'[^a-zA-Z0-9 ]','',self.title).replace(' ','-').lower()

When trying to perform a query using name, SQLAlchemy throws an error:

Session.query(Entry).filter(Entry.name == my_name).first()
>>> ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement or string

After investigating for a while, I found that maybe comparable_using() could help, but I couldn't find any example that shows a comparator that references another column of the table.

Is this even possible or is there a better approach?

score 13 · Accepted Answer · answered Oct 01 '11 at 14:57

13

From SqlAlchemy 0.7 you can achieve this using hybrid_property see the docs here: http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html

answered Oct 01 '11 at 14:57

marcinkuzminski

1,751
14
11

Is it possible to give an example of how to use this to do a string replace as above? I'm getting things like `TypeError: expected string or buffer` and `AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Entry.page_title has an attribute 'partition'` but I am trying to treat my objects page_title as a string which I can see it's not... Not sure how to get the string value though. – hello-klol Mar 19 '14 at 14:49

score 4 · Answer 2 · answered Jan 19 '11 at 18:03

Can you imagine what SQL should be issued for your query? The database knows nothing about name, it has neither a way to calculate it, nor to use any index to speed up the search.

My best bet is a full scan, fetching title for every record, calculating name then filtering by it. You can rawly do it by [x for x in Session.query(Entry).all() if x.name==my_name][0]. With a bit more of sophistication, you'll only fetch id and title in the filtering pass, and then fetch the full record(s) by id.

Note that a full scan is usually not nice from performance POV, unless your table is quite small.

Thanks, I now see that I must try another approach. – amercader Jan 20 '11 at 09:16 — amercader, Jan 20 '11 at 09:16

SQLAlchemy: query custom property based on table field

2 Answers2