psycopg2.DataError: invalid input syntax for integer: ""

Question

I am trying to insert data into a table. When I try to insert an empty string into a Textfield, I am getting the invalid input syntax for integer error message.

Other textfields work fine with empty string.

My code:

cur_p.execute("""
                INSERT INTO a_recipient (created, mod, agreed, address, honor)
                VALUES (current_timestamp, current_timestamp, current_timestamp, %s, %s)""", (None, None))

psycopg2.DataError: invalid input syntax for integer: "" LINE 35: ... '', ''..

The code works fine if I remove the last current_timestamp in the values as well as the agreed but if I put it back, the error message re-appears.

I checked other threads opened here in SO, I found this but the problem was about the values in array input error: integer

Any advice?

created = timestamp with timezone mod = timestamp with timezone agreed = timestamp with timezone address = text honor = text — tango ward
– tango ward, Commented May 7, 2018 at 6:11

Gregory Arenius · Accepted Answer · 2018-05-07 03:48:15Z

1

So there appear to be a few issues here.

First, in your INSERT INTO you have five columns that you name (created, mod, etc.) but in your VALUES statement (%s, %s) you only have two variables.

I don't know what the data types of your columns are but the error may be because you're trying to insert empty strings '' into an integer field. Try using None instead of the empty strings. Psycopg2 converts Python None objects to SQL NULL.

I also don't think you need the trailing comma after "honor".

edited May 7, 2018 at 3:48

answered May 7, 2018 at 3:36

Gregory Arenius

3,3046 gold badges33 silver badges47 bronze badges

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

6 Comments

tango ward Over a year ago

I updated my code. the "honor" column is a textfield. Tried replacing the empty string with None, still no good.

Gregory Arenius Over a year ago

Do you get the same error? Also, the None should not be in quotes.

tango ward Over a year ago

Sweet baby Jesus, it works! Is it advisable to put None instead of empty strings? I have like 150 textfield columns that are not nullable so i put ' ' in them instead of None.

tango ward Over a year ago

Oh, problem sorry. When I replace the empty string with None, I am getting the error honor" violates not-null constraint cause the field is not nullable. As I workaround to other textfields that are nullable too, I put empty string ' '

Gregory Arenius Over a year ago

If you don't have data for the fields and you can do so making them nullable would be better than filling them with strings of ' '. If the fields aren't nullable though None won't work because it is converted to NULL by psycopg2.

|

Collectives™ on Stack Overflow

psycopg2.DataError: invalid input syntax for integer: ""

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related