database questions ala newbie pythonist

[Steve Holden]

Dan Sommers wrote:

> On Wed, 1 Dec 2004 20:45:13 -0500,
> "chris" <splungent at aol.com> 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

>     sql = 'UPDATE categories SET delstatus = %s WHERE productid = %s'
>     values = (varA, varB)
>     mycursor.execute( sql, values )
> 
when Uwe Grauer thinks that
> 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