[DB-SIG] PyGreSQL and NULLs

Marc Colosimo mcolosimo at mitre.org
Tue Feb 10 16:49:07 EST 2004


On Feb 10, 2004, at 4:51 AM, Andy Todd wrote:

> Marc Colosimo wrote:
>> I hope this list is alive. The DB-API 2.0 document says that "SQL 
>> NULL values are represented by Python None signleton on imput and 
>> output" under Type Objects and Constructors. However, when passing in 
>> a None object using the pyformat it replaces it with a blank string.
>> for example,
>> my_user = {"id":10,"name":"Marc"}
>> cursor.execute("INSERT into myTable (my_id, my_name, my_pet_id) 
>> values " +
>>             "(%(id)s,'%(name)s',%(pet)s) % my_user)
>> would result in this SQL command,
>> INSERT into myTable (my_id, my_name, my_pet_id) values (10, 'Marc', );
>> instead of this one
>> INSERT into myTable (my_id, my_name, my_pet_id) values (10, 'Marc', 
>> NULL);
>> is this a known problem?
>> Thanks
>> Marc
>
> I don't have Postgres or PyGreSQL installed but I suspect that if you 
> change the first line to;
>
> my_user = {'id':10, 'name':'Marc', 'pet':None}
>
> You would get the result you are expecting.
>
oops, I forgot to add that in my pseudo code. Here is a real example. 
As you see it puts in None, instead of Null. If I change "pet":None to 
"pet":"Null" it works.

%psql test
test=# CREATE TABLE myTable (
test(# my_id INTEGER,
test(# my_name TEXT,
test(# my_pet_id INTEGER);
CREATE TABLE

Now for my_test.py
!#/usr/bin/env python

import pgdb

db = pgdb.connect(database="test")
cursor = db.cursor()

my_user = {"id":10,"name":"Marc","pet":None}

cursor.execute("INSERT into myTable (my_id, my_name, my_pet_id) values 
" +
             "(%(id)s,'%(name)s',%(pet)s)" % my_user)

db.commit()
cursor.close()
db.close()

% ./my_test.py
Traceback (most recent call last):
   File "my_test.py", line 8, in ?
     cursor.execute("INSERT into myTable (my_id, my_name, my_pet_id) 
values " +
   File "/sw/lib/python2.3/site-packages/pgdb.py", line 189, in execute
     self.executemany(operation, (params,))
   File "/sw/lib/python2.3/site-packages/pgdb.py", line 208, in 
executemany
     raise DatabaseError, "error '%s' in '%s'" % ( msg, sql )
pgdb.DatabaseError: error 'ERROR:  Attribute "none" not found
' in 'INSERT into myTable (my_id, my_name, my_pet_id) values 
(10,'Marc',None)'







More information about the DB-SIG mailing list