Thread: Column does not exist when trying to insert data.

Hello,

My table 'orderitems' looks like this

create table orderitem (
id serial not null primary key,
item integer not null,
quantity numeric(6,2) not null,

unit varchar(10),
conference integer not null,
seller integer not null,
incharge integer not null,
orderdate date not null,
duedate date not null,
returnedas varchar,
);

I get all the values from a form, validate them and try to insert into my postgres database table.

The integers in the above table are foreign key refernces (which I have done using constraints).
The fields 'unit' and 'returnedas' are optional. I use python-psycopg to handle the DB part.

When I execute the following statement..


 "INSERT INTO orderitem (item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas)
 VALUES (%d,%f,%s,%d,%d,%d,%s,%s,%s)" % (params['item'],params['quantity'],params['unit'],

params['conference'],params['seller'],params['incharge'],params['orderdate'],params['duedate'],params['returnedas'])

I get the following error in the browser, whent he fields for unit and returnedas are left blank and hence becomes None.
I also tried to have some string value such as 'nos' and 'not reqd' for 'unit' and 'requiredas' but still it get a similar error
saying column 'nos' does not exist.

ProgrammingError: ERROR: column "none" does not exist INSERT INTO orderitem (item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas) VALUES (6,10.000000,None,39,1,1,2007-3-16,20-03-2007,None)
      args = ('ERROR: column "none" does not exist\n\nINSERT INT...,10.000000,None,39,1,1,2007-3-16,20-03-2007,None)',)

Where am I getting wrong ?

--
With Regards

---
Parthan.S.R.
Research Assistant
National Resource Center for Free/Open Source Software
Python Developer n00b

Parthan SR wrote:
> I get the following error in the browser, whent he fields for unit and
> returnedas are left blank and hence becomes None.
> I also tried to have some string value such as 'nos' and 'not reqd' for
> 'unit' and 'requiredas' but still it get a similar error
> saying column 'nos' does not exist.
>
> *ProgrammingError*: ERROR: column "none" does not exist INSERT INTO
> orderitem
> (item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas)
>
> VALUES (6,10.000000,None,39,1,1,2007-3-16,20-03-2007,None)
>      args = ('ERROR: column "none" does not exist\n\nINSERT INT...,
> 10.000000,None,39,1,1,2007-3-16,20-03-2007,None)',)

You're looking for "Null" rather than "None".

Oh, and your dates need to be quoted too.

--
   Richard Huxton
   Archonet Ltd

On Thursday 15 March 2007 9:52 pm, Parthan SR wrote:
> Hello,
>
> My table 'orderitems' looks like this
>
> create table orderitem (
> id serial not null primary key,
> item integer not null,
> quantity numeric(6,2) not null,
> unit varchar(10),
> conference integer not null,
> seller integer not null,
> incharge integer not null,
> orderdate date not null,
> duedate date not null,
> returnedas varchar,
> );
>
> I get all the values from a form, validate them and try to insert into
> my postgres database table.
> The integers in the above table are foreign key refernces (which I
> have done using constraints).
> The fields 'unit' and 'returnedas' are optional. I use python-psycopg
> to handle the DB part.
>
> When I execute the following statement..
>
>  "INSERT INTO orderitem
> (item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas
>) VALUES (%d,%f,%s,%d,%d,%d,%s,%s,%s)" %
> (params['item'],params['quantity'],params['unit'],
> params['conference'],params['seller'],params['incharge'],params['orderdate'
>],params['duedate'],params['returnedas'])
To make it make work here I had to use pyformat formatting. To illustrate-
VALUES(%(item)s,%(quantity)s,%(unit)s,%(conference)s,%(seller)s,%(incharge)s,
%(orderdate)s,%(duedate)s,% (returnedas)),
{'item':params['item'],'quantity':params['quantity'],'unit':params['unit'],'conference':
['conference'],'seller':params['seller'],'incharge':params['incharge'],
'orderdate':params[orderdate'],'duedate':params['duedate'],'returnedas':params['returnedas']}

> params['conference'],params['seller'],params['incharge'],params['orderdate'
>],params['duedate'],params['returnedas']
>
> I get the following error in the browser, whent he fields for unit and
> returnedas are left blank and hence becomes None.
> I also tried to have some string value such as 'nos' and 'not reqd' for
> 'unit' and 'requiredas' but still it get a similar error
> saying column 'nos' does not exist.
>
> *ProgrammingError*: ERROR: column "none" does not exist INSERT INTO
> orderitem
> (item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas
>) VALUES (6,10.000000,None,39,1,1,2007-3-16,20-03-2007,None)
>       args = ('ERROR: column "none" does not exist\n\nINSERT INT...,
> 10.000000,None,39,1,1,2007-3-16,20-03-2007,None)',)
>
> Where am I getting wrong ?
This is probably a psycopg issue. If the solution I suggested above does not
work you may want to take up the problem on the psycopg list.

--
Adrian Klaver
aklaver@comcast.net