使用SQLModel/FastAPI/SQLAlchemy处理Date对象遇Oracle类型不匹配错误

我尝试用SQLModel、FastAPI和SQLAlchemy操作Oracle数据库，定义了如下Evergreen ORM类：

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 

编写的查询函数如下：

@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在Oracle中是DATE类型，执行时触发错误：

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)}]

不理解为何数据库期望数值类型而非日期类型。

错误原因 #

Oracle中两个DATE类型直接相减，返回的是天数差（数值类型）。你的条件date.today() >= Evergreen.phase_end - margin里，Evergreen.phase_end - margin计算后得到的是天数数值，但date.today()是日期类型，两者类型不匹配，因此数据库抛出了类型不一致的错误。

修正方案 #

根据业务需求，有两种正确的写法：

1. 直接筛选phase_end不早于margin（即今天往前推days_ago天的日期）：

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

2. 如果需要筛选phase_end到今天的间隔不超过days_ago天，用Oracle内置函数计算日期差（确保是数值比较）：

from sqlalchemy import func

# ... 其他代码不变
if days_ago:
    query = query.where(func.trunc(func.sysdate()) - func.trunc(Evergreen.phase_end) <= days_ago)

修改后的完整查询函数 #

@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(Evergreen.phase_end >= margin)
    
    return session.exec(query).fetchall()

内容的提问来源于stack exchange，提问作者moth