SQLMODEL background error with date object

Question

I'm trying to compare date objects using sqlmodel,fastapi,sqlalchemy. My ORM class looks like that:

class Evergreen(SQLModel,table=True):
    id_seq: Optional[int] = Field(default=None,primary_key=True)
    phase_end: Optional[date] = None
    phase_start: Optional[date] = None
    phase_type: Optional[str] = None
    software_product_version_name: Optional[str] = None
    product_name: Optional[str] = None
    software_product_version_id: Optional[int] = None
    product_id: int 

and my function like this:

@app.get(
    "/products/",
    summary="Query all evergreen products",
    response_description="Successful Query",
    tags=[Tags.items]
)
def get_product_evergreen(product_id: Optional[int] = None,days_ago: Optional[int] = None ,session: Session = Depends(get_session)) -> list:
    query = select(Evergreen,Mapping.product_alias).where(Evergreen.product_id == Mapping.eim_product_id)
    if product_id:
        query = query.where(Mapping.eim_product_id == product_id)
    if days_ago:
        margin = date.today() - timedelta(days_ago)
        query = query.where(date.today() >= Evergreen.phase_end - margin)
    
    return session.exec(query).fetchall()

Evergreen.phase_end has DATE type in oracle which is my database on the backend. I get the following error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00932: inconsistent datatypes: expected NUMBER got DATE
[SQL: SELECT evergreen.id_seq, evergreen.phase_end, evergreen.phase_start, evergreen.phase_type, evergreen.software_product_version_name, evergreen.product_name, evergreen.software_product_version_id, evergreen.product_id, 
mapping.product_alias
FROM evergreen, mapping
WHERE evergreen.product_id = mapping.eim_product_id AND evergreen.phase_end - :phase_end_1 <= :param_1]
[parameters: {'phase_end_1': datetime.date(2022, 10, 26), 'param_1': datetime.date(2022, 11, 25)}]
(Background on this error at: https://sqlalche.me/e/14/4xp6)

I don't understand why the database is expecting a number.

Answer 1

It seems like your query typing is wrong.

Try this,

    if days_ago:
        margin = date.today() - timedelta(days_ago)
        query = query.where(Evergreen.phase_end =< date.today() + margin)