2

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?

tango ward
  • 307
  • 1
  • 4
  • 18

1 Answers1

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".

Gregory Arenius
  • 2,904
  • 5
  • 26
  • 47
  • I updated my code. the "honor" column is a textfield. Tried replacing the empty string with None, still no good. – tango ward May 07 '18 at 03:41
  • Do you get the same error? Also, the None should not be in quotes. – Gregory Arenius May 07 '18 at 03:44
  • 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 May 07 '18 at 03:45
  • 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 `' '` – tango ward May 07 '18 at 03:48
  • 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. – Gregory Arenius May 07 '18 at 03:49
  • My backend is Django and as per the documentation, it's not advisable to put null=True https://docs.djangoproject.com/en/2.0/ref/models/fields/ – tango ward May 07 '18 at 03:52