基于django框架的数据操作

python写一个入库脚本 通过excel上传用例 入库

 user-views下增加init_db方法：

def init_db(request):
    #1、excel读取数据
    #读作者信息 就会同步两张表 引入xlrd
    #读取第一个sheet页
    workbook  = xlrd.open_workbook('./图书信息表.xlsx')
    sheet = workbook.sheet_by_name('作者信息')
    #添加数据之前先删除数据  先查询后删除
    Author.objects.all().delete()
    AuthorDetail.objects.all().delete()
    for i in range(1,sheet.nrows): #nrows所有行 
        data= sheet.row_values(i)
        # print(sheet.row_values(i)) #打印每行 list形式
    # 引models
    # 2插入数据
        author = Author.objects.create(name=data[0]) 作者表
        if data[1] =='男': #excel的数据性别和 数据model定义的不一样 需要转化为数据存储的形式
            data[1] =1
        else:
            data[1] =0
        AuthorDetail.objects.create(sex=data[1],age=int(data[2]),
                                    email=data[3],phone_number=data[4],author=author)
    #完成出版社的新增数据
    sheet2 = workbook.sheet_by_name('出版社信息')
    Pulisher.objects.all().delete()
    for i in range(1, sheet2.nrows):  # nrows所有行
        data = sheet2.row_values(i)
        pubulisher = Pulisher.objects.create(name=data[0],adress=data[1],city=data[2],website=data[3])
    return HttpResponse(123)

路由ulrs 增加 path('init_db/',views.init_db),

 

#1、excel读取数据
#读作者信息 就会同步两张表 引入xlrd
#读取第一个sheet页
workbook  = xlrd.open_workbook('./图书信息表.xlsx')
sheet = workbook.sheet_by_name('作者信息')
#添加数据之前先删除数据  先查询后删除
Author.objects.all().delete()
AuthorDetail.objects.all().delete()
cursor = connection.cursor()
#编号从1开始
sql1= 'UPDATE sqlite_sequence SET seq = 0 WHERE name = "user_author"'
sql2 = 'UPDATE sqlite_sequence SET seq = 0 WHERE name = "user_authordetail"'
sql3 = 'UPDATE sqlite_sequence SET seq = 0 WHERE name = "user_publisher"'
sql4 = 'UPDATE sqlite_sequence SET seq = 0 WHERE name = "user_book"'
cursor.execute(sql1)
cursor.execute(sql2)
cursor.execute(sql3)
cursor.execute(sql4)
cursor.close()
for i in range(1,sheet.nrows): #nrows所有行
    data= sheet.row_values(i)
    # print(sheet.row_values(i)) #打印每行 list形式
# 引models
# 2插入数据
    author = Author.objects.create(name=data[0])
    if data[1] =='男':
        data[1] =1
    else:
        data[1] =0
    AuthorDetail.objects.create(sex=data[1],age=int(data[2]),
                                email=data[3],phone_number=data[4],author=author)
#完成出版社的新增数据
sheet2 = workbook.sheet_by_name('出版社信息')
Pulisher.objects.all().delete()
for i in range(1, sheet2.nrows):  # nrows所有行
    data = sheet2.row_values(i)
    pubulisher = Pulisher.objects.create(name=data[0],adress=data[1],city=data[2],website=data[3])

#完成书的新增
sheet3 = workbook.sheet_by_name('图书信息')
Book.objects.all().delete()
for i in range(1,sheet3.nrows):
    data= sheet3.row_values(i)
    pulisher = Pulisher.objects.filter(name=data[2]) #加if 异常处理
    d = datetime(*xldate_as_tuple(data[3], 0))#出版社日期单独处理 excel读取的日期格式直接转日期
    book = Book.objects.create(name=data[0],publish_data=d,
                               price=data[4],pulisher=pulisher.first())
    for n in data[1].slipt(','):
        author = Author.objects.filter(name=data[n])
        book.author.add(author.first())
return HttpResponse(123)

from django.shortcuts import render
from rest_framework.decorators import api_view
from django.shortcuts import HttpResponse,HttpResponseRedirect
from xlrd import xldate_as_tuple
import xlrd
from user.models import *
# Create your views here.
import json
from django.db import connection
import datetime