CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Sam
Sam

Reputation: 313

mogrify and returning with psycopg2

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?

Upvotes: 4

Views: 20021

Answers (1)

Sam
Sam

Reputation: 313

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

Upvotes: 9

Related Questions