OverflowError: int to big to convert

[J Sherwood]

Hello,

I am trying to transfer an Oracle Database(12) to an SQL database(2016) table by table.

I read it from the Oracle database using:

ocon = cx_Oracle.connect(username,password,dsn,encoding='UTF-8') df = pd.read_sql("SELECT * FROM TABLE",con=ocon)

I then try to write the dataframe to the SQL Server using:

engine = sqlalchemy.create+engine("mssql+pyodbc://user:pass@server:1433/Database?driver=SQL+Server") df.to_sql(table_name,engine,if_exists='replace')

It reads no problem and creates the table in the SQL Server but throws this error:

Traceback (most recent call last): File "c:/Users/user/Desktop/Python/pythonfile.py", line 37, in <module> dfotable.to_sql("table_name",engine,if_exists='replace') File "C:\Users\user\Anaconda3\lib\site-packages\pandas\core\generic.py", line 2653, in to_sql sql.to_sql( File "C:\Users\user\Anaconda3\lib\site-packages\pandas\io\sql.py", line 512, in to_sql pandas_sql.to_sql( File "C:\Users\user\Anaconda3\lib\site-packages\pandas\io\sql.py", line 1317, in to_sql table.insert(chunksize, method=method) File "C:\Users\user\Anaconda3\lib\site-packages\pandas\io\sql.py", line 755, in insert exec_insert(conn, keys, chunk_iter) File "C:\Users\user\Anaconda3\lib\site-packages\pandas\io\sql.py", line 669, in _execute_insert conn.execute(self.table.insert(), data) File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1014, in execute return meth(self, multiparams, params) File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_clauseelement ret = self._execute_context( File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1317, in _execute_context self._handle_dbapi_exception( File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1515, in _handle_dbapi_exception util.raise_(exc_info[1], with_traceback=exc_info[2]) File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_ raise exception File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1257, in _execute_context self.dialect.do_executemany( File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\dialects\mssql\pyodbc.py", line 416, in do_executemany super(MSDialect_pyodbc, self).do_executemany( File "C:\Users\user\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_executemany cursor.executemany(statement, parameters) OverflowError: int too big to convert

There are some large numbers in NUMBER types in the Oracle database that I suspect are the issue, I just do not know how to get around it.

I have also tried pulling the rows and creating a dictionary with the datatypes so that the SQL data types are Numeric instead of int and it made no difference so I do not think this is an error thrown by the SQL Server.

I am using anaconda with sqlalchemy version 1.3.18

Any help would be appreciated.

[Mike Bayer]

that error is thrown by SQL Server and/or the ODBC driver you are using.  make sure you are using an appropriate datatype for the column in the database.      You can likely get more help on the pyodbc issue tracker at https://github.com/mkleehammer/pyodbc/issues as SQLAlchemy just passes these values through.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/6ef26eb0-b649-4069-a714-4325af3f7c3dn%40googlegroups.com.