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.
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)