sqalchemy + postgresql:将uid转换为text并与之匹配

Question

如何将uid转换为文本并使用like操作符进行匹配?例:我想在sqlalchemy中执行以下操作:

SELECT * FROM user
WHERE id::text like '%0c%';

PS:列定义为

from sqlalchemy.dialects.postgresql import UUID
id = Column(
UUID(as_uuid=True),
primary_key=True,
index=True,
nullable=False,
default=uuid4,
)

Answer 1

使用强制转换函数

import sqlalchemy as sa
...
with Session() as s:
results = s.scalars(
sa.select(MyModel).where(
sa.cast(MyModel.id, sa.TEXT).like(f'%{fragment}%')
)
)

id::text在Postgresql中是^*的简写:

with x as (select gen_random_uuid() as uu)
select uu::text, 
cast(uu as text), 
uu::text = cast(uu as text) as "Equals?" from x;
uu                  │                  uu                  │ Equals? 
══════════════════════════════════════╪══════════════════════════════════════╪═════════
b23efe1b-fb05-4d8a-92a3-59339d2ea0f4 │ b23efe1b-fb05-4d8a-92a3-59339d2ea0f4 │ t

---

^*嗯，基本上…