上一篇 Django学习小记3-学员管理数据库① 我们提到的是最基本的单表操作。实际还有一对多、多对多的案例。
多表涉及到连表操作。
理解：Form表单提交，页面会刷新。
1. 查看
mysql> SELECT * FROM student LEFT JOIN class ON student.`class_ID`=class.`id`; +----+-----------+----------+------+-----------------+ | id | name | class_ID | id | title | +----+-----------+----------+------+-----------------+ | 1 | 张英杰 | 9 | 9 | 全栈12期1222 | +----+-----------+----------+------+-----------------+ 1 row in set (0.00 sec)
mysql> SELECT student.id,student.name,class.title FROM student LEFT JOIN class ON student.`class_ID`=cllass.`id`;
+----+-----------+-----------------+
| id | name | title |
+----+-----------+-----------------+
| 1 | 张英杰 | 全栈12期1222 |
+----+-----------+-----------------+
1 row in set (0.00 sec)
所以在数据库语句就可以替换成：
#展示学生 def students(request): import pymysql conn = pymysql.connect(host='10.0.4.x', port=3306, user='root', passwd='123@#123', db='oldboys',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("SELECT student.id,student.name,class.title FROM student LEFT JOIN class ON student.`class_ID`=class.`id`") student_list = cursor.fetchall() cursor.close() conn.close() return render(request,'students.html',{'student_list':student_list})
2.添加
在页面让用户添加所属班级时，可以设置一个下拉框，用<select><option>标签实现：add_student.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>添加学生</title> </head> <body> <h1>增加学生</h1> <form method="POST" action="/add_student/"> <p>学生姓名<input type="text" name="name"/></p> <p>所属班级： <select name="class_id"> {% for row in class_list %} <option value="{{ row.id }}">{{ row.title }}</option>> {% endfor %} </select> </p> <input type="submit" value="提交"> </form> </body> </html>
注意：在select标签加入了class_id属性，这样提交的学生name作为key，class_id作为value来提交。
views.py里添加add_student函数：
... #添加学生，下拉框 def add_student(request): if request.method == "GET":
#由数据库提取班级名称填充value到add_student.html的下拉框 import pymysql conn = pymysql.connect(host='10.0.4.x', port=3306, user='root', passwd='123!@#123', db='oldboys',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("select id,title from class") class_list = cursor.fetchall() cursor.close() conn.close() return render(request,'add_student.html',{'class_list':class_list}) else: name = request.POST.get('name') class_id = request.POST.get('class_id') #提交至数据库 import pymysql conn = pymysql.connect(host='10.0.4.x', port=3306, user='root', passwd='123!@#123', db='oldboys',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("insert into student(name,class_id) values(%s,%s)",[name,class_id]) conn.commit() cursor.close() conn.close() return redirect('/students/')
其实这样每次都去操作数据库太繁琐了，可以把他们封装在utils目录下起名sqlheper.py
sqlheper.py
import pymysql #fetchall def get_list(sql,args): conn = pymysql.connect(host='10.0.4.x', port=3306, user='root', passwd='123!@#123', db='oldboys',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute(sql,args) result = cursor.fetchall() cursor.close() conn.close() return result #fetchone def get_one(sql,args): conn = pymysql.connect(host='10.0.4.x', port=3306, user='root', passwd='123!@#123', db='oldboys',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute(sql,args) result = cursor.fetchone() cursor.close() conn.close() return result #commit def modify(sql,args): conn = pymysql.connect(host='10.0.4.x', port=3306, user='root', passwd='123!@#123', db='oldboys',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute(sql,args) conn.commit() cursor.close() conn.close()
Ajax (jQuery)
“页面不刷新的情况下偷偷提交数据”