原理暂时没法说明白,菜鸟一个
可以从这里学习一下
http://www.pythondoc.com/flask-sqlalchemy/quickstart.html
数据库
骨架
layout.html
<!doctype html>
<html>
<head>
<title>{% block title %}{% endblock %} </title>
<link rel="stylesheet" href="/static/css/bootstrap.min.css" type="text/css" />
<script type="text/javascript" src="/static/js/jquery-3.4.1.min.js"></script>
<script type="text/javascript" src="/static/js/bootstrap.min.js"></script>
</head>
<body>
{% block content %}{% endblock %}
</body>
</html>
mysql.py
from flask import Flask,request,render_template
import pymysql
app=Flask(__name__)
@app.route('/mysql',methods=['POST','GET'])
def mysql():
students=getStudents()
return render_template('mysql.html',student=students)
def getStudents():
# 打开数据库连接 主机地址 用户名 密码 数据库
db = pymysql.Connect("localhost", "root", "ZJM199933","flask")
cursor=db.cursor()
sql="select * from student"
cursor.execute(sql)
rows=cursor.fetchall()
db.close()
return rows
if __name__=='__main__':
app.run('127.0.0.1',5002,True)
mysql.html
{% extends "layout.html" %}
{% block title %}猪圈{% endblock %}
{% block content %}
<div class="container">
<table border="1" style="width: 600px;height: 100px">
<thead><th>ID</th><th>name</th><th>age</th></thead>
<tbody>
{% for s in student %}
<tr><td>{{ s[0] }}</td><td>{{ s[1] }}</td><td>{{ s[2] }}</td></tr>
{% endfor %}
</tbody>
</table>
</div>
{% endblock %}
结果:
在前端进行数据库的查询、删除、编辑、添加
骨架layout.html不变
mysql.html
{% extends "layout.html" %}
{% block title %}猪圈{% endblock %}
{% block content %}
<div class="container">
<form class="form" action="/searchstudent" method="post">
<input name="name" >
<button type="submit">提交</button>
</form>
</div>
<div class="container">
<table border="1" style="width: 600px;height: 100px">
<thead><th>ID</th><th>name</th><th>age</th><th>操作</th></thead>
<tbody>
{% for s in students %}
<tr><td>{{ s[0] }}</td><td>{{ s[1] }}</td><td>{{ s[2] }}</td>
<td><a href="/deletestudent/{{s[0]}}">删除</a>
<a href="/editstudent/{{s[0]}}">编辑</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
<div class="container">
<form class="form" action="/addstudent" method="post">
<input name="id" type="hidden" value="{{id}}">
<input name="name" value="{{name}}" >
<input name="age" value="{{age}}">
<button type="submit">提交</button>
</form>
</div>
{% endblock %}
mysql.py
from flask import Flask,request,render_template
import pymysql
app=Flask(__name__)
@app.route('/mysql',methods=['POST','GET'])
def mysql():
students=getStudents()
return render_template('mysql.html',students=students,editStudent={})
@app.route('/deletestudent/<int:id>', methods=['GET'])
def deletestudent(id):
removeStudent(id)
students = getStudents()
return render_template('mysql.html',students=students)
@app.route('/searchstudent', methods=['POST'])
def searchstudent():
name = request.form.get("name")
students = searchStudents(name)
return render_template('mysql.html',students=students)
@app.route('/addstudent', methods=['POST'])
def addstudent():
name = request.form.get("name")
age = request.form.get("age")
id = request.form.get("id")
if id:
updateStudent(id,name,age)
else:
addStudent(name,age)
students = getStudents()
return render_template('mysql.html',students=students)
@app.route('/editstudent/<int:id>', methods=['GET'])
def editstudent(id):
students = getStudents()
editStudent = getStudent(id)
name = editStudent[1]
age = editStudent[2]
return render_template('mysql.html',students=students,id = id,name=name,age=age)
def getStudent(id):
db = pymysql.connect("localhost", "root", "ZJM199933", "flask")
cursor = db.cursor()
cursor.execute("select * from student where id="+str(id))
row = cursor.fetchone()
db.close()
return row
def getStudents():
# 打开数据库连接 主机地址 用户名 密码 数据库
db = pymysql.Connect("localhost", "root", "ZJM199933","flask")
cursor=db.cursor()
sql="select * from student"
cursor.execute(sql)
rows=cursor.fetchall()
db.close()
return rows
#查询
def searchStudents(name):
# 打开数据库连接 主机地址 用户名 密码 数据库
db = pymysql.Connect("localhost", "root", "ZJM199933","flask")
cursor = db.cursor()
cursor.execute("select * from student where name like '%" + name + "%'")
rows = cursor.fetchall()
for row in rows:
print(row[1])
db.close()
return rows
#添加
def addStudent(name,age):
db = pymysql.Connect("localhost", "root", "ZJM199933", "flask")
cursor = db.cursor()
sql = "insert into student (name,age) VALUES ('"+name+"',"+age+")"
cursor.execute(sql)
db.commit()#提交数据库
db.close()
#修改
def updateStudent(id,name,age):
db = pymysql.Connect("localhost", "root", "ZJM199933", "flask")
cursor = db.cursor()
cursor.execute("update student set name='" + name + "',age=" + str(age) + " where id=" + str(id))
db.commit()
db.close()
#删除
def removeStudent(id):
db = pymysql.Connect("localhost", "root", "ZJM199933", "flask")
cursor = db.cursor()
cursor.execute("delete from student where id=" + str(id))
db.commit()
db.close()
if __name__=='__main__':
app.run('127.0.0.1',5002,True)
结果
查询
删除
添加:
编辑
高级版本,是上面的改进
1、DB.py是连接数据库,并有获取,删除,查询和通过id获得数据 的方法
2、student.py是继承DB.py ,并添加了 修改,添加数据 的方法
3、layout.html 是前端的骨架
4、mysql.html在layout.html的骨架上进行操作web页面,将数据库的内容和操作展示在web
5、mysql.py 是flask ,通过路由将数据库的数据传达到前端
6、static是 web所需要的插件工具
代码
DB.py
import pymysql
class DB():
def __init__(self,table="", host='localhost', port=3306, db='flask', user='root', passwd='ZJM199933', charset='utf8'):
# 建立连接
self.conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset=charset)
# 创建游标,操作设置为字典类型
self.cur = self.conn.cursor(cursor = pymysql.cursors.DictCursor)
def getTable(self):
return ""
def getAll(self):
sql="select * from " + self.getTable()
self.cur.execute(sql)
rows=self.cur.fetchall()
self.conn.close()
return rows
def remove(self,id):
sql="delete from "+self.getTable()+" where id=" + str(id)
self.cur.execute(sql)
self.conn.commit()
self.conn.close()
def search(self,field,keywords):
sql="select * from "+self.getTable()+" where "+field+" like '%" + keywords+ "%'"
self.cur.execute(sql)
rows = self.cur.fetchall()
self.conn.close()
return rows
def get(self,id):
sql="select * from "+self.getTable()+" where id=" + str(id)
self.cur.execute(sql)
row = self.cur.fetchone()
self.conn.close()
return row
student.py
import pymysql
from model.DB import DB
class student(DB):
def getTable(self):
return "student"
def add(self, name, age):
sql = "insert into student (name,age) values('" + name + "'," + str(age) + ")"
self.cur.execute(sql)
self.conn.commit()
self.conn.close()
def update(self, id, name, age):
sql = "update student set name='" + name + "',age=" + str(age) + " where id=" + str(id)
self.cur.execute(sql)
self.conn.commit()
self.conn.close()
mysql.py
from flask import Flask,request,render_template
import pymysql
from model.student import student
app=Flask(__name__)
@app.route('/mysql',methods=['POST','GET'])
def mysql():
students = student().getAll()
return render_template('mysql.html',students=students,editStudent={})
@app.route('/deletestudent/<int:id>', methods=['GET'])
def deletestudent(id):
students = student().remove(id)
students = student().getAll()
return render_template('mysql.html',students=students)
@app.route('/searchstudent', methods=['POST'])
def searchstudent():
field=request.form.get("field")
keywords= request.form.get("keywords")
students = student().search(field,keywords)
return render_template('mysql.html',students=students)
@app.route('/addstudent', methods=['POST'])
def addstudent():
name = request.form.get("name")
age = request.form.get("age")
id = request.form.get("id")
if id:
student().update(id,name,age)
else:
student().add(name,age)
students = student().getAll()
return render_template('mysql.html',students=students)
@app.route('/editstudent/<int:id>', methods=['GET'])
def editstudent(id):
students = student().getAll()
editStudent = student().get(id)
name = editStudent['name']
age = editStudent['age']
return render_template('mysql.html',students=students,id = id,name=name,age=age)
if __name__=='__main__':
app.run('127.0.0.1',5002,True)
骨架layout.html
<!doctype html>
<html>
<head>
<title>{% block title %}{% endblock %} </title>
<link rel="stylesheet" href="/static/css/bootstrap.min.css" type="text/css" />
<script type="text/javascript" src="/static/js/jquery-3.4.1.min.js"></script>
<script type="text/javascript" src="/static/js/bootstrap.min.js"></script>
</head>
<body>
{% block content %}{% endblock %}
</body>
</html>
菜鸟教程https://www.runoob.com/html/html-tutorial.html
mysql.html
{% extends "layout.html" %}
{% block title %}猪圈{% endblock %}
{% block content %}
<div class="container">
<form class="form" action="/searchstudent" method="post">
field <input name="field" >
keywords <input name="keywords" >
<button type="submit">提交</button>
</form>
</div>
<br>
<div class="container">
<table border="1" style="width: 600px;height: 100px">
<thead><th>ID</th><th>name</th><th>age</th><th>操作</th></thead>
<tbody>
{% for s in students %}
<tr><td>{{ s['id'] }}</td><td>{{ s['name'] }}</td><td>{{ s['age'] }}</td>
<td><a href="/deletestudent/{{s['id']}}">删除</a>
<a href="/editstudent/{{s['id']}}">编辑</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
<br>
<div class="container">
<form class="form" action="/addstudent" method="post">
<input name="id" type="hidden" value="{{id}}">
<input name="name" value="{{name}}" >
<input name="age" value="{{age}}">
<button type="submit">提交</button>
</form>
</div>
{% endblock %}
原数据,记得启动数据库
运行结果:
查询:
提交后
删除:
删后
添加
编辑(修改)
点击后
改
提交后