EXCEL文件导入oracle

1. #coding:gbk
3. import xlrd
4. import cx_Oracle
6. fields = []
7. data = []
8. table_name = 'ygl_test1'
10. wb = xlrd.open_workbook("清单.xlsx")
11. sheet1 = wb.sheet_by_index(0)
13. #取第一行做数库字段名
14. title = sheet1.row_values(0)
16. #取第二行开始做数据
17. for row in range(1, sheet1.nrows):
18. rowvalue = sheet1.row_values(row)
19. data.append(rowvalue)
21. #联接数据库
22. conn = cx_Oracle.connect('user/user123@user')
23. cursor = conn.cursor()
25. #生成create table语句中字段名字符串
26. for i in title:
27. fields.append(i+' varchar2(40)')
28. fields_str = ', '.join(fields)
29. sql = 'create table %s (%s)' % (table_name, fields_str)
31. print sql
32. #根据excel列名创建数据表的字段
33. cursor.execute(sql)
35. #生成executemany语句中的:1,:2字段参数格式
36. a = [':%s' %i for i in range(sheet1.ncols+1)]
37. value= ','.join(a[1:])
38. sql = 'insert into %s values(%s)' %(table_name, value)
40. print sql
41. #把所有字段插入数据值
42. cursor.prepare(sql)
43. cursor.executemany(None, data)
45. cursor.close()
46. conn.commit()
47. conn.close()

来自为知笔记(Wiz)

附件列表

 

转载于:https://www.cnblogs.com/highroom/p/d4d07bb9b29a9e3708b919f72a933ed4.html