Constrain on model attribute of where clause when using select

[Jijun-TANG]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional
import sqlite3
from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key = True)
    name: str
    secret_name: str
    age: Optional[int] = None


hero_1 = Hero(name = "Deadpond", secret_name="Dive Wilson")

engine = create_engine("sqlite:///database.db")

SQLModel.metadata.create_all(engine)


### It doesn't work with SQLModel
with Session(engine) as session:
    session.add(hero_1)
    session.commit()
    session.refresh(hero_1)
    print(hero_1, " added successfully into database")
    statement = select(Hero).where(Hero.name.lower() == "deadpond")
    result = session.exec(statement)
    heros = result.fetchall()
    print("Hero fetched: ", heros)



### An alternative way which works using sqlite3
con = sqlite3.connect("database.db")
cur = con.cursor()
res = cur.execute("SELECT * FROM Hero where lower(name) == 'deadpond'")
hero = res.fetchall()[0]

print(hero)

Description

Hello, I want to use select and impose a constraint on one of the attributes of selected model (adapting to the types of selected model, which is possible in sql query language ) like below:

statement = select(Hero).where(Hero.name.lower() == "deadpond")

But it doesn't work.

Here is the error message:

Traceback (most recent call last):
  File "/usr/Lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 1599, in __getattr__ 
    return getattr(self.comparator, key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'TDComparator' object has no attribute 'upper'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/Lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 472, in __getattr__
    return getattr(self.comparator, key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/Lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1335, in __getattr__
    return self._fallback_getattr(key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/Lib/python3.9/site-packages/sqlalchemy/orm/properties.py", line 474, in _fallback_getattr
    return getattr(self.__clause_element__(), key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/Lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 1601, in __getattr__

All in all, the functionality I want to realize is to be able to impose string transformation on the attribute of Model.
And right now it can only be realized by sqlite3:

con = sqlite3.connect("database.db")
cur = con.cursor()
res = cur.execute("SELECT * FROM Hero where lower(name) == 'deadpond'")
hero = res.fetchall()[0]

print(hero)

(1, 'Deadpond', 'Dive Wilson', None)

Operating System

Linux, Windows

Operating System Details

Linux Ubuntu 18.04, Windows 10

SQLModel Version

0.0.14

Python Version

3.11.5

Additional Context

I think it is a feature to be added. Thanks in advance for anyone who read and test my codes in the question!

[wangxin688]

from sqlalcmey import func

statement = select(Hero).where(func.lower(Hero.name) == "deadpond")