Getting 'integer out of range' error sqlalchemy

Question

So, I am using odo for data migration but came across this error:

sqlalchemy.exc.DataError: (psycopg2.DataError) integer out of range

Both the source and the destination tables have the same schema but in the sql statement being executed in the backend the integer values have .0 with them. Like the integer 34 in the source table is shown as 34.0:

[SQL: INSERT INTO table2 (col1, col2,col3) VALUES (%(col1)s, %(col2)s, %(col3)s] 
[parameters: ({'col2' : val2', 'col3' : val3', 'col1' : val1})]

Please let me know if more information is needed.

This might be because of `NaN` value for an integer column in the parameter dictionary. Added a new question: http://stackoverflow.com/questions/38074487/copy-empty-string-to-integer-column-postgres — delalli, Jun 28 '16 at 11:06

score 0 · Answer 1 · answered Jun 28 '16 at 11:21

0

The sql string should be:

INSERT INTO table2 (col1, col2, col3) VALUES (
    %(col1)s::numeric::int, 
    %(col2)s::numeric::int, 
    %(col3)s::numeric::int
)

If the target column is not nullable and the source value can be None then coalesce it:

    coalesce(%(col1)s::numeric::int, 0),

answered Jun 28 '16 at 11:21

Clodoaldo Neto

118,695
26
233
260

The sql string is what odo is showing as output when the error is occurring. I can't change it. – delalli Jun 28 '16 at 11:23
As I cannot change the sql string, how can I go about this? – delalli Jun 28 '16 at 11:32

Getting 'integer out of range' error sqlalchemy

1 Answers1