首页 > 代码库 > Python+Django+SAE系列教程13-----MySQL记录的添\删\改

Python+Django+SAE系列教程13-----MySQL记录的添\删\改

建立了数据库后,我们就来做一个简单的表(person_classroom)的添加、删除、修改的操作。

首先我们建立一个添加的页面的模板Classroom_Add.html(添加的表单)并把它放在Bidding\templates\person中:

Classroom_Add.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
    <title>数据库操作简单表的添加</title>
</head>
<body>
    <h1>这里是Classroom的添加页面</h1>
    {% if error %}
        <p style="color: red;">请输入班级名称和导师姓名</p>
    {% endif %}
   <form action="" method="get">
        <table border="1" cellpadding="10">
          <tr>
            <td align="center">项目</td>
            <td align="center">内容</td>
          </tr>
          <tr>
            <td align="right">班级名称:</td>
            <td><input type="text" name="name"></td>
          </tr>
          <tr>
            <td align="right">导师姓名:</td>
            <td><input type="text" name="tutor"></td>
          </tr>
          <tr>
            <td colspan="2"><input type="submit" value=http://www.mamicode.com/"添加">>

Classroom_Add_results.html

<html>
<head>
    <title>查询用户结果页</title>
</head>
<body>
    <table border="1" cellpadding="5"><tr>
      <td>班级:{{name}}添加成功 !</td></tr>
      <tr>
        <td><a href=http://www.mamicode.com/"http://127.0.0.1:8000/ClassRoom/add/">点击返回>上面的 这个文件时添加后的结果页。

然后建立对应的view,我们修改person/views.py 文件

Views.py

# -*- coding: utf-8 -*-
from django.shortcuts import render_to_response
from django.db import connection,transaction
from person.models import *

def ClassroonAdd(request):
    if ‘name‘ in request.GET and request.GET[‘name‘] and ‘tutor‘ in request.GET and request.GET[‘tutor‘]:
        name = request.GET[‘name‘]
        tutor = request.GET[‘tutor‘]

        cursor=connection.cursor()
        sql=‘insert into person_classroom (name,tutor) values (\‘‘+name+‘\‘,\‘‘+tutor+‘\‘)‘
        cursor.execute(sql)
        transaction.commit_unless_managed()
        cursor.close()
        
        return render_to_response(‘person/Classroom_Add_results.html‘,
            {‘name‘: name})
    else:
        return render_to_response(‘person/Classroom_Add.html‘, {‘error‘: True})

在修改一下urls.py文件:

from django.conf.urls import patterns, include, url


# Uncomment the next two lines to enable the admin:
# from django.contrib import admin
# admin.autodiscover()

urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘Bidding.views.home‘, name=‘home‘),
    # url(r‘^Bidding/‘, include(‘Bidding.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
    # url(r‘^admin/‘, include(admin.site.urls)),
    url(r‘^hello/$‘, ‘Bidding.views.hello‘),
    url(r‘^time/$‘, ‘Bidding.views.current_datetime‘),
    url(r‘^time/plus/(\d{1,2})/$‘, ‘Bidding.views.hours_ahead‘),
    url(r‘^hello_base/$‘, ‘Bidding.views.hello_base‘),
    url(r‘^request_test/$‘, ‘Bidding.views.request_test‘),
    url(r‘^UsersSearch/$‘, ‘Bidding.Users.views.search_form‘),
    url(r‘^search/$‘, ‘Bidding.Users.views.search‘),
    url(r‘^ClassRoom/add/$‘, ‘person.views.ClassroonAdd‘),
)

这时我们的添加就做好了,访问一下classroom/add这个 页面,就可以看到结果了。




不过上面我们所说的办法是执行一个原始的sql语句,这个方式其实并不是Django推荐的,我们可以直接使用models操作数据库的方法,改造一下ClassroomAdd这个视图:


def ClassroonAdd(request):
    if ‘name‘ in request.GET and request.GET[‘name‘] and ‘tutor‘ in request.GET and request.GET[‘tutor‘]:
        name = request.GET[‘name‘]
        tutor = request.GET[‘tutor‘]
        c = ClassRoom(name=name,tutor=tutor)
        c.save()
        
        return render_to_response(‘person/Classroom_Add_results.html‘,
            {‘name‘: name})
    else:
        return render_to_response(‘person/Classroom_Add.html‘, {‘error‘: True})

这样的方法即简单,有不用我们很多sql的语法,并且最重要的是如果更换数据库类型(sqlserver->oracle),也不会因为受sql语法不一致的影响。

在接下来,我们来做一个列表页,把数据库中的Classroom表的记录以一个表格的形式显示出来。还是从模板先入手,建立一个Classroom_List.html,放入Bidding\templates\person文件夹下:

Classroom_List.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
    <title>数据库操作简单表的添加</title>
</head>
<body>
    <h1>这里是Classroom的管理页面</h1>
        <table border="1" cellpadding="10">
          <tr>
            <td align="center">序号</td>
            <td align="center">班级名称</td>
            <td align="center">导师姓名</td>
          </tr>
          {% for myclass in ClassroonList%}
          <tr>
            <td align="right">{{ myclass.id }}</td>
            <td align="right">{{ myclass.name }}</td>
            <td align="right">{{ myclass.tutor }}</td>
          </tr>
          {% endfor %} 
        </table>
  
</body>
</html>

添加视图:

# -*- coding: utf-8 -*-
from django.shortcuts import render_to_response
from django.db import connection,transaction
from person.models import *

def ClassroonAdd(request):
    if ‘name‘ in request.GET and request.GET[‘name‘] and ‘tutor‘ in request.GET and request.GET[‘tutor‘]:
        name = request.GET[‘name‘]
        tutor = request.GET[‘tutor‘]

        cursor=connection.cursor()
        sql=‘insert into person_classroom (name,tutor) values (\‘‘+name+‘\‘,\‘‘+tutor+‘\‘)‘
        cursor.execute(sql)
        transaction.commit_unless_managed()
        cursor.close()
        
        return render_to_response(‘person/Classroom_Add_results.html‘,
            {‘name‘: name})
    else:
        return render_to_response(‘person/Classroom_Add.html‘, {‘error‘: True})


def ClassroonAdd(request):
    if ‘name‘ in request.GET and request.GET[‘name‘] and ‘tutor‘ in request.GET and request.GET[‘tutor‘]:
        name = request.GET[‘name‘]
        tutor = request.GET[‘tutor‘]
        c = ClassRoom(name=name,tutor=tutor)
        c.save()
        
        return render_to_response(‘person/Classroom_Add_results.html‘,
            {‘name‘: name})
    else:
        return render_to_response(‘person/Classroom_Add.html‘, {‘error‘: True})





def ClassroonList(request):
        cursor=connection.cursor()
        sql=‘select id,name,tutor from person_classroom‘
        ClassroonList=ClassRoom.objects.raw(sql)
        return render_to_response(‘person/Classroom_List.html‘,
            {‘ClassroonList‘: ClassroonList})

配置urls.py:

from django.conf.urls import patterns, include, url


# Uncomment the next two lines to enable the admin:
# from django.contrib import admin
# admin.autodiscover()

urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘Bidding.views.home‘, name=‘home‘),
    # url(r‘^Bidding/‘, include(‘Bidding.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
    # url(r‘^admin/‘, include(admin.site.urls)),
    url(r‘^hello/$‘, ‘Bidding.views.hello‘),
    url(r‘^time/$‘, ‘Bidding.views.current_datetime‘),
    url(r‘^time/plus/(\d{1,2})/$‘, ‘Bidding.views.hours_ahead‘),
    url(r‘^hello_base/$‘, ‘Bidding.views.hello_base‘),
    url(r‘^request_test/$‘, ‘Bidding.views.request_test‘),
    url(r‘^UsersSearch/$‘, ‘Bidding.Users.views.search_form‘),
    url(r‘^search/$‘, ‘Bidding.Users.views.search‘),
    url(r‘^ClassRoom/add/$‘, ‘person.views.ClassroonAdd‘),
    url(r‘^ClassRoom/list/$‘, ‘person.views.ClassroonList‘),
)

如同上述讨论的一样,我们现在的视图执行的是一个原始的sql,现在我们需要用models来修改一下:

def ClassroonList(request):
        cursor=connection.cursor()
        ClassroonList=ClassRoom.objects.all()
        #ClassroonList=ClassRoom.objects.filter(name__icontains=‘大‘)
        return render_to_response(‘person/Classroom_List.html‘,
            {‘ClassroonList‘: ClassroonList})  

如果需要执行where或者order by等操作可以这样:

 ClassroonList=ClassRoom.objects.filter(name__icontains=‘‘).order_by(‘name’)

这里还有很多关于选择的内容以后我们逐渐会介绍到。

做完了列表页,我们在来做一个修改的页面,思路是这样的:在列表页中的每一行的后面添加一列“修改”按钮,点击按钮后跳转到修改页面,首先以此条记录的主键专递到修改页面,在修改页面中要先读取出数据库中的信息,然后点击确定按钮以后执行修改操作。

我们首先来修改这个管理页面的模板:

Classroom_List.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
    <title>数据库操作简单表的添加</title>
</head>
<body>
    <h1>这里是Classroom的管理页面</h1>
        <table border="1" cellpadding="10">
          <tr>
            <td align="center">序号</td>
            <td align="center">班级名称</td>
            <td align="center">导师姓名</td>
            <td align="center">操作</td>
          </tr>
          {% for myclass in ClassroonList%}
          <tr>
            <td align="right">{{ myclass.id }}</td>
            <td align="right">{{ myclass.name }}</td>
            <td align="right">{{ myclass.tutor }}</td>
            <td align="right"><input type="button" onClick="Modify({{ myclass.id }})" value=http://www.mamicode.com/"修改">>

建立一个Classroom_Modify.html模板,把它放在Bidding\templates\person文件夹下

Classroom_Modify.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
    <title>数据库操作简单表的修改</title>
</head>
<body>
    <h1>这里是Classroom--{{name}}的修改页面</h1>
    {% if error %}
        <p style="color: red;">请输入班级名称和导师姓名</p>
    {% endif %}
   <form action="" method="get">
        <table border="1" cellpadding="10">
          <tr>
            <td align="center">项目</td>
            <td align="center">内容</td>
          </tr>
          <tr>
            <td align="right">班级名称:</td>
            <td><input type="text" name="name" value=http://www.mamicode.com/"{{name}}">>

Classroom_Modify_results.html

<html>
<head>
    <title>查询用户结果页</title>
</head>
<body>
    <table border="1" cellpadding="5"><tr>
      <td align="center"> </td>
      <td align="center">修改前</td>
      <td align="center">修改后</td>
      </tr>
      <tr>
        <td align="right">班级名称:</td>
        <td align="right">{{old_name}}</td>
        <td align="right">{{new_name}}</td>
      </tr>
      <tr>
        <td align="right">导师姓名:</td>
        <td align="right">{{old_tutor}}</td>
        <td align="right">{{new_tutor}}</td>
      </tr>
      <tr>
        <td colspan="3" align="center">修改成功!</td>
      </tr>
      <tr>
        <td colspan="3" align="center"><a href=http://www.mamicode.com/list/">点击返回>

添加视图:

def ClassroonModify(request,id1):
    cursor=connection.cursor()
    sql=‘select id,name,tutor from person_classroom where id=‘+id1
    ClassroonList=ClassRoom.objects.raw(sql)
    old_name = ClassroonList[0].name
    old_tutor = ClassroonList[0].tutor
    if ‘name‘ in request.GET and request.GET[‘name‘] and ‘tutor‘ in request.GET and request.GET[‘tutor‘]:
        new_name = request.GET[‘name‘]
        new_tutor = request.GET[‘tutor‘]
        cursor=connection.cursor()
        sql=‘update person_classroom set name=\‘‘+new_name+‘\‘,tutor=\‘‘+new_tutor+‘\‘ where id=\‘‘+id1+‘\‘‘
        cursor.execute(sql)
        transaction.commit_unless_managed()
        cursor.close()
        return render_to_response(‘person/Classroom_Modify_results.html‘,
            {‘old_name‘: old_name,‘old_tutor‘:old_tutor,‘new_name‘:new_name,‘new_tutor‘:new_tutor})
    else:
        return render_to_response(‘person/Classroom_Modify.html‘, {‘error‘: True,‘id‘:id1,‘name‘:old_name,‘tutor‘:old_tutor})

编辑urls.py,这里面需要注意的是正则的写法,这个之前的章节已经说过了,这里我们可以再复习一遍:

from django.conf.urls import patterns, include, url


# Uncomment the next two lines to enable the admin:
# from django.contrib import admin
# admin.autodiscover()

urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘Bidding.views.home‘, name=‘home‘),
    # url(r‘^Bidding/‘, include(‘Bidding.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
    # url(r‘^admin/‘, include(admin.site.urls)),
    url(r‘^hello/$‘, ‘Bidding.views.hello‘),
    url(r‘^time/$‘, ‘Bidding.views.current_datetime‘),
    url(r‘^time/plus/(\d{1,2})/$‘, ‘Bidding.views.hours_ahead‘),
    url(r‘^hello_base/$‘, ‘Bidding.views.hello_base‘),
    url(r‘^request_test/$‘, ‘Bidding.views.request_test‘),
    url(r‘^UsersSearch/$‘, ‘Bidding.Users.views.search_form‘),
    url(r‘^search/$‘, ‘Bidding.Users.views.search‘),
    url(r‘^ClassRoom/add/$‘, ‘person.views.ClassroonAdd‘),
    url(r‘^ClassRoom/list/$‘, ‘person.views.ClassroonList‘),
    url(r‘^ClassRoom/modify/(\d+)/$‘, ‘person.views.ClassroonModify‘),
)

如同添加时候的问题,我们这里面使用的仍然是最原始的sql语句,我们同样可以给他修改成为model的方式:

def ClassroonModify(request,id1):
    cursor=connection.cursor()
    Classroon=ClassRoom.objects.get(id=id1)
    old_name = Classroon.name
    old_tutor = Classroon.tutor
    cursor.close()
    if ‘name‘ in request.GET and request.GET[‘name‘] and ‘tutor‘ in request.GET and request.GET[‘tutor‘]:
        new_name = request.GET[‘name‘]
        new_tutor = request.GET[‘tutor‘]
        Classroon.name=new_name
        Classroon.tutor=new_tutor
        Classroon.save()
        return render_to_response(‘person/Classroom_Modify_results.html‘,
            {‘old_name‘: old_name,‘old_tutor‘:old_tutor,‘new_name‘:new_name,‘new_tutor‘:new_tutor})
    else:
        return render_to_response(‘person/Classroom_Modify.html‘, {‘error‘: True,‘id‘:id1,‘name‘:old_name,‘tutor‘:old_tutor})

这样看起来是不是简便多了?我们打开 页面看看效果吧 :




接下来我们来做删除的功能,首先修改列表页的模板,加入一列删除按钮:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
    <title>数据库操作简单表的添加</title>
</head>
<body>
    <h1>这里是Classroom的管理页面</h1>
        <table border="1" cellpadding="10">
          <tr>
            <td align="center">序号</td>
            <td align="center">班级名称</td>
            <td align="center">导师姓名</td>
            <td align="center">操作</td>
          </tr>
          {% for myclass in ClassroonList%}
          <tr>
            <td align="right">{{ myclass.id }}</td>
            <td align="right">{{ myclass.name }}</td>
            <td align="right">{{ myclass.tutor }}</td>
            <td align="right"><input type="button" onClick="Modify({{ myclass.id }})" value=http://www.mamicode.com/"修改">>

Classroom_Delete_results.html:

<html>
<head>
    <title>查询用户结果页</title>
</head>
<body>
    <table border="1" cellpadding="5"><tr>
      <td>班级:{{name}}删除成功 !</td></tr>
      <tr>
        <td><a href=http://www.mamicode.com/"http://127.0.0.1:8000/ClassRoom/list/">点击返回>

修改视图:

def ClassroonDelete(request,id1):
    cursor=connection.cursor()
    Classroon=ClassRoom.objects.get(id=id1)
    old_name = Classroon.name
    Classroon.delete()
    ClassroonList=ClassRoom.objects.all()
    cursor.close()
  return render_to_response(‘person/Classroom_Delete_results.html‘,{‘name‘:old_name})

配置urls.py:

from django.conf.urls import patterns, include, url


# Uncomment the next two lines to enable the admin:
# from django.contrib import admin
# admin.autodiscover()

urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘Bidding.views.home‘, name=‘home‘),
    # url(r‘^Bidding/‘, include(‘Bidding.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
    # url(r‘^admin/‘, include(admin.site.urls)),
    url(r‘^hello/$‘, ‘Bidding.views.hello‘),
    url(r‘^time/$‘, ‘Bidding.views.current_datetime‘),
    url(r‘^time/plus/(\d{1,2})/$‘, ‘Bidding.views.hours_ahead‘),
    url(r‘^hello_base/$‘, ‘Bidding.views.hello_base‘),
    url(r‘^request_test/$‘, ‘Bidding.views.request_test‘),
    url(r‘^UsersSearch/$‘, ‘Bidding.Users.views.search_form‘),
    url(r‘^search/$‘, ‘Bidding.Users.views.search‘),
    url(r‘^ClassRoom/add/$‘, ‘person.views.ClassroonAdd‘),
    url(r‘^ClassRoom/list/$‘, ‘person.views.ClassroonList‘),
    url(r‘^ClassRoom/modify/(\d+)/$‘, ‘person.views.ClassroonModify‘),
    url(r‘^ClassRoom/delete/(\d+)/$‘, ‘person.views.ClassroonDelete‘),
)


到此,我们就做好了一个简单的表的添加、删除、修改的操作。