CODEWITHSUNDEEP
pythonoracle-databasesqlalchemyoracle12c
Ross Palmer
Ross Palmer

Reputation: 58

SQLAlchemy and Oracle 12c Connection Issues

I am using SQLAlchemy CORE (1.0.12) on Python 3.5 to connect with my organization's Oracle database. My database scripts worked without issue until my organization upgraded the Oracle database from 11g to 12c. After the upgrade I commonly run into the error below when trying to INSERT data into existing tables.

sqlalchemy.exc.OperationalError: (cx_Oracle.OperationalError) ORA-03135: connection lost contact
Process ID: 10355
Session ID: 184 Serial number: 60376

I did not change any of the SQLAlchemy scripts during the upgrade and the error does not always occur, generally only happening when I am trying to insert large datasets. I have researched issues associated with the ORA-03135 error code and confirmed that I am not experiencing any firewall issues (I am able to connect sometimes) or timeouts (error prompt appears within seconds which is much too soon for timeout).

Has anyone else had this issue and found a solution?

Traceback (most recent call last):
  File "C:\ross\repository\Analytics\OptTekSQL\run.py", line 5, in <module>
'Demand Analysis 8/17/16')
  File "C:\ross\repository\Analytics\OptTekSQL\optsql\data_load.py", line 43, in demand_output_load
s.insert_df(df, 'ACCDC_DEMAND_OUTPUTS')
  File "C:\ross\repository\Analytics\OptTekSQL\optsql\base.py", line 38, in insert_df
self.eng.connect().execute(ins, data)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute
return meth(self, multiparams, params)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context
context)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
exc_info
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\util\compat.py", line 200, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\util\compat.py", line 183, in reraise
raise value.with_traceback(tb)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\engine\base.py", line 1116, in _execute_context
context)
  File "C:\Users\palmer\AppData\Local\Continuum\Anaconda2\envs\py3\lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py", line 964, in do_executemany
cursor.executemany(statement, parameters)

Upvotes: 2

Views: 1788

Answers (1)

anonymous
anonymous

Reputation: 26

We've run into this exact issue and the following workaround prevented the error:

On the oracle database server, add parameter to the sqlnet.ora file (using a large number such as below to prevent timeout):

SQLNET.SEND_TIMEOUT=600000

Also add same parameter on the client's sqlnet.ora connecting to the database via python.

Upvotes: 1

Related Questions