Flask项目使用Oracle数据库踩坑指南——ID自增与Cast自动类型转换

概述

为了尝试快速开发，使用Flask框架构建了web项目，但因需要适配Oracle数据库，从而折腾出一堆问题。
flask项目一般使用 flask-sqlalchemy 基于 SQLAlchemy中间件实现多种类型数据库的适配，Oracle也在其中。

ID字段自增如何适配

当采用sqlite、mysql等数据库时，表ID字段允许设置为 autoincrement，实现自增ID。
一般在BaseModel中定义ID字段，只需要指定Integer和 primary_key=True即可：

id = Column(Integer, primary_key=True)

但换成Oracle后，无法获取ID自增新值，一般做法是改为通过 sequence获取，这样的话需要为每张表维护一个独立的sequence；同时，通过flask-migrate 自动生成迁移脚本时，不会自动创建sequence语句，需要手工调整migrations相关脚本。
其实sqlalchemy官网对此有介绍，Oracle 从12c版本开始，支持 Identity的方式实现自增ID：auto-increment-behavior。
相应调整BaseModel：

from sqlalchemy.schema import Identity

id = Column(Integer, Identity(start=1), primary_key=True)

Oracle的 Identity，本质上还是通过 sequence来实现的，只不过是随着ID字段创建后，由Oracle数据库自行管理的。

条件查询时String字段自动类型转换CAST VARCHAR2导致报错

报错信息（部分）：

cx_Oracle.DatabaseError: ORA-00906: missing left parenthesis

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis
[SQL: SELECT ... FROM ... 
WHERE lower(CAST(table_name_.column_name1_ AS VARCHAR2)) LIKE lower(:param_1) OR lower(CAST(table_name_.column_name2_ AS VARCHAR2)) LIKE lower(:param_2)) anon_2 
WHERE ROWNUM <= 10 + 0) anon_1 
WHERE ora_rn > 0]
[parameters: {'param_1': '%1%', 'param_2': '%1%'}]

Model中定义的String()格式 Column，在通过 sqlalchemy查询时被自动进行了 cast(... as varchar2) 类型转换。这导致Oracle在执行SQL时报错：ORA-00906: missing left parenthesis，Oracle认为VARCHAR2后面应该有括号和长度值。

查阅资料后，发现有类似问题和解决思路。原因就是，SQLAlchemy在处理字段CAST时在Oracle适配上有缺陷。
官网资料中有提到，String类型（sqlalchemy.types.String）会被转换为Varchar2，同时其length参数，一般用于生成DDL和CAST表达式，可能因为适配的数据库较多，忽略了Oracle数据库的Varchar2必须带长度的特殊要求。

为此，通过阅读 sqlalchemy源码，并借鉴alembic生成ddl时采用@compiles拦截器的方式解决此问题：

from sqlalchemy.sql.elements import Cast
from sqlalchemy.types import String
from sqlalchemy.ext.compiler import compiles

@compiles(Cast, "oracle")
def visit_cast(element, compiler, **kwargs):
    """
    oracle compiler拦截器，拦截 visit_cast方法，解决 CAST(table.column AS VARCHAR2) oracle执行报错问题
    """
    if isinstance(element.type, String):
        return compiler.process(element.clause, **kwargs)
    return compiler.visit_cast(element, **kwargs)

该方法可以拦截sqlalchemy compiler方法，当编译Oracle的 cast() 表达式SQL时，增加判断：若为 String类型的字段，则不再进行 cast语句转写，直接生成 element.clause子句，即：

WHERE lower(table_name_.column_name1_) LIKE lower(:param_1)

参考资料

Oracle数据库及其数据类型的说明@sqlalchemy官网
SQLAlchemy cast to binary(N)@stackoverflow