Re: How to convert None to null value
Gabriel Genellina
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
Carsten Haese
> Hi ,
>
> 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
>
If you were using a DB-API compliant module, you should use parameter
binding instead of building query strings by hand:
http://informixdb.blogspot.com/2007/07/filling-in-blanks.html
However, nothing in DB-API has a .query method, so either you're using a
module that's not conforming to DB-API, or you're using some kind of
wrapper around a DB-API module. In any case, you should try to find out
how to do parameter binding with whatever it is you're using. If it
doesn't have a parameter binding mechanism, you should throw it away and
replace it with a DB-API complaint module.
HTH,
--
Carsten Haese
http://informixdb.sourceforge.net
Carsten Haese
> On Fri, 07 Sep 2007 09:07:49 -0400, Carsten Haese <car...@uniqsys.com>
> declaimed the following in comp.lang.python:
>
>
> > doesn't have a parameter binding mechanism, you should throw it away and
> > replace it with a DB-API complaint module.
> >
Yeah, I noticed the typo a minute too late. Dyslexics of the world,
untie! ;-)
Steve Holden
> On Fri, 2007-09-07 at 12:10 -0700, Dennis Lee Bieber wrote:
>> On Fri, 07 Sep 2007 09:07:49 -0400, Carsten Haese <car...@uniqsys.com>
>> declaimed the following in comp.lang.python:
>>
>>
>>> replace it with a DB-API complaint module.
>>>
>
> Yeah, I noticed the typo a minute too late. Dyslexics of the world,
> untie! ;-)
>
regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
--------------- Asciimercial ------------------
Get on the web: Blog, lens and tag the Internet
Many services currently offer free registration
----------- Thank You for Reading -------------