python3 django+mysql+bootstrap增删改查-优快云博客

本文链接：https://blog.youkuaiyun.com/lanquankk/article/details/98750454

本文详细介绍如何使用Python的Django框架结合MySQL数据库实现增删改查功能，涵盖数据库配置、模型定义、视图处理及前后端交互，适用于初学者快速上手。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

python3 django+mysql+bootstrap增删改查

#安装mysql连接
pip3 install pymysql#安装pymysql
访问mysql运行异常参考https://blog.youkuaiyun.com/weixin_33127753/article/details/89100552

开发工具JetBrains

修改数据库连接地址
settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test',         #数据库名称
        'HOST': '127.0.0.1',    #IP
        'PORT': '3307',         #端口
        'USER': 'root',         #账号
        'PASSWORD':'123456',    #密码
        'CONN_MAX_AGE': None    #长连接
    }
}

在这里插入图片描述
python manage.py makemigrations # 将类（数据库）的变化提交
python manage.py migrate # 执行变化
执行结果

models.py代码

from django.db import models


#定义图书模型类BookInfo
class BookInfo(models.Model):
    id = models.AutoField(primary_key=True)  # 创建自增的一个主键
    name = models.CharField(max_length=255, verbose_name='名称')
    price = models.CharField(max_length=255, verbose_name='Price')
    createdate = models.DateTimeField(verbose_name='添加日期')

    class Meta:
        db_table = 'tb_books'  # 指明数据库表名
        verbose_name = '图书'  # 在admin站点中显示的名称

        verbose_name_plural = verbose_name  # 显示的复数名称

    def __str__(self):
        """定义每个数据对象的显示信息"""
        return self.name

在这里插入图片描述
urls.py添加连接

	url(r'^$',views.index),#加载页面
    url(r'data',views.tb_book_json),#MySQL列表数据
    url(r'info',views.tb_book_info),#修改获取数据
    url(r'save',views.tb_book_save),#添加，修改保存
    url(r'del',views.tb_book_del),#删除，批量删除

views.py

# Create your views here.

from django.shortcuts import render#导入render模块
from django.http.response import JsonResponse
from lanquan import models#引用models
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
import logging
import datetime
from django.views.decorators.csrf import csrf_exempt
logger = logging.getLogger('stu')# 指定所用的logger



def index(request):
    return render(request,'index.html')#通过render模块把index.html这个文件返回到前端


def tb_book_json(request):
    response = {}
    """
    #不分页使用
    ret = models.BookInfo.objects.all().order_by("id")
    response["records"] = list(ret.values())
    response['total'] = models.BookInfo.objects.all().count()
    return JsonResponse(response)"""
    #分页
    offset = int(request.GET.get('offset'))
    limit = int(request.GET.get('limit'))
    keyword = request.GET.get('KEYW')
    if keyword:
        tb_book_list = models.BookInfo.objects.values('id', 'name', 'price').filter(name__contains=keyword).order_by("id")
    else:
        tb_book_list = models.BookInfo.objects.values('id', 'name', 'price').order_by("id")
    if tb_book_list:
        paginator = Paginator(tb_book_list, limit)
        try:
            page_object_list = paginator.page(offset / limit + 1)
            logger.info('获取数据成功,path：' + request.path)
        except PageNotAnInteger:
            logger.error('If page is not an integer, deliver first page.,path：' + request.path)
            page_object_list = paginator.page(1)
        except EmptyPage:
            logger.error('If page is out of range (e.g. 9999), deliver last page of results.,path：' + request.path)
            page_object_list = paginator.page(paginator.num_pages)

    """rows = []
    for item in page_object_list:
        # 将数组中的每个元素提取出来拼接为rows的内容
        rows.append({'id': item['id'], 'name': item['name'], 'price': item['price']})"""

    response["records"] = page_object_list.object_list
    if keyword:
        response['total'] = models.BookInfo.objects.filter(name__contains=keyword).count()
    else:
        response['total'] = models.BookInfo.objects.all().count()
    return JsonResponse(response)


#edit修改获取数据
@csrf_exempt
def tb_book_info(request):
    response = {}
    keyid = int(request.POST.get('id'))
    response['row'] = list(models.BookInfo.objects.filter(id=keyid).values('id', 'name', 'price'))
    return JsonResponse(response)


#add,edit 保存
@csrf_exempt
def tb_book_save(request):
    response = {}
    keyid = int(request.POST.get('id'))
    name = request.POST.get('name')
    price = request.POST.get('price')
    if keyid == 0:
        #print("添加:" + str(name) + ";" + str(price))
        entity = models.BookInfo()
        entity.name = name
        entity.price = price
        entity.createdate = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        # 调用save则保存数据到数据库中
        try:
            entity.save()
            response['row'] = "ok"
        except MyError as e:
            response['row'] = "error"
            response['message'] = e.msg
    else:
        #print("修改:" + str(keyid) + ";" + str(name) + ";" + str(price))
        try:
            models.BookInfo.objects.filter(id=keyid).update(name=name, price=price)
            response['row'] = "ok"
        except MyError as e:
            response['row'] = "error"
            response['message'] = e.msg
    return JsonResponse(response)


#删除，批量删除根据ID
@csrf_exempt
def tb_book_del(request):
    response = {}
    ids = request.POST.get('ids')
    try:
        models.BookInfo.objects.extra(where=['id IN (' + ids + ')']).delete()
        response['row'] = "ok"
    except MyError as e:
        response['row'] = "error"
        response['message'] = e.msg
    return JsonResponse(response)


class MyError(Exception):
    def __init__(self, msg):
        self.msg = msg

    def __str__(self):
        return self.msg

模板样式目录
在这里插入图片描述
templates/index.html

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>test</title>
        <link rel="stylesheet" href="/static/css/bootstrap.min.css">
        <link rel="stylesheet" href="/static/css/bootstrap-table.min.css">
        <link rel="stylesheet" href="/static/css/font-awesome.min.css">
        <link rel="stylesheet" href="/static/css/util.css">
    </head>
    <body>
    <div class="container">
        <div id="toolbar" >
            <ul class="searchul">
                <li class="lileft"><span class="input-icon"><input  type="text" id="keyword" value="" placeholder="Item Name"><i class="icon-search"></i></span></li>
                <li class="lileft"><button type="button" class="btn btn-mini btn-light" onclick="boottable.refresh();" title="搜索"><i class="icon-search"></i></button></li>
                <li class="lileft"><button type="button" class="btn btn-mini btn-success" onclick="boottable.add()"><i class="icon-plus"></i>添加</button></li>
                <li class="lileft"><button type="button" class="btn btn-mini btn-danger" onclick="boottable.delall();"><i class="icon-trash"></i>批量删除</button></li>
            </ul>
        </div>
        <table id="table"></table>
    </div>
    </body>
    <script type="text/javascript" src="/static/js/jquery.min.js"></script>
    <script type="text/javascript" src="/static/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="/static/js/bootstrap-table.js"></script>
    <script type="text/javascript" src="/static/js/layer/layer.js"></script>
<script type="text/javascript">
	var boottable = {
		loading:function(){
			boottable.gridload();
		},
		gridload:function(){
			$("#table").bootstrapTable({
				url : '/data', 		//请求后台的URL（*）
				toolbar : '#toolbar',
				method : 'get', 				//请求方式（*）
				striped : true, 				//是否显示行间隔色
				cache : false, 					//是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
				pagination : true, 				//是否显示分页（*）
				sidePagination : "server", 		//分页方式：client客户端分页，server服务端分页（*）
				pageNumber : 1, 				//初始化加载第一页，默认第一页
				pageSize : 10, 	//每页的记录行数（*）
				uniqueId : 'id', 			//每一行的唯一标识，一般为主键列
				clickToSelect : true, 			//是否启用点击选中行
				height : 400,
				queryParams:function(params){
					params['KEYW'] = $.trim($("#keyword").val());
					return params;
				},
				columns : [
					{ checkbox : true , align : 'center',valign : 'middle' },
					{ title : 'ID', field : 'id', align : 'left', valign : 'middle'},
					{ title : 'Item Name', field : 'name', align : 'left', valign : 'middle'},
					{ title : 'Price', field : 'price', align : 'left', valign : 'middle'},
					{ title : '操作', align : 'left', valign : 'middle',  formatter:function(value, row, index) {
							return '<a class="btn btn-minier btn-info" onclick="boottable.edit(' + row.id + ')" title="修改"><i class="icon-edit"></i></a>&nbsp;<a class="btn btn-minier btn-danger" onclick="boottable.del(' + row.id + ');" title="删除"><i class="icon-trash"></i></a>';
						}
					}
				],
				responseHandler : function(res) { return { total : res.total, rows : res.records }; } ,
				onSearch : function(text){
					console.log("执行查询方法：查询值："+text);
				}
			});
		},
		refresh:function(){
			$('#table').bootstrapTable('refresh', { url : '/data' });
		},
		add:function(){
			var a = layer.open({
			  type: 1,
			  title: '添加',
			  area: ['600px', '300px'],
			  shadeClose: true,
			  content: '<form class="bt-form pd20 pb70"><div class="line"><span class="tname">Item Name:</span><input type="text" class="bt-input-text" id="add_name" value="" style="width:330px"></div><div class="line"><span class="tname ">Price:</span><input type="text" class="bt-input-text" id="add_price" value="" style="width:330px"></div><div class="bt-form-submit-btn"><button type="button" class="btn btn-danger btn-sm" onclick="layer.closeAll()">关闭</button><button type="button" id="dlok" class="btn btn-success btn-sm dlok">确定</button></div></form>'
			});
			$("#dlok").click(function() {
			    $.ajax({type: "POST",url: '/save',data :{ id: 0 ,name : $("#add_name").val(),price : $("#add_price").val() ,_ : Math.random()},cache : false,
                    success: function(data){
                        /*保存完成，关闭层*/
                        layer.close(a);
                        if(data.row == "ok"){
                            layer.msg("保存成功",{icon: 1});
                        }else{
                            layer.msg("保存失败"+data.message ,{icon: 2});
                        }
                        /*刷新列表*/
                        boottable.refresh();
                    },
                    error : function(XMLHttpRequest, textStatus, errorThrown) {
                        layer.msg(errorThrown,{icon: 2});
                    }
                });
			});
		},
		edit:function(id){
			/*ajax加载数据*/
            $.ajax({type: "POST",url: '/info',data :{ id: id ,_ : Math.random()},cache : false,
				success: function(data){
                    console.log(data)
                    var e = layer.open({
                      type: 1,
                      title: '修改',
                      area: ['600px', '300px'],
                      shadeClose: true,
                      content: '<div class="bt-form pd20 pb70"><input type="hidden" id="edit_id" value="'+data.row[0].id+'"><div class="line"><span class="tname">Item Name:</span><input type="text" class="bt-input-text" id="edit_name" value="'+data.row[0].name+'" style="width:330px"></div><div class="line"><span class="tname ">Price:</span><input type="text" class="bt-input-text" id="edit_price" value="'+data.row[0].price+'" style="width:330px"></div><div class="bt-form-submit-btn"><button type="button" class="btn btn-danger btn-sm" onclick="layer.closeAll()">关闭</button><button type="button" id="dlok" class="btn btn-success btn-sm dlok">确定</button></div></div>'
                    });
                    $("#dlok").click(function() {
                        $.ajax({type: "POST",url: '/save',data :{ id: $("#edit_id").val() ,name : $("#edit_name").val(),price : $("#edit_price").val() ,_ : Math.random()},cache : false,
				            success: function(data){
                                /*保存完成，关闭层*/
                                layer.close(e);
                                if(data.row == "ok"){
                                    layer.msg("保存成功",{icon: 1});
                                }else{
                                    layer.msg("保存失败"+data.message ,{icon: 2});
                                }
                                /*刷新列表*/
                                boottable.refresh();
                            },
                            error : function(XMLHttpRequest, textStatus, errorThrown) {
                                layer.msg(errorThrown,{icon: 2});
                            }
                        });
                    });
                },
				error : function(XMLHttpRequest, textStatus, errorThrown) {
                    layer.msg(errorThrown,{icon: 2});
                }
			});
		},
		del:function(id){
			var d = layer.confirm('确认删除数据？', { btn: ['确认','取消']},
			function(){
				var dl = layer.msg('正在删除中...', { icon: 16, time: 1000 * 60, });
				/*执行操作返回结果后关闭加载层*/
                $.ajax({type: "POST",url: '/del',data :{ ids: id ,_ : Math.random()},cache : false,
                    success: function(data){
                        layer.close(dl);
                        /*提示成功或失败（【icon: 1】成功，【icon: 2】失败））*/
                        if(data.row == "ok"){
                            layer.msg("删除成功",{icon: 1});
                        }else{
                            layer.msg("删除失败"+data.message ,{icon: 2});
                        }
                        /*刷新列表*/
                        boottable.refresh();
                    },
                    error : function(XMLHttpRequest, textStatus, errorThrown) {
                        layer.msg(errorThrown,{icon: 2});
                    }
                });
			},
			function(){
				/*取消操作关闭层*/
				layer.close(d);
			});
		},
		delall:function(){
			/*获取选中行数据*/
			var hrs = $("#table").bootstrapTable('getSelections');
			var strid = "";
			if (hrs.length < 1) {
				/*没有选中行*/
				layer.msg('请选择数据',{icon: 2});
			} else {
				for (var i = 0; i < hrs.length; i++) {
					if(strid != ""){
						strid += ",";
					}
					strid += hrs[i].id;
				}
			}
			/*判断行数据是否存在，存在即执行删除提示*/
			if(strid != ""){
				var d = layer.confirm('确认删除数据？', { btn: ['确认','取消']},
				function(){
					var dl = layer.msg('正在删除中...', { icon: 16, time: 1000 * 60, });
					/*执行操作返回结果后关闭加载层*/
                    $.ajax({type: "POST",url: '/del',data :{ ids: strid ,_ : Math.random()},cache : false,
                        success: function(data){
                            layer.close(dl);
                            /*提示成功或失败（【icon: 1】成功，【icon: 2】失败））*/
                            if(data.row == "ok"){
                                layer.msg("删除成功",{icon: 1});
                            }else{
                                layer.msg("删除失败"+data.message ,{icon: 2});
                            }
                            /*刷新列表*/
                            boottable.refresh();
                        },
                        error : function(XMLHttpRequest, textStatus, errorThrown) {
                            layer.msg(errorThrown,{icon: 2});
                        }
                    });
				},
				function(){
					/*取消操作关闭层*/
					layer.close(d);
				});
			}
		}
	}

    $(function () {
		boottable.loading();
    });
</script>
</html>

static/css/util.css手写部分样式

li {
    display: list-item;
    text-align: -webkit-match-parent;
}
user agent stylesheet
ul {
    list-style-type: disc;
}

.searchul {
    margin: 0;
	margin-left: -40px;
    margin-top: 5px;
}
.searchul .lileft {
    float: left;
    display: block;
    line-height: 23px;
    min-height: 28px;
    padding-right: 5px;
}

.btn-mini {
    padding: 0 5px;
    line-height: 22px;
    border-width: 2px;
    font-size: 12px;
}

.btn-minier {
    padding: 0 2px;
    line-height: 16px;
    border-width: 2px;
    font-size: 14px;
}
.bt-form {
    height: 100%;
}
.pb70 {
    padding-bottom: 70px;
}
.pd20 {
    padding: 20px;
}
.line {
    padding: 5px 0;
}
.line .tname {
    display: block;
    float: left;
    height: 32px;
    line-height: 32px;
    overflow: hidden;
    padding-right: 20px;
    text-align: right;
    text-overflow: ellipsis;
    white-space: nowrap;
    width: 100px;
}
.bt-input-text {
    border: 1px solid #ccc;
    height: 30px;
    line-height: 30px;
    padding-left: 5px;
    border-radius: 2px;
    -webkit-transition: border-color ease-in-out .15s, -webkit-box-shadow ease-in-out .15s;
    -o-transition: border-color ease-in-out .15s, box-shadow ease-in-out .15s;
    transition: border-color ease-in-out .15s, box-shadow ease-in-out .15s;
}
.bt-form-submit-btn {
    background: #f6f8f8;
    border-top: 1px solid #edf1f2;
    bottom: 0;
    left: 0;
    padding: 8px 20px 10px;
    position: absolute;
    text-align: right;
    width: 100%;
}
.bt-form-submit-btn .btn:first-child {
    margin-right: 4px;
}
.btn-danger, .btn-danger:focus {
    background-color: #d15b47!important;
    border-color: #d15b47;
}
.btn {
    vertical-align: inherit;
}
.btn-success {
    color: #fff;
    background-color: #20a53a;
    border-color: #20a53a;
}
.btn-group-sm>.btn, .btn-sm {
    padding: 5px 10px;
    font-size: 12px;
    line-height: 1.5;
    border-radius: 3px;
}

span.input-icon {
    display: inline-block;
}

.input-icon {
    position: relative;
    line-height: 20px;
}
.input-icon>input {
    padding-left: 24px;
    padding-right: 6px;
}
.input-icon>[class*=icon-] {
    padding: 0 3px;
    z-index: 2;
    position: absolute;
    top: 1px;
    bottom: 1px;
    left: 3px;
    line-height: 24px;
    display: inline-block;
    color: #909090;
    font-size: 16px;
}

在这里插入图片描述

源码下载：https://download.youkuaiyun.com/download/lanquankk/11491974

#自定义SQL返回json
arry = models.BookInfo.objects.raw('select id,name,price,createdate from tb_books')#RawQuerySet
alist = []
for item in list(arry):
    alist.append({"id":item.id,"name":item.name,"price":item.price})
print(alist)