cx_Oracle.DatabaseError: ORA-01036: 非法的变量名/编号

最新推荐文章于 2024-05-15 22:58:57 发布

rick_grace

最新推荐文章于 2024-05-15 22:58:57 发布

阅读量5.2k

点赞数 4

分类专栏： Python

本文链接：https://blog.csdn.net/jsut_rick/article/details/104612985

版权

Python 专栏收录该内容

23 篇文章

订阅专栏

#-*-coding:utf-8-*- 
#encoding=gbk

import os
import cx_Oracle
import xlrd
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

def orcl_insert():
    try:
        book1 = xlrd.open_workbook("import_file\MIS.xls")  #读取文件
    except:
        print("open excel file failed!")
    try:
        sheet1 = book1.sheet_by_name("省公司") #execl里面的worksheet1
    except:
        print("locate worksheet in excel failed!")
    
    #连接数据库
    conn = cx_Oracle.connect('用户名/密码@数据库ip:1521/库名')
    cursor = conn.cursor()

    delete1 = "delete from mis" 
    cursor.execute(delete1) #执行sql语句
    #line = cursor.fetchmany()

    conn.commit()#提交

    for i in range(1,sheet1.nrows): #第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
        company = sheet1.cell(i,0).value #取第i行第0列
        department = sheet1.cell(i,1).value#取第i行第1列，下面依次类推
        employee_number = sheet1.cell(i,2).value
        name = sheet1.cell(i,3).value
        permissions = sheet1.cell(i,4).value
        retain = sheet1.cell(i,5).value
        query_permission = sheet1.cell(i,6).value
        note = sheet1.cell(i,7).value

        #insert = "INSERT INTO mis(company,department,employee_number,name1,permissions,retain,query_permission,note) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"  #会提示cx_Oracle.DatabaseError: ORA-01036: 非法的变量名/编号
        insert = "INSERT INTO mis(company,department,employee_number,name,permissions,retain,query_permission,note) VALUES(:1,:2,:3,:4,:5,:6,:7,:8)"
        cursor.execute(insert)
        conn.commit()#提交

if __name__ == '__main__':
    orcl_insert()

遇到的问题就是提示错误：cx_Oracle.DatabaseError: ORA-01036: 非法的变量名/编号，这里需要是用的参数需要使用：占位符可以解决此问题，即

insert = "INSERT INTO mis(company,department,employee_number,name1,permissions,retain,query_permission,note) VALUES(:1,:2,:3,:4,:5,:6,:7,:8)"