gh at ghaering.de
Tue Aug 3 00:13:33 CEST 2004
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.
More information about the Python-list