#coding:gbk
import xlrd
import cx_Oracle
fields = []
data = []
table_name = 'ygl_test1'
wb = xlrd.open_workbook("清单.xlsx")
sheet1 = wb.sheet_by_index(0)
#取第一行做数库字段名
title = sheet1.row_values(0)
#取第二行开始做数据
for row in range(1, sheet1.nrows):
rowvalue = sheet1.row_values(row)
data.append(rowvalue)
#联接数据库
conn = cx_Oracle.connect('user/user123@user')
cursor = conn.cursor()
#生成create table语句中字段名字符串
for i in title:
fields.append(i+' varchar2(40)')
fields_str = ', '.join(fields)
sql = 'create table %s (%s)' % (table_name, fields_str)
print sql
#根据excel列名创建数据表的字段
cursor.execute(sql)
#生成executemany语句中的:1,:2字段参数格式
a = [':%s' %i for i in range(sheet1.ncols+1)]
value= ','.join(a[1:])
sql = 'insert into %s values(%s)' %(table_name, value)
print sql
#把所有字段插入数据值
cursor.prepare(sql)
cursor.executemany(None, data)
cursor.close()
conn.commit()
conn.close()