所谓的多对多表操作
-实际上和学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/‘)