How to pass the value of a postgres array to asyncpg connection.execute as parameter?

Question

I am trying to build a piece of code that uses asyncpg to add content to a table in my postgres database, defined as follows:

CREATE TABLE my_table (
    id              SERIAL NOT NULL UNIQUE,
    nested_field    varchar(100) NOT NULL UNIQUE,
    subfields       varchar(100)[]
);

From my POV, the difficult part is to save content into the postgres array variable.

The piece of code that I have built is the following one:

try:
    await connection.execute(query, thing_string, subfields_string)
    return None
except (Exception, asyncpg.UniqueViolationError) as integrError:
    # some other action
except (Exception, asyncpg.ConnectionFailureError) as error:
    # some other action
finally:
    # some other action

where the query it runs is defined as:

query = """
    INSERT INTO my_table(thing, subfields) 
    VALUES($1,$2);
    """

and the args*

(here are the docs about args* argument for function connection.execute of asyncpg)

that will be put into the string as $1 and $2 are

thing_string, defined as thing_string = "something"
and subfields_string, obtained by running the line

subfields_string = from_list_to_stringified_set(list_of_subfields)

where

list_of_subfields = ["one, two, three"]

and the function defined as follows:

def from_list_to_stringified_set(list_of_subfields):
    """
    Given a list of subfields
    [ "subfield1", "subfield2", "subfield3" ]
    it returns
    '{ "subfield1", "subfield2", "subfield3" }'
    """
    subfields_string = ""
    for subfield in list_of_subfields:
        subfields_string = subfields_string + '", "' + subfield
    subfields_string = '{' + subfields_string[3:] + '"}'
    return subfields_string

so that the value of subfields_string results '{"one, two, three"}' (this result is achieved correctly by my code).

In order to work correctly, the query run on the database should be:

# desired result
INSERT INTO my_table(title, subfields) 
VALUES('something','{"one", "two", "three"}');

However, as I try to run the script I get

asyncpg.exceptions.DataError: invalid input for query argument $2: '{"one", "two", "three"}' (a sized iterable container expected (got type 'str'))

So connection.execute(...) is not accepting my 2nd argument, subfields_string, having value '{"one, two, three"}', because apparently it wants an iterable instead of a string.

But why?

The other arguments I am passing to connection.execute(...) as part of args* are also strings, so why the second argument is rejected and the first one is accepted?

And how can I change my code in order to get the # desired result ?

klin · Accepted Answer · 2021-09-30 14:15:53Z

2

Use a list of strings as an argument.

    query = """
        INSERT INTO my_table(nested_field, subfields) 
        VALUES($1,$2);
        """
    thing_string = 'something'
    subfields_string = ["one", "two", "three"]
    await connection.execute(query, thing_string, subfields_string)

The column subfields is a varchar array. The corresponding Python type for it is a list, as stated in the documentation

edited Sep 30, 2021 at 14:15

answered Sep 30, 2021 at 13:55

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Tms91 Over a year ago

So I don't need to transform asyncpg input from list_of_subfields to subfields_string, sounds logic. I could not think that every person that needs to fill in an array field via asyncpg would had to implement a stringifying function... Thanks!

Collectives™ on Stack Overflow

How to pass the value of a postgres array to asyncpg connection.execute as parameter?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related