En Fri, 07 Sep 2007 06:19:25 -0300, pradeep kumar <[EMAIL PROTECTED]>
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
--
http://mail.python.org/mailman/listinfo/python-list
