Re: [cx-oracle-users] Null value converts to python None and when used for update converts to string 'None'

[Jani T. <re...@gm...>]

2.4.2013 5:51, Anurag Chourasia kirjoitti:
> *Dear friends...Could you please help me with this issue?*
>
> *Steps to reproduce?*
> 1. Fetch the value of a varchar2 column that has Null as its current value.
> 2. Use the fetched value to update any table's column.
>
> *Expected output?*
> Instead of updating to Null, the table column gets updated to a string value of 'None'
>
> *Version in use*
> Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on win32
> cx_Oracle - 5.1.2
> Oracle 11G
>
> *Additional information*
>
> This could be because in the update query, I am using '%s' format....for example
>
> cursor.execute("UPDATE EMP SET NAME='%s'" %(None))
>
> But there is no other way. If i simply use
>
> cursor.execute("UPDATE EMP SET NAME=%s" %(None)) then I get a syntax error.
>
> There should be a way to update a column to NULL

Why are you trying to inject data instead of using standard Python API 
to do things right?

If you persists constructing query string completely by hand (like you 
have done), then you have to take care of NULL by yourself.

In most cases following works better:

cursor.execute('UPDATE EMP SET NAME=:arg1', {'arg1' : None })

When you do it that way Python DB-API takes care of correct quotation 
and makes sure that there is no way to inject malicious data and in most 
cases correct datatype conversion takes a place (there is few occassions 
when this fails)

cx_Oracle doesn't support %s format, it supports only :name (named) 
format [1].

[1] http://www.python.org/dev/peps/pep-0249/#paramstyle
-- 
Jani Tiainen

- Well planned is half done and a half done has been sufficient before...