Inserting rows into db from a list of tuples using cursor.mogrify gives error

Question

I am trying to insert large number of rows into postgres using cursor.mogrify using this psycopg2: insert multiple rows with one query

data is a list of tuples where each tuple is a row that needs to be inserted.

 cursor = conn.cursor()

    args_str = ','.join(cursor.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in data)

    cursor.execute(
        "insert into table1 (n, p, r, c, date, p1, a, id) values " + args_str)`

but getting error :

TypeError: sequence item 0: expected str instance, bytes found

at line:

  args_str = ','.join(cursor.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in data)

If I try to change to b''.join(cursor.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in data) , then execute query gives error for inserting byte....

Am I doing something wrong ?

@RomanTkachuk Agree with you that COPY is many times faster than this.. but sometimes writing to a file is not an option or even if it is sometimes copying the file to server is not. — MANU, Feb 02 '17 at 10:20

Clodoaldo Neto · Accepted Answer · 2017-02-02T12:07:30.380

8

data = [(1,2),(3,4)]
args_str = ','.join(['%s'] * len(data))
sql = "insert into t (a, b) values {}".format(args_str)
print (cursor.mogrify(sql, data).decode('utf8'))
#cursor.execute(sql, data)

Output:

insert into t (a, b) values (1, 2),(3, 4)

edited Feb 02 '17 at 12:07

answered Feb 02 '17 at 11:32

Clodoaldo Neto

118,695
26
233
260

Maurice Meyer · Answer 2 · 2017-02-02T11:49:27.413

You could do something like this, but verify dict values to prevent sql injection.

>>> from psycopg2.extensions import AsIs
>>> _insert_sql = 'INSERT INTO myTable (%s) VALUES %s RETURNING id'    
>>> data = {"col_1": "val1", "col_2": "val2"}
>>> values = (AsIs(','.join(data.keys())), tuple(data.values()))
>>> print(cur.mogrify(_insert_sql, values))
b"INSERT INTO myTable (col_1,col_2) VALUES ('val1', 'val2') RETURNING id"

Inserting rows into db from a list of tuples using cursor.mogrify gives error

2 Answers2

Linked