近期批量數(shù)據(jù)對賬，經(jīng)常收到客戶提供的excel要求對數(shù)，每次通過excel處理非常不方便在扰，所以想利用python腳本，來幫忙處理雷客，對于大批量數(shù)據(jù)芒珠，從excel導(dǎo)入到mysql數(shù)據(jù)庫處理非常方便，但從文件導(dǎo)入到excel步驟也很多佛纫，比較麻煩妓局，想偷懶，做到拿到excel直接建表入庫數(shù)據(jù)一條龍搞定呈宇，想起來就做好爬，趁周末1個小時左右搞定腳本，基本可以滿足需求甥啄，分享記錄下來存炮。
需求：選擇入庫的excel數(shù)據(jù)文件，指定表名蜈漓，自動建表并將數(shù)據(jù)入庫到表中穆桂。
實現(xiàn)：
1、利用python的xlrd庫實現(xiàn)excel文件的操作融虽，mysql的MySQLdb鏈接數(shù)據(jù)庫操作享完；
2、以excel數(shù)據(jù)第1條表頭的字段為庫表名有额，自動創(chuàng)建庫表般又，入庫第1條之后的所有數(shù)據(jù)彼绷，為提高性能，批量插入茴迁。
代碼：
import MySQLdb
import xlrd
import re
import time
import datetime
#這個函數(shù)用來判斷表是否存在
def table_exists(con,table_name):
? ? sql= "show tables;"
? ? con.execute(sql)
? ? tables= [con.fetchall()]
? ? table_list= re.findall('(\'.*?\')',str(tables))
? ? table_list= [re.sub("'",'',each)for eachin table_list]
? ? if table_name in table_list:
? ? ? ? return 1
? ? else:
? ? ? ? return 0
# 創(chuàng)建庫表
def create_table(cur,sheet,table_name):
? ? #判斷表是否存在
? ? if (table_exists(cur, table_name)!= 1):
? ? ? ? print("1--庫表{}不存在寄悯，新建庫表!".format(table_name))
? ? else:
? ? ? ? print("1--庫表{}已存在，刪除重建!".format(table_name))
? ? ? ? cur.execute("drop table if exists {}".format(table_name))
? ? ? ? #exit()
? ? create_table_sql= "create table {}( ".format(table_name)
? ? insert_sql= 'insert into {} ('.format(table_name)
? ? # 拼裝create table語句堕义，取excel第一行做庫表的字段名稱
? ? first_row= sheet.row(0)
? ? for iin range(0, len(first_row)):
? ? ? ? # 剔除表頭中特殊字符猜旬，影響建表語句執(zhí)行
? ? ? ? column_name= first_row[i].value.replace('(', '').replace(')', '').replace('-', '')
? ? ? ? if i== len(first_row)- 1:
? ? ? ? ? ? create_table_sql= create_table_sql+ "{} varchar(255) )".format(column_name)
? ? ? ? ? ? insert_sql= insert_sql+ '{} )'.format(column_name)
? ? ? ? else:
? ? ? ? ? ? create_table_sql= create_table_sql+ "{} varchar(255) ,".format(column_name)
? ? ? ? ? ? insert_sql= insert_sql+ '{} ,'.format(column_name)
? ? cur.execute(create_table_sql)
? ? print('2--表已創(chuàng)建成功!')
? ? return insert_sql+" values"
# 批量插入數(shù)據(jù)到庫表中
def insert_table(cur, sheet, table_name,insert_sql):
? ? print('3--待入庫記錄總數(shù)：',sheet.nrows-1)
? ? begintime= datetime.datetime.now()
? ? # 取列表數(shù)據(jù)，從第1行開始倦卖，0行作為表字段名
? ? sql_values= ' '
? ? for iin range(1,sheet.nrows):
? ? ? ? sql_values+=? '( '
? ? ? ? #得到行數(shù)據(jù)
? ? ? ? row_values= sheet.row_values(i)
? ? ? ? for jin range(len(row_values)):
? ? ? ? ? ? ctype= sheet.cell(i,j).ctype
? ? ? ? ? ? value= sheet.cell(i,j).value
? ? ? ? ? ? #類型如果為數(shù)字洒擦，則變?yōu)檎危駝t會有.0情況
? ? ? ? ? ? if ctype==2 and value% 1==0:
? ? ? ? ? ? ? ? value= int(value)
? ? ? ? ? ? if j== len(row_values)-1:
? ? ? ? ? ? ? ? #str += row_values[j]
? ? ? ? ? ? ? ? sql_values+= "'"+str(value)+"'"+')'
? ? ? ? ? ? else:
? ? ? ? ? ? ? ? sql_values+= "'"+str(value)+"'"+','
? ? ? ? if i== sheet.nrows-1:
? ? ? ? ? ? sql_values+= ''
? ? ? ? else:
? ? ? ? ? ? sql_values+= ','
? ? # 獲取拼裝后的批量sql
? ? exec_sql= insert_sql + sql_values
? ? cur.execute(exec_sql)
? ? endtime= datetime.datetime.now()
? ? print('4--入庫成功記錄總數(shù)：',sheet.nrows-1,',消耗時間:',(endtime-begintime).seconds)
#導(dǎo)入數(shù)據(jù)
def importToMysql(import_file,table_name):
? ? # 連接數(shù)據(jù)庫糖耸，查詢數(shù)據(jù)
? ? db= MySQLdb.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="test",charset='GBK')
? ? # 使用cursor()方法獲取操作游標(biāo)
? ? cur= db.cursor()
? ? book= xlrd.open_workbook(import_file)
? ? sheet= book.sheet_by_index(0)
? ? if sheet.nrows>0:
? ? ? ? # 創(chuàng)建庫表
? ? ? ? insert_sql= create_table(cur, sheet, table_name)
? ? ? ? #插入數(shù)據(jù)
? ? ? ? insert_table(cur, sheet, table_name, insert_sql)
? ? else:
? ? ? ? print('文件為空秘遏，無需導(dǎo)入')
? ? cur.close()
? ? db.commit()
? ? db.close()
if __name__== '__main__':
? ? importToMysql("test.xlsx",'test4')
執(zhí)行測試，結(jié)果如下：
2.8萬數(shù)據(jù)嘉竟，入庫執(zhí)行9秒邦危，速度算比較快了。
后期不用繁瑣的到mysql建表舍扰、執(zhí)行導(dǎo)入等操作了倦蚪，直接運行腳本、指定表名边苹，一鍵搞定陵且，知識就是生產(chǎn)力。
