MySQLdb + Python + quotes

[Paul Boddie]

Lars Hoeyrup Jensen wrote:
> 
> I know, I _had_ found that in the news archives, but as Jeff says: it's a
> different MySQL-module from what I use (I use MySQLdb, not MySQL). Therefore
> it didn't work.

Rather than using Python's value substitution in strings as suggested, it should
be possible to use "bind variables" in MySQLdb like this:

  cursor.execute("SELECT * FROM MY_TABLE WHERE MY_COLUMN = %s", (my_column,))

Note the use of the Python/C style "%s", but note also that we aren't using such
"placeholders" with Python's % operator - an additional tuple parameter is
required. As a result, the database module should process the values in the
tuple and hopefully get the database to bind the values to the query (or action)
accordingly.

This is all just speculation on my part, however, as I have never really done
any work with MySQL, but I do remember something about this in the MySQLdb
documentation. Other database modules use different syntax for the placeholders;
for example, Sybase products prefer "?", and replacing the placeholders in the
above example would indeed make it work on Sybase products.

I would discourage the use of simple string substitution when building queries.
After all, the database modules are there to make the handling of all kinds of
values, and the issues surrounding them (such as quote escaping) transparent. If
your database module doesn't support this kind of functionality then I would
recommend either improving it so that it does, or finding another which does; I
wouldn't consider modules which are deficient in this respect as being suitable
for serious work.

Paul