python - DPI-1010：插入 Oracle DB 时未连接错误
问题描述
我正在尝试将数据帧插入表中。有没有其他方法可以将数据帧快速插入具有各种数据类型的数据库表中。
我的代码：
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)
print("Record inserted successfully")
except Exception as e:
print(e)
执行上述脚本时出现以下错误
PS C:\Users\ncdex1124> & C:/Users/ncdex1124/AppData/Local/Programs/Python/Python39/python.exe D:\test\insertcsvscript.py
Engine(oracle+cx_oracle://NCSWEB:***@192.168.213.151:1515/?service_name=devnc12c)
CL_SEQ CL_TMID CL_CLIENT_ID CL_PAN CL_CLI_STATUS CL_PAN_STATUS CL_ACTION_TYPE CL_CREATED_DATE CL_CREATED_BY CL_MODIFIED_DATE CL_MODIFIED_BY
0 3793375 21 MEN0008 ARJPP6330Q A V 0 9/6/2019 14:23 SYSTEM 9/9/2019 11:28 SYSTEM
1 3793378 8 AACCB1987D AACCB1987D A V 0 9/7/2019 14:23 SYSTEM 9/10/2019 11:28 SYSTEM
2 3793381 10 AABCI2081G AABCI2081G A V 0 9/8/2019 14:23 SYSTEM 9/11/2019 11:28 SYSTEM
3 3793383 11 AABCN9894G AABCN9894G A V 0 9/9/2019 14:23 SYSTEM 9/12/2019 11:28 SYSTEM
4 3793387 12 AAACM0267F AAACM0267F A V 0 9/10/2019 14:23 SYSTEM 9/13/2019 11:28 SYSTEM
... ... ... ... ... ... ... ... ... ... ... ...
180011 3793368 185 AACCC0537F AACCC0537F A V 0 7/14/2512 14:23 SYSTEM 9/6/2019 14:24 SYSTEM
180012 3793369 161 AAACP7015P AAACP7015P A V 0 7/15/2512 14:23 SYSTEM 9/6/2019 14:24 SYSTEM
180013 3793370 159 AAACA8392E AAACA8392E A V 0 7/16/2512 14:23 SYSTEM 9/6/2019 14:24 SYSTEM
180014 3793371 167 AACCC5501F AACCC5501F A V 0 7/17/2512 14:23 SYSTEM 9/6/2019 14:24 SYSTEM
180015 3793372 168 AAHCS7515E AAHCS7515E A V 0 7/18/2512 14:23 SYSTEM 9/6/2019 14:24 SYSTEM
[180016 rows x 11 columns]
Error closing cursor
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)
数据库表结构如下所示
CL_SEQ NUMBER
CL_TMID VARCHAR2(5 BYTE)
CL_CLIENT_ID VARCHAR2(10 BYTE)
CL_PAN VARCHAR2(10 BYTE)
CL_CLI_STATUS CHAR(1 BYTE)
CL_PAN_STATUS CHAR(2 BYTE)
CL_ACTION_TYPE VARCHAR2(1 BYTE)
CL_CREATED_DATE DATE
CL_CREATED_BY VARCHAR2(10 BYTE)
CL_MODIFIED_DATE DATE
CL_MODIFIED_BY VARCHAR2(10 BYTE)
我在这里做错了什么。或者还有其他方法吗？