Thread: batch inserts in python & libpq

Hi all,

using Java I'm able to get a 10000 inserts/sec on our server using batch updates (using preparedStatement.addBatch()).

Using Python I can't get past 2000 inserts/sec, which is roughly the same performance of Java without addBatch.

Is there a Python driver that uses the same protocol form of addBatch in Java?
I even looked at the libpq to see, but I couldn't find any reference to batch updating... not even in the protocol
specs.Sniffing the protocol I think I got that mainly the difference is in the round-trips to the server... but don't
knowhow to tell the Python driver/libpq to do it "the Java way". 

On Fri, Aug 07, 2009 at 01:37:08PM +0000, Scara Maccai wrote:
> using Java I'm able to get a 10000 inserts/sec on our server using
> batch updates (using preparedStatement.addBatch()).

I'd probably generate SQL that looks somewhat like the following:

  UPDATE foo f SET v = x.v
    FROM (VALUES (1,'hi'), (2,'bye')) x(i,v)
    WHERE f.id = x.i;

The "best" solution depends on the details of the problem though!

--
  Sam  http://samason.me.uk/

> Is there a Python driver that uses the same protocol form
> of addBatch in Java?


I'll answer my own question: the method to be used should be

cursor.executemany()