Python传递Pandas数据至PostgreSQL函数的参数类型问题排查

问题背景 #

读取Excel文件存入Pandas DataFrame后，尝试将数据写入PostgreSQL数据库时，在参数化调用PostgreSQL函数环节遇到问题。

错误的参数化调用代码 #

使用以下代码调用数据库函数：

cursor.execute('CALL controldubbel2(%s, %s, %s, %s, %s, %s, %s)', (datum2, naamtegen, tegenrekening, omschrijving1, bedrag, saldo, code))

触发的错误信息 #

Exception has occurred: UndefinedFunction
procedure controldubbel2(unknown, unknown, unknown, unknown, integer, numeric, unknown) does not exist
LINE 1: CALL controldubbel2('2023-01-05', 'Betaalverzoek...
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.

能运行但存在风险的写法 #

以下格式化字符串拼接的写法可以正常执行，但存在严重的SQL注入风险：

cursor.execute(f"SELECT controldubbel2('{datum2}', '{naamtegen}', '{tegenrekening}', '{omschrijving1}', '{bedrag}', '{saldo}', '{code}')")

修改格式符后的错误尝试 #

曾尝试将数值类型对应的%s改为%d，触发如下错误：

Exception has occurred: ValueError
unsupported format character 'd' (0x64) at index 37
  File "/home/hvn/intranet/intranet/Rekening/ing.py", line 87, in <module>
    cursor.execute('CALL controldubbel2(%s, %s, %s, %s, %d, %d, %s)', (datum2, naamtegen, tegenrekening, omschrijving1, bedrag, saldo, code))
ValueError: unsupported format character 'd' (0x64) at index 37

对应的PostgreSQL函数定义 #

CREATE OR REPLACE FUNCTION public.controldubbel2(_datum date, _naamtegen character varying,
_tegenrekening character varying, _omschrijving character varying,
_bedrag numeric, _saldo numeric, _code character varying)
 RETURNS TABLE(bestaat boolean)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT EXISTS (SELECT 1 FROM "rekening" WHERE "DATUM"=_datum
    AND "TEGENREKENING_IBAN_BBAN"=_tegenrekening AND
    "NAAM_TEGENPARTIJ"=_naamtegen AND "Omschrijving_1"=_omschrijving AND
    "BEDRAG"=_bedrag AND "saldo"=_saldo AND "CODE"=_code) as bestaat
LIMIT 1;
END
$function$

解决方案 #

核心问题解析 #

1. 混淆了PostgreSQL的函数（FUNCTION）与存储过程（PROCEDURE）：你定义的是FUNCTION，但用了调用存储过程的CALL语法，正确调用函数需使用SELECT。
2. Python DBAPI参数化查询统一使用%s作为占位符，无论参数类型，无需改成%d。

正确的参数化调用代码 #

cursor.execute('SELECT controldubbel2(%s, %s, %s, %s, %s, %s, %s)', (datum2, naamtegen, tegenrekening, omschrijving1, bedrag, saldo, code))
# 获取查询结果
result = cursor.fetchone()
exists_flag = result[0]

可选：显式类型转换 #

若担心Python变量类型与数据库期望类型不匹配，可在SQL中显式添加类型转换：

cursor.execute('SELECT controldubbel2(%s::date, %s::varchar, %s::varchar, %s::varchar, %s::numeric, %s::numeric, %s::varchar)', 
               (datum2, naamtegen, tegenrekening, omschrijving1, bedrag, saldo, code))

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