-
Notifications
You must be signed in to change notification settings - Fork 145
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
How import datetime object to db/oracle with jaydebeapi? #204
Comments
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
The |
For more details on casting or other useful tips on using JPype i recommend starting with https://jpype.readthedocs.io/en/latest/quickguide.html |
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 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"? |
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 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 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. |
For anyone wanting to try out 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}) |
Thanks! 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() |
jpype.dbapi2._UnsupportedTypeError: no setter found for 'Decimal' what should i do if i get this error? i tried with
|
I try to insert datatime object to oracle db but I get errors:
Here is my testing case:
Any idea how to solve it?
The text was updated successfully, but these errors were encountered: