0

I am trying to copy data from 1 oracle table to another in a different schema using the python odbc library. Here is what I'm doing

source = SomeString (source Oracle DataTable) target = SomeString (target Oracle DataTable)

Connecting to data source to retrieve data: source_data = pyodbc.connect(source) source_cursor = source_data.cursor()

Connect to Target data source target = pyodbc(target) target_cursor = target.cursor()

I now declare my source data query source_query = SELECT * FROM TABLE where TYPE = X

I put the data in a dataframe and then convert it to a list data = pd.read_sql(source_query, source) data = data.values.tolist()

I am now trying to insert data in my "data" list to my target table. I declare an insert statement and then run executemany as follows:

sql = "INSERT INTO SCHEMA.TABLE (column1, column 2, etc...) Values (?,?, etc..)

Now since I have my data and target connection established I execute the following target_cursor.executemany(sql, data)

I get the following error below and the weird part is that the code inserted 1 line in the new table properly and then it fails and nothing happens.

Can you please guide me on how to fix this?

I get the following error:

C:\WinPy3770x64\python-3.7.7.amd64\lib\encodings\utf_16_le.py in decode(input, errors)
     15 def decode(input, errors='strict'):
---> 16     return codecs.utf_16_le_decode(input, errors, True)
     17 

UnicodeDecodeError: 'utf-16-le' codec can't decode bytes in position 184-185: illegal encoding

The above exception was the direct cause of the following exception:
SystemError                               Traceback (most recent call last)
<ipython-input-70-ec2d225d6132> in <module>
----> 1 target_cursor.executemany(sql_statement, data)

SystemError: <class 'pyodbc.Error'> returned a result with an error set
Nabeel
  • 1
  • Out of interest, why are you using pyodbc and not cx_Oracle to access Oracle DB? – Christopher Jones May 09 '20 at 22:58
  • Hi Christopher - thanks for looking at my question. I work in an enterprise environment where we sometimes don't get the libraries we require or may take a long time to install. So the only library I have access to at the moment is pyodbc. I actually ended up solving my problem in a different way. I basically import my source table to a list and created a for loop where it create an "Insert Into..." string and use cursor.execute and loop through the insert statements. Probably not the most efficient way of doing things but it solved my problem. – Nabeel May 10 '20 at 17:25
  • Thanks for the info. – Christopher Jones May 10 '20 at 22:58

0 Answers0