SQLAlchemy + oracledb/cx_Oracle Insert truncates Timestamp to seconds

I need to merge dataframe which contains data in usual "pandas._libs.tslibs.timestamps.Timestamp" type. This df has the following structure

my_test_date = pd.to_datetime(1674009901958454, unit='us')

df = pd.DataFrame(data = {'payment_id_pay': [1, 2],
                          'DT_START': [my_test_date, my_test_date],
                          'DT_END': [my_test_date, my_test_date]})

df looks like

Since I was working with pandas, I used sqlalchemy to merge it to my Oracle DB:

from sqlalchemy import create_engine, text

sql = f'''
MERGE INTO mytable
    USING dual
    ON (payment_id_pay = :1)
    WHEN MATCHED THEN UPDATE SET DT_START = :2, DT_END = :3
    WHEN NOT MATCHED THEN INSERT (payment_id_pay, DT_START, DT_END) 
    VALUES (:1, :2, :3)
'''
engine = create_engine(f'oracle+oracledb://login:pass@host:1521/?service_name=service_name')
with engine.connect() as conn:      
    conn.execute(text(sql), [dict(zip([f'{i+1}' for i in range(len(df.columns))], i)) for i in df.values.tolist()])
    conn.commit()
engine.dispose()

List comprehension is the parameters for execute method

And the result is that in my table in Oracle DB all timestamps truncated to seconds, all milliseconds = 000000

I found this question on cx_Oracle github https://github.com/oracle/python-cx_Oracle/issues/161

Since I didt find any clear to me documentation where I can find out how to use cursor.setinputsizes(cx_Oracle.TIMESTAMP) using SQLAlchemy, i did it like in this example Python cx_Oracle Insert Timestamp With Milliseconds I tried 3 different ways: SQLAlchemy, pure oracledb and pure cx_Oracle

1st: pure cx_Oracle as recommended in github question:

import cx_Oracle

sql = f'''
MERGE INTO mytable
    USING dual
    ON (payment_id_pay = :1)
    WHEN MATCHED THEN UPDATE SET DT_START = :2, DT_END = :3
    WHEN NOT MATCHED THEN INSERT (payment_id_pay, DT_START, DT_END) 
    VALUES (:1, :2, :3)
'''
conn = cx_Oracle.connect(user=user, password=password, dsn=dsn)
conn.cursor().setinputsizes(None, cx_Oracle.TIMESTAMP, cx_Oracle.TIMESTAMP)
conn.cursor().executemany(sql, [dict(zip([f'{i+1}' for i in range(len(df.columns))], i)) for i in df.values.tolist()])
conn.commit()
conn.close()

2nd: I tried the same but with oracledb instead of cx_Oracle:

import oracledb

sql = f'''
MERGE INTO mytable
    USING dual
    ON (payment_id_pay = :1)
    WHEN MATCHED THEN UPDATE SET DT_START = :2, DT_END = :3
    WHEN NOT MATCHED THEN INSERT (payment_id_pay, DT_START, DT_END) 
    VALUES (:1, :2, :3)
'''

conn = oracledb.connect(user=user, password=password, host="host", port=1521, service_name="name")
conn.cursor().setinputsizes(None, oracledb.TIMESTAMP, oracledb.TIMESTAMP)
conn.cursor().executemany(sql, [dict(zip([f'{i+1}' for i in range(len(df.columns))], i)) for i in df.values.tolist()])
conn.commit()
conn.close()

3d: I didnt find how to use setinputsizes() method in SQLAlchemy, but I found that this class and method exists "method sqlalchemy.engine.interfaces.DBAPICursor.setinputsizes(sizes: Sequence[Any]) → None" I tried the following:

import sqlalchemy
from sqlalchemy.engine.interfaces import DBAPICursor

sql = f'''
MERGE INTO mytable
   USING dual
   ON (payment_id_pay = :1)
   WHEN MATCHED THEN UPDATE SET DT_START = :2, DT_END = :3
   WHEN NOT MATCHED THEN INSERT (payment_id_pay, DT_START, DT_END) 
   VALUES (:1, :2, :3)
'''
engine = sqlalchemy.create_engine(f'oracle+oracledb://login:pass@host:1521/?service_name=service_name')
with engine.connect() as conn:   
   DBAPICursor.setinputsizes(oracledb.TIMESTAMP, oracledb.TIMESTAMP)
   conn.execute(text(sql), [dict(zip([f'{i+1}' for i in range(len(df.columns))], i)) for i in df.values.tolist()])
   conn.commit()
engine.dispose()

All the code above runs without problems and gives me precisely the same result: datetimes truncated to seconds in my DB

I also tried converting data from pandas Timestamp to datetime.datetime, nothing changed

I use: SQLAlchemy 2.0.25, oracledb 1.4.2, cx-Oracle 8.3.0

Database: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

How can I insert my data without truncation?

For textual SQL, use

• parameter placeholders that are valid Python names, e.g., p1 instead of 1,
• an oracledb cursor, and
• oracledb's .setinputsizes() to declare datetime/Timestamp parameters

import datetime

import oracledb
import sqlalchemy as sa

engine = sa.create_engine(
    "oracle+oracledb://scott:tiger@192.168.0.199/?service_name=xepdb1"
)

with engine.begin() as conn:
    sql = f"""\
    MERGE INTO mytable
        USING dual
        ON (payment_id_pay = :p1)
        WHEN MATCHED THEN UPDATE SET DT_START = :p2, DT_END = :p3
        WHEN NOT MATCHED THEN INSERT (payment_id_pay, DT_START, DT_END) 
        VALUES (:p1, :p2, :p3)
    """
    test_data = [
        {
            "p1": 1,
            "p2": datetime.datetime(2012, 1, 1, 12, 0, 0, 123000),
            "p3": datetime.datetime(2013, 1, 1, 12, 0, 0, 456000),
        },
        {
            "p1": 2,
            "p2": datetime.datetime(2022, 1, 1, 12, 0, 0, 123000),
            "p3": datetime.datetime(2023, 1, 1, 12, 0, 0, 456000),
        },
    ]
    cursor = conn.connection.cursor()
    cursor.setinputsizes(p2=oracledb.TIMESTAMP, p3=oracledb.TIMESTAMP)
    cursor.executemany(sql, test_data)