Victor Subervi wrote:
On Thu, May 27, 2010 at 2:54 PM, MRAB <pyt...@mrabarnett.plus.com
<mailto:pyt...@mrabarnett.plus.com>> wrote:
Victor Subervi wrote:
Hi;
But what about this?
sql = "select pic%d from %s where ID='%%s';" % (pic, store)
cursor.execute(sql % id)
If I try and rewrite the last line like this:
cursor.execute(sql, id)
it doesn't work. What do?
How about this one:
cursor.execute("insert into categories (Store, Category,
Parent) values('%s', '%s', Null)", (store, cat))
For some reason it puts single quotes around my variables! This
doesn't happen if I change that comma for a percent sign! What do?
How about this one:
sql = 'select * from options%s where ID=%%s',
(opTable[0].upper() + opTable[1:])
# cursor.execute(sql, id)
cursor.execute('select * from options%s where ID=%s' %
(opTable[0].upper() + opTable[1:], id))
The last one works, but if I comment it out and uncomment the
middle line, it doesn't. Same here:
sql = "update options%s set PriceDiff='%%s' where
Field='%%s' and ID=%%s and Store='%%s'" % (opTable[0].upper() +
opTable[1:])
# cursor.execute(sql, (value, opName, id, store))
cursor.execute('update options%s set PriceDiff="%s" where
Field="%s" and ID=%s and Store="%s"' % (opTable[0].upper() +
opTable[1:], value, opName, id, store))
As has already been explained, when working with SQL in Python there are
2 forms of placeholder:
1. Python's %s placeholder, replaced by Python's % operator.
2. SQL's %s placeholder, replaced by the .execute method.
SQL might not let you use its %s placeholder for table or column names,
but they are normally hidden from the user and fixed by the application.
For user-supplied values there's the risk of SQL-injection attacks.
There are 2 ways of approaching that:
1. The hard way: check the values and add any necessary quoting or
escaping before using Python's % operator, then pass the fully-formed
SQL statement to result to .execute.
2. The easy way: pass the SQL statement to .execute with a %s for each
value and let the method substitute the values itself (it'll add
whatever quoting or escaping is necessary).
Ok, so you're telling me I'm trying to do it the hard way. That's
because I still don't have my head wrapped around the easy way. I was
able to follow what Kushal Kumaran supplied; however I must still be
lost on how that applies to the above examples. Could you illustrate
with the first and let me try and figure out the rest?
First build the SQL statement with placeholder(s) for the values:
sql = "select pic%d from %s where ID=%%s;" % (pic, store)
Then execute the SQL statement, passing the value(s) so that .execute
performs the substitution itself:
cursor.execute(sql, id)
Placeholders which are handled by .execute shouldn't be wrapped in
quotes, even is the value is a string, because .execute will handle that
(and any other details) itself.
--
http://mail.python.org/mailman/listinfo/python-list
