python 多excel导入到mysql中去

场景：文件夹下有N多excel,每个excel中有1个sheet，sheet页中首行为表格的列头名

实现：将以上所有表格导入到mysql中去，以文件名命名为表名，以列头名命名为字段名

细节：将所有字段处理成字符串，其中半角"处理成了全角”    等

不足：……

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import xlrd
import string
import os
import MySQLdb

import sys 
reload(sys) 
sys.setdefaultencoding("utf-8")




def IsSubString(SubStrList,Str):  
    ''''' 
    #判断字符串Str是否包含序列SubStrList中的每一个子字符串 
    #>>>SubStrList=['F','EMS','txt'] 
    #>>>Str='F06925EMS91.txt' 
    #>>>IsSubString(SubStrList,Str)#return True (or False) 
    '''  
    flag=True  
    for substr in SubStrList:  
        if not(substr in Str):  
            flag=False  
    return flag  



def fn_get_filelist(FindPath,FlagStr=[]):  
    ''''' 
    #获取目录中指定的文件名 
    #>>>FlagStr=['F','EMS','txt'] #要求文件名称中包含这些字符 
    #>>>FileList=GetFileList(FindPath,FlagStr) # 
    '''  
    import os  
    FileList=[]  
    FileNames=os.listdir(FindPath)  
    if (len(FileNames)>0):  
       for fn in FileNames:  
           if (len(FlagStr)>0):  
               #返回指定类型的文件名  
               if (IsSubString(FlagStr,fn)):  
                   fullfilename=os.path.join(FindPath,fn)  
                   FileList.append(fullfilename)  
           else:  
               #默认直接返回所有文件名  
               fullfilename=os.path.join(FindPath,fn)  
               FileList.append(fullfilename)  
    #对文件名排序  
    if (len(FileList)>0):  
        FileList.sort()  
    for i in range(len(FileList)):
        print FileList[i]
    return FileList 

def fn_create(f):
    book = xlrd.open_workbook(f)
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    cols = sheet.ncols
    c = ""
    for ic in range(cols):
        arr_v = sheet.col_values(ic)
        h = fn_arr_maxLen(arr_v)
        c = c + '`' + sheet.cell(0,ic).value + '` varchar(' + str(h) + ') null  comment \"' +  sheet.cell(0,ic).value + "\"," + "\n\r"
    t = unicode(f.split("/")[-1].split(".")[0],"utf-8")# f.replace('/tmp/excel/','')
    t1 = ' create table if not exists `suyang_'
    t2 = ' id int AUTO_INCREMENT ,primary key (id)) ENGINE=INNODB   DEFAULT CHARSET=utf8;'
    t = t1 + t + '` (' + c + t2
    #print t
    return t



def fn_insert(f):
    book = xlrd.open_workbook(f)
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    cols = sheet.ncols
    c = ''
    for ic in range(cols):
        c = c + '`' + sheet.cell(0,ic).value + '`,' 
    t = f.split("/")[-1].split(".")[0]    # f.replace('/tmp/excel/','')
    print t
    insertSql = ' insert into  `suyang_' + t + '` (' + c.rstrip(',') + ' ) values ( '
    for ir in range(1,rows):
        row_values = ''

        insertSql = ' insert into  `suyang_' + t + '` (' + c.rstrip(',') + ' ) values ( '
        for icc in range(cols):
            if isinstance(sheet.cell(ir,icc).value,int) or isinstance(sheet.cell(ir,icc).value,float):
                vs = repr(sheet.cell(ir,icc).value).split(".")[0]
            else:
                vs = str(sheet.cell(ir,icc).value)
            insertSql = insertSql + row_values + '"' + str(vs).replace('"','”') + '",'
        insertSql = insertSql.rstrip(',')+');' #sheet.cell(ir,icc)
        yield insertSql   

    #print t

def fn_arr_maxLen(arr):
    o = 0    
    for v in arr:
        if (o < len(str(v).strip())):
            o = len(str(v).strip())
    return o

        


if __name__ == '__main__': 
    '''''cs=fn_create('/tmp/excel/注销信息.xlsx')
    print cs'''
    mycn = MySQLdb.connect("10.18.141.52","dba","xxxxxxxxxxxxxxxx","ys" )
    mycn.set_character_set('utf8')
    mycursor = mycn.cursor()
    mycursor.execute('SET CHARACTER SET utf8;')
    mycursor.execute('SET NAMES utf8;')
    mycursor.execute('SET character_set_connection=utf8;')

    fs=fn_get_filelist("/tmp/excel",['xlsx'])
    for ii in range(len(fs)):
        tcreateSql = fn_create(fs[ii])
        print tcreateSql
        try:
            mycursor.execute(tcreateSql)
            for isql in fn_insert(fs[ii]):
                #print isql
                mycursor.execute(isql)
            mycn.commit()
        except Exception , e:
            print e
    mycursor.close()
    mycn.close()