MySQLdb warnings problem

[Gerhard Häring]

* Terry Hancock <hancock at anansispaceworks.com> [2002-06-08 09:03 -0700]:
> I'm writing this crawler now, and it's my first
> experience with "Python Database API 2.0", which seems
> really overblown and complicated compared to the simple
> interface described in O'Reilly's MySQL/mSQL book, or
> with the ZSQL interface in Zope. I suppose this is
> for the benefit of more complicated RDBMs than MySQL.

The DB-API 2.0 is really a rather slim interface, and in no way
overblown or complicated. Good beginner-level online docs on it are
sparse, though.

> Anyway, I hope I'm not making a mistake using it --
> I'm just trying to use the most "standard" Python
> interface to MySQL, and I'm under the impression that
> this is it. Please advise if I'm wrong.

No, MySQLdb is the way to go, and it is a really fine interface.

> [snip traceback]

Sorry, I don't know what's the reason for this traceback.
 
> For reference, here's a snippet of the code where this
> broke:
> 	sqlcmd= """
> 		UPDATE user SET nposts=%d, nrecent=%d, keytopic='%s'
> 		WHERE username='%s'
> 		""" % ( n_posts, n_recent, keytopic, user[0] )
> 
> 	dbcursor.execute(sqlcmd)

Here we have the single most common misuse of the cursor's execute
statement. Consider what happens if your string contains a single
apostrophe. Yes, right, the SQL will break.

That's why the cursor's execute command has an optional second
parameter:

.execute(operation[,parameters]) 
           
    Prepare and execute a database operation (query or command).
    Parameters may be provided as sequence or mapping and will be bound
    to variables in the operation.  Variables are specified in a
    database-specific notation (see the module's paramstyle attribute
    for details).

Ok, so let's rewrite this to the appropriate form:

    sqlcmd= """UPDATE user SET nposts=%d, nrecent=%d, keytopic='%s'
               WHERE username='%s'"""

    dbcursor.execute(sqlcmd, (n_posts, n_recent, keytopic, user[0]))

This form ensures that the appropriate quoting is used for each of the
parameters (n_posts, n_recent, keytopic and user[0]).

Gerhard
-- 
This sig powered by Python!
Außentemperatur in München: 16.8 °C      Wind: 2.4 m/s