无法将labNumber传入SQL查询的技术问题求助

_labNumber变量无法传入SQL查询方法，同类其他方法均可正常运行。已确认变量赋值正确，调用时用try-catch块包裹，报错信息为An error occurred: not enough arguments for format string。

调用代码 #

try:
    _comment = self.dataServices.GetComment(_labNumber)
    _value2 = self.dataServices.GetValue2(_labNumber)
    _value1 = self.dataServices.GetValue1(_labNumber)
    _results = self.dataServices.getResults(_labNumber)
except AttributeError:
    _comment = None
    _value2 = None
    _value1 = None
    _results = None

数据查询类方法示例（GetValue2） #

def GetValue2(self, _LabNumber):
    _LabNumberStr = str(_LabNumber)
    try:
        with connection.cursor() as _cursor:
            _cursor.execute("{CALL dbo.uspShireXGetValue2(%s)}", [_LabNumberStr])
            row = _cursor.fetchone()
            if row:
                return {'VALUE2': row[0]}#21
            else:
                return {'VALUE2': None}
    except Exception as e:
        # Handle or log the exception as appropriate
        print(f"An error occurred: {e}")
        return {'VALUE2': None}

已验证正常的存储过程（硬编码参数可返回数据） #

@LabNumber VARCHAR(10)
AS

SET NOCOUNT ON
SELECT
    WDR.COMMENT
FROM dbo.DNA_WORKSHEET_DET WD
INNER JOIN dbo.DNA_WORKSHEET W ON WD.WORKSHEET = W.WORKSHEET
LEFT OUTER JOIN dbo.DNA_TEST T ON W.TEST = T.TEST
LEFT OUTER JOIN dbo.DNA_LAB_TEST LT ON WD.LABNO = LT.LABNO AND W.TEST = LT.TEST
LEFT OUTER JOIN dbo.DNA_WORKSHEET_DET_RESULT WDR ON WD.WORKSHEET = WDR.WORKSHEET AND WD.LANE = WDR.LANE
WHERE WD.LABNO = @LabNumber
return

完整堆栈追踪 #

None
[28/Feb/2024 11:12:13] "GET /Molecular/SolidCancer/?csrfmiddlewaretoken=8hPU3Tfu7mwUNfvNfQSWMYjwy2jEldjJsUh8MUMQ0kT1nmNixlLhkqMOqJmLsV0f&txtCriteriaDateFrom=2019-12-30&txtCriteriaDateTo=2024-03-29&ddlCriteriaStatus=NOTFINAL&txtCriteriaLabnumber=&txtCriteriaLastname=&ddlCriteriaPriority=&ddlCriteriaDiseaseIndication1=&ddlCriteriaDiseaseIndication2=&ddlCriteriaDiseaseIndication3=&ddlAlertCriteria=False&ddlCriteriaReasonForDiseaseIndication1=&ddlCriteriaReasonForDiseaseIndication2=&ddlCriteriaReasonForDiseaseIndication3=&txtPageNumber=1&ddlCriteriaNoResult=0&ddlCriteriaItemsPerPage=20&btnSubmit=Search HTTP/1.1" 200 175938
[28/Feb/2024 11:12:14] "GET /static/Styles.css HTTP/1.1" 404 179
[28/Feb/2024 11:12:14] "GET /static/Scripts.js HTTP/1.1" 404 179
[28/Feb/2024 11:12:14] "GET /static/js/Scripts.js HTTP/1.1" 404 179

解决方案 #

核心原因 #

报错是因为Python将SQL语句中的%s识别为自身的字符串格式化符号，而非数据库参数占位符，导致传参时格式不匹配。

修正步骤 #

1. 更换占位符格式
   若使用pymssql等驱动，调用存储过程需用?作为占位符，同时用元组传递参数（部分驱动对元组支持更稳定）：

   _cursor.execute("{CALL dbo.uspShireXGetValue2(?)}", (_LabNumberStr,))
   

2. 使用命名参数（推荐）
   直接对应存储过程的参数名，避免占位符混淆：

   _cursor.execute("{CALL dbo.uspShireXGetValue2(@LabNumber)}", {'LabNumber': _LabNumberStr})
   

3. 排查参数有效性
   在方法开头添加打印日志，确认传入的_LabNumber无空值或异常格式：

   def GetValue2(self, _LabNumber):
       print(f"当前传入的LabNumber: {_LabNumber}")
       _LabNumberStr = str(_LabNumber)
       # 后续代码...
   

4. 对齐同类方法的写法
   对比其他正常运行的查询方法，统一execute语句的占位符和传参方式，避免不一致导致的问题。

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