Mest SQL（1）---利用Python将Excel数据表导入MySQL数据库

【最终呈现效果】

【原始Excel数据表】

【代码实现及注释】

import pandas as pd
import time
import re
ti = time.strftime('%Y_%m_%d_%H%M%S',time.localtime())

myexcel=r'G:\Mest\Mest Property Sample.xlsx'            #Excel的数据源
my_list=pd.read_excel(myexcel,sheet_name=None)
tab_list = []
for x in my_list.keys():
     tab_list.append(x)
print(tab_list)                                 #打印出各个表名

j=0
while j<len(tab_list):                          #转换为SQL语句
    mycolumn=pd.read_excel(myexcel,sheet_name=tab_list[j],names=None).columns.tolist()
    mytablehead=mycolumn
    eachcolumn = pd.read_excel(myexcel, sheet_name=tab_list[j], usecols=mycolumn, names=None).values.tolist()
    mycolumn2=re.sub("'","",str(mycolumn))
    for x in mytablehead:
        if 'ID' in str(x):
            mytablehead.insert(mytablehead.index(x), str(x)+str(' int'))
            mytablehead.pop(mytablehead.index(x))
        elif 'Notes' in str(x):
            mytablehead.insert(mytablehead.index(x), str(x) + str(' varchar(10000)'))
            mytablehead.pop(mytablehead.index(x))
        else:
            mytablehead.insert(mytablehead.index(x), str(x) + str(' char(100)'))
            mytablehead.pop(mytablehead.index(x))
    mytablehead2=re.sub("'","",str(mytablehead))
    myoutput = r'G:/Mest/sql_script_' + ti + '.sql'
    myfile = open(myoutput, mode='a', encoding='utf-8')
    myfile.write('CREATE TABLE '+str(tab_list[j])+' ('+str(mytablehead2)[1:-1]+');')
    for x in eachcolumn:
        for i in x:
            if '00:00:00' in str(i):
                x.insert(x.index(i), str(i)[0:10])
                x.pop(x.index(i))
            elif type(i)==int or type(i)==float:
                x.insert(x.index(i), str(i))
                x.pop(x.index(i))
        myfile.write('INSERT INTO '+tab_list[j] +' (' + str(mycolumn2)[1:-1] + ')')
        myfile.write('VALUES(' + str(x)[1:-1] + ');')
        print('INSERT INTO '+tab_list[j] +' (' + str(mycolumn2)[1:-1] + ')')
        print('VALUES(' + str(x)[1:-1] + ');')
    j+=1
print('source ' + str(myoutput)+';')

【生成的SQL脚本】

【执行语句】

source G:/Mest/sql_script_2022_11_19_104956.sql;