源碼及說明 import pymysql # 操作mysql的子產品
import openpyxl # xlsx格式對應的操作子產品
import time
import threadpool # 線程池子產品
import math
from datetime import datetime
successList = [] # 儲存每個線程成功的數目,用于統計
def readRow(rows):
conn = pymysql.connect(host="192.168.0.243",port=,user="root",passwd="root",db="mydb",charset="utf8")
cur = conn.cursor() # 擷取遊标
num =
for row in rows:
itemNo = row[].value if row[].value != None else
itemName = row[].value.replace("'","") if row[].value != None else ""
itemName = itemName.replace("\\", "|")
pym = row[].value.replace("'","") if row[].value != None else ""
pym = pym.replace("\\", "|")
itemSize = row[].value.replace("'","") if row[].value != None else ""
itemSize = itemSize.replace("\\", "|")
unitNo = row[].value.replace("'","") if row[].value != None else ""
unitNo = unitNo.replace("\\", "|")
productArea = row[].value.replace("'","") if row[].value != None else ""
productArea = productArea.replace("\\", "|")
args = (itemNo,itemName,pym,itemSize,unitNo,productArea)
print(args)
try:
sql = r'''
insert into bar_code_dcm1 (itemNo,itemName,pym,itemSize,unitNo,productArea)
values
(%s,'%s','%s','%s','%s','%s')
''' % args
# print(sql, "\r\n----------------------------------------------------------")
cur.execute(sql)
conn.commit()
num = num +
except Exception as e:
print(Exception, e,"SQL:%s " % sql)
else:
pass
finally:
pass
if num % == :
print("---目前線程已導入:", num," 條 %s" % time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) )
successList.append(num)
conn.close()
# time.sleep(1)
def excel2Mysql(excelFileName):
wb = openpyxl.load_workbook(excelFileName) # 打開excel檔案 ;16.5s
sheetList = wb.sheetnames# 擷取工作簿所有工作表名
for sheetName in sheetList: # 周遊,每一個工作簿
sheetObj = wb.[sheetName] #擷取工作簿對象
rows = sheetObj.iter_rows()
bigList = [] # 每個元素為一行excel表格内容
poolArgsList = [] # 每個元素為一萬行excel表格内容,傳遞給線程池的集合
for row in rows:
if len(row[].value) != : # 過濾掉不标準的條形碼資料(标準數字條形碼長度為13)
continue
else:
bigList.append(row)
cycle = math.ceil(len(bigList) / )
for index in range(,cycle+):
thisList = bigList[(index-)*:index*] # list切片
poolArgsList.append(thisList)
pools = threadpool.ThreadPool() # 初始化10個線程(不一定全用上,python會自己排程,最好是1w資料對應1個線程)
print("-*-資料讀取,組裝完畢-*-*-*開啟 %d 個線程-*-*-*- \n\r" % )
tasks = threadpool.makeRequests(readRow, poolArgsList) # 建立任務(處理函數,可疊代對象),每一個疊代元素即為處理函數的參數
[pools.putRequest(task) for task in tasks] # 線程池和任務都有了,将任務放入線程池中,執行
pools.wait()
wb.close()
if __name__ == '__main__':
startTime = datetime.now()
excelFileName = "C:/Users/xusanduo/Desktop/excelData/50-55w.xlsx"
print("[ %s ] [ 開始導入 %s " % ( time.strftime("%Y-%m-%d %H:%M:%S", time.localtime() ), excelFileName),"檔案 ]" )
excel2Mysql(excelFileName)
endTime = datetime.now()
print( "[ %s ] %s " % ( time.strftime("%Y-%m-%d %H:%M:%S", time.localtime() ) , "[ 導入完畢,總導入:" + str(sum(successList)) + "條 ]") , "[ 用時 %d 秒]" % (endTime-startTime).seconds )
- 關于源碼的說明:
- 你可能需要安裝pymysql ,openpyxl ,threadpool 子產品,如果你沒有的話,執行指令:pip install [module_name]
- 你可能需要修改讀取的檔案絕對路徑
- 你可能需要修改mysql的連接配接參數
- 如果你需要舉一反三的話,你可能需要修改readRow()函數裡面的資料組裝部分以及SQL部分