#-*-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)"