Python脚本多占位符SQL执行报错ORA-01036问题求助

你遇到的问题是因为Oracle的Python数据库驱动（如cx_Oracle）不支持?作为参数占位符，它要求使用位置占位符（:n，n为数字序号）或命名占位符，而非其他数据库常用的?格式。

修改后的代码（位置占位符） #

starting_date='01/01/2023'
ending_date='01/04/2023'

params = (starting_date, ending_date)
cur.execute('''
    SELECT analisi.codice, analisi.descrizione 
    FROM analisi 
    LEFT JOIN ris ON analisi.codice = ris.analisi 
    LEFT JOIN ric ON ris.richiesta_id = ric.richiesta_id 
    WHERE ric.richiesta_ins_data >= TO_DATE(:1, 'dd/mm/yyyy') 
      AND ric.richiesta_ins_data <= TO_DATE(:2, 'dd/mm/yyyy') 
      AND ris01 IS NOT NULL 
      AND ris01 NOT IN ('N01','N10') 
    GROUP BY analisi.codice, analisi.descrizione
''', params)

更清晰的命名占位符写法 #

starting_date='01/01/2023'
ending_date='01/04/2023'

params = {'start_date': starting_date, 'end_date': ending_date}
cur.execute('''
    SELECT analisi.codice, analisi.descrizione 
    FROM analisi 
    LEFT JOIN ris ON analisi.codice = ris.analisi 
    LEFT JOIN ric ON ris.richiesta_id = ric.richiesta_id 
    WHERE ric.richiesta_ins_data >= TO_DATE(:start_date, 'dd/mm/yyyy') 
      AND ric.richiesta_ins_data <= TO_DATE(:end_date, 'dd/mm/yyyy') 
      AND ris01 IS NOT NULL 
      AND ris01 NOT IN ('N01','N10') 
    GROUP BY analisi.codice, analisi.descrizione
''', params)

更优方案：直接传入datetime对象 #

避免手动处理日期格式，让驱动自动完成转换，减少格式错误风险：

from datetime import datetime

starting_date = datetime(2023, 1, 1)
ending_date = datetime(2023, 4, 1)

params = (starting_date, ending_date)
cur.execute('''
    SELECT analisi.codice, analisi.descrizione 
    FROM analisi 
    LEFT JOIN ris ON analisi.codice = ris.analisi 
    LEFT JOIN ric ON ris.richiesta_id = ric.richiesta_id 
    WHERE ric.richiesta_ins_data >= :1 
      AND ric.richiesta_ins_data <= :2 
      AND ris01 IS NOT NULL 
      AND ris01 NOT IN ('N01','N10') 
    GROUP BY analisi.codice, analisi.descrizione
''', params)

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