cannot insert None value in postgres using psycopg2

Question

I have a database(postgresql) with more than 100 columns and rows. Some cells in the table are empty,I am using python for scripting so None value is placed in empty cells but it shows the following error when I try to insert into table.

"  psycopg2.ProgrammingError: column "none" does not exist"

Am using psycopg2 as python-postgres interface............Any suggestions??

Thanks in advance......

Here is my code:-

list1=[None if str(x)=='nan' else x for x in list1];

cursor.execute("""INSERT INTO table VALUES %s""" %list1;
);

Does list1 hold just one value? Or are you trying to use this for multiple insertions? — Martijn Pieters
– Martijn Pieters, Commented Jan 20, 2014 at 17:39

Martijn Pieters · Accepted Answer · 2014-01-20 17:40:28Z

6

Do not use % string interpolation, use SQL parameters instead. The database adapter can handle None just fine, it just needs translating to NULL, but only when you use SQL parameters will that happen:

list1 = [(None,) if str(x)=='nan' else (x,) for x in list1]

cursor.executemany("""INSERT INTO table VALUES %s""", list1)

I am assuming that you are trying to insert multiple rows here. For that, you should use the cursor.executemany() method and pass in a list of rows to insert; each row is a tuple with one column here.

If list1 is just one value, then use:

param = list1[0]
if str(param) == 'nan':
    param = None

cursor.execute("""INSERT INTO table VALUES %s""", (param,))

which is a little more explicit and readable.

edited Jan 20, 2014 at 17:40

answered Jan 20, 2014 at 17:29

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

cannot insert None value in postgres using psycopg2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related