程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式師世界 >> 編程語言 >  >> 更多編程語言 >> Python

How Python inserts date data using JDBC

編輯:Python
The phenomenon and background of the problem

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'

My solution ideas and tried methods
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

What I want to achieve

  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式師世界 All Rights Reserved