MySQL + SQL Statements + Quote escaping

[Chris Stromberger]

>Christian Theune wrote:
>
>    i just tuned into python and it's really cool. I like it.
>    Before I came to python i used to write scripts in php.
>    There was a function called "addslashes" it escaped quotes and
>    slashes to make strings sql-safe.
>
>Database modules that conform to the DB-API specfication, such as
>MySQLdb, provide a placeholder mechanism that will take care of the
>quoting automatically. For example:
>
>    cursor.execute(
>        "select * from customers where surname = %s and age < %s", 
>        ("O'Hara", 40)
>    )
>
>The %s placeholders are replaced by correctly quoted values.
>
>There are two advantages to this approach.
>
>Firstly, database engines differ in how they escape quotes. Many, such
>as MySQLdb, prefix them with a backslash, but other DB engines escape
>quotes by doubling them (thus 'O''Reilly'). Using the placeholder
>mechanism lets the particular database module deal with that for you.
>
>The second advantage is that the execute() method can optimise its
>performance when the same operation is repeatedly executed but with
>different bound values for the placeholders. But if you build the query
>string yourself on each iteration, then the execute() method will have
>to parse it each time for correctness, thus losing the opportunity for
>optimisation.
>
>Hamish Lawson
>


This doesn't work for me.  I have to add single quotes around the %s
and then it still doesn't escape the single quote in the substituted
string.  What am I missing?

Thanks,
Chris

Eg (this works if there's no quotes in the substituted string):

    cursor.execute(
        "select * from customers where surname = '%s' and age < %s", 
        ("O'Hara", 40)
    )