pandas - read_sql() 有效，但 to_sql 给出错误 Orcale DB sqlalchemy

问题描述

我正在尝试使用 pd.read_sql 从 Oracle DB 读取，它提供输出并插入相同但 to_sql 给出无效标识符错误 无法理解为什么会出现这种奇怪的行为。最终我需要插入一百万行的数据框

我的代码：

from sqlalchemy import create_engine,types
import cx_Oracle
import pandas as pd 
import time

ip = '192.168.213.151'
port = 1515
SID = 'abc'
User='abc'
pwd ='abc'

dsn_tns = cx_Oracle.makedsn(ip, port, SID)
engine = create_engine('oracle+cx_oracle://%s:%s@%s' % (User, pwd, dsn_tns))


def InsertIntoDB():
    try:
        engine.connect()
        FinalDataframe = pd.read_sql('select * from NCSUSR.COLL_CLIENT_SUBMISSION_DTLS', engine)
        start_time = time.time()
        FinalDataframe.to_sql('NCSUSR.COLL_CLIENT_SUBMISSION_DTLS',engine,index=False, if_exists='append', chunksize=10**4)
        print("--- %s seconds ---" % (time.time() - start_time))
        print("successful")
    except exception as ex :
        print(ex)



InsertIntoDB()

错误：

    username: (1, 12345678, datetime.datetime(2021, 9, 16, 0, 0), 'A1017', 'BCIPB1234B', '01266', 'BCIPB1234T', 'NCDXPRE01', None, None, 'BCIPB1234M', 'P', 'CM', None, Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), 
Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), Decimal('2000'), 'N', 'N', 'Y', datetime.datetime(2021, 9, 16, 0, 0), 'SYSTEM', None, None)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
FinalDataframe    ccsd_id  ccsd_file_id ccsd_batch_dt ccsd_cm_id ccsd_cm_pan_no  ... ccsd_status ccsd_created_dt ccsd_created_by ccsd_modified_dt ccsd_modified_by
0        1      12345678    2021-09-16      A1017     BCIPB1234B  ...           Y      2021-09-16          SYSTEM             None             None    

[1 rows x 63 columns] None
Traceback (most recent call last):
  File "C:\Users\ncdex1124\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\ncdex1124\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-00904: "CCSD_NCCL_CC_APR_COMM": invalid identifier

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "d:\Alok\CR06645_PythonCode\insertdf.py", line 78, in InsertIntoDB
    FinalDataframe.to_sql('NCSUSR.COLL_CLIENT_SUBMISSION_DTLS', con=engine,index=False, if_exists='append', chunksize=10**4)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: "CCSD_NCCL_CC_APR_COMM": invalid identifier
[SQL: INSERT INTO "NCSUSR.COLL_CLIENT_SUBMISSION_DTLS" (ccsd_id, ccsd_file_id, ccsd_batch_dt, ccsd_cm_id, ccsd_cm_pan_no, ccsd_tm_id, ccsd_tm_pan_no, ccsd_cp_code, ccsd_cp_pan_no, ccsd_cli_cd, ccsd_cli_pan_no, ccsd_account_type, ccsd_segment_indicator, ccsd_ucc, ccsd_cp_fin_ledger_bal_a, ccsd_cp_fin_ledger_bal_b, ccsd_cp_fin_peak_ledger_bal_c, ccsd_cp_bg, ccsd_cp_fdr, ccsd_cp_apr_sec_cash_com, ccsd_cp_apr_sec_non_cash_com, ccsd_cp_non_apr_sec, ccsd_cp_cc_apr_comm, ccsd_cp_othr_coll, ccsd_tm_cred_enty_ledger_epi, ccsd_tm_pool_account, ccsd_tm_cash_retained, ccsd_tm_bg, ccsd_tm_fdr, ccsd_tm_apr_sec_cash_com, ccsd_tm_apr_sec_non_cash_com, ccsd_tm_non_apr_sec, ccsd_tm_cc_apr_comm, ccsd_tm_othr_coll, ccsd_cm_cash_placed, ccsd_cm_bg, ccsd_cm_fdr, ccsd_cm_apr_sec_cash_com, ccsd_cm_apr_sec_non_cash_com, ccsd_cm_non_apr_sec, ccsd_cm_cc_apr_comm, ccsd_cm_othr_coll, ccsd_cm_cash_retained, ccsd_cm_rtd_bg, ccsd_cm_rtd_fdr, ccsd_cm_rtd_apr_sec_cash_com, ccsd_cm_rtd_apr_sec_n_cash_com, ccsd_cm_rtd_non_apr_sec, ccsd_cm_rtd_cc_apr_comm, ccsd_cm_rtd_othr_coll, ccsd_nccl_cash_placed, ccsd_nccl_bg, ccsd_nccl_fdr, ccsd_nccl_apr_sec_cash_com, ccsd_nccl_aprv_sec_non_cash_com, ccsd_nccl_cc_apr_comm, ccsd_email_flag, ccsd_sms_flag, ccsd_status, ccsd_created_dt, ccsd_created_by, ccsd_modified_dt, ccsd_modified_by) VALUES (:ccsd_id, :ccsd_file_id, :ccsd_batch_dt, 
:ccsd_cm_id, :ccsd_cm_pan_no, :ccsd_tm_id, :ccsd_tm_pan_no, :ccsd_cp_code, :ccsd_cp_pan_no, :ccsd_cli_cd, :ccsd_cli_pan_no, :ccsd_account_type, :ccsd_segment_indicator, :ccsd_ucc, :ccsd_cp_fin_ledger_bal_a, :ccsd_cp_fin_ledger_bal_b, :ccsd_cp_fin_peak_ledger_bal_c, :ccsd_cp_bg, :ccsd_cp_fdr, :ccsd_cp_apr_sec_cash_com, :ccsd_cp_apr_sec_non_cash_com, :ccsd_cp_non_apr_sec, :ccsd_cp_cc_apr_comm, :ccsd_cp_othr_coll, :ccsd_tm_cred_enty_ledger_epi, :ccsd_tm_pool_account, :ccsd_tm_cash_retained, :ccsd_tm_bg, :ccsd_tm_fdr, :ccsd_tm_apr_sec_cash_com, :ccsd_tm_apr_sec_non_cash_com, :ccsd_tm_non_apr_sec, :ccsd_tm_cc_apr_comm, :ccsd_tm_othr_coll, :ccsd_cm_cash_placed, :ccsd_cm_bg, :ccsd_cm_fdr, :ccsd_cm_apr_sec_cash_com, :ccsd_cm_apr_sec_non_cash_com, :ccsd_cm_non_apr_sec, :ccsd_cm_cc_apr_comm, :ccsd_cm_othr_coll, :ccsd_cm_cash_retained, :ccsd_cm_rtd_bg, :ccsd_cm_rtd_fdr, :ccsd_cm_rtd_apr_sec_cash_com, :ccsd_cm_rtd_apr_sec_n_cash_com, :ccsd_cm_rtd_non_apr_sec, :ccsd_cm_rtd_cc_apr_comm, :ccsd_cm_rtd_othr_coll, :ccsd_nccl_cash_placed, :ccsd_nccl_bg, :ccsd_nccl_fdr, :ccsd_nccl_apr_sec_cash_com, :ccsd_nccl_aprv_sec_non_cash_com, :ccsd_nccl_cc_apr_comm, :ccsd_email_flag, :ccsd_sms_flag, :ccsd_status, :ccsd_created_dt, :ccsd_created_by, :ccsd_modified_dt, :ccsd_modified_by)]
[parameters: {'ccsd_id': 1, 'ccsd_file_id': 12345678, 'ccsd_batch_dt': datetime.datetime(2021, 9, 16, 0, 0), 'ccsd_cm_id': 'A1017', 'ccsd_cm_pan_no': 'BCIPB1234B', 'ccsd_tm_id': '01266', 'ccsd_tm_pan_no': 'BCIPB1234T', 'ccsd_cp_code': 'NCDXPRE01', 'ccsd_cp_pan_no': None, 'ccsd_cli_cd': None, 'ccsd_cli_pan_no': 'BCIPB1234M', 'ccsd_account_type': 'P', 'ccsd_segment_indicator': 'CM', 'ccsd_ucc': None, 'ccsd_cp_fin_ledger_bal_a': 2000.0, 'ccsd_cp_fin_ledger_bal_b': 2000.0, 'ccsd_cp_fin_peak_ledger_bal_c': 2000.0, 'ccsd_cp_bg': 2000.0, 'ccsd_cp_fdr': 2000.0, 'ccsd_cp_apr_sec_cash_com': 2000.0, 'ccsd_cp_apr_sec_non_cash_com': 2000.0, 'ccsd_cp_non_apr_sec': 2000.0, 'ccsd_cp_cc_apr_comm': 2000.0, 'ccsd_cp_othr_coll': 2000.0, 'ccsd_tm_cred_enty_ledger_epi': 2000.0, 'ccsd_tm_pool_account': 2000.0, 'ccsd_tm_cash_retained': 2000.0, 'ccsd_tm_bg': 2000.0, 'ccsd_tm_fdr': 2000.0, 'ccsd_tm_apr_sec_cash_com': 2000.0, 'ccsd_tm_apr_sec_non_cash_com': 2000.0, 'ccsd_tm_non_apr_sec': 2000.0, 'ccsd_tm_cc_apr_comm': 2000.0, 'ccsd_tm_othr_coll': 2000.0, 'ccsd_cm_cash_placed': 2000.0, 'ccsd_cm_bg': 2000.0, 'ccsd_cm_fdr': 2000.0, 'ccsd_cm_apr_sec_cash_com': 2000.0, 'ccsd_cm_apr_sec_non_cash_com': 2000.0, 'ccsd_cm_non_apr_sec': 2000.0, 'ccsd_cm_cc_apr_comm': 2000.0, 'ccsd_cm_othr_coll': 2000.0, 'ccsd_cm_cash_retained': 2000.0, 'ccsd_cm_rtd_bg': 2000.0, 'ccsd_cm_rtd_fdr': 2000.0, 'ccsd_cm_rtd_apr_sec_cash_com': 2000.0, 'ccsd_cm_rtd_apr_sec_n_cash_com': 2000.0, 'ccsd_cm_rtd_non_apr_sec': 2000.0, 'ccsd_cm_rtd_cc_apr_comm': 2000.0, 'ccsd_cm_rtd_othr_coll': 2000.0, 'ccsd_nccl_cash_placed': 2000.0, 'ccsd_nccl_bg': 2000.0, 'ccsd_nccl_fdr': 2000.0, 'ccsd_nccl_apr_sec_cash_com': 2000.0, 'ccsd_nccl_aprv_sec_non_cash_com': 2000.0, 'ccsd_nccl_cc_apr_comm': 2000.0, 'ccsd_email_flag': 'N', 'ccsd_sms_flag': 'N', 'ccsd_status': 'Y', 'ccsd_created_dt': datetime.datetime(2021, 9, 16, 0, 0), 'ccsd_created_by': 'SYSTEM', 'ccsd_modified_dt': None, 'ccsd_modified_by': None}]

标签： pandasoraclesqlalchemy

pandas - read_sql() 有效，但 to_sql 给出错误 Orcale DB sqlalchemy

问题描述

解决方案

推荐阅读