[DJANGO] excel十幾萬行資料快速匯入資料庫研究，django十幾萬行

[DJANGO] excel十幾萬行資料快速匯入資料庫研究，django十幾萬行

先貼原來的匯入資料代碼:

8 import os os.environ.setdefault("DJANGO_SETTINGS_MODULE", "www.settings") '''Django 版本大於等於1.7的時候，需要加上下面兩句import djangodjango.setup()否則會拋出錯誤 django.core.exceptions.AppRegistryNotReady: Models aren't loaded yet.'''import djangoif django.VERSION >= (1, 7):#自動判斷版本    django.setup()from arrears.models import D072Qfimport xlrd #excel讀工具from datetime import datetimefrom xlrd import xldate_as_tupleimport timeimport random  time1 = time.time()#data= xlrd.open_workbook('11.xlsx') 開啟檔案with xlrd.open_workbook('11.xlsx') as data:    print u"讀取檔案結束,開始匯入!"    time2 = time.time()    table = data.sheet_by_index(0) #擷取工作表    time3 = time.time()    n=1    x = y = z = 0    WorkList = []    for line in range(n,table.nrows):#nrows = table.nrows #行數 ncols = table.ncols #列數 print sh.row_values(rownum)        row = table.row_values(line)        if row: #查看行值是否為空白            for i in [0,1,2,4,28,30,32]:                if type(row[i]) == float:                    row[i] = int(row[i])            if D072Qf.objects.filter(acct_month = row[0],serv_id=row[1]).exists():#判斷該行值是否在資料庫中重複                x = x + 1 #重複值計數            else:                WorkList.append(D072Qf(acct_month=row[0],serv_id=row[1],acc_nbr=row[2],user_name=row[3],acct_code=row[4],                                   acct_name=row[5],product_name=row[6],current_charge=row[7],one_charge=row[8],                                   two_charge=row[9],three_charge=row[10],four_charge=row[11],five_charge=row[12],                                   six_charge=row[13],seven_charge=row[14],eight_charge=row[15],nine_charge=row[16],                                   ten_charge=row[17],eleven_charge=row[18],twelve_charge=row[19],oneyear_charge=row[20],                                   threeyear_charge=row[21],upthreeyear_charge=row[22],all_qf=row[23],morethree_qf=row[24],                                   aging=row[25],serv_state_name=row[26],mkt_chnl_name=row[27],mkt_chnl_id=row[28],                                   mkt_region_name=row[29],mkt_region_id=row[30],mkt_grid_name=row[31],mkt_grid_id=row[32],                                   prod_addr=row[33]))                           y = y + 1 #非重複計數                        else:             z = z + 1     #空行值計數        n = n + 1        if n % 9999 == 0:            D072Qf.objects.bulk_create(WorkList)                                 WorkList = []            time.sleep(random.random()) #讓Cpu隨機休息0 <= n < 1.0 s            print "匯入成功一次!"            print '資料匯入成功,匯入'+str(y)+'條,重複'+str(x)+'條,有'+str(z)+'行為空白!'                 time4 = time.time()            print "讀取檔案耗時"+str(time2-time1)+"秒,匯入資料耗時"+str(time4-time3)+"秒!"

這條代碼目前未全部將十幾萬行資料全部匯入資料庫中,只花了1個小時把5萬行資料匯入其中後,後面越來越慢,主要慢在excel表到了7萬行資料左右後,讀取excel中資料很慢了,總體來說影響匯入速度有幾個原因:

1、一直以來採用xlrd匯入xls格式檔案,如果檔案有十幾萬行,只是讀取檔案就會花200秒,若換成csv則幾乎不花時間

2、代碼中這行語句也會影響速度,特別當資料庫中資料很大時:if D072Qf.objects.filter(acct_month = row[0],serv_id=row[1]).exists():#判斷該行值是否在資料庫中重複

3、若一次性將字典添加十幾萬行資料,就windows的cpu而已是遭受不住的!所以建議1萬條資料匯入一次後,清空列表

改善後的代碼:

最佳化部分:採用csv格式;取消掉檢查重複資料語句;每5萬匯入一次資料

#coding:utf-8 import os os.environ.setdefault("DJANGO_SETTINGS_MODULE", "www.settings") '''Django 版本大於等於1.7的時候，需要加上下面兩句import djangodjango.setup()否則會拋出錯誤 django.core.exceptions.AppRegistryNotReady: Models aren't loaded yet.'''import djangoif django.VERSION >= (1, 7):#自動判斷版本    django.setup()from arrears.models import D072Qf import timeimport randomtime1 = time.time()f = open('11.csv')print u"讀取檔案結束,開始匯入!"time2 = time.time()WorkList = []next(f) #將檔案標記移到下一行y = 0n = 1for line in f:    row = line.replace('"','') #將字典中的"替換空    row = row.split(';') #按;對字串進行切片    y = y + 1    WorkList.append(D072Qf(acct_month=row[0],serv_id=row[1],acc_nbr=row[2],user_name=row[3],acct_code=row[4],                                   acct_name=row[5],product_name=row[6],current_charge=row[7],one_charge=row[8],                                   two_charge=row[9],three_charge=row[10],four_charge=row[11],five_charge=row[12],                                   six_charge=row[13],seven_charge=row[14],eight_charge=row[15],nine_charge=row[16],                                   ten_charge=row[17],eleven_charge=row[18],twelve_charge=row[19],oneyear_charge=row[20],                                   threeyear_charge=row[21],upthreeyear_charge=row[22],all_qf=row[23],morethree_qf=row[24],                                   aging=row[25],serv_state_name=row[26],mkt_chnl_name=row[27],mkt_chnl_id=row[28],                                   mkt_region_name=row[29],mkt_region_id=row[30],mkt_grid_name=row[31],mkt_grid_id=row[32],                                   prod_addr=row[33]))    n = n + 1    if n%50000==0:        print n        D072Qf.objects.bulk_create(WorkList)        WorkList = []        time3 = time.time()        print "讀取檔案耗時"+str(time2-time1)+"秒,匯入資料耗時"+str(time3-time2)+"秒!"time3 = time.time()print nD072Qf.objects.bulk_create(WorkList)print "讀取檔案耗時"+str(time2-time1)+"秒,匯入資料耗時"+str(time3-time2)+"秒!"WorkList = []print "成功匯入資料"+str(y)+"條"f.close() 

結果讓人大吃一驚!!!,只耗時73秒

Python 2.7.10 (default, May 23 2015, 09:40:32) [MSC v.1500 32 bit (Intel)] on win32Type "copyright", "credits" or "license()" for more information.>>> ================================ RESTART ================================>>> 讀取檔案結束,開始匯入!50000讀取檔案耗時0.0秒,匯入資料耗時34.3279998302秒!100000讀取檔案耗時0.0秒,匯入資料耗時67.3599998951秒!138400讀取檔案耗時0.0秒,匯入資料耗時73.4379999638秒!成功匯入資料138399條>>>