Oracle存储过程转SQLAlchemy+SQLite遇instr/position兼容问题求助

我正在将Oracle存储过程转换为SQLAlchemy Python代码，测试环境必须使用SQLite（架构由上级决定无法更改），但SQLite不支持instr/position等函数，引发兼容问题。

最初尝试在WHERE子句中使用func.substr和func.strpos实现逻辑，但strpos在SQLite中不存在导致执行失败；之后改用Python切片和find编写过滤语句，却出现“getItem is not supported in this expression”错误。

原PL/SQL语句 #

UPDATE MC.SFCOBS SFC
SET SFC.WINDDIRECTION = NULL,
    SFC.WINDSPEED = NULL,
    SFC.WINDCONDITIONS = NULL,
    SFC.WINDCONDITIONSQUALITYCODE = '5',
    SFC.WINDSPEEDQUALITYCODE = CASE WHEN SFC.WINDSPEED = 0 THEN '5' ELSE NULL END
WHERE SFC.WINDCONDITIONS = 'C'
AND (SFC.WINDSPEED = 0 OR SFC.WINDSPEED IS NULL)
AND EXISTS (
    SELECT 'X'
    FROM MC.JOBS OBS
    WHERE OBS.OBSERVATIONID = SFC.OBSERVATIONID
    AND OBS.REPORTTYPECODE IN ('FM-12', 'FM-13', 'FM-14')
    AND OBS.INSERTIONTIME >= startdatetime
    AND OBS.INSERTIONTIME < enddatetime
    AND OBS.OBSERVATIONID = SFC.OBSERVATIONID)
    AND SFC.OBSERVATIONID = (
        SELECT MSG.OBSERVATIONID
        FROM MC.JMO_MESSAGE MSG
        WHERE MSG.OBSERVATIONID = SFC.OBSERVATIONID
        AND (
            SUBSTR(MSG.ORIGINALMESSAGE, 16, 4) = '// 1'
            OR SUBSTR(MSG.ORIGINALMESSAGE, 14, 5) = '//// '
            OR SUBSTR(MSG.ORIGINALMESSAGE, INSTR (MSG.ORIGINALMESSAGE, ' 26/// ') + 10, 3) = '// '
            AND INSTR(MSG.ORIGINALMESSAGE, ' 26/// ') > 3
            OR SUBSTR(MSG.ORIGINALMESSAGE, INSTR (MSG.ORIGINALMESSAGE, ' 99') + 22, 3) = '// '
            AND INSTR(MSG.ORIGINALMESSAGE, ' 99') BETWEEN 11 AND 20
            OR SUBSTR (MSG.ORIGINALMESSAGE, 7, 6) IN ('45/// ', '46/// ')
            AND SUBSTR (MSG.ORIGINALMESSAGE, 16, 3) = '// ')
);

最初编写的WHERE子句 #

.where(
                JmoMessageTable.observationid == sfc.observationid
                or_(
                    func.substr(JmoMessageTable.originalmessage, 16, 4) == "// 1",
                    func.substr(JmoMessageTable.originalmessage, 14, 5) == "//// ",
                    func.substr(JmoMessageTable.originalmessage, func.strpos(JmoMessageTable.originalmessage, " 26/// ") + 10, 3) == "// "
                ),
                or_(
                    func.substr(JmoMessageTable.originalmessage, 16, 4) == "// 1",
                    func.substr(JmoMessageTable.originalmessage, func.strpos(JmoMessageTable.originalmessage, " 99") + 22, 3) == "// "
                ),
                or_(
                    func.strpos(JmoMessageTable.originalmessage, " 99").between(11, 20),
                    func.substr(JmoMessageTable.originalmessage, 7, 6).in_("45/// ", "46/// ")
                ),
                func.substr(JmoMessageTable.originalmessage, 16, 3) == "// "
            )

后续尝试的过滤语句 #

.filter(
                or_(
                    JmoMessageTable.originalmessage[16:20] == "// 1",
                    JmoMessageTable.originalmessage[14:19] == "//// ",
                    JmoMessageTable.originalmessage[(JmoMessageTable.originalmessage.find(" 26/// ") + 10):((JmoMessageTable.originalmessage.find(" 26/// ") + 10) + 3)] == "// "
                ),
                or_(
                    JmoMessageTable.originalmessage[16:20] == "// 1",
                    JmoMessageTable.originalmessage[(JmoMessageTable.originalmessage.find(" 99") + 22):((JmoMessageTable.originalmessage.find(" 99") + 22) + 3)] == "// ",
                ),
                or_(
                    (JmoMessageTable.originalmessage.find(" 99") >= 11 & JmoMessageTable.originalmessage.find(" 99") <= 20),
                    (JmoMessageTable.originalmessage[7:13] == "45/// " | JmoMessageTable.originalmessage[7:13] == "46/// ")
                ),
                JmoMessageTable.originalmessage[16:19] == "// "
            )

解决方案 #

1. 给SQLite注册自定义instr函数（推荐） #

SQLAlchemy允许给SQLite注册自定义函数，对齐Oracle的instr行为（注意Oracle的instr返回从1开始的索引，Python的find返回从0开始，需要加1转换）。这样可以直接在SQLAlchemy查询中使用func.instr，同时substr在Oracle和SQLite的参数格式一致，无需额外修改。

from sqlalchemy import create_engine
import sqlite3

def sqlite_instr(str_val, substr_val):
    if not str_val or not substr_val:
        return 0
    # Oracle instr返回1开始的位置，SQLite find返回0开始，加1对齐
    return str_val.find(substr_val) + 1

# 创建SQLite引擎时注册函数
engine = create_engine('sqlite:///your_test_db.db')
with engine.connect() as conn:
    # 注册名为instr的函数，接收2个参数
    conn.connection.create_function('instr', 2, sqlite_instr)

重构后的WHERE子句，严格对应原PL/SQL的逻辑：

from sqlalchemy import func, or_, and_

.where(
    JmoMessageTable.observationid == sfc.observationid,
    or_(
        # 条件1：从第16位取4个字符等于"// 1"
        func.substr(JmoMessageTable.originalmessage, 16, 4) == "// 1",
        # 条件2：从第14位取5个字符等于"//// "
        func.substr(JmoMessageTable.originalmessage, 14, 5) == "//// ",
        # 条件3：找到" 26/// "的位置大于3，且从该位置+10开始取3个字符等于"// "
        and_(
            func.instr(JmoMessageTable.originalmessage, " 26/// ") > 3,
            func.substr(JmoMessageTable.originalmessage, func.instr(JmoMessageTable.originalmessage, " 26/// ") + 10, 3) == "// "
        ),
        # 条件4：找到" 99"的位置在11-20之间，且从该位置+22开始取3个字符等于"// "
        and_(
            func.instr(JmoMessageTable.originalmessage, " 99").between(11, 20),
            func.substr(JmoMessageTable.originalmessage, func.instr(JmoMessageTable.originalmessage, " 99") + 22, 3) == "// "
        ),
        # 条件5：从第7位取6个字符是"45/// "或"46/// "，且从第16位取3个字符等于"// "
        and_(
            func.substr(JmoMessageTable.originalmessage, 7, 6).in_(["45/// ", "46/// "]),
            func.substr(JmoMessageTable.originalmessage, 16, 3) == "// "
        )
    )
)

2. 拉取数据后在Python层过滤（备选） #

如果注册函数不可行，可先查询出基础条件的记录，再用Python字符串操作过滤。这种方法适合小数据集，大数据量会影响性能。

# 先查询所有匹配observationid的记录
messages = session.query(JmoMessageTable).filter(
    JmoMessageTable.observationid == sfc.observationid
).all()

filtered_obs_ids = []
for msg in messages:
    om = msg.originalmessage
    # 注意Python切片从0开始，对应Oracle的起始位置要减1
    cond1 = om[15:19] == "// 1"  # Oracle substr(16,4) → Python [15:19]
    cond2 = om[13:18] == "//// "  # substr(14,5) → [13:18]
    pos_26 = om.find(" 26/// ")
    cond3 = pos_26 > 3 and om[pos_26 + 10 : pos_26 + 13] == "// "
    pos_99 = om.find(" 99")
    cond4 = 11 <= pos_99 <= 20 and om[pos_99 + 22 : pos_99 + 25] == "// "
    cond5 = om[6:12] in ("45/// ", "46/// ") and om[15:18] == "// "  # substr(7,6) → [6:12]
    
    if cond1 or cond2 or cond3 or cond4 or cond5:
        filtered_obs_ids.append(msg.observationid)

# 后续用filtered_obs_ids作为过滤条件继续处理

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