Psycopg2 query parameter types

I have a python script that calls postgresql proc using psycopg2. Proc has the following signature.

CREATE FUNCTION utility_function(p_id_file bigint, p_size bigint, p_id_volume smallint, p_id_type_file bigint, p_id_user bigint) RETURNS void


When I call it with the following code, I get this error ...

Traceback (most recent call last):
  File "/tmp/regenerate/", line 173, in <module>
    execute_process(db, out_csv, out_file, start_date, end_date, limit, offset)
  File "/tmp/regenerate/", line 57, in execute_process
    db.execute(sql, (id_file, size, id_volume, id_type_file, id_user))
psycopg2.ProgrammingError: function utility_function(integer, integer, integer, integer, integer) does not exist
LINE 1: select * from utility_function(13456, 132456798, 0...
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


I cannot figure out how to pass parameters to the correct postgresql type.

The python code looks like this.

sql = 'select * from utility_function(%s, %s, %s, %s, %s)'
logging.debug(db.mogrify(sql, (id_file, size, id_volume, id_type_file, id_user)))
db.execute(sql, (id_file, size, id_volume, id_type_file, id_user))


db.mogrify returns this:

select * from utility_regenerate_tsstream(13456, 132456798, 0, 42, 1324)


Thanks in advance for your help :) Sam


source to share

2 answers

You can specify types in the request as cast:

sql = 'select * from utility_function(%s::bigint, %s::bigint, %s::smallint, %s::bigint, %s::bigint)'




I managed to overcome the problem by changing the type of the proc parameter from smallint to int. It seems that psycopg2 does not handle type inference for smallint ...

p_id_volume smallint -> p_id_volume int

There were no other changes.



All Articles