csv、excel导入oracle

hcsv导入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()

来自为知笔记(Wiz)

csv、excel导入oracle

附件列表