executemany & UPDATE (MySQL & Python)

[Andy Dustman]

On Fri, 2002-06-14 at 15:17, Gerhard Häring wrote:
> (Cc-ing Andy Dustman)
> 
> * Nick Arnett <narnett at mccmedia.com> [2002-06-14 11:02 -0700]:
> > > Gerhard wrote:
> > > > Nick Arnett wrote:
> > > > > Can anyone confirm that it is *not* possible to pass a list to
> > > > > the Python MySQLdb module in UPDATE operations?
> 
> ...
> 
> > > For database modules that don't support prepared statements, the db module
> > > implements executemany with multiple calls to execute, anyway.
> > 
> > Well, since MySQLdb is much faster at inserting that way, I'm assuming that
> > if it'll do it on an UPDATE, that will also be much faster.
> 
> I mistakenly assumed that MySQLdb's executemany was implemented the same
> way as pyPgSQL's. That assumption was wrong. Looking into the source
> reveals that MySQLdb's implementation is really clever, but
> unfortunately hardcoded to only work with INSERT statements.

Yes, MySQL has syntax for a multi-row INSERT, but apparently no such
similar syntax for UPDATE, so they have to be done as seperate queries.

REPLACE has the same syntax as INSERT (a MySQL extension that INSERTS
but overwrites any existing rows), and that should also work fine with
executemany(), though I haven't used it.

I will change MySQLdb though to simply perform the query multiple times
if it does not seem to match the VALUES (...), (...), ... syntax; I
consider it a bug that it doesn't do this already, but nobody seems to
care (until now).

-- 
Andy Dustman         PGP: 0x930B8AB6
    @       .net     http://dustman.net/andy
"Cogito, ergo sum." -- Rene Descartes
"I yam what I yam and that's all what I yam." -- Popeye