Dan Sommers wrote:
On Wed, 1 Dec 2004 20:45:13 -0500, "chris" <[EMAIL PROTECTED]> wrote:
But when I try to use a variable such as:
################################################################### ... varA = '0' varB = '1190' mycursor.execute('Update Categories Set DelStatus = ' varA 'Where ProductID = ' varB) ... ###################################################################
Let the database module (looks like odbc) do that for you:
Well, by now chris is probably wondering why you suggest
when Uwe Grauer thinks thatsql = 'UPDATE categories SET delstatus = %s WHERE productid = %s' values = (varA, varB) mycursor.execute( sql, values )
You could also use: curs.execute('UPDATE Categories SET DelStatus=? WHERE ProductID=?;', (varA, 1190))
is more appropriate, and Weinhandl Herbert suggested
use string formatting expressions (which are easier to handle than string concatenation) :
'UPDATE Categories SET DelStatus=%d WHERE ProductID=%s;' % (varA,'1190')
or maybe
"UPDATE Categories SET DelStatus='%d' WHERE ProductID='%d';" % (0,varB)
if your DB wants your int's as string
So, having gone through these newbie questions myself several decades ago (I exaggerate for effect) I thought I might try to resolve the differences. Although this is addressed to you out of politeness (since it's your post I'm replying to), please don't think I believe you need the lecture, since your reply seems to do what's needed.
You are, of course, correct in saying
The database module will know exactly how to quote and escape and whatever else is necessary to build a valid SQL statement. Your particular module may support other options, too, but it knows more than you do (and has, in theory, already been debugged).
See also PEP 249, <http://www.python.org/peps/pep-0249.html>.
and your reference to the DB API sepcification gives chris the chance to RTFM should he so desire.
The plain fact of the matter, however, is that the DB API specification is less than satisfactory in certain respects, and the odbc module that's a part of the win32all extensions isn't in any case fully compatible with DB API 2.0.
Firstly, the suggestion that one generates one's own well-formed SQL statement is valid, but sub-optimal. Following Herbert's suggestion, one might expect to be able to use
>>> 'UPDATE Categories SET DelStatus=%d WHERE ProductID=%s;' % (1234,'1190')
'UPDATE Categories SET DelStatus=1234 WHERE ProductID=1190;'
I'm not quite sure why string substitution was used for the ProductID when decimal was used for the DelStatus value, but that's beside the point - both values were presented as strings in the OP's question.
This does indeed generate a well-formed SQL statement, but doing so isn't as easy as you might think in the general case. To insert a string value into the database. one might expect
>>> "UPDATE MyTable SET charcol='%s' WHERE ProductID=%d" % ('a string', 1234)
"UPDATE MyTable SET charcol='a string' WHERE ProductID=1234"
to work, and for much of the time it does. Unfortunately in the case where the string value contains single quotes it will break:
>>> "UPDATE MyTable SET charcol='%s' WHERE ProductID=%d" % ("it's a
string", 12
34)
"UPDATE MyTable SET charcol='it's a string' WHERE ProductID=1234"yields something that clearly isn't valid SQL. One answer is to take all string values and replace any single quotes that are found with two single quotes - this is the accepted SQL escaping mechanism, though some "rogue" database will also accept variations such as backslash stropping. So
>>> "UPDATE MyTable SET charcol='%s' WHERE ProductID=%d" % ("it's a string".replace("'", "''"), 1234)
"UPDATE MyTable SET charcol='it''s a string' WHERE ProductID=1234"
does give correct SQL, but at the expense of some additional complexity, plus the extra burden of having to treat string values differently from other types of values. Personally I try to write my code so it works no matter what type of value I'm deadling with, and while this is an ideal it's surprisingly easy to stay close to the ideal.
The real answer is in your and Uwe's suggestion that the OP provides a "parameterized" SQL statement, providing the actual values to be substituted for the parameter marks. This has the further advantage that the SQL engine will see the same stateme nt every time, which means that it can go through the optimization stages just once, and use the same execution plan with repeated uses of the same parameterized statement. If we try to write the whole SQL statement ourselves not only is it more complex to program, it throws away this potential optimization.
So why did you use "... SET delstatus = %s WHERE productid = %s" when Uwe used "... SET DelStatus=? WHERE ProductID=?"?
The answer here is the DB API's additional obstacle to database portability: authors of interface modules can choose from one of a number of different styles for paraeterization, indicating their choice by setting the paramstyle attribute in their module appropriately. So
>>> import mx.ODBC.Windows as db >>> db.paramstyle 'qmark'
shows that Uwe's suggestion would have been valid for mxODBC, but
>>> import MySQLdb as db >>> db.paramstyle 'format'
shows that you would have been correct for MySQLdb, and finally
>>> import odbc >>> odbc.paramstyle Traceback (most recent call last): File "<stdin>", line 1, in ? AttributeError: 'module' object has no attribute 'paramstyle'
reminds us that odbc is not DB API 2.0 compliant. In fact the correct paramstyle for the odbc module is 'qmark', so Uwe's answer was the best response to the original question.
Sorry to beat this one to death, but this is the sort of thing that beginners need to understand with some clarity right from the start.
just-trying-to-help-ly y'rs - steve -- http://www.holdenweb.com http://pydish.holdenweb.com Holden Web LLC +1 800 494 3119 -- http://mail.python.org/mailman/listinfo/python-list
