python3 拼接并批量生成sql

发布时间:2019-08-24 09:24:13编辑:auto阅读(1378)

    #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()

关键字