mogrify and returning with psycopg2
The problem was that I thought I had to put an string parameter interpolation for every value to insert. I'll explain it:
Let's say I have a list with 2 dictionaries (with length of three) with the data to insert:
lst = [{'a': 21, 'b': 9, 'c': 33}, {'a': 76, 'b': 84, 'c': 46}]
In order to insert those values I did something similar to:
query = curs.mogrify("INSERT INTO {} ({}) VALUES {} RETURNING {}".format(
table,
', '.join(lst[0].keys()),
', '.join(['%s'] * len(lst[0].values())),
'id'
), [tuple(v.values()) for v in lst])
appending a total of three '%s'
(one for every item in a single dict in lst
). The result for this is the exception message error list index out of range
and this happens because curs.mogrify()
is expecting just one '%s'
for every dict in lst
, so for this case it only needs two '%s'
instead three.
Therefore, the correct form to insert data from lst
is:
query = curs.mogrify("INSERT INTO {} ({}) VALUES {} RETURNING {}".format(
table,
', '.join(lst[0].keys()),
', '.join(['%s'] * len(lst)),
'id'
), [tuple(v.values()) for v in lst])
Replacing len(lst[0].values())
by len(lst)
.
This how I solved my problem (I did not understand this because I did not read the code of the example properly). I hope this helps.
Sam
Sam
BY DAY: Husband and father, just to go unnoticed and learn more about the weird species known as humans BY NIGHT: Student trying to learn all about coding, preparing myself to save the world. FOR FUN: learning, coding, movies, did I said coder?
Updated on June 04, 2022Comments
-
Sam almost 2 years
I'm trying to generate automaticatilly queries. I was thinking using executemany, but I need to use returning statement so I did:
def format_bind(cols, rows): return '(' + '), ('.join([', '.join(['%s'] * cols)] * rows) + ')' def insert_many(table, values, id_column): if not values: return [] keys = values[0].keys() conn = psycopg2.connect("dbname='db' user='postgres' host='localhost' password='postgres'") cursor = conn.cursor() query = cursor.mogrify("INSERT INTO {} ({}) VALUES {} RETURNING {}".format(table, ', '.join(keys), format_bind(len(keys), len(values)), id_column), [tuple(v.values()) for v in values]) cursor.execute(query) return [t[0] for t in (cursor.fetchall())]
The problem is when I execute it, I get:
error list index out of range
Can anybody help me to fix this?