psycopg2 executemany with simple list?

Question

I'm trying to use psycopg2 executemany for a simple multi-insert but I can only make it work using dict and not "plain" sequence of values:

# given:
values = [1, 2, 3] ; cursor = conn.cursor()

# this raises TypeError: 'int' object does not support indexing:
cursor.executemany('INSERT INTO t (col_a) VALUES ( %s )', values)
# I also tried encapsulating my 'values' into a tuple/list but it gives another exception (TypeError: not all arguments converted during string formatting).

# while this is ok:
cursor.executemany('INSERT INTO t (col_a) VALUES ( %(value)s )', [  dict(value=v) for v in values ])

isn't it possible to give a "simple" list/tuple of values without using "named" parameter (%(value)s) ?

score 7 · Accepted Answer · answered Oct 03 '13 at 08:43

7

executemany expects a sequence of sequences, eg. a list of lists:

[[v] for v in values]

answered Oct 03 '13 at 08:43

Janne Karila

24,266
6
53
94

1

You should use a list of tuples. Using lists with `execute()` and `executemany()` is not officially supported by psycopg. – fog Feb 21 '19 at 10:35
@fog Is it not? [The docs](http://initd.org/psycopg/docs/cursor.html#cursor.execute) state: "Parameters may be provided as sequence or mapping". Lists and tuples are both sequences in Python. – Janne Karila Feb 21 '19 at 11:46
@JanneKarila You are (mostly) right. Initially psycopg supported only tuples and the documentation referred explicitly to that. Even today all the `execute()` examples _except one_ show tuples, not lists. The code always supported lists because the access is done using `PySequence_*()` methods and we never checked explicitly for tuples. I still better like tuples, even if lists are accepted and sometimes I forget and I write misleading comments like the one above. :) – fog Feb 21 '19 at 12:23

score 3 · Answer 2 · answered Oct 03 '13 at 08:44

executemany() takes a list of parameters and each single parameter should be an object that works with execute(), i.e., a tuple or a dict, but not a simple value like a number or string. That's why the second version is OK: you're generating multiple dicts. You can also just write:

values = [(1,), (2,), (3,)]

where each element of the list is a tuple.

psycopg2 executemany with simple list?

2 Answers2

Linked