#coding=utf-8 from openpyxl import load_workbook #读取excel的数据 def read_excel(): #打开一个workbook wb = load_workbook(filename='data4.xlsx') #获取所有表格(worksheet)的名字 sheets = wb.get_sheet_names() #遍历每一个sheet,并且拿到worksheet对象 for i in range(len(sheets)): ws = wb.get_sheet_by_name(sheets[i]) #声明list list_A_B = [] # list_A_B.append('123') #遍历A,B列的所有数据值,ws.max_row:获取最大行数 for rowNum in range(2,ws.max_row+1): if (ws.cell(row=rowNum,column=1).value != None) and (ws.cell(row=rowNum,column=2).value != None): #ws.cell(row=rowNum,column=1).value:拿到指定列的行数据 sql = 'insert into ind_hgjj_zbtj_m (id,index_code,index_name,index_pcode,area_code,area_name,date_type,date_value,statistic_data,statistic_data_cunit,is_valid,update_date) values('+ \ str(ws.cell(row=rowNum,column=1).value)+','\ + "'" + str(ws.cell(row=rowNum,column=2).value) + "'"+',' \ + "'" + str(ws.cell(row=rowNum,column=3).value) + "'"+',' \ +"'" + str(ws.cell(row=rowNum,column=4).value) + "'" +','\ + str(530111501)+ ','\ + "'" + str('经开区') + "'" + ','\ + "'" + str(ws.cell(row=rowNum,column=5).value) + "'"+',' \ + "'" + str(ws.cell(row=rowNum,column=6).value) + "'" +','\ + str(ws.cell(row=rowNum,column=7).value)+',' \ + "'" + str(ws.cell(row=rowNum,column=8).value)+ "'" +','\ +str(1)+','\ + "'" +str('2018/3/7') + "'" + ');' list_A_B.append(sql) print(sql) #print(list_A_B) text_save(list_A_B,'A.txt') print(str(sheets[i]) + '已完成!') print('执行成功!') #将读取的数据存入txt文档中 def text_save(content,filename,mode='a'): #打开文件 file = open(filename,mode) for i in range(len(content)): number = content[i] + '\n' file.write(number) file.close() if __name__ == '__main__': read_excel()
python3 拼接并批量生成sql
最新推荐文章于 2024-07-08 03:52:10 发布