database questions ala newbie pythonist

[chris]

Hello,
Just started with python and databases.
I am making a small script to update a local database.
Looked around and couldn't find to many links with info about python with
databases.
Any links or other resources anyone can recommend is appreciated.

My question is in the following small script:

##################################################################
import dbi
import odbc
myconn = odbc.odbc('testpy')
mycursor = myconn.cursor()
mycursor.execute('Update Categories Set DelStatus = 0 Where ProductID =
1190' )
mycursor.close()
myconn.close()
....
###################################################################

This works fine using the literals 0 (For Delstatus) and 1190 (for
ProductID)
But when I try to use a variable such as:

###################################################################
....
varA = '0'
varB = '1190'
mycursor.execute('Update Categories Set DelStatus = ' varA 'Where ProductID
= ' varB)
....
###################################################################

I get errors. Please excuse my ignorance in what is probably obvious to
most others within this newsgroup.....
What I am trying to do is to update my database from a 2 field .CSV file
I figured I could load the CSV file into a dictionary and parse each row
running a query using the values from varA and VarB from the key value
pairs.
Right now I am just trying to get the database part to work that is why
there is no CSV code.
So I guess what I am asking is how do I pass values through the sql
statement:
###################################################################
....
mycursor.execute('Update Categories Set DelStatus = X1 Where ProductID =
X2' ) # Values replacing X1 and X2.
....
###################################################################
Any help is greatly appreciated.

Chris

 

[mensanator]

Hello,
Just started with python and databases.
I am making a small script to update a local database.
Looked around and couldn't find to many links with info about python with
databases.
Any links or other resources anyone can recommend is appreciated.

My question is in the following small script:

##################################################################
import dbi
import odbc
myconn = odbc.odbc('testpy')
mycursor = myconn.cursor()
mycursor.execute('Update Categories Set DelStatus = 0 Where ProductID =
1190' )
mycursor.close()
myconn.close()
...
###################################################################

This works fine using the literals 0 (For Delstatus) and 1190 (for
ProductID)
But when I try to use a variable such as:

###################################################################
...
varA = '0'
varB = '1190'
mycursor.execute('Update Categories Set DelStatus = ' varA 'Where ProductID
= ' varB)

Try concatenating the components:

mycursor.execute('Update Categories Set DelStatus = ' + varA + 'Where
ProductID = ' + varB)

Without the '+' signs, you've put 4 strings inside the .exexcute
function
instead of the 1 it wants.

 

[Weinhandl Herbert]

chris wrote:

....

This works fine using the literals 0 (For Delstatus) and 1190 (for
ProductID)
But when I try to use a variable such as:

###################################################################
...
varA = '0'
varB = '1190'
mycursor.execute('Update Categories Set DelStatus = ' varA 'Where ProductID
= ' varB)

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

###################################################################

I get errors. Please excuse my ignorance in what is probably obvious to
most others within this newsgroup.....
What I am trying to do is to update my database from a 2 field .CSV file
I figured I could load the CSV file into a dictionary and parse each row
running a query using the values from varA and VarB from the key value
pairs.

happy pythoning

Herbert

 

[Uwe Grauer]

chris wrote:

...




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

You could also use:
curs.execute('UPDATE Categories SET DelStatus=? WHERE ProductID=?;',
(varA, 1190))

Uwe

 

[Dan Sommers]

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:

sql = 'UPDATE categories SET delstatus = %s WHERE productid = %s'
values = (varA, varB)
mycursor.execute( sql, values )

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>.

HTH,
Dan

 

[Steve Holden]

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
(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
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:
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
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
'qmark'

shows that Uwe's suggestion would have been valid for mxODBC, but
'format'

shows that you would have been correct for MySQLdb, and finally
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