0

Background

I have 3 separate python scripts that share the same structure and effectively do the same thing - call a request to an API, retrieve XML data, convert XML to ElementTree object then to pandas DataFrame object then use .to_sql() to import that dataframe into an oracle database. This was successful for two out of three of the scripts I have written but the third is not writing to the DB, there are no errors returned, the table is created empty, the script hangs

Code from successful files:

oracle_db = sa.create_engine('oracle://sName:sName@123.456.78/testDB')
connection = oracle_db.connect()
df.to_sql('TABLE_NAME', connection, if_exists='append',index = False)

I would post the code for the unsuccessful file but it is quite literally the same besides the table and variable name.

What I have Tried

I have tried to use cx_oracle's engine to drive the connection to the DB with no success:

   conn = cx_Oracle.connect("sName", "sName","123.456.789.1/Test", encoding = "UTF-8")

I have verified the dataframe is valid.

I have verified the connection to the DB.

Jamm6
  • 3
  • 5
  • Did you check to see if any other code (perhaps the first two scripts?) has an open transaction on the table in question? That would explain the hang quite nicely! – Anthony Tuininga Sep 24 '19 at 19:56
  • @AnthonyTuininga No dice, I moved a 'connection.close()' to the front of the script to grab any open connections and while it doesn't throw an error the data is still not being written to the DB. – Jamm6 Sep 24 '19 at 20:41
  • Check to see if there are any locks blocking the insert by looking at the ``dba_locks`` view. – Anthony Tuininga Sep 25 '19 at 20:41

1 Answers1

0

SOLVED - there was a column that was strictly integers so I had to specify the data type in the to.sql() call.

Jamm6
  • 3
  • 5