今天的Django教训汇总：后台上传Excel到数据库-20210412

最新推荐文章于 2024-07-04 14:10:06 发布

i see the future

最新推荐文章于 2024-07-04 14:10:06 发布

阅读量707

点赞数 2

分类专栏： django 文章标签： upload django excel python orm

本文链接：https://blog.csdn.net/m0_46629123/article/details/115642024

版权

django 专栏收录该内容

210 篇文章 20 订阅

订阅专栏

1.文件上传功能

最简单的文件上传

参考链接
#views.py

# 上传文件
from pathlib import Path
BASE_DIR = Path(__file__).resolve().parent.parent

import os
def upload_data(request):
    if request.method == "POST":    # 请求方法为POST时，进行处理
        myFile =request.FILES.get("myfile", None)    # 获取上传的文件，如果没有文件，则默认为None
        if not myFile:
            return HttpResponse("no files for upload!")
        destination = open(os.path.join(BASE_DIR,'static',myFile.name),'wb+')    # 打开特定的文件进行二进制的写操作
        for chunk in myFile.chunks():      # 分块写入文件
            destination.write(chunk)
            
        destination.close()
        return HttpResponse("upload over!")
    else:
        return render(request,'myclass/upload_data.html',{})

#upload_data.html

<html>
<head>
    <title>Employee_form</title>
</head>

<body>
    <form enctype="multipart/form-data" action="" method="POST">
        <input type="file" name="myfile" />
        <br/>
        <input type="submit" value="upload"/>
     </form>

</body>
</html>

在这里插入图片描述
点击upload之后，显示403错误。

#upload_data.html增加{% csrf_token %}

<html>
<head>
    <title>Employee_form</title>
</head>

<body>
    <form enctype="multipart/form-data" action="" method="POST">
        <input type="file" name="myfile" />
        <br/>
        {% csrf_token %}
        <input type="submit" value="upload"/>
     </form>

</body>
</html>

显示正常
在这里插入图片描述

2. 在后台将文件上传到数据库

包含无关系数据(Import_Employee、Import_Teacher)、一对多关系(Import_Subject)、多对多关系(Import_Employee_Subject)。先建立import_models来构造批量上传功能，然后模仿models的模型建立utils，并在admin.py中新建class来保存数据，最后admin.py登记新的模型。

#models.py

class Employee(models.Model):
    eid = models.CharField(max_length=200,default='1',unique=True)
    name = models.CharField(max_length=200,null=True)
    dept_code = models.CharField(max_length=200,null=True)
    GENDER_CHOICES = (
        (u'Male', u'Male'),
        (u'Female', u'Female'),
    )
    gender = models.CharField(max_length=200,choices=GENDER_CHOICES,null=True)
    on_board = models.BooleanField(null=True)
    Range_CHOICES = (
        (u'R1', u'R1'),
        (u'R2', u'R2'),
        (u'R3', u'R3'),
        (u'R4', u'R4'),
    )
    Range = models.CharField(max_length=200,choices=Range_CHOICES,null=True)

    def __str__(self):
        return self.name


class Teacher(models.Model):
    tid = models.CharField(max_length=200,default='1',unique=True)
    name = models.CharField(max_length=200,null=True)
    dept = models.CharField(max_length=200,null=True)
    GENDER_CHOICES = (
        (u'Male', u'Male'),
        (u'Female', u'Female'),
    )
    gender = models.CharField(max_length=200,choices=GENDER_CHOICES,null=True)
    on_board = models.BooleanField(null=True)
    GRADE_CHOICES = (
        (u'Junior', u'Junior'),
        (u'Middle', u'Middle'),
        (u'Senior', u'Senior'),
    )
    teacher_grade = models.CharField(max_length=200,choices=GRADE_CHOICES,null=True)

    def __str__(self):
        return self.name



class Subject(models.Model):
    Subject_id = models.CharField(max_length=200,default='1',unique=True)
    subject_name = models.CharField(max_length=200)
    teacher = models.ForeignKey(Teacher, on_delete=models.CASCADE,null=True)
    employee = models.ManyToManyField(Employee, through='Employee_Subject')

    def __str__(self):
        #return '%s.%s' % (self.Subject_id, self.subject_name)
        return self.Subject_id

    class Meta:
        #排序
        ordering = ['Subject_id']


class Employee_Subject(models.Model):
    esid = models.AutoField(primary_key=True)
    subject = models.ForeignKey(Subject,on_delete=models.CASCADE)
    employee = models.ForeignKey(Employee,on_delete=models.CASCADE)
    
    def __str__(self):
        return '%s : %s' % (self.subject.Subject_id, self.employee.name)

    
    class Meta:
        #增加唯一性限制
        unique_together = (("subject", "employee"),)
        #排序
        ordering = ['subject']


# 定义后台上传Employee文件的接口
class Import_Employee(models.Model):
    file = models.FileField(upload_to='file')
    name = models.CharField(max_length=50,verbose_name=r'文件名')

    class Meta:
        ordering = ['name']

    def __str__(self):
        return self.name



# 定义后台上传Teacher文件的接口
class Import_Teacher(models.Model):
    file = models.FileField(upload_to='file')
    name = models.CharField(max_length=50,verbose_name=r'文件名')

    class Meta:
        ordering = ['name']

    def __str__(self):
        return self.name



# 定义后台上传Subject文件的接口
class Import_Subject(models.Model):
    file = models.FileField(upload_to='file')
    name = models.CharField(max_length=50,verbose_name=r'文件名')

    class Meta:
        ordering = ['name']

    def __str__(self):
        return self.name




# 定义后台上传Employee_Subject文件的接口
class Import_Employee_Subject(models.Model):
    file = models.FileField(upload_to='file')
    name = models.CharField(max_length=50,verbose_name=r'文件名')

    class Meta:
        ordering = ['name']

    def __str__(self):
        return self.name

#utils.py保存方法，为admin.py所使用。

from openpyxl import Workbook,load_workbook
from openpyxl.utils import get_column_letter
from .models import Import_Employee,Employee,Teacher,Subject,Import_Employee_Subject,Employee_Subject


# 批量上传employee
def import_employee(self, request, obj, change):

    wb = load_workbook(filename=obj.file.path)
    ws = wb.get_sheet_names()
    ws = wb.get_sheet_by_name(ws[0])
    max_row = ws.max_row
    headers = ['eid', 'name', 'dept_code', 'gender','on_board','Range']
    lists = []
    for row in range(2, max_row+1):
        r = {}
        for col in range(1, len(headers) + 1):
            key = headers[col - 1]
            r[key] = ws.cell(row=row, column=col).value
        lists.append(r)
    print('lists',lists)
    sqllist = []
    for cell in lists:
        eid = cell['eid']
        name = cell['name']
        dept_code = cell['dept_code']
        gender = cell['gender']
        on_board = cell['on_board']
        Range = cell['Range']
        sql = Employee(eid=eid, name=name, dept_code=dept_code, gender=gender, on_board=on_board, Range=Range)
        sqllist.append(sql)
    print('sqllist',sqllist)
    Employee.objects.bulk_create(sqllist)

    print('上传成功')



# 批量上传teacher
def import_teacher(self, request, obj, change):

    wb = load_workbook(filename=obj.file.path)
    ws = wb.get_sheet_names()
    ws = wb.get_sheet_by_name(ws[0])
    max_row = ws.max_row
    headers = ['tid', 'name', 'dept', 'gender','on_board','teacher_grade']
    lists = []
    for row in range(2, max_row+1):
        r = {}
        for col in range(1, len(headers) + 1):
            key = headers[col - 1]
            r[key] = ws.cell(row=row, column=col).value
        lists.append(r)
    print('lists',lists)
    sqllist = []
    for cell in lists:
        tid = cell['tid']
        name = cell['name']
        dept = cell['dept']
        gender = cell['gender']
        on_board = cell['on_board']
        teacher_grade = cell['teacher_grade']
        sql = Teacher(tid=tid, name=name, dept=dept, gender=gender, on_board=on_board, teacher_grade=teacher_grade)
        sqllist.append(sql)
    print('sqllist',sqllist)
    Teacher.objects.bulk_create(sqllist)

    print('上传成功')




# 批量上传subject
def import_subject(self, request, obj, change):

    wb = load_workbook(filename=obj.file.path)
    ws = wb.get_sheet_names()
    ws = wb.get_sheet_by_name(ws[0])
    max_row = ws.max_row
    headers = ['Subject_id', 'subject_name', 'teacher']
    lists = []
    for row in range(2, max_row+1):
        r = {}
        for col in range(1, len(headers) + 1):
            key = headers[col - 1]
            r[key] = ws.cell(row=row, column=col).value
        lists.append(r)
    print('lists',lists)
    sqllist = []
    for cell in lists:
        Subject_id = cell['Subject_id']
        subject_name = cell['subject_name']
        # 实例化对象
        teacher_name = cell['teacher']
        teacher = Teacher.objects.get(name=teacher_name)
        sql = Subject(Subject_id=Subject_id, subject_name=subject_name, teacher=teacher)
        sqllist.append(sql)
    print('sqllist',sqllist)
    Subject.objects.bulk_create(sqllist)

    print('上传成功')





# 批量上传Employee_Subject
def import_employee_subject(self, request, obj, change):

    wb = load_workbook(filename=obj.file.path)
    ws = wb.get_sheet_names()
    ws = wb.get_sheet_by_name(ws[0])
    max_row = ws.max_row
    headers = ['subject', 'employee']
    lists = []
    for row in range(2, max_row+1):
        r = {}
        for col in range(1, len(headers) + 1):
            key = headers[col - 1]
            r[key] = ws.cell(row=row, column=col).value
        lists.append(r)
    print('lists',lists)
    sqllist = []
    for cell in lists:
        Subject_id = cell['subject']
        employee_name = cell['employee']
        # 实例化对象
        subject = Subject.objects.get(Subject_id=Subject_id)
        employee = Employee.objects.get(name=employee_name)
        sql = Employee_Subject(subject=subject, employee=employee)
        sqllist.append(sql)
    print('sqllist',sqllist)
    Employee_Subject.objects.bulk_create(sqllist)

    print('上传成功')

#admin.py

from django.contrib import admin
from myclass.models import *
from myclass.forms import NewEmployee

# Register your models here.


class EmployeeAdmin(admin.ModelAdmin):
    list_display = ('eid', 'name')



# 因为我们要在后台保存，所以我们需要重写ModelAdmin的save_mode
from .utils import import_employee
class ImportEmployeeFileAdmin(admin.ModelAdmin):

    list_display = ('file','name',)
    list_filter = ['name',]

    def save_model(self, request, obj, form, change):

        re = super(ImportEmployeeFileAdmin,self).save_model(request, obj, form, change)
        import_employee(self, request, obj, change)
        return re



# 因为我们要在后台保存，所以我们需要重写ModelAdmin的save_mode
from .utils import import_teacher
class ImportTeacherFileAdmin(admin.ModelAdmin):

    list_display = ('file','name',)
    list_filter = ['name',]

    def save_model(self, request, obj, form, change):

        re = super(ImportTeacherFileAdmin,self).save_model(request, obj, form, change)
        import_teacher(self, request, obj, change)
        return re



# 因为我们要在后台保存，所以我们需要重写ModelAdmin的save_mode
from .utils import import_subject
class ImportSubjectFileAdmin(admin.ModelAdmin):

    list_display = ('file','name',)
    list_filter = ['name',]

    def save_model(self, request, obj, form, change):

        re = super(ImportSubjectFileAdmin,self).save_model(request, obj, form, change)
        import_subject(self, request, obj, change)
        return re



# 因为我们要在后台保存，所以我们需要重写ModelAdmin的save_mode
from .utils import import_employee_subject
class ImportEmployeeSubjectFileAdmin(admin.ModelAdmin):

    list_display = ('file','name',)
    list_filter = ['name',]

    def save_model(self, request, obj, form, change):

        re = super(ImportEmployeeSubjectFileAdmin,self).save_model(request, obj, form, change)
        import_employee_subject(self, request, obj, change)
        return re


admin.site.register(Employee)
admin.site.register(Subject)
admin.site.register(Teacher)
admin.site.register(Employee_Subject)
admin.site.register(Person)
admin.site.register(Group)
admin.site.register(Membership)
admin.site.register(NewEmployeeModel)
admin.site.register(Questionnaire)
admin.site.register(Subject_Score)
admin.site.register(Import_Employee,ImportEmployeeFileAdmin)
admin.site.register(Import_Teacher,ImportTeacherFileAdmin)
admin.site.register(Import_Subject,ImportSubjectFileAdmin)
admin.site.register(Import_Employee_Subject,ImportEmployeeSubjectFileAdmin)

记得更新模型：python manage.py makemigrations和python manage.py migrate

成功建模截图：

在这里插入图片描述

错误1：must be a instance。

针对一对多、多对多关系的数据维护，需要实例化外键。
在这里插入图片描述

错误2：employee = Employee.objects.get(name=employee_name)的get得到超过2个值。

解决方法1：删除重复项目
解决方法2：get换成filter&切片
在这里插入图片描述

i see the future

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
2
评论
今天的Django教训汇总：后台上传Excel到数据库-20210412

1.文件上传功能最简单的文件上传参考链接#views.py# 上传文件from pathlib import PathBASE_DIR = Path(__file__).resolve().parent.parentimport osdef upload_data(request): if request.method == "POST": # 请求方法为POST时，进行处理 myFile =request.FILES.get("myfile", None)
复制链接

扫一扫

专栏目录