python+sqlite3实现生成excel模板、解析excel文件并批量入库（最全源码）

1、生成excel模板

import pandas as pd
import sqlite3

import openpyxl

    def list(self, request, *args, **kwargs):
        data = {
            "序号":[],
            "姓名": [],
            "性别": [],
            "是否内训师": [],
            "来源": [],
            "单位名称": [],
            "部门": [],
            "职务": [],
            "手机号": [],
            "课费(元)": [],
            "身份证号": [],
            "开户行信息": [],
            "个人介绍": [],
            "备注": []
        }
        frame = pd.DataFrame(data)
        excel = frame.to_excel('example.xlsx', index=False)
        return JsonResponse(excel,safe=False)

2、解析excel文件并批量入库

 def getexcel(self, request):
        # 连接数据库
        connect = sqlite3.connect("./db.sqlite3")
        cursor = connect.cursor()
        # 读取excel文件
        workbook = openpyxl.load_workbook('./example.xlsx')
        # 获取excel文件当前表格
        active = workbook.active
        # 写sql
        insert_sql = ("insert into Teacher(name,gender,internal_trainer,source,unit_name,branch,post,"
                       "phone_num,cost,id_num,bank_info,self_intro,remark) values (?,?,?,?,?,?,?,?,?,?,?,?,?)")
        # 可以理解为裁剪表格
        for row in active.iter_rows(min_row=2,min_col=2,max_col=14, max_row=active.max_row):
            row_ = [cell.value for cell in row]
            cursor.execute(insert_sql,row_)
        connect.commit()
        connect.close()
        return JsonResponse(res201(REQUEST_PUT_SUCCESS))