#从excel读取数据写入mysql
def excel_to_mysql (filename):
ip = ' 133.0.186.3 '
Port = 11521
SID = ' Billdb '
Dsn_tns = ORA.MAKEDSN (IP, port, SID)
conn = Ora.connect (' username ', ' password ', dsn_tns)
cur = conn.cursor () #连接数据库
Book = Xlrd.open_workbook (filename)
Sheet = book.sheet_by_name (' Sheet1 ')
rows = Sheet.nrows #获取行数
for R in range (1,rows): #将标题之外的其他行写入数据库
R_values = Sheet.row_values (r)
Print ('----')
Print (r_values)
#sql = ' INSERT into STU2 values (%s,%s,%s,%s,%s,%s,%s,%s) '
sql = ' INSERT INTO STU2 values (: 1,:2,:3,:4,:5,:6,:7,:8) '
#报错cx_Oracle. databaseerror:ora-01036: Illegal variable name/number, using placeholder: Resolving
data = Cur.execute (sql,r_values) #将每一行插入sql
Conn.commit () #插入所有数据后提交
Cur.close ()
Conn.close ()
Excel_to_mysql (' suspected non-real name. xlsx ')
The problem is the prompt error: cx_oracle.databaseerror:ora-01036: Illegal variable name/number, here need to use the parameters required: placeholder can solve this problem,
March 28 "Reading input database from Excel"