User-Defined Functioned within Textual SQL in SQLAlchemy

Question

I'm reading through the docs pertaining to SQLAlchemy's textual SQL and custom functions, and it's not clear to me how to combine the two.

For example, let's say I have the following code:

# relevant imports

def f(username):
    if len(username) < 5:
        return True
    else:
        return False

t = text("select * from users where f(username)")
db.session.execute(t)

I know that this particular example may not requred a user-defined function, but I would appreciate a general answer that will work for any user-define function.

Haldean Brown · Accepted Answer · 2017-03-06 23:22:16Z

1

I think you might be a bit confused about the meaning of those custom functions. They're referencing functions that are available in your SQL database, not functions that you've defined in Python. The docs give the example of NOW or CURRENT_TIMESTAMP; those are both functions that are usually provided by your database server software.

Many databases support "stored procedures"; for example, here are the docs for stored procedures in PostgreSQL. These are more similar to what you're describing: they're functions that can be evaluated during the query process. In your specific case, though, it's almost certainly easier to write your query to check the string length directly. For PostgreSQL or MySQL, that query would be:

select * from users where char_length(username) < 5

answered Mar 6, 2017 at 23:22

Haldean Brown

12.8k5 gold badges45 silver badges60 bronze badges

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

2 Comments

N4v Over a year ago

Appreciate it. Do you know if MySQL has any built-in functions similar to re.search()?

Haldean Brown Over a year ago

Yup! You can use WHERE field REGEXP pattern: dev.mysql.com/doc/refman/8.0/en/regexp.html

Collectives™ on Stack Overflow

User-Defined Functioned within Textual SQL in SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related