windows下python3 使用cx_Oracle，xlrd外掛程式進行excel資料清洗錄入

標籤：comment   processor   格式   name   fetch   迴圈   rip   today   www   

我們在做資料分析，清洗的過程中，很多時候會面對各種各樣的資料來源，要針對不同的資料來源進行清洗，入庫的工作。當然python這個語言，我比較喜歡，開發效率高，基本上怎麼寫都能運行，而且安裝配置簡單，基本上有網的環境pip install全部都搞定，沒網的話，把whl包copy過來一行命令也就解決了( windows下python3.5使用pip離線安裝whl包)。

本篇部落格就針對，在windows平台下使用python3（python2社區將要停止支援，使用3是大勢所趨），讀取xls，xlsx格式的資料進行清洗入庫做一個小例子。

初步商務程序

整個業務的流程十分簡單：兩個大的步驟

1. 讀取xlsx資料進行清洗2. cx_Oracle批量入庫

建表語句：

create table temp_table(importtime varchar2(128),carrier varchar2(32),);select * from temp_table

一個例子指令碼：

# -*- coding: utf-8 -*-import xlrdimport datetimeimport cx_Oracleimport timefrom itertools import isliceimport osos.environ[‘NLS_LANG‘]=‘SIMPLIFIED CHINESE_CHINA.ZHS16GBK‘LineName = [‘1號線‘,‘2號線‘]StationName = []########################連結資料庫相關######################################def getConnOracle(username,password,ip,service_name):    try:        conn = cx_Oracle.connect(username+‘/‘+password+‘@‘+ip+‘/‘+service_name)  # 串連資料庫        return conn    except Exception:        print(Exception)#######################進行資料批量插入#######################def insertOracle(conn,data,input_file_name):    sheetnumber = getSheetNumber(data)    cursor = conn.cursor()    try:        for x in range(0,sheetnumber):            templist = excel_table_byindex(input_file_name,0,x)            cursor.prepare(‘insert into temp_table(importtime ,carrier) values(:1,:2)‘)              # 使用cursor進行各種操作，templist數值需要和表temp_table對應             cursor.executemany(None,templist)        conn.commit()    except cx_Oracle.DatabaseError as msg:        print(msg)    finally:        cursor.close()        conn.close()###########################開啟excel檔案########################def openXLS(path):    try:        data = xlrd.open_workbook(path)        return data    except Exception:        print(Exception)def getSheetNumber(data):    sheet_num = len(data.sheets())    return sheet_num#######################一些資料清洗工作########################def getlineName(str):    for x in LineName:        if x in str:            return  xdef getStationName(str):    for x in StationName:        if x in str:            return x##########將excel中除去表頭的一個sheet讀出來,返回一個list#############def excel_table_byindex(path,colnameindex = 0,by_index = 0):    today = time.strftime(‘%Y%m%d‘, time.localtime(time.time()))    data = openXLS(path)    table = data.sheets()[by_index]    nrows = table.nrows    ncols = table.ncols    colnames = table.row_values(colnameindex)    list = []    for rownum in range(1,nrows):        row = table.row_values(rownum)        temp_lineName = getlineName(row[6])        temp_stationName = getStationName(row[6])        if row:            app = [today, str(row[1]), str(row[2]),temp_stationName,temp_lineName]            # for i in range(len(colnames)):            #     app[colnames[i]] = row[i]            list.append(app)    return list###################一個可以從檔案第二行開始讀的辦法#############def getAllStationName(path):    StationName_file = open(path, ‘r‘, encoding=‘utf-8‘)    #count = len(StationName_file.readlines())    for line in islice(StationName_file,1,None):        str_temp = line.strip(‘\n‘)        if str_temp not in LineName and str_temp !=‘----‘and str_temp!=‘‘:            StationName.append(str_temp)####################################################################def getStationNamefromexcel(path):    data = openXLS(path)    table = data.sheets()[0]    nrows = table.nrows    ncols = table.ncols    colnames = table.row_values(0)    list = []    for rownum in range(0,nrows):        row = table.row_values(rownum)[0]        if row:            list.append(row)    return list#################################################################def main():    username = ‘xx‘    password = ‘xx‘    ip = ‘192.168.1.1‘    service_name = ‘iop‘    #擷取資料庫連結    conn = getConnOracle(username,password,ip,service_name)    input_file_name = (r"E:\code\python\findS\subwayBase\xx.xlsx")    #output_file_name = input("Enter the output file name:")    getAllStationName(r"E:\code\python\findS\subwayBase\網站.txt")    begin = datetime.datetime.now()    insertOracle(conn,openXLS(input_file_name),input_file_name)    # x.fetchone()    # c.close()  # 關閉cursor    # conn.close()  # 關閉串連    end = datetime.datetime.now()    print((end - begin).seconds)if __name__ ==‘__main__‘:    main()

python3 windows下使用cx_Oracle操作oracle的報錯問題

報錯資訊如下：

Traceback (most recent call last):  File "E:/code/python/findS/findSubwayBase.py", line 134, in <module>    main()  File "E:/code/python/findS/findSubwayBase.py", line 124, in main    insertOracle(conn,openXLS(input_file_name),input_file_name)  File "E:/code/python/findS/findSubwayBase.py", line 32, in insertOracle    cursor.executemany(None,templist)UnicodeEncodeError: ‘ascii‘ codec can‘t encode characters in position 1-6: ordinal not in range(128)Process finished with exit code 1

在使用python3 的cx_Oracle操作oracle資料時候，不可避免的會遇到中文的編碼問題，當然，上網一搜全是python2的，解決方案是：

#在開頭加上import sysreload(sys)sys.setdefaultencoding( "utf-8" )

python3中的解決方案為：加上核心代碼

import osos.environ[‘NLS_LANG‘]=‘SIMPLIFIED CHINESE_CHINA.ZHS16GBK‘

就ok啦，其實就是設定一下用戶端編碼 ，參考：python編碼 OS.ENVIRON詳解

xlrd 操作excel

demo代碼：

#擷取一個工作表table = data.sheets()[0]          #通過索引順序擷取table = data.sheet_by_index(0) #通過索引順序擷取table = data.sheet_by_name(u‘Sheet1‘)#通過名稱擷取#擷取整行和整列的值（數組） 　　table.row_values(i)table.col_values(i)#擷取行數和列數　　nrows = table.nrowsncols = table.ncols#迴圈行列表資料for i in range(nrows ):      print table.row_values(i)#儲存格cell_A1 = table.cell(0,0).valuecell_C4 = table.cell(2,3).value#使用行列索引cell_A1 = table.row(0)[0].valuecell_A2 = table.col(1)[0].value#簡單的寫入row = 0col = 0# 類型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorctype = 1 value = ‘儲存格的值‘xf = 0 # 擴充的格式化table.put_cell(row, col, ctype, value, xf)table.cell(0,0)  #儲存格的值‘table.cell(0,0).value #儲存格的值‘

參考連結

[OS.ENVIRON詳解]: http://blog.csdn.net/junweifan/article/details/7615591[python編碼]:http://www.cnblogs.com/fkissx/p/5417363.html

再次強烈推薦，精通oracle+python系列：官方文檔
http://www.oracle.com/technetwork/cn/articles/dsl/mastering-oracle-python-1391323-zhs.html

windows下python3 使用cx_Oracle，xlrd外掛程式進行excel資料清洗錄入