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.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/6ef26eb0-b649-4069-a714-4325af3f7c3dn%40googlegroups.com.
