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.

Not a direct answer, but you might want to read initd.org/psycopg/docs/sql.html. — chepner
– chepner, Commented Mar 25, 2018 at 16:41

klin · Accepted Answer · 2018-03-25 16:54:30Z

5

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()),))

answered Mar 25, 2018 at 16:54

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

AlexW Over a year ago

Yes, it was because my column was of type char[]. Thanks!

Collectives™ on Stack Overflow

Python: malformed array literal when inserting a string

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