Dynamic cursor.setinputsizes()

[corey-dawson]

When using binding for an insert statement (binding the dataframe column to database table columns), there does not appear to be an obvious way of dynamically adding the inputsizes (curosr.setinputsizes()). The setinput sizes function doesnt seem to accept the standard python datatypes to do this, which would make it possible to do this in the case of binding by name. Please provide some input on how to do this or update the function to be able to pass a dictionary

Working

cursor.setinputsizes(
  col1 = 8,
  col2 = cx_Oracle.DATETIME)

Desired

d  = {"col1": 8, "col2": cx_Oracle.DATETIME}
cursor.setinputsizes(d)

If columns are binded, and the setinputsizese is used with positional arguments (cursor.setinputsizes(8, cx_Oracle.DATETIME)), an exception is raised, "ProgrammingError: positional and named binds cannot be intermixed". Please provide a method of dynamically adding named inputsizes for binding by name method. If there is a way of already doing this, please provide an example. The documentation doesn't provide good clarity to this, so if there is a method, it would help to update the documentation.

sudo code for future state

from datetime import datetime
import pandas as pd
import cx_Oracle as cx
data = {
    "col1": ["hello", "goodbye"],
    "col2": [datetime.now(), datetime.now()]
}
df = pd.DataFrame(data)

d = {}
for col in df.select_dtypes("object").columns:
    d[col] = df[col].str.len().max()
for col in df.select_dtypes("datetime64").columns:
    d[col] = cx.DATETIME

sql = "insert into myschema.insert_tst values (:col1, :col2)"
conn = cx.connect(user=user, password=password, dsn=dsn)
cursor = conn.cursor()
cursor.prepare(sql)
cursor.setinputsizes(d)
cursor.bindarraysize = 1000
cursor.executemany(sql, df.to_dict("records"))
conn.commit()
cursor.close()
conn.close()

Thanks in advance!

[anthony-tuininga]

If you have a dictionary containing keyword arguments, the correct approach is as follows:

d  = {"col1": 8, "col2": cx_Oracle.DATETIME}
cursor.setinputsizes(**d)

This is the way it would be done normally (static):

cursor.setinputsizes(col1=8, col2=cx_Oracle.DATETIME)

This is "normal" Python behavior for a method that has the following signature:

def setinputsizes(*args, **kwargs)

Am I missing something? I'm not sure exactly what the problem is!

[corey-dawson]

@anthony-tuininga This is exactly what i was looking for! thanks for the quick response, i really appreciate it.