Python SQL代码执行报错：Not all parameters were used 求助排查

运行自定义Python函数insertUpdateTestCase时，持续触发两条CRITICAL级错误：

CRITICAL >> Not all parameters were used in the SQL statement
CRITICAL >> Exception for errors programming errors

已排查SQL语句参数与字段数量一致，源数据库执行查询能返回无空值/空字段的结果，但仍未找到问题，请求定位错误原因。相关代码如下：

#************ TestCase Table Insertion *********************
def insertUpdateTestCase(prev_max_weeknumber):
        log.Log('insertUpdateTestCase START', 'info')
        insertUpdateTestCase_start_time = datetime.now()

        testcases = """INSERT INTO prtm_testcase (testplan_identifier, testcase_name, testcase_identifier, testcase_longidentifier, testcase_uri, globalconfiguration_identifier, weeknumber, localconfiguration_identifier)
                                                VALUES
                                                (%s,%s,%s,%s,%s,%s,%s,%s)
                                        ON CONFLICT (testplan_identifier, testcase_identifier, globalconfiguration_identifier, localconfiguration_identifier, weeknumber)
                                        DO
                                          UPDATE SET testcase_name = EXCLUDED.testcase_name,
                                          testcase_longidentifier=EXCLUDED.testcase_longidentifier,
                                          testcase_uri=EXCLUDED.testcase_uri,
                                          weeknumber=EXCLUDED.weeknumber """

        # Define some variables for executing Select Query based on limits
        offset = 0
        per_query = 10000

        while True:
                #execute query based on limits using projects
                cursor_remets.execute("select tsdata.ts_moduleid, coalesce(tsdata_extended.ts_objecttext,'Unknown') as ts_objecttext, tsdata.ts_objectidentifier, SUBSTRING_INDEX(tsdata.ts_variant, ',', -1) as after_comma_value, tsdata.weeknumber,SUBSTRING_INDEX(tsdata.ts_variant, ',', -1) as project_id,SUBSTRING_INDEX(tsdata.ts_variant, ',', 1) as gc_id from tsdata left join tsdata_extended on tsdata_extended.ts_objectidentifier = tsdata.ts_objectidentifier and tsdata.ts_moduleid = tsdata_extended.ts_moduleid and tsdata.ts_variant = tsdata_extended.ts_variant where tsdata.weeknumber=%s OFFSET %s", (prev_max_weeknumber, per_query, offset))

                rows = cursor_remets.fetchall()

                if len(rows) > 0:
                        for row in rows:
                                #print(row)
                                testcond = (row[0] and row[1] and row[2] and row[4] and row[5] and row[6])
                                #testcond = True
                                if testcond:
                                        cursor_prtm.execute(testcases,(row[0],row[1].replace("\x00", "\uFFFD").replace('\\', '\\\\'),row[2],None,None,row[6],row[4],row[5]))
                                        conn_prtm.commit()
                                        DataMigration.validateInsertedRecord('insertUpdateTestCase', row)
                                else:
                                        log.Log('In insertUpdateTestCase, row validation failed ' + str(row), 'info')
                else:
                        break

                #print(str(len(rows)) + ' rows written successfully to prtm_testcase')

                offset += per_query

        log.Log('insertUpdateTestCase completed execution in ' + str(datetime.now()-insertUpdateTestCase_start_time), 'info')

1. SELECT语句参数与占位符不匹配（直接触发报错） #

问题出在分页查询的execute调用：

cursor_remets.execute("select ... where tsdata.weeknumber=%s OFFSET %s", (prev_max_weeknumber, per_query, offset))

• SQL语句中仅定义了2个占位符%s（对应weeknumber和OFFSET）
• 但传入的参数是3个：prev_max_weeknumber、per_query、offset
• 多余的per_query参数未被SQL使用，直接导致Not all parameters were used in the SQL statement错误

修复方式 #

补上漏写的LIMIT子句（符合你分页取数的逻辑）：

cursor_remets.execute(
    "select tsdata.ts_moduleid, coalesce(tsdata_extended.ts_objecttext,'Unknown') as ts_objecttext, tsdata.ts_objectidentifier, SUBSTRING_INDEX(tsdata.ts_variant, ',', -1) as after_comma_value, tsdata.weeknumber,SUBSTRING_INDEX(tsdata.ts_variant, ',', -1) as project_id,SUBSTRING_INDEX(tsdata.ts_variant, ',', 1) as gc_id from tsdata left join tsdata_extended on tsdata_extended.ts_objectidentifier = tsdata.ts_objectidentifier and tsdata.ts_moduleid = tsdata_extended.ts_moduleid and tsdata.ts_variant = tsdata_extended.ts_variant where tsdata.weeknumber=%s LIMIT %s OFFSET %s",
    (prev_max_weeknumber, per_query, offset)
)

2. 冗余的UPDATE字段（非直接报错，但需优化） #

INSERT语句的ON CONFLICT部分，weeknumber=EXCLUDED.weeknumber是冗余操作：

• 冲突条件已经包含weeknumber，冲突发生时新旧记录的weeknumber值必然一致，无需更新
• 删除该字段简化SQL：

testcases = """INSERT INTO prtm_testcase (testplan_identifier, testcase_name, testcase_identifier, testcase_longidentifier, testcase_uri, globalconfiguration_identifier, weeknumber, localconfiguration_identifier)
                VALUES
                (%s,%s,%s,%s,%s,%s,%s,%s)
        ON CONFLICT (testplan_identifier, testcase_identifier, globalconfiguration_identifier, localconfiguration_identifier, weeknumber)
        DO
          UPDATE SET testcase_name = EXCLUDED.testcase_name,
          testcase_longidentifier=EXCLUDED.testcase_longidentifier,
          testcase_uri=EXCLUDED.testcase_uri"""

3. 频繁提交的性能优化（可选） #

当前代码每条记录插入后就调用conn_prtm.commit()，频繁提交会大幅降低性能。建议调整为每处理完一批数据后统一提交：

if len(rows) > 0:
    for row in rows:
        testcond = (row[0] and row[1] and row[2] and row[4] and row[5] and row[6])
        if testcond:
            cursor_prtm.execute(testcases,(row[0],row[1].replace("\x00", "\uFFFD").replace('\\', '\\\\'),row[2],None,None,row[6],row[4],row[5]))
            DataMigration.validateInsertedRecord('insertUpdateTestCase', row)
        else:
            log.Log('In insertUpdateTestCase, row validation failed ' + str(row), 'info')
    # 每批处理完成后统一提交
    conn_prtm.commit()

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