csv、excel导入oracle

hcsv导入oracle
  1. #coding:gbk
  2. import csv
  3. import cx_Oracle
  4. fields = []
  5. data = []
  6. table_name = ‘ygl_test3‘
  7. file_name = ‘清单.csv‘
  8. with open(file_name, ‘rb‘) as f:
  9. reader = csv.reader(f)
  10. contents = [i for i in reader]
  11. title = contents[0]
  12. data = contents[1:]
  13. conn = cx_Oracle.connect(‘user/passwd@cd‘)
  14. cursor = conn.cursor()
  15. #生成create table语句中字段名字符串
  16. for i in title:
  17. fields.append(i+‘ varchar2(200)‘)
  18. fields_str = ‘, ‘.join(fields)
  19. sql = ‘create table %s (%s)‘ % (table_name, fields_str)
  20. print sql
  21. #根据excel列名创建数据表的字段
  22. cursor.execute(sql)
  23. #生成executemany语句中的:1,:2字段参数格式
  24. a = [‘:%s‘ %i for i in range(len(title)+1)]
  25. value= ‘,‘.join(a[1:])
  26. sql = ‘insert into %s values(%s)‘ %(table_name, value)
  27. print sql
  28. #把所有字段插入数据值
  29. cursor.prepare(sql)
  30. cursor.executemany(None, data)
  31. cursor.close()
  32. conn.commit()
  33. conn.close()



升级版:
  1. #coding:gbk
  2. import cx_Oracle
  3. import csv
  4. import xlrd
  5. class ImportOracle(object):
  6. #工厂模式初始化导入函数inoracle,用作读取文件数据
  7. def inoracle(self):
  8. pass
  9. #oracle 连接和写入函数
  10. def ConnOracle(self):
  11. conn = cx_Oracle.connect(‘user/passwd@cd‘)
  12. cursor = conn.cursor()
  13. #以数字开头的字段加个字符a
  14. self.title = [i if i[0].isdigit() == False else ‘a‘+i for i in self.title ]
  15. fields = [i+‘ varchar2(200)‘ for i in self.title]
  16. fields_str = ‘, ‘.join(fields)
  17. sql = ‘create table %s (%s)‘ % (self.table_name, fields_str)
  18. print sql
  19. cursor.execute(sql)
  20. a = [‘:%s‘ %i for i in range(len(self.title)+1)]
  21. value= ‘,‘.join(a[1:])
  22. sql = ‘insert into %s values(%s)‘ %(self.table_name, value)
  23. print sql
  24. cursor.prepare(sql)
  25. cursor.executemany(None, self.data)
  26. cursor.close()
  27. conn.commit()
  28. conn.close()
  29. #ImportOracle的子类,用作导入csv文件的类
  30. class ImportOracleCsv(ImportOracle):
  31. #重构父类的inoracle函数,用作从csv读取数据,返回标题和内容
  32. def inoracle(self):
  33. with open(self.filename, ‘rb‘) as f:
  34. reader = csv.reader(f)
  35. contents = [i for i in reader]
  36. title = contents[0]
  37. data = contents[1:]
  38. return (title, data)
  39. #ImportOracle的子类,用作导入excel文件的类
  40. class ImportOracleExcel(ImportOracle):
  41. #重构父类的inoracle函数,用作从EXCEL文件读取数据,返回标题和内容
  42. def inoracle(self):
  43. wb = xlrd.open_workbook(self.filename)
  44. sheet1 = wb.sheet_by_index(0)
  45. title = sheet1.row_values(0)
  46. data = [sheet1.row_values(row) for row in range(1, sheet1.nrows)]
  47. return (title, data)
  48. #ImportOracle的子类,在文件类型错的情况返回
  49. class ImportError(ImportOracle):
  50. def inoracle(self):
  51. print ‘Undefine file type‘
  52. return 0
  53. #工厂选择类,csv文件返回ImportOracleCsv类,EXCEL文件返回ImportOracleExcel类
  54. class ChooseFactory(object):
  55. choose = {}
  56. choose[‘csv‘] = ImportOracleCsv()
  57. choose[‘xlsx‘] = ImportOracleExcel()
  58. choose[‘xls‘] = ImportOracleExcel()
  59. def choosefile(self, ch):
  60. if ch in self.choose:
  61. op = self.choose[ch]
  62. else:
  63. op = ImportError()
  64. return op
  65. if __name__ =="__main__":
  66. #定义文件名和数据库表名
  67. file_name = ‘清单.xlsx‘
  68. table_name= ‘ygl_test‘
  69. #获取文件类型
  70. op = file_name.split(‘.‘)[-1]
  71. factory = ChooseFactory()
  72. #选择工厂类处理,cal被赋值为处理的具体类
  73. cal = factory.choosefile(op)
  74. #设置类属性(文件名)
  75. cal.filename = file_name
  76. #根据函数返回值设置类属性(标题,内容)
  77. (cal.title, cal.data) = cal.inoracle()
  78. #设置类属性(表名)
  79. cal.table_name = table_name
  80. #调用类的导入数据库函数
  81. cal.ConnOracle()

技术分享




附件列表

     

    郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。