MySQLdb select

[Gerhard Häring]

Raaijmakers, Vincent (GE Infrastructure) wrote:
> I'm so glad that this issue is raised here.
> Can someone help me in this thread explaining me how to use multiple parameters.
> Jaco helped me already a lot and perhaps someone in this thread can give me the final answer:
> 
> My table contains a blob and a bigint for storing images. The table name is also a variable, so the query looks like:
> SQL = "INSERT INTO %s (number, image) VALUES (%s, %s)"
> 
> Well, I get a mysql exception if the execution looks like:
> cursor.execute(SQL, (tableName, aValue, jpgImage))
> 
> The execution only works when I fill out the data until the blob value is a 'left over' as a %s:
> Yes, this looks ugly.. but it works...
> 
> SQL = "INSERT INTO %s (number, image) VALUES (%s," % (tableName, aValue) ## table name and number
> SQL += "%s)" ## left over... only the image
> cursor.execute(SQL, jpgImage)
> 
> Can someone explain this to me?

While the DB-API way of passing parameters was neatly explained in other 
posts in this thread, there are still some places where you need to 
construct SQL manually, like in your case, where you dynamically insert 
the name of the table into your SQL string.

A proper way to solve this task is to do it in two steps (untested code):

SQL = INSERT INTO %s (number, image) VALUES %%s, %%s" % tableName
cursor.execute(SQL, (aValue, jpgImage))

Note that the first line only inserts the table name into the SQL 
string. Because we want to still have 'VALUES %s, %s' in the SQL string 
after applying the % operator, we escape the percent signs with an 
additional percent sign.

Then, in the second line, you can let the DB-API do all the real passing 
  of query parameters for you.

-- Gerhard