cx_Oracle how to run executemany() with Date and Datetime (ORA-01861)-CodePudding

How can I run executemany to insert Date and Datetime without error ORA-01861 - literal does not match format string?

DB Structre

CREATE TABLE test_db(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    strMsg VARCHAR2(200) NOT NULL,
    dt DATE NOT NULL,
    dt_time TIMESTAMP(6) NOT NULL,
    PRIMARY KEY(id)
);

Python

import cx_Oracle
import pandas as pd
from datetime import *

apiToDf={
    'strMsg':[
        'AA',
        'BB',
        'CC',
        'DD'
    ],
    'dt':[
        '2022-02-01',
        '2022-02-02',
        '2022-02-03',
        '2022-02-04'
    ]
}

df=pd.dataframe(apiToDf)


data = []
PROCESS_TIME = datetime.now()

for ind in df.index:
    dataList = []
    paramOne = df['strMsg'][ind]
    paramTwo = datetime.date(df['dt'][ind])
    paramThree = PROCESS_TIME
    
    dataList.append(paramOne)
    dataList.append(paramTwo)
    dataList.append(paramThree)
    dataTupe = Tuple(dataList)

conn = cx_Orace.connet('Test','123456','127.0.0.1/testDb')
cursor = conn.cursor()
target_tb = 'test_db'
script = "insert into {tb} values (:1, :2, :3)".format(tb=target_tb)
cursor.executemany(script, data)
conn.commit()
cursor.close()
conn.close()

CodePudding user response：

There are some issue which need to be fixed

currently you should get the error
```
AttributeError: module'pandas' has no attribute 'dataframe' 
```
because dataframe should be renamed as DataFrame

and you should get another error

TypeError: descriptor 'date' for 'datetime.datetime' objects doesn't apply to a 'str' object

rather you can use datetime.strptime

concatenate the connection string like this 'Test/[email protected]:1521/testDb'
the columns except for id(already generated automatically) next to the INSERT statement should explicitly be listed
moreover, using exception handling, and sprinkling some print commands within the code ,in order to follow what's going on, is a remarkable habit to be considered

So, overall code might be rewritten as

import cx_Oracle
import pandas as pd  
from datetime import *

apiToDf={
    'strMsg':[
        'AA',
        'BB',
        'CC',
        'DD'
    ],
    'dt':[
        '2022-02-01',
        '2022-02-02',
        '2022-02-03',
        '2022-02-04'
    ]
}

df=pd.DataFrame(apiToDf)


data = []
process_time = datetime.now()

for ind in df.index:
    dataList = []
    
    dataList.append(df['strMsg'][ind])    
    dataList.append(datetime.strptime(df['dt'][ind], "%Y-%m-%d")) 
    dataList.append(process_time)
    data.append(dataList)

conn = cx_Oracle.connect('Test/[email protected]:1521/testDb')
try:
    cursor = conn.cursor()
    target_tb = 'test_db'
    script = "INSERT INTO {tb}(strMsg,dt,dt_time) VALUES(:1, :2, :3)".format(tb=target_tb)
    cursor.executemany(script, data)
    cursor.close()
except Exception as err:
    print('Error', err)
else:
    conn.commit()
    
conn.close()

CodePudding user response：

If we convert the data to Tuple, we can use executemany normally.

    PROCESS_TIME = datetime.now()
    for ind in df.index:
        paramOne = df['strMsg'][ind]
        paramTwo = datetime.date(df['dt'][ind])
        paramThree = PROCESS_TIME

        dataTuple = (paramOne, paramTwo, paramThree)
        data.append(dataTuple)

    # Insert many
    target_tb = 'test_db'
    script = "insert into {tb} values (:1, :2, :3)".format(tb=target_tb)
    cursor.executemany(script, data)