Home > database >  Python passing decimal values with comma's to a postgresql function
Python passing decimal values with comma's to a postgresql function

Time:01-31

I have a postgresql function which needs decimal/numeric values as variables. The variables come from a database which holds decimal values with comma's as delimeters. Problem i have is that de postgresql-function now reads these comma's as a delimeter for the different variables.

This is my psql-function:

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 "xxxxxxxxxxxxxxx" 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$

This is my python code to execute the function:

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

Error i get:

ERROR: cannot cast type record to numeric 
LINE 1: SELECT controldubbel2('2023-01-05'::date,'DL453453530005300114'::varchar,'HALTEST'::varchar,'Naam: John Doe'::varchar,10,00::numeric, 23686,98::numeric, 'GT'::varchar)
SQL state: 42846

The problem is the comma's in the numeric values i believe.

Thnx in advanced.

CodePudding user response:

As @user2357112 mentioned, use parametrized queries:

   cursor.execute("SELECT controldubbel2(%s, %s, %s, %s, %s, %s, %s)", (datum2, tegenrekening, naamtegen, omschrijving1, bedrag, saldo, code))
  • Related