CODEWITHSUNDEEP
pythonpandasoracle-databasedataframesqlalchemy
Alok Sharma
Alok Sharma

Reputation: 115

Dataframe did not insert into DB but throws DPI Not connected error

I need to insert a dataframe into oracle sql and fastest method I could found was sqlalchemy but this doesn't seem to work in my favor.

My code :

import pandas as pd 
from sqlalchemy.engine import create_engine
from sqlalchemy import types
import cx_Oracle

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'NCSWEB' #enter your username
PASSWORD = '########' #enter your password
HOST = '192.168.213.151' #enter the oracle db host url
PORT = 1515 # enter the oracle port number
SERVICE = 'devnc12c' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)
print(engine)
#read data from csv
fileName='C:\\Users\\ncdex1124\Desktop\\New folder\\file4.csv'
df = pd.read_csv(fileName)
print(df)
dtyp = {c:types.VARCHAR(df[c].str.len().max())
    for c in df.columns[df.dtypes == 'object'].tolist()}
try:
    df.to_sql('UCIDBA.FUT_CLIENT_LIST_COPY', engine.connect(), if_exists='append', index=False, chunksize=10000,dtype=dtyp)
except Exception as e:
    print(e)
print("Record inserted successfully")

Output:

        Traceback (most recent call last):
  File "C:\Users\ncdex1124\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1751, in _execute_context
    self.dialect.do_executemany(
  File "C:\Users\ncdex1124\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py", line 1347, in do_executemany        
    cursor.executemany(statement, parameters)
cx_Oracle.DatabaseError: DPI-1080: connection was closed by ORA-3135

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\ncdex1124\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1866, in _safe_close_cursor
    cursor.close()
cx_Oracle.DatabaseError: DPI-1010: not connected
(cx_Oracle.DatabaseError) DPI-1010: not connected
(Background on this error at: https://sqlalche.me/e/14/4xp6)

I dont understand what is wrong in this case .

Upvotes: 2

Views: 722

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

By default, cx_oracle rollbacks any transaction without explicit commit.

Autocommitting mode

The Connection object has an attribute called autocommit that allows you to commit the transaction automatically. By default, its value sets to False. To instruct cx_Oracle commit the transaction automatically, you set the value of the Connection.autocommit to True as follows:

connection.autocommit = True

As you are using Pandas and create_engine from SQL Alchemy, you need to

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="AUTOCOMMIT"
)

Regarding your code, I would try this, keeping in consideration that you are using latest version of Pandas and Sql Alchemy. It should work.

import pandas as pd 
from sqlalchemy.engine import create_engine
from sqlalchemy import types
import cx_Oracle
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'NCSWEB' #enter your username
PASSWORD = '########' #enter your password
HOST = '192.168.213.151' #enter the oracle db host url
PORT = 1515 # enter the oracle port number
SERVICE = 'devnc12c' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH,max_identifier_length=128)
print(engine)
#read data from csv
fileName='C:\\Users\\ncdex1124\Desktop\\New folder\\file4.csv'
df = pd.read_csv(fileName)
print(df)
dtyp = {c:types.VARCHAR(df[c].str.len().max())
    for c in df.columns[df.dtypes == 'object'].tolist()}
try:
    engine = engine.connect().execution_options(autocommit=True) 
    df.to_sql('UCIDBA.FUT_CLIENT_LIST_COPY', con=engine, if_exists='append', index=False, chunksize=10000,dtype=dtyp)
except Exception as e:
    print(e)
print("Record inserted successfully")

An example how it works the connection with autocommit on my own enviroment

C:\python>python
Python 3.8.1 (tags/v3.8.1:1b293b6, Dec 18 2019, 23:11:46) [MSC v.1916 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> from sqlalchemy.engine import create_engine
>>> from sqlalchemy import types
>>> import cx_Oracle
>>> DIALECT = 'oracle'
>>> SQL_DRIVER = 'cx_oracle'
>>> USERNAME = 'PYTHON' #enter your username
>>> PASSWORD = '#######' #enter your password
>>> HOST = 'ODCGRC1R.SCGER.DEV.CORP' #enter the oracle db host url
>>> PORT = 60995 # enter the oracle port number
>>> SERVICE = 'odcgrc1r' # enter the oracle db service name
>>> ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
>>> engine = create_engine(ENGINE_PATH_WIN_AUTH,max_identifier_length=128)
>>> print(engine)
Engine(oracle+cx_oracle://PYTHON:***@ODCGRC1R.SCGER.DEV.CORP:60995/?service_name=odcgrc1r)
>>> #read data from csv
>>> engine = engine.connect().execution_options(autocommit=True)
>>>

Upvotes: 1

Related Questions