Thread: psycopg2.DataError: invalid input syntax for integer: ""
I need advice on how to solve this problem. I am new to this kind of task. I want to insert data into my PostgreSQL DB. The field that I will store data is a textfield but I am getting an error invalid input syntax for integer: ""
My code so far is this:
cur_p.execute(""" INSERT INTO a_recipient (created, mod, agreed, address, honor,) VALUES (%s, %s)""", (current_timestamp, current_timestamp, current_timestamp, '', ''))If I remove the agreed and the last current_timestamp, it works fine as well if I remove the honor which is a textfield and leave the 3 current_timestamp. I don't know if it is the date field with time and timezone or the textfield which is having an issue.
I've read some related threads in SO but the ones I saw were either related to CSV or Flask related.
Any suggestions please?
Thanks,
J
On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com> wrote:
cur_p.execute("""INSERT INTO a_recipient (created, mod, agreed, address, honor,) VALUES (%s, %s)""", (current_timestamp, current_timestamp, current_timestamp, '', ''))
That code doesn't even run (extra comma after honor, not enough columns in values). Provide working code and the definition of a_recipient.
In short, you've passed an empty string to an integer column, which doesn't work. Supply null or pass a number.
David J.
Yes, my apologies.
May I also ask if there's a limitation for the number of timestamp with timezone fields in a table?On Mon, May 7, 2018 at 1:37 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Please keep replies on-list, don't top-post, and double-check that the database table doesn't somehow have an integer column where you think its text.
On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com> wrote:Yeah, the error doesn't really explain much. I have tried putting the string formatter in ' ', still no good.On Mon, May 7, 2018 at 12:14 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com> wrote:I updated the code intoHi,There's a mistake in the code, my bad.The code still won't work. The address and honor fields are textfields in Django models.py.cur_p.execute(""" INSERT INTO a_recipient (created, mod, agreed, address, honor) VALUES (current_timestamp, current_timestamp, current_timestamp, %s, %s)""", ('', ''))Not sure but I'm thinking you at least need to add single quotes around the %s symbols. That doesn't really explain the integer input error though I'm not familiar with the exact features of the execute method in Python.David J.
On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com> wrote:
Yes, my apologies.May I also ask if there's a limitation for the number of timestamp with timezone fields in a table?
Not one that is likely to matter in practice. There's a page discussing limitations on the website/docs somewhere if you wish to find out more.
David J.
I think I've found the culprit of the problem.
I have a field which is varchar from the source DB while on the destination DB its integer. On Mon, May 7, 2018 at 2:39 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com> wrote:Yes, my apologies.May I also ask if there's a limitation for the number of timestamp with timezone fields in a table?Not one that is likely to matter in practice. There's a page discussing limitations on the website/docs somewhere if you wish to find out more.David J.
Reading the documentation: http://www.postgresqltutorial.I think I've found the culprit of the problem.I have a field which is varchar from the source DB while on the destination DB its integer.com/postgresql-cast/ but it gives me error `psycopg2.DataError: invalid input syntax for integer: ""`
You haven't asked a question and your statement is unsurprising to me. Are you good now or do you still harbor confusion?
David J.
On 05/06/2018 11:05 PM, tango ward wrote:
> Yes, my apologies.
>
> May I also ask if there's a limitation for the number of timestamp with
> timezone fields in a table?
>
> On Mon, May 7, 2018 at 1:37 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> Please keep replies on-list, don't top-post, and double-check that
> the database table doesn't somehow have an integer column where you
> think its text.
>
>
> On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com
> <mailto:tangoward15@gmail.com>> wrote:
>
> Yeah, the error doesn't really explain much. I have tried
> putting the string formatter in ' ', still no good.
>
> On Mon, May 7, 2018 at 12:14 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>
> wrote:
>
> On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com
> <mailto:tangoward15@gmail.com>> wrote:
>
> Hi,
>
> There's a mistake in the code, my bad.
>
> I updated the code into
>
> |cur_p.execute(""" INSERT INTO a_recipient (created, mod,
> agreed, address, honor) VALUES (current_timestamp,
> current_timestamp, current_timestamp, %s, %s)""",('', ''))
>
>
>
> |
>
> The code still won't work. The address and honor fields
> are textfields in Django models.py.
>
>
> Not sure but I'm thinking you at least need to add single
> quotes around the %s symbols. That doesn't really explain
> the integer input error though I'm not familiar with the
> exact features of the execute method in Python.
They do not need to be quoted:
http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
>
> David J.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Not sure but I'm thinking you at least need to add single
quotes around the %s symbols. That doesn't really explain
the integer input error though I'm not familiar with the
exact features of the execute method in Python.
They do not need to be quoted:
http://initd.org/psycopg/docs/usage.html#passing-parameters- to-sql-queries
Yeah, upon further reflection overnight I figured it must incorporate sql-injection prevention. The use of "%s", which is typically a printf construct and printf doesn't do that kind of thing, threw me.
David J.
On 05/07/2018 12:28 AM, tango ward wrote: > I think I've found the culprit of the problem. > > I have a field which is varchar from the source DB while on the > destination DB its integer. > > Reading the documentation: > http://www.postgresqltutorial.com/postgresql-cast/ but it gives me error > `psycopg2.DataError: invalid input syntax for integer: ""` Would need to see your code to be sure, but I am gong to guess you are trying to CAST the string to integer in the SQL e.g CAST(some_str_value AS INTEGER) or some_str_value::integer. The error you are getting is : test=# select CAST('' AS INTEGER); ERROR: invalid input syntax for integer: "" LINE 1: select CAST('' AS INTEGER); ^ test=# select ''::integer; ERROR: invalid input syntax for integer: "" LINE 1: select ''::integer; Two options: 1) You will need the catch the '' on the Python side before they get to the database and turn them into None(if Nulls allowed in column) or 0 otherwise. 2) If possible convert the integer column on the destination db to a varchar one. Though I would do some investigation before doing this as this may very well mess up other code. > > On Mon, May 7, 2018 at 2:39 PM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com > <mailto:tangoward15@gmail.com>> wrote: > > Yes, my apologies. > > May I also ask if there's a limitation for the number of > timestamp with timezone fields in a table? > > > Not one that is likely to matter in practice. There's a page > discussing limitations on the website/docs somewhere if you wish to > find out more. > > David J. > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi All,
Thanks for the suggestions.My apologies for consuming your time, it's my first time to work with DB and DB migration.
On Mon, May 7, 2018 at 9:49 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 05/07/2018 12:28 AM, tango ward wrote:I think I've found the culprit of the problem.
I have a field which is varchar from the source DB while on the destination DB its integer.
Reading the documentation: http://www.postgresqltutorial.com/postgresql-cast/ but it gives me error `psycopg2.DataError: invalid input syntax for integer: ""`
Would need to see your code to be sure, but I am gong to guess you are trying to CAST the string to integer in the SQL e.g CAST(some_str_value AS INTEGER) or some_str_value::integer. The error you are getting is :
test=# select CAST('' AS INTEGER);
ERROR: invalid input syntax for integer: ""
LINE 1: select CAST('' AS INTEGER);
^
test=# select ''::integer;
ERROR: invalid input syntax for integer: ""
LINE 1: select ''::integer;
Two options:
1) You will need the catch the '' on the Python side before they get to the database and turn them into None(if Nulls allowed in column) or 0 otherwise.
2) If possible convert the integer column on the destination db to a varchar one. Though I would do some investigation before doing this as this may very well mess up other code.
On Mon, May 7, 2018 at 2:39 PM, David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com
<mailto:tangoward15@gmail.com>> wrote:
Yes, my apologies.
May I also ask if there's a limitation for the number of
timestamp with timezone fields in a table?
Not one that is likely to matter in practice. There's a page
discussing limitations on the website/docs somewhere if you wish to
find out more.
David J.
--
Adrian Klaver
adrian.klaver@aklaver.com