Path: csiph.com!usenet.pasdenom.info!news.albasani.net!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: How to find bad row with db api executemany()?
Date: Fri, 29 Mar 2013 23:53:53 -0400
Organization: > Bestiaria Support Staff <
References: <F40EFF8D-9F4F-4ACB-8671-450F3CD761CA@panix.com> <5155E32A.1000403@davea.name> <mailman.3971.1364595940.2939.python-list@python.org> <roy-A61512.20410329032013@news.panix.com> <roy-1FF146.22445329032013@news.panix.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.3985.1364615647.2939.python-list@python.org>
Lines: 57
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:42313

On Fri, 29 Mar 2013 22:44:53 -0400, Roy Smith <roy@panix.com> declaimed
the following in gmane.comp.python.general:

> 
> OMG, this is amazing.
> 
> http://stackoverflow.com/questions/3945642/
> 
> It turns out, the MySQLdb executemany() runs a regex over your SQL and 
> picks one of two algorithms depending on whether it matches or not.
> 
	Hmm, I never tracked deeper than the cursors.executemany() block...
Then again, I don't do regex's (for my uses I've often been able to code
a simple parser using .split(), "string" in list-or-string, etc. faster
than reading the help system for regex syntax).

> restr = (r"\svalues\s*"
>         r"(\(((?<!\\)'[^\)]*?\)[^\)]*(?<!\\)?'"
>         r"|[^\(\)]|"
>         r"(?:\([^\)]*\))"
>         r")+\))")
> 
> Leaving aside the obvious line-noise aspects, the operative problem here 
> is that it only looks for "values" (in lower case).
>
	Well, I suppose if one's application documentation is complete
enough to mention it, one could maybe edit that part of MySQLdb to be
case insensitive (or do a .lower() on the query string only where this
check is performed). Documented so the next time one updates the adapter
one as a reminder to adjust that functionality.
  
> I've lost my initial test script which convinced me that executemany() 
> would be a win; I'm assuming I used lower case for that.  Our production 
> code uses "VALUES".
>
	I'd not have been affected -- I don't write SQL with uppercase
keywords in practice; maybe only when discussing in a forum will I take
the time to uppercase them...
 
> The slow way (i.e. "VALUES"), I'm inserting 1000 rows about every 2.4 
> seconds.  When I switch to "values", I'm getting more like 1000 rows in 
> 100 ms!
>
	The slow algorithm literally is "one .execute() per record" --
looking at my MySQL references MySQL does support a form of INSERT in
which it supplies a "list" of parameter values (not as a "prepared
statement" with placeholders, which is what I suspect PostgreSQL's
adapters use).

> A truly breathtaking bug.

	Especially given how long MySQLdb has existed (the multi-record
INSERT goes back to MySQL 3.something).
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/