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

1. thing_string, defined as thing_string = "something"

2. 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 ?

CodePudding user response：

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