Python: malformed array literal when inserting a string

Question

I'm trying to read from a file and insert the data to a postgresql table in python using the psycopg2.

Here's the function I wrote:

def insert(file, table, col, conn):
    sql = "INSERT INTO "+table+"("+col+") VALUES(%s)"
    cur = conn.cursor()
    with open(os.path.join(DEFAULTS_FOLDER, file)) as fp:
        line = fp.readline()
        while line:
            cur.execute(sql, (line.rstrip(),))
            line = fp.readline()
        conn.commit()
        cur.close()
    return

For some reason I get an error:

cur.execute(sql, (line.rstrip(),)) psycopg2.DataError: malformed array literal: "hello" LINE 1: INSERT INTO greetings(gname) VALUES('hello')

I also tried to insert a plain string and I still get the same error.

Answer 1

The error message means that the column gname of the table greetings is an array, not a plain text. If it is a text array, the query should look like this:

INSERT INTO greetings(gname) VALUES('{hello}')

You should change the relevant fragment of your code, e.g.:

cur.execute(sql, ("{{{}}}".format(line.rstrip()),))