最近在學(xué)習(xí)python，又剛好有導(dǎo)出數(shù)據(jù)的需求，所以做了一下嘗試，在知乎看過一些對比，覺得xlwing性能應(yīng)該比較好，所以最后選擇了xlwings來操作excel

導(dǎo)出數(shù)據(jù)

這里碰到一個問題，google查不到方案，最后選擇了一個繞過的小技巧。

問題是：當(dāng)數(shù)據(jù)庫字段類型為字符時，且存的是"001301"類似于此類的編碼，在用xlwings導(dǎo)出到excel時，前面的00會丟失。

解決辦法：是針對該字段，查詢語句前面在該字段加" ' "， 加了單引號后，excel自動默認(rèn)該列為字符，這樣前面的00就不會丟失

導(dǎo)出腳本：

ora-2-exl.py

# -*- coding: utf-8 -*-
import ora_conn
import sys
import xlwings as xw
import cx_Oracle
import datetime

def ora_to_excel():
    with open(sys.argv[1],'rb') as f:
        sql = f.read()
    cursor = ora_conn.connect.cursor()
    cursor.execute(sql)
    app = xw.App(visible=False)
    wb = xw.Book()
    sht = wb.sheets[0]
    #處理表頭
    i = 0
    start = datetime.datetime.now()
    for col in cursor.description:
        i += 1
        sht.range((1,i)).value = col[0]
    
    #寫入數(shù)據(jù)
    offset = 2
    while True:
        results = cursor.fetchmany(30000)
        if not results:
            break
        #對于數(shù)據(jù)庫為字符串的'001',需要在查詢的sql語句加上',否則前面的00在excel會丟失
        sht.range('A'+ str(offset)).options().value = results
        offset = offset + len(results)
    wb.save(sys.argv[2])
    wb.close()
    app.quit()
    cursor.close()
    ora_conn.connect.close()
    print(datetime.datetime.now() - start)
if __name__ == '__main__':
    ora_to_excel()

導(dǎo)入數(shù)據(jù)

這里碰到一個問題就是日期格式的問題，excel的日期格式最好是yyyy/mm/dd hh:mi:ss

exl-2-ora.py

# -*- coding: utf-8 -*-
import xlwings as xw
import ora_conn
import datetime
import sys

def exl_to_ora(table_name,file_name):
    app = xw.App(visible=False)
    wb = app.books.open(file_name)
    sht = wb.sheets[0]
    data = sht.range('A1').expand().value
    rows = len(data) - 1
    cols = data[0]
    
    cursor = ora_conn.connect.cursor()
    #處理插入的語句
    cols_str = '('
    for x in cols:
        cols_str = cols_str + str(x) + ','
    cols_str = cols_str.strip(',') + ')'
    
    values_str = ' values ('
    for i in range(len(cols)):
        values_str = values_str + ':' + str(i+1) + ','
    values_str = values_str.strip(',') + ')'
    
    offset = 1
    #print("insert into " + table_name + cols_str + values_str)
    try:
        start = datetime.datetime.now()
        while(offset < rows):
            cursor.executemany("insert into " + table_name + cols_str + values_str,data[offset:offset+20000])
            offset += 20000
            ora_conn.connect.commit()
            #print('commit data rows:'+ str(offset))
        
    finally:
        wb.close()
        app.quit()
        cursor.close()
        ora_conn.connect.close()
    print(datetime.datetime.now() - start )
    
if __name__ == '__main__':
    exl_to_ora(sys.argv[1],sys.argv[2])

ora_conn.py

import cx_Oracle
dbs = {
    'dev':'use1/dev@10.xx.xx.xx/dev',
    'test':'use1/test@10.xx.xx.xx/test',
    'uat':'use1/uat@10.xx.xx.xx/uat'
}
connect = cx_Oracle.connect(dbs['dev'])
print('connect success')