Apply a function to every query

[Berislav Lopac]

Hi, does anyone know of a simple way to apply a (SQL) function to each and every query in an ORM model? For example, this article[0] suggests building a lower()-based index for emails in order to normalise them, and then using lower() every time you search for an email: select * from users where lower(email) = lower('PERSO...@example.com').

Is there a way to define a SA model to be able to do that automatically, i.e. to wrap both sides in a function when doing basic queries like get, filter and filter_by? Thanks!

Berislav

[0] https://hashrocket.com/blog/posts/working-with-email-addresses-in-postgresql

[Mike Bayer]

On Fri, Jun 14, 2019, at 6:22 AM, Berislav Lopac wrote:

Hi, does anyone know of a simple way to apply a (SQL) function to each and every query in an ORM model? For example, this article[0] suggests building a lower()-based index for emails in order to normalise them, and then using lower() every time you search for an email: select * from users where lower(email) = lower('PERSO...@example.com').

Is there a way to define a SA model to be able to do that automatically, i.e. to wrap both sides in a function when doing basic queries like get, filter and filter_by? Thanks!

So you are describing two different things.   Apply a SQL function to every *query* is at the query level, and you would use the before_compile hook to add this criteria:

https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile

However, if you want a particular column to use lower() on both sides, that's a different question, as it applies only to specific columns using specific datatypes (strings).  The easiest way to get that is to define a custom type, such as:

from sqlalchemy import Column

from sqlalchemy import func

from sqlalchemy import Integer

from sqlalchemy import String

from sqlalchemy import TypeDecorator

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class LowerCaseString(TypeDecorator):

    impl = String

    class comparator_factory(TypeDecorator.Comparator):

        def __eq__(self, other):

            return func.lower(self) == func.lower(other)

        def __ne__(self, other):

            return func.lower(self) != func.lower(other)

class A(Base):

    __tablename__ = "a"

    id = Column(Integer, primary_key=True)

    x = Column(LowerCaseString)

print(A.x == 'hi')

print(A.x != 'hi')

docs for this general idea are at https://docs.sqlalchemy.org/en/13/core/custom_types.html#redefining-and-creating-new-operators but this is likely a good example to add

Berislav

[0] https://hashrocket.com/blog/posts/working-with-email-addresses-in-postgresql

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fd8628c5-4f3a-4003-9cb6-56d5da96794d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Friday, June 14, 2019 at 9:33:49 AM UTC-4, Mike Bayer wrote:

docs for this general idea are at https://docs.sqlalchemy.org/en/13/core/custom_types.html#redefining-and-creating-new-operators but this is likely a good example to add

Mike- This is actually a great example to add, because in Postgres (and likely other databases) the query planner will only consult a function index if that function is used in the query.

Sorry to sidetrack this for a moment, but can the TypeDecorator support multiple comparisons or would you recommend another SqlAlchemy internal?

The use-case I am concerned with a table that has an index on a 32 char md5 value and function index that is a 6char substring of that md5 value.   Every time I want to query:

     query.filter(Foo.md5 == md5)

I have to write something like the following, so the query planner will use the function index:

     query.filter((Foo.md5 == md5, func.substring(Foo.md5, 0, 6) == md5[:6])

With a few billion records in a table, the difference is a few seconds of processing vs instant results.  Right now I'm automating this with a function, but a TypeDecorator looks to be a much better option.

[Mike Bayer]

you can do that, sure, have the __eq__() method return that whole function.    Hybrid property can do this too w/ a custom comparator as can column_property() with the same idea.    There are Comparator objects all over the place and they are all pluggable.   makes for very difficult stack traces.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/75fff3a2-3e08-44b9-832a-1335443fea9f%40googlegroups.com.

[Berislav Lopac]

Thank you Mike! You're right -- I had in mind the latter but used the terminology more suited to the former.

Berislav