psycopg2.DataError: invalid input syntax for integer: ""
Good day to all,
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:
Show quoted text
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:
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.David J.
Import Notes
Reply to msg id not found: CAKFQuwZhP9-z7psieZFMbkAFNLzsbjShUBKvF-8ikeQ_JtiSfw@mail.gmail.com
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.
Reading the documentation:
http://www.postgresqltutorial.com/postgresql-cast/ but it gives me error
`psycopg2.DataError: invalid input syntax for integer: ""`
On Mon, May 7, 2018 at 2:39 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
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.
On Mon, May 7, 2018 at 12:28 AM, tango ward <tangoward15@gmail.com> 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: ""`
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
On Mon, May 7, 2018 at 6:35 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
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.
I managed to fix this by running CASE on the column. I also fix the %s to
avoid SQLi as per discussed in the documentation of psycopg2.
My apologies for consuming your time, it's my first time to work with DB
and DB migration.
Thanks,
J
On Mon, May 7, 2018 at 9:49 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Show quoted text
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