python-excel数据读取并存入mysql

#-*-coding:utf-8 -*-
import os
import xlrd
import xlsxwriter

import numpy as np
import pandas as pd
#from sqlalchemy import create_engine
#from sqlalchemy.types import VARCHAR, Float, Integer
from excelutils import excelutils as excels

import pymysql


HOST='localhost'
PORT=3306
USER='root'
PASSWD='root'
DATEBASE='db'

#创建表
def create_table(table_name):
    conn=pymysql.connect(
                            host=str(HOST),
                            port=PORT,
                            user=str(USER),
                            passwd=str(PASSWD),
                            db=str(DATEBASE))
    conn.ping(True)
    cur=conn.cursor()
    conn.set_charset("utf8")
    strDrop="DROP TABLE IF EXISTS `"+table_name+"`;"
    print(strDrop)
    cur.execute(strDrop)
    
    str_sql="CREATE TABLE `"+table_name+"`("
    str_sql=str_sql+"`id` varchar(255) COLLATE 
    str_sql=str_sql+") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"
    print(str_sql)
    try:
       cur.execute(str_sql)
       print(table_name+"表成功")
    except Exception as e:
       print("创建数据库失败：case%s"%e)
    finally:
        cur.close()
        conn.close()

def insert_pymysql(strSql):
    conn=pymysql.connect(
                            host=str(HOST),
                            port=PORT,
                            user=str(USER),
                            passwd=str(PASSWD),
                            db=str(DATEBASE))
    conn.ping(True)
    cur=conn.cursor()
    conn.set_charset("utf8")
    try:
       cur.execute(strSql)
       print(strSql+"执行成功")
       return 
    except Exception as e:
       print("执行失败：case%s"%e)
    finally:
        cur.close()
        conn.close()



def query_dataValue(strSql):
    conn=pymysql.connect(
                            host=str(HOST),
                            port=PORT,
                            user=str(USER),
                            passwd=str(PASSWD),
                            db=str(DATEBASE))
    conn.ping(True)
    cur=conn.cursor()
    conn.set_charset("utf8")
    try:
       cur.execute(strSql)
       returnValue=cur.fetchall()
       print(strSql+"执行成功")
       return 
    except Exception as e:
       print("执行失败：case%s"%e)
    finally:
        cur.close()
        conn.close()
    return returnValue

def main():
    e=excels.excelutils()
    allxls=e.readfile('C:/Users/xxx/xxxx/')
   
    datavalue=['']
    for fl in allxls:
        f=e.open_xls(fl)
        #读取excel文件名称
        file_name=str(fl).split('2/')[1].split('_')[0]
        print(file_name)
        x=e.getshnum(f)
        for shnum in range(x):
            #print("正在读取文件："+str(fl)+"的第"+str(shnum)+"个sheet表的内容。。。。")
            #读取excel中sheet名称
            sheet_name=xlrd.open_workbook(fl).sheet_names()[shnum]
            
           
           
            #创建表
            create_table(sheet_name)


            value=e.getFile(fl,shnum,datavalue)
            value.pop(0)
            value.pop(0)
            listlen=len(value)
           
             for m in range(listlen):
                str_sql="insert into `"
                str_sql=str_sql+sheet_name+"` (`id`) values ('"
                str_sql=str_sql+str(value[m][0])+"');"
       
                insert_pymysql(str_sql)
          


            
        
if __name__ == "__main__":
    main()