注册 登录
编程论坛 Python论坛

python 通过jdbc 实现A表查询结果写入B表

ccqz 发布于 2022-05-11 10:45, 976 次点击
程序代码:

import sys
import os
import jaydebeapi

url = 'jdbc:oracle:thin:@8.142.180.78:1521:MAEASDB'
driver = 'oracle.jdbc.driver.OracleDriver'
userFrom = 'test'
passwordFrom = 'test'
jarFile = 'D:/Tools/JDBC/ojdbc8.jar'
connFrom = jaydebeapi.connect(jclassname=driver,
                          url=url,
                          driver_args=[userFrom, passwordFrom],
                          jars=jarFile)
cursFrom = connFrom.cursor()
userTo = 'MAEAS'
passwordTo = 'MAEAS'
jarFile = 'D:/Tools/JDBC/ojdbc8.jar'
connTo = jaydebeapi.connect(jclassname=driver,
                          url=url,
                          driver_args=[userTo, passwordTo],
                          jars=jarFile)
cursTo = connTo.cursor()
sqlStr = 'select FREGIONCODE, FCATEGORYNUMBER, FCATEGORYNAME, FCATEGORYSUBCODE, FCATEGORYSUBNAME, FPAYITEMNUMBER, FPAYITEMNAME, FPAYITETYPE, FCATEGORYID, FCATEGORYSUBID from CT_CUS_PAYITEMSCATE_INIT order by FCATEGORYNUMBER,FCATEGORYSUBCODE,FPAYITEMNUMBER'
cursFrom.execute(sqlStr)
resultFrom = cursFrom.fetchall()
cursTo = connTo.cursor()
   #print (i)
   sqlInsert="insert into CT_CUS_PAYITEMSCATE_INIT (FREGIONCODE, FCATEGORYNUMBER, FCATEGORYNAME, FCATEGORYSUBCODE, FCATEGORYSUBNAME, FPAYITEMNUMBER, FPAYITEMNAME, FPAYITETYPE, FCATEGORYID, FCATEGORYSUBID) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') "
   cursTo.executemany(sqlInsert,resultFrom )


这种方式执行时提示
File D:\Programs\Python\Python\lib\site-packages\jaydebeapi\__init__.py:523, in Cursor._set_stmt_parms(self, prep_stmt, parameters)
    520 def _set_stmt_parms(self, prep_stmt, parameters):
    521     for i in range(len(parameters)):
    522         # print (i, parameters[i], type(parameters[i]))
--> 523         prep_stmt.setObject(i + 1, parameters[i])
java.sql.SQLException: java.sql.SQLException: 无效的列索引
如果改成单行循环插入,这种就不报错。应该怎么改造才可以
1 回复
#2
sssooosss2022-05-11 20:01
共同进步
1