How import datetime object to db/oracle with jaydebeapi?

[zdenop]

I try to insert datatime object to oracle db but I get errors:

TypeError: No matching overloads found for oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(int,datetime.date), options are:
    public void oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(int,java.lang.Object,int,int) throws java.sql.SQLException
    public void oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(int,java.lang.Object,java.sql.SQLType,int) throws java.sql.SQLException
    public void oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(int,java.lang.Object,java.sql.SQLType) throws java.sql.SQLException
    public void oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(int,java.lang.Object,int) throws java.sql.SQLException
    public void oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(int,java.lang.Object) throws java.sql.SQLException

Here is my testing case:

import os
import sys
import jpype
import jaydebeapi
import datetime

oracle_driver= "/opt/oracle/sqlcl/lib/ojdbc8.jar"
JAVA_HOME = "/usr/lib/jvm/java-8-openjdk-armhf"
if not os.environ.get("JAVA_HOME"):
        os.environ["JAVA_HOME"] = JAVA_HOME

def _to_datetime(rs, col):
    java_val = rs.getTimestamp(col)
    if not java_val:
        return
    d = datetime.datetime.strptime(str(java_val)[:19], "%Y-%m-%d %H:%M:%S")
    return d.replace(microsecond=int(str(java_val.getNanos())[:6]))

jaydebeapi._DEFAULT_CONVERTERS.update(
    {"TIMESTAMP": _to_datetime, 
     "BIGINT": jaydebeapi._java_to_py("longValue")}
)

args = f'-Djava.class.path={oracle_driver}'

if jpype.isJVMStarted():
    jpype.shutdownJVM()

jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args, convertStrings=True) 

conn = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver',
                          'jdbc:oracle:thin:@hostname:port:sid',
                          ["user", "pwd"])
cursor = conn.cursor()

query_insert = """INSERT INTO fx_rates VALUES(:1, :2, :3, :4, :5)"""
cursor.execute(query_insert, [datetime.datetime(2021, 11, 1, 0, 0), 'EUR', 'USD', 1.1559, 'D'])
cursor.close()
conn.close()

Any idea how to solve it?

[Thrameos]

You would need to cast or convert the item to a Java timestamp object. JPype and its dbapi2 implementation have this conversion defined but I dont know if JayDeBeAPI does.

I would try the following jpype code and see if it fixes JayBeDeAPI

import jpype
import jpype.imports
#start jaydebeapi 
 ...
# import java so we have java types
import java

#create a datetime
import datetime
dt = datetime.datetime.now()

#cast to java
jdt = java.sql.Timestamp@dt

#Call your insert statement
...

The java.sql.Timestamp@ is a casting operator.

[Thrameos]

For more details on casting or other useful tips on using JPype i recommend starting with https://jpype.readthedocs.io/en/latest/quickguide.html

[zdenop]

Thank you very much! This works for me:

cursor.execute(query_insert, [java.sql.Timestamp@datetime.datetime(2021, 11, 1, 0, 0), 'EUR', 'USD', 1.1559, 'D'])

Actually I want to run executemany like this:

cursor.executemany(query_insert ,fx_data)

So do I need to convert each datetime object in advance:

for idx, col in enumerate(row):
        if isinstance(col, datetime.date):
             row[idx] = java.sql.Date@col
        if isinstance(col, datetime.datetime):
             row[idx] = java.sql.Timestamp@col

or is there any trick/hint how to setup "auto-conversion"?

[Thrameos]

I am not aware of any automatic adapters being available in JayDeBeAPI. Though perhaps other JayDeBeAPI users may have alternative formulations that I am not aware off. The best I can suggest is looking at jpype.dbapi2 which supports adapters for execute many.

https://jpype.readthedocs.io/en/latest/dbapi2.html

In particular the defined type conversions are shown in

https://jpype.readthedocs.io/en/latest/dbapi2.html#jdbc-types

The key difference is that jpype.dbapi2 checks the type of argument to be inserted and then choses the adapter based in the requested type rather than always calling setObject and hoping the type gets converted. JayDeBeAPI is an older interface which was intended to work both with CPython and Jython. It is stable and well tested but its implementation of dbapi2 was rather limited, so I created an alternative driver which fit for the needs of my employer.

JPype has a separate dbapi2 implementation which works only for JPype (no Jython) and has more features for working with Python/Java type mixing, so it may be better for what you are attempting. (jpype.dbapi2 is also several times faster as it has close integration with the type conversion system and less Python code) Of course, as most code using JDBC with Python is aimed at the older JayDeBeAPI, fewer people have tested with jpype.dbapi2 so it may still not have what you need. If you have issues with it, please direct questions to the JPype issue tracker as this one is strictly for JayDeBeAPI.

[slokhorst]

For anyone wanting to try out jpype.dbapi2 as a replacement for jaydebeapi:

The equivalent for this:

conn = jaydebeapi.connect(jclassname, url, [user, password], jars)

is this:

jpype.startJVM(classpath=jars)
conn = jpype.dbapi2.connect(url, driver=jclassname, driver_args={'user': user, 'password': password})

[zdenop]

Thanks! jpype.dbapi2 works without conversion to java object. For reference here is working example:

import os
import datetime
import jpype
import jpype.dbapi2

# settings
ORACLE_JDBC_DRIVER = r"C:\oracle\ora122\client_x64\jdbc\lib\ojdbc8.jar"
JAVA_HOME = r"C:\oracle\ora122\client_x64\jdk"
ora_params = {'user': 'usr', 'password': 'pwd', 'host': '1.1.1.1', 'sid': 'db', 'port': '1521'}

f not os.environ.get("JAVA_HOME"):
    os.environ["JAVA_HOME"] = JAVA_HOME
if not jpype.isJVMStarted():
    try:
        jpype.startJVM(classpath=[ORACLE_JDBC_DRIVER], convertStrings=True)
    except OSError as error:
        print(error)

jclassname = "oracle.jdbc.driver.OracleDriver"
url = f"jdbc:oracle:thin:@{ora_params['host']}:{ora_params['port']}:{ora_params['sid']}"
args = {'user': ora_params['user'], 'password': ora_params['password']}

conn = jpype.dbapi2.connect(url, driver=jclassname, driver_args=args)
cursor = conn.cursor()
query_insert = "INSERT INTO fx_rates VALUES(:1, :2, :3, :4, :5)"
fx_data = [datetime.datetime(2021, 11, 1, 0, 0), "EUR", "USD", 1.1559, "D"]
cursor.execute(query_insert, fx_data )
cursor.close()
conn.close()

[pavanprabhu2000]

jpype.dbapi2._UnsupportedTypeError: no setter found for 'Decimal'

what should i do if i get this error?

i tried with

   import decimal
# imports and connection with both jpype.dbapi2/jaydebeapi 

  sql1 = ('INSERT INTO USAGEDIM_GLUE.ACCOUNTS (ACNT_ID, ACNT_NAME, ACNT_STATUS) VALUES(?, ?, ?;')
  for row in dataFrameSplitNotNUll.rdd.toLocalIterator():
      cursor.execute(sql1,[decimal.Decimal(row['ACNT_ID']), row['ACNT_NAME'], row['ACNT_STATUS']])