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('person5...@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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@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
>
> <https://groups.google.com/d/msgid/sqlalchemy/fd8628c5-4f3a-4003-9cb6-56d5da96794d%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@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/afae1519-745a-494a-bef1-b3b6c2cf7005%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
