How to convert None to null value

[Gabriel Genellina]

En Fri, 07 Sep 2007 06:19:25 -0300, pradeep kumar <staris28 at gmail.com>  
escribi�:

> i am trying to insert records from one table to another table in postgres
> sql using the the following code
>
> posConn.query("insert into
> usr(usr_id,usr_name,usr_passwd,grp_cde,usr_type,usr_chk_till_val, \
>
> usr_active,usr_daily_auth,usr_lst_login,usr_lst_logout,usr_shift_id,usr_role_level)
> values \
>              ('%s','%s','%s',%s,%i,%d,'%s',%i,%i,%i,%i,%i)"
> %(row[0],row[1],row[2],row[3],row[4],row[5], \
>               row[7],row[8],row[10],row[11],row[12],row[14]) )
>
> here row[4] column is having null value so python consides it as None but
> will insert into the table it inserts as None not null value

- You are building the sql statement "by hand": don't do that. The query  
method receives two arguments: an sql template with placeholders, and a  
tuple of arguments. This way, None objects are correctly translated into  
NULL values, embedded quotes are correctly handled, etc. An important  
point is security - building the sql statement by hand may open the door  
to sql injection attacks.
See <http://www.python.org/dev/peps/pep-0249/> for details.

- You don't need those \ for line continuation: any expression containing  
an open ( [ { automatically continues on the following lines until all ) ]  
} are closed.

- Python automatically joins contiguous strings.

Combining all the above:

posConn.query("insert into usr "
    "(usr_id,usr_name,usr_passwd,grp_cde,usr_type,"
    "usr_chk_till_val,usr_active,usr_daily_auth,"
    "usr_lst_login,usr_lst_logout,usr_shift_id,"
    "usr_role_level) values "
    "(%s,%s,%s,%s,%i,%d,%s,%i,%i,%i,%i,%i)" ,
    (row[0],row[1],row[2],row[3],row[4],row[5],
     row[7],row[8],row[10],row[11],row[12],row[14]))

Note the TWO arguments to the query method: all those string fragments are  
joined into a single string, followed by a comma, and a tuple containing 6  
parameters.

Parameter markers may be %s,%s,%s... as above, or :1,:2,:3... or ?,?,?...
The exact syntax depends on your specific database adapter - you'll have  
to read its documentation.

-- 
Gabriel Genellina