csv、excel导入oracle
#coding:gbk
import csv
import cx_Oracle
fields = []
data = []
table_name = ‘ygl_test3‘
file_name = ‘清单.csv‘
with open(file_name, ‘rb‘) as f:
reader = csv.reader(f)
contents = [i for i in reader]
title = contents[0]
data = contents[1:]
conn = cx_Oracle.connect(‘user/passwd@cd‘)
cursor = conn.cursor()
#生成create table语句中字段名字符串
for i in title:
fields.append(i+‘ varchar2(200)‘)
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(len(title)+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()
#coding:gbk
import cx_Oracle
import csv
import xlrd
class ImportOracle(object):
#工厂模式初始化导入函数inoracle,用作读取文件数据
def inoracle(self):
pass
#oracle 连接和写入函数
def ConnOracle(self):
conn = cx_Oracle.connect(‘user/passwd@cd‘)
cursor = conn.cursor()
#以数字开头的字段加个字符a
self.title = [i if i[0].isdigit() == False else ‘a‘+i for i in self.title ]
fields = [i+‘ varchar2(200)‘ for i in self.title]
fields_str = ‘, ‘.join(fields)
sql = ‘create table %s (%s)‘ % (self.table_name, fields_str)
print sql
cursor.execute(sql)
a = [‘:%s‘ %i for i in range(len(self.title)+1)]
value= ‘,‘.join(a[1:])
sql = ‘insert into %s values(%s)‘ %(self.table_name, value)
print sql
cursor.prepare(sql)
cursor.executemany(None, self.data)
cursor.close()
conn.commit()
conn.close()
#ImportOracle的子类,用作导入csv文件的类
class ImportOracleCsv(ImportOracle):
#重构父类的inoracle函数,用作从csv读取数据,返回标题和内容
def inoracle(self):
with open(self.filename, ‘rb‘) as f:
reader = csv.reader(f)
contents = [i for i in reader]
title = contents[0]
data = contents[1:]
return (title, data)
#ImportOracle的子类,用作导入excel文件的类
class ImportOracleExcel(ImportOracle):
#重构父类的inoracle函数,用作从EXCEL文件读取数据,返回标题和内容
def inoracle(self):
wb = xlrd.open_workbook(self.filename)
sheet1 = wb.sheet_by_index(0)
title = sheet1.row_values(0)
data = [sheet1.row_values(row) for row in range(1, sheet1.nrows)]
return (title, data)
#ImportOracle的子类,在文件类型错的情况返回
class ImportError(ImportOracle):
def inoracle(self):
print ‘Undefine file type‘
return 0
#工厂选择类,csv文件返回ImportOracleCsv类,EXCEL文件返回ImportOracleExcel类
class ChooseFactory(object):
choose = {}
choose[‘csv‘] = ImportOracleCsv()
choose[‘xlsx‘] = ImportOracleExcel()
choose[‘xls‘] = ImportOracleExcel()
def choosefile(self, ch):
if ch in self.choose:
op = self.choose[ch]
else:
op = ImportError()
return op
if __name__ =="__main__":
#定义文件名和数据库表名
file_name = ‘清单.xlsx‘
table_name= ‘ygl_test‘
#获取文件类型
op = file_name.split(‘.‘)[-1]
factory = ChooseFactory()
#选择工厂类处理,cal被赋值为处理的具体类
cal = factory.choosefile(op)
#设置类属性(文件名)
cal.filename = file_name
#根据函数返回值设置类属性(标题,内容)
(cal.title, cal.data) = cal.inoracle()
#设置类属性(表名)
cal.table_name = table_name
#调用类的导入数据库函数
cal.ConnOracle()
附件列表
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。