python 使用jdbc插入數據庫,日期類型應該怎麼進行變化才能被識別
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(): # 遍歷要插入的數據DataFrame row = rows[1:] # 獲取需要插入的數據。注意:rows[0]是索引。 insert_data.append(row) print(insert_data) cursor.executemany(insert_sql, insert_data) conn.commit()cursor.close()conn.close()```python
###### 運行結果及報錯內容 ```sql INSERT INTO CTRLUNITTEST (FID,FNUMBER,FNAME_L2,FCREATETIME) VALUES(:1,:2,:3,:4)[('CfMAAAAACNPM567U', '1200', '天津', 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')"
這種方式前面加上to_char函數後被視為字符了,不是函數
如果df_ParentData[‘FCREATETIME’] = datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’)
變成插入語句為
INSERT INTO CTRLUNITTEST (FID,FNUMBER,FNAME_L2,FCREATETIME) VALUES(:1,:2,:3,:4)[(‘CfMAAAAACNPM567U’, ‘1200’, ‘天津’, ‘2022-06-16 12:27:24’)]
也是不符合規則