Partial UUID match in Python/SQLAlchemy

Question

Trying to develop a script in Python/SQLAlchemy that can take a substring and partially match it against a UUID. For example: the string "d78d" would match UUID('3f522efe-d78d-4081-99a5-9aad6b5332be'). Obviously, you can't filter a query by comparing a UUID object to a string, so I tried the below script:

import sqlalchemy
from sqlalchemy.sql.expression import cast
my_search_value = "d78d"
my_query = db.session.query(People.id).filter(
      cast(People.id, sqlalchemy.String).ilike(f"%{my_search_value}%")

Unfortunately, this did not return any results even though I am positive there is a record in my database that contains that search value. However, when I debug the script, I can see the result of how SQLAlchemy translates this query to Postgresql:

Select people.id
From people
Where lower(CAST(people.id as VARCHAR)) LIKE lower(:param_1)

When I copy this and paste it into my db client (substituting lower(:param_1) with '%d78d%'), it performs as expected. So, I would assume that there's nothing inherently wrong with my trying to partially match a substring against a UUID, but that I'm missing something within Python/SQLAlchemy that is preventing my script from working.

For what it's worth, I have tried using the following operators: LIKE, ILIKE, MATCH....all with the same result.

Answer 1

What worked for me:

Install :- sqlalchemy_utils

from sqlalchemy_utils.functions import cast_if

# Get the keys. kwargs is the dict that I receive as a parameter.
kwarg_keys = kwargs.keys()

# Check if the key 'org_uuid' exists
org_uuid = kwargs['org_uuid'] if 'org_uuid' in kwarg_keys else ''

rows = my_model.query.filter(cast_if(my_model.org_uuid, sa.String).like(f'%{org_uuid}%'))

Above, my_model has a uuid column org_uuid which I want to query and filter. It is not guaranteed that the function will receive a dict with the org_uuid.