sqlalchemy using custom methods in filter

Question

I'm having a problem with this sqlalchemy query:

def bvalue(value):
    if isinstance(value, unicode):
        value = re.sub('[^\w]', "", value).lower()
    return value

basicValue = bvalue(someVariable)

q = self.session.query(sheet.id).\
    filter(bvalue(sheet.column) == basicValue)

The bvalue function works. I'm trying to match values after stripping them from any special characters and capitalisation. The stripped variable does match with the stripped db value, but still the query is not retrieving any results.

What am I doing wrong? Can't you use custom methods in filters?

Community · Accepted Answer · 2017-05-23 11:51:11Z

0

You are aware that SQLAlchemy translates your queries into plain SQL statements that are then emitted to your configured database?

So naturally you can't simply add arbitrary python functions, since they would have to be translated into SQL which can't be done in a generic way.

Aside from this general issue, bvalue(sheet.column) will simply return sheet.column (since it's not a unicode instance) and it is evaluated before creating the query. So your query is in fact equivalent to:

q = self.session.query(sheet.id).\
    filter(sheet.column == basicValue)

How to get the regex into SQL depends on the database you're using. Check e.g. REGEXP_LIKE in SQLAlchemy for a some suggestions.

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Jul 24, 2015 at 8:29

sebastian

9,71628 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlalchemy using custom methods in filter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related