Batch loading multiple CSV files which include Date into Oracle database Date

Question

I am trying to load CSV files, which include a YYYY-MM-DD date format, to oracle database. But, always ended up with "DatabaseError: ORA-01861: literal does not match format string".

I think it needs a type of TO-DATE get invloved, but don'w know to to implement it in batch mode.

    # Locating CSVs
    
    DATA_FILES_PATH = 'apt-data-files'
    data_files = os.listdir(DATA_FILES_PATH)
    data_files = [ f'{DATA_FILES_PATH}/{r}' for r in data_files ]
    
    # dataframe to tuple list
    testD_tp = [ tuple(r) for r in testD.values ]
    
    # dataframe -> DB insert function
    
    def bulk_insert(tuple_data):
        bulk_insert_sql = """
            INSERT INTO APT_DEAL_PRICE
            VALUES (
                SEQ_ID.NEXTVAL, :REGION, :REGION_SUB, :APT_NAME,
                :APT_SIZE, :APT_FLOOR, :CONTRACT_DATE, :CONTRACT_PRICE,
                :COMPLETION_YEAR, :STREET_NUM, :STREET_NAME)
            """
    
        conn = cx_Oracle.connect('house', 'house', 'localhost:1521/xe')
        cursor = conn.cursor()
    
        cursor.prepare(bulk_insert_sql)
        cursor.executemany(None, tuple_data)
        
        conn.commit()
        cursor.close()
        conn.close()
        
        
    # Batching dataframe to DB
    
    bulk_insert(testD_tp)

It tells me I have a DatabaseError

DatabaseError: ORA-01861: literal does not match format string

Any help would be very appreciated.

A side comment: there's nothing specific to array binding (i.e batching) in this question. The same issue would occur with inserting a single row using `execute()`. — Christopher Jones, Apr 09 '21 at 22:50

score 1 · Accepted Answer · answered Apr 09 '21 at 11:09

Assuming that contract_date is the only date column, you probably want

INSERT INTO APT_DEAL_PRICE
        VALUES (
            SEQ_ID.NEXTVAL, :REGION, :REGION_SUB, :APT_NAME,
            :APT_SIZE, :APT_FLOOR, to_date(:CONTRACT_DATE, 'yyyy-mm-dd'),
            :CONTRACT_PRICE, :COMPLETION_YEAR, :STREET_NUM, :STREET_NAME)

As a general practice, I'd strongly suggest listing the columns you are inserting data into both to make your code self-documenting and so that your code doesn't fail if someone later adds a new column to the table. I'm guessing at the column names for your table but something like

INSERT INTO APT_DEAL_PRICE( apt_deal_price_id, region, region_sub, apt_name,
                            apt_size, apt_floor, contract_date,
                            contract_price, completion_year, street_num, street_name )
        VALUES (
            SEQ_ID.NEXTVAL, :REGION, :REGION_SUB, :APT_NAME,
            :APT_SIZE, :APT_FLOOR, to_date(:CONTRACT_DATE, 'yyyy-mm-dd'),
            :CONTRACT_PRICE, :COMPLETION_YEAR, :STREET_NUM, :STREET_NAME)

Thank you, Justin. This is a perfect answer for me! Thank you for your advice! — SY Chun, Apr 12 '21 at 04:18

Batch loading multiple CSV files which include Date into Oracle database Date

1 Answers1