Batch Inserting Date Records into Oracle and MySQL with Python

Purpose

The goal is to process data with Python and store the results in SQL databases, specifically handling a column of datetime type that caused insertion errors.

Sample Data

from datetime import datetime
import pandas as pd

df = pd.DataFrame({
    'time': datetime.now().replace(microsecond=0),
    'idx': [80, 90]
})

Processing Method – Oracle

Connect to Oracle via a JDK driver (details omitted). Use a cursor to execute a bulk insert.

sql = "INSERT INTO Test_Table (Time, idx) VALUES(:1, :2)"

cursor = conn.cursor()  # get cursor
try:
    cursor.executemany(sql, df.values.tolist())  # insert dataframe rows
except Exception as e:
    conn.rollback()  # rollback on failure
    print(f'Insert failed, {str(e)}')
else:
    conn.commit()  # commit on success
finally:
    cursor.close()  # close cursor

When running this against Oracle, insertion fails because the Time column in the database is defined as DATE, while the pandas datetime is converted to a string like 2022-05-01 18:12:31. Oracle cannot store that string directly in a DATE column.

Fix for Oracle – Use TO_DATE

Modify the SQL to convert the string to a DATE inside Oracle:

sql = "INSERT INTO Test_Table (Time, idx) VALUES(to_date(:1,'yyyy-mm-dd HH24:MI:SS'), :2)"

The date format must match the string being inserted; hours can be set to 24‑hour format.

Processing Method – MySQL

Install the connector:

pip install mysql-connector-python

Import and connect similarly to Oracle (details omitted). Use a MySQL‑style placeholder %s in the INSERT statement:

sql = "INSERT INTO Test_Table (time, idx) VALUES (%s, %s)"

cursor = conn.cursor()
try:
    cursor.executemany(sql, df.values.tolist())
except Exception as e:
    conn.rollback()
    print(f'Insert failed, {str(e)}')
else:
    conn.commit()
finally:
    cursor.close()

MySQL accepts the datetime string more leniently, but you can also explicitly convert it using STR_TO_DATE:

sql = "INSERT INTO Test_Table (time, idx) VALUES (STR_TO_DATE(%s, '%Y-%m-%d %H:%i:%S'), %s)"

If the Python datetime includes microseconds, append .%f to the format string.

Summary

The article demonstrates how to batch‑insert pandas datetime data into Oracle and MySQL databases using Python. Oracle requires explicit conversion with TO_DATE, while MySQL is more tolerant but can also use STR_TO_DATE. Proper transaction handling (commit/rollback) ensures the program remains robust.