Re: [cx-oracle-users] Update problem

[<dan...@co...>]

On Tuesday, January 16, 2007 10:15 PM, Jesse G. Lands wrote:

> I have some code that works fine when I query, but when try an update
> statement I get a response back of
> "Expecting string or none"

[snip]

> 	     sql =3D """UPDATE T_PERSONNEL SET last_name =3D
> '%s',first_name =3D '%s' WHERE login =3D '%s'""" % (row['Last Name'],
> row['First Name'], row['ID'])
>          print sql
>          cursor.execute(sql)

I am not sure about the readexcel() functionality, but I assume
row['ID'] returns a number and not a string.

I strongly recommend to use bind variables instead of constructing the
SQL string manually. It is faster and safer. Refer for example to
http://www.joelonsoftware.com/items/2006/11/01.html for an explanation
why bind variables are the way to go.

So a better approach would be:

sql =3D """
UPDATE T_PERSONNEL
   SET last_name =3D :lastname,
       first_name =3D :firstname
 WHERE login =3D :login
"""
cursor.prepare(sql)
for sheet in sheetnames:
    for row in xl.getiter(sheet):
        cursor.execute(None, [
               row['Last Name'],
               row['First Name'],
               row['ID']
               ])
cursor.close()

Now implicit data type conversion takes place, if necessary. Of course
you can always make explicit data type conversion if you would like to.

HTH,
Daniel