Re: speed concerns with executemany()

On Mon, Jan 30, 2017 at 11:18:36AM +0000, Daniele Varrazzo wrote:
> 3) a single insert with many params. Plays well with PQexecParams
> but would need some form of generation by the client
> - insert into table values (...), (...), (...);
>
> While the proposed executemany is a nice low hanging fruit it will
> break on PQexecParams and it's far from being optimal anyway. Wonder
> if there is a way to help users at least to have 3 without bothering
> with mogrify (due to break too with the PQexecParams switch).

Supporting a general case would surely require parsing the sql
statement to some extent, but a simple insert could be done quite
easily, particularly if you change the call signature so the caller
does your work for you:

def insert_batch(cur, sql, template, args):
argslist = list(args)
sql_full = sql + ','.join([template] * len(argslist))
cur.execute(sql_full, reduce(operator.add, argslist))

insert_batch(cur, "insert into testmany (num, data) values ", "(%s, %s)", data)