python Use jdbc Insert database , How to change the date type to be recognized
import osimport datetimeimport jpypeimport jpype.importsfrom jpype.types import *import jpype.dbapi2import pandas as pd# settingsORACLE_JDBC_DRIVER = r"D:\Tools\JDBC\ojdbc8.jar"JAVA_HOME = r"D:\kingdee850\eas\clientjdk"ora_params = { 'user': 'test', 'password': 'test', 'host': '8.142.180.78', 'sid': 'MAEASDB', 'port': '1521'}if not os.environ.get("JAVA_HOME"): os.environ["JAVA_HOME"] = JAVA_HOMEif 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()sqlStr = "SELECT FID,FNumber,FName_l2,fcreatetime FROM t_org_ctrlunit order by fnumber"cursor.execute(sqlStr)a = cursor.fetchall()df_ParentData = pd.DataFrame( a, columns=['FID', 'FNUMBER', 'FNAME_L2', 'FCREATETIME'])# print(df_ParentData)# df_ParentData['FCREATETIME'] = df_ParentData['FCREATETIME'].astype(# str) + "', 'yyyy-mm-dd hh24:mi:ss')"# df_ParentData['FCREATETIME'] = datetime.datetime.now().strftime(# '%Y-%m-%d %H:%M:%S')insert_sql = 'INSERT INTO CTRLUNITTEST (FID,FNUMBER,FNAME_L2,FCREATETIME) VALUES(:1,:2,:3,:4)'print('datafram insert\n', insert_sql)insert_data = []for rows in df_ParentData.itertuples(): # Traverse the data to insert DataFrame row = rows[1:] # Get the data to be inserted . Be careful :rows[0] It's the index . insert_data.append(row) print(insert_data) cursor.executemany(insert_sql, insert_data) conn.commit()cursor.close()conn.close()```python
###### Operation results and error reporting contents ```sql INSERT INTO CTRLUNITTEST (FID,FNUMBER,FNAME_L2,FCREATETIME) VALUES(:1,:2,:3,:4)[('CfMAAAAACNPM567U', '1200', ' tianjin ', Timestamp('2021-12-22 11:01:58'))]
jpype.dbapi2._UnsupportedTypeError: no setter found for 'Timestamp'
df_ParentData['FCREATETIME'] = df_ParentData['FCREATETIME'].astype( str) + "', 'yyyy-mm-dd hh24:mi:ss')"
This method is preceded by to_char Function is treated as a character , It's not a function
If df_ParentData[‘FCREATETIME’] = datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’)
Become insert statement as
INSERT INTO CTRLUNITTEST (FID,FNUMBER,FNAME_L2,FCREATETIME) VALUES(:1,:2,:3,:4)[(‘CfMAAAAACNPM567U’, ‘1200’, ‘ tianjin ’, ‘2022-06-16 12:27:24’)]
It is also against the rules