Django学习日记-06新url多对多表添加 编辑操作
所谓的多对多表添加操作
-实际上和学sql语句时的两个连表操作类似 两个LEFT JOIN ON 然后select出想要的数据即可
class_list = sqlhelp.get_all("SELECT teacher.id,teacher.`name`,class.`title` FROM teacher2class LEFT JOIN teacher ON teacher.`id`=teacher2class.`teacher_id`LEFT JOIN class ON class.`id`=teacher2class.`class_id`",[])
-为了使数据呈现更加的美观，用对字典元素操作 出理想的HTML页面
class_list = [ {'id': 1, 'name': '李白', 'title': 'A班'}, {'id': 1, 'name': '李白', 'title': 'C班'}, {'id': 2, 'name': '杜甫', 'title': 'A班'}, {'id': 2, 'name': '杜甫', 'title': 'E32班'}, {'id': 3, 'name': '王安石', 'title': '2D班'}, {'id': 4, 'name': '辛弃疾', 'title': 'F班'}, {'id': 5, 'name': '白居易', 'title': 'B班'}, {'id': 5, 'name': '白居易', 'title': 'E32班'} result = [] for row in class_list: id = row['id'] if id in result: result[id][''title].append(row['title']) else: result[id] = {'id':row['id'],'name':row['name'],'title':[row['title']]} for row in result.values(): print(row)
-在<a>标签中加入超链接，编写add_teacher.html页面
注意下方循环选择下拉框的写法
multiple为多选
<p>任教班级：
<select name="class_id" multiple>
{% for i in result %}
<option value="{{ i.id }}">{{ i.title }}</option>
{% endfor %}
</select>
</p>
-编写add_teacher函数 ， 由于select是多选 索引class_id用response.POST.getlist.
def add_teacher(request): if request.method == 'GET': result= sqlhelp.get_all('select id,title from class ', [ ] ) return render(request,'add_teacher.html',{'result':result}) else: name =request.POST.get('name') class_id = request.POST.getlist('class_id') teacher_id = obj.last_row_id('insert into teacher(name) values (%s)',[name, ]) 多次链接 多次提交 # for cls_id in class_id: # sqlhelp.get_commit('insert into teacher2class(teacher_id,class_id) values (%s,%s)',[teacher_id,cls_id,])
-多次链接多次提交浪费资源时间，可通过编写sql类简化继承函数
__init__ 使用时自动调用 object是对象
class SqlHelper(object): def __init__(self): self.connect() def connect(self): self.conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database='day6') self.cursor =self.conn.cursor(cursor=pymysql.cursors.DictCursor) def get_one(self,sql,list): self.cursor.execute(sql,list) result = self.cursor.fetchone() return result def get_all(self,sql,list): self.cursor.execute(sql,list) ressult = self.cursor.fetchall() return ressult def get_commit(self,sql,list): self.cursor.execute(sql,list) self.conn.commit() def last_row_id(self,sql,list): self.cursor.execute(sql,list) self.conn.commit() return self.cursor.lastrowid def multiple_get_commit(self,sql,list): self.cursor.executemany(sql,list) self.conn.commit() def close(self): self.cursor.close() self.conn.close()
-一次链接 多次提交
# 一次链接 多次提交 # for cls_id in class_id: # obj.get_commit('insert into teacher2class(teacher_id,class_id) values (%s,%s)',[teacher_id,cls_id,]) # obj.close()
-一次链接 一次提交
data_list = [ ] for cls_id in class_id: temp = (teacher_id,cls_id,) data_list.append(temp) obj.multiple_get_commit('insert into teacher2class(teacher_id,class_id) values (%s,%s)',data_list) obj.close() return redirect('/teacher/')
多对多表的编辑操作
-在<a>标签添加超链接 路由系统中添加url
-在views目录中写入edit_teacher函数
首先是method=='GET' 注意要联想之后的html页面编写内容
def edit_teacher(request): if request.method == 'GET': id = request.GET.get('id') obj = sqlhelp.SqlHelper() #填充老师的姓名 和 id teacher_list = obj.get_one("select id,name from teacher where id = %s", [id, ]) #抓取老师任职的课程ID class_id_list = obj.get_all('select class_id from teacher2class where teacher_id= %s',[id, ]) #建字典对象转化为列表形式 temp = [] for itme in class_id_list: temp.append(itme['class_id']) #抓取全部课程 class_list = obj.get_all('select id,title from class ',[]) obj.close() return render(request, 'edit_teacher.html', { 'teacher_list':teacher_list, 'class_id_list':temp, 'class_list':class_list
-编写html页面
注意点是<option >里面的参数是value xx="{{xxx}}"！value xx="{{xxx}}"！ value xx="{{xxx}}"！ 不是 id xx=“{{xx}}”
两次循环可以对的正确的课程进行筛选 有 selected是标记出正确的选择 multiple是多项选择
<body>
<h1>修改编辑</h1>
<form method="POST" action="/edit_teacher/?id={{ teacher_list.id }}">
<p>
老师名称：<input type="text" name="name" value="{{ teacher_list.name }}">
</p>
<p>老师教职班级：
<select name="class_id" multiple size="10">
{% for item in class_list %}
{% if item.id in class_id_list %}
<option value="{{ item.id }}" selected>{{ item.title }}</option>
{% else %}
<option value="{{ item.id }}" >{{ item.title }}</option>
{% endif %}
{% endfor %} }·
</select>
</p>
<p>
<input type="submit" value="提交">
</p>
</form>
</body>
-最后是method=='POST'部分
上面出现过可以填写的参数都可以通过xx = response.POST.get （'xx'）获取
class_id 是多项选择 所以用getlist
对于不熟悉的元组操作 要多多练习
else: id = request.GET.get('id') name = request.POST.get('name') class_id = request.POST.getlist('class_id') obj = sqlhelp.SqlHelper() obj.get_commit('update teacher set name =%s where id=%s ',[name,id ]) #对于更新 把旧的删除 插入新数据即可 obj.get_commit('delete from teacher2class where teacher_id =%s', [id, ]) #对于多种参数 利用multple对元组数据进行操作 data_list = [] for cls_id in class_id: temp = (id, cls_id,) data_list.append(temp) print(data_list) obj.multiple_get_commit('insert into teacher2class(teacher_id,class_id) values (%s,%s)', data_list) obj.close() # return HttpResponse('...') return redirect('/teacher/')
