#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('xx/xxx@cd')
        cursor = conn.cursor()

        #以数字开头的字段加个字符a

        self.title = [i if i[0].isdigit() == False else 'a'+i for i in self.title ]
        
        fields = [i+' varchar2(800)' 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)
            print type(reader)
            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 = '附件8：清单.xlsx'
    table_name= 'ygl_temp0624_8'

    #获取文件类型
    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()