Summary

In an attempt to develop quickly, a web project was built using the Flask framework, but the need to adapt the Oracle database created a bunch of problems.
Flask projects typically use flask-sqlalchemy to adapt to multiple types of databases based on SQLAlchemy middleware, including Oracle.

How ID field self-increment adapts

When using databases such as sqlite and mysql, the table ID field is allowed to be set to autoincrement to achieve self-increasing IDs.
Generally, you define an ID field in BaseModel by specifying Integer and primary_key=True:

id = Column(Integer, primary_key=True)

However, after switching to Oracle, the ID self-additions cannot be obtained. Instead, the usual practice is to obtain the ID through a sequence, which requires maintaining a separate sequence for each table; at the same time, when migration scripts are automatically generated through flask-migrate, the sequence statement is not automatically created and migrations-related scripts need to be manually adjusted.
In fact, this is described on the sqlalchemy website. Since the 12c version of Oracle, it supports the way Identity implements self-incrementing ID s: auto-increment-behavior.
Adjust BaseModel accordingly:

from sqlalchemy.schema import Identity

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

Oracle's Identity is essentially achieved through sequence, but it is managed by the Oracle database itself as the ID field is created.

String Field Automatic Type Conversion CAST VARCHAR2 Causes Error in Conditional Query

Error message (partial):

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%'}]

String() format Column defined in Model is cast(... as varchar2) type converted automatically when querying via sqlalchemy. This causes Oracle to make an error when executing SQL: ORA-00906: missing left parenthesis, which Oracle believes should have parentheses and length values after VARCHAR2.

After consulting the data, similar problems and solutions were found because SQLAlchemy had a defect in the Oracle adapter when processing field CAST.
It is mentioned in the official website data. String type (sqlalchemy.types.String) It is converted to Varchar2, and its length parameter is generally used to generate DDL and CAST expressions, possibly because of the large number of adapted databases, ignoring the special requirement that Varchar2 in Oracle databases must have a length.

To do this, solve this problem by reading the sqlalchemy source code and using the @compiles interceptor when alembic generates ddl:

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 Interceptor, Intercept visit_cast Method, solution CAST(table.column AS VARCHAR2) oracle Execution error problem
    """
    if isinstance(element.type, String):
        return compiler.process(element.clause, **kwargs)
    return compiler.visit_cast(element, **kwargs)

This method intercepts the sqlalchemy compiler method and adds judgment when compiling Oracle's cast() expression SQL: if the field is of type String, no cast statement transcription is performed and an element.clause clause Clause is generated directly, that is:

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

Reference material

sqlalchemy's description of Oracle database and its data type