Convert a python list to an SQL composite type array to pass as parameter to a postgreSQL function

Question

I have following composite type in postgreSQL:

 Composite type "public.type_data"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 series_id   | text    | 
 data_date   | date    | 
 data_value  | numeric | 
 data_source | text    |

that I use to pass as argument VARIADIC input_array type_data[] to a function in postgreSQL. I also have the following list in python:

list = [['BRLUSD', '2021-02-26', 5.5302, 'gen'], ['BRLUSD', '2021-02-25',  5.46, 'gen']]

that I want to pass as argument to my function. I use query = cur.mogrify("SELECT * FROM data_update(%s::type_data);", (list)) to get the query string but it returns the following error:

query = cur.mogrify("SELECT * FROM data_update(%s::type_data);", (list) )
TypeError: not all arguments converted during string formatting

What am I missing. Is there a better way to pass a list as an array argument for a postgreSQL function?

snakecharmerb · Accepted Answer · 2021-04-05 09:46:25Z

1

You need to cast to an array of type_data rather than the type type_data Also, the inner lists should be tuples: psycopg2 treats lists as arrays.

The register_composite function may be useful if you need to work with composite types returned by Postgresql in Python code.

import psycopg2


with psycopg2.connect(database="test") as conn:
    cur = conn.cursor()

    cur.execute("""DROP FUNCTION IF EXISTS get_my_values""")
    cur.execute("""DROP TYPE IF EXISTS my_type""")
    conn.commit()

    cur.execute(
        """CREATE TYPE my_type AS (series_id text, data_date date, data_value numeric, data_source text)"""
    )

    cur.execute(
        """CREATE FUNCTION get_my_values(my_type[]) RETURNS numeric AS $$
             SELECT $1[1].data_value AS dv
           $$ LANGUAGE SQL;"""
    )

    conn.commit()

    data = [
        (
            "BRLUSD",
            "2021-02-26",
            5.5302,
            "gen",
        ),
        (
            "BRLUSD",
            "2021-02-25",
            5.46,
            "gen",
        ),
    ]

    cur.execute("""SELECT get_my_values(%s::my_type[])""", (data,))

    (result,) = cur.fetchone()
    print(result)

edited Apr 5, 2021 at 9:46

answered Apr 2, 2021 at 6:53

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

M Garcia Over a year ago

Thank you. Two questions: I converted the list to tuples using data = tuple(zip(list[0],list[1],list[2], list[3])) where list = [['BRLUSD', 'BRLUSD'], ['2021-02-26', '2021-02-25'] , [5.5302, 5.46 ], ['gen', 'gen']] and returns ouput (('BRLUSD', '2021-02-26', 5.5302, 'gen'), ('BRLUSD', '2021-02-25', 5.46, 'gen')) ** with class **<class 'tuple'>, but when I run cur.execute("SELECT * FROM data_update(%s::my_type[]);", (data,) ) I get error psycopg2.errors.CannotCoerce: cannot cast type record to my_type[] LINE 1: ...02, 'gen'), ('BRLUSD', '2021-02-25', 5.46, 'gen'))::my_type[...

M Garcia Over a year ago

If I create data with data= [('BRLUSD', '2021-02-26', 5.5302, 'gen'), ('BRLUSD', '2021-02-25', 5.46, 'gen')] it returns class <class 'list'> but cur.execute("SELECT * FROM data_update(%s::my_type[]);", (data,) ) returns psycopg2.errors.UndefinedFunction: function data_update(my_type[]) does not exist LINE 1: SELECT * FROM data_update(ARRAY[('BRLUSD', '2021-02-26', 5.5... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

M Garcia Over a year ago

I am calling a function, that's why there is no WHERE, the only way I was able to make it work is passing the array of values with the tuple data structure as string and callling "SELECT * FROM data_update('" + array_with_string_values + "' );".

snakecharmerb Over a year ago

I'm sorry, I don't know how I missed that you were using a Postgres function. I've amended the answer. If it still doesn't work perhaps you could add your function's signature to the question.

Collectives™ on Stack Overflow

Convert a python list to an SQL composite type array to pass as parameter to a postgreSQL function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related