Python Flask SQLite数据库开发实例

Flask可以利用python的SQLite3模块来创建数据库Web应用程序。在本教程的这一部分中, 我们将创建一个CRUD(创建-读取-更新-删除)应用程序。

由于我们已经详细介绍了python应用程序如何与SQLite数据库进行交互, 因此要修改此概念, 请访问链接：Python SQLite。

CRUD在Python Flask中的应用

在这里, 我们将使用管理员可以与之交互的Flask脚本来管理SQLite数据库中的员工信息。为此, 数据库employee.db包含相关的表, 而表Employees包含有关雇员的信息。

首先, 让我们使用以下python脚本在SQLite中创建数据库employee.DB和表employee。

EmoloyeeDB.py

import sqlite3

con = sqlite3.connect("employee.db")
print("Database opened successfully")

con.execute("create table Employees (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, address TEXT NOT NULL)")

print("Table created successfully")

con.close()

为了在Python Flask中构建一个CRUD应用程序, 我们必须专注于视图函数(获取输入)和控制器(将数据保存到数据库中)。

让我们看一下视图函数：与URL(/)关联的index()。它呈现一个模板index.html。

@app.route("/")
def index():
	return render_template("index.html");

以下HTML模板(index.html)被视为我们应用程序的主页。它提供了链接, 我们可以使用这些链接来添加, 查看和删除数据库中存储的数据。

index.html

<!DOCTYPE html>
<html>
<head>
	<title>home</title>
</head>
<body>
	<h2>Hi, welcome to the website</h2>
	<a href="/add">Add Employee</a><br><br>
	<a href ="/view">List Records</a><br><br>
	<a href="/delete">Delete Record</a><br><br>
</body>
</html>

与URL(/ add)关联的视图函数add()呈现下面给出的模板add.html。它提供了用于输入员工信息的表格。

add.html

<!DOCTYPE html>
<html>
<head>
	<title>Add Employee</title>
</head>
<body>
	<h2>Employee Information</h2> 
	<form action = "/savedetails" method="post">
	<table>
		<tr><td>Name</td><td><input type="text" name="name"></td></tr>
		<tr><td>Email</td><td><input type="email" name="email"></td></tr>
		<tr><td>Address</td><td><input type="text" name="address"></td></tr>
		<tr><td><input type="submit" value="Submit"></td></tr>
	</table>
	</form>
</body>
</html>

管理员输入的所有详细信息都会发布到与功能saveDetails()关联的URL / savedetails。下面给出了函数saveDetails(), 其中包含用于提取管理员输入的数据并将该数据保存到员工表中的代码。

它还会根据成功插入数据或发生某些错误的情况来生成消息。

@app.route("/savedetails", methods = ["POST", "GET"])
def saveDetails():
	msg = "msg"
	if request.method == "POST":
		try:
			name = request.form["name"]
			email = request.form["email"]
			address = request.form["address"]
			with sqlite3.connect("employee.db") as con:
				cur = con.cursor()
				cur.execute("INSERT into Employees (name, email, address) values (?, ?, ?)", (name, email, address))
				con.commit()
				msg = "Employee successfully Added"
		except:
			con.rollback()
			msg = "We can not add the employee to the list"
		finally:
			return render_template("success.html", msg = msg)
			con.close()

它呈现一个模板success.html以向管理员显示消息。它还包含一个链接, 以查看用户输入的记录。

success.html

<!DOCTYPE html>
<html>
<head>
	<title>save details</title>
</head>
<body>
	<h3>Hi Admin, {{msg}}</h3>
	<a href="/view">View Employees</a>
</body>
</html>

函数delete()与URL / delete相关联。它呈现一个HTML模板delete.html, 该模板将格式提供给管理员, 提示输入要删除其记录的Employee_Id。它还包含指向/ view URL的链接, 该链接向管理员显示所有记录。

HTML模板delete.html如下所示。

delete.html

<!DOCTYPE html>
<html>
<head>
	<title>delete record</title>
</head>
<body>

	<h3>Remove Employee from the list</h3>

<form action="/deleterecord" method="post">
Employee Id <input type="text" name="id">
<input type="submit" value="Submit">
</form>
</body>
</html>

管理员输入的Employee_Id将发布到URL / deleterecord, 其中包含建立与数据库的连接的python代码, 然后删除指定Employee ID的所有记录。 URL / deleterecord与下面提供的函数deleterecord()相关联。

@app.route("/deleterecord", methods = ["POST"])
def deleterecord():
	id = request.form["id"]
	with sqlite3.connect("employee.db") as con:
		try:
			cur = con.cursor()
			cur.execute("delete from Employees where id = ?", id)
			msg = "record successfully deleted"
		except:
			msg = "can't be deleted"
		finally:
			return render_template("delete_record.html", msg = msg)

函数deleterecord()会根据情况成功删除数据还是发生某些错误, 生成一条消息。它呈现一个HTML模板delete_record.html以向管理员显示消息。

delete_record.html

<!DOCTYPE html>
<html>
<head>
	<title>delete record</title>
</head>
<body>
<h3>{{msg}}</h3>
<a href="/view">View List</a>
</body>
</html>

模板delete_record.html包含指向URL / view的链接, 该链接显示了Admin的Employee记录。

它与函数view()关联, 该函数建立与数据库的连接, 获取所有信息, 并将该信息传递给HTML模板view.html以在客户端浏览器上显示。

app.route("/view")
def view():
	con = sqlite3.connect("employee.db")
	con.row_factory = sqlite3.Row
	cur = con.cursor()
	cur.execute("select * from Employees")
	rows = cur.fetchall()
	return render_template("view.html", rows = rows)

下面给出了显示浏览器上所有信息的HTML模板view.html。

view.html

<!DOCTYPE html>
<html>
<head>
	<title>List</title>
</head>
<body>

<h3>Employee Information</h3>
<table border=5>
	<thead>
		<td>ID</td>
		<td>Name</td>
		<td>Email</td>
		<td>Address</td>
	</thead>
	
	{% for row in rows %}
	
		<tr>
			<td>{{row["id"]}}</td>
			<td>{{row["name"]}}</td>
			<td>{{row["email"]}}</td>
			<td>{{row["address"]}}</td>
		</tr>
	
	{% endfor %}
</table>
<br><br>

<a href="/">Go back to home page</a>

</body>
</html>

完整的python脚本在下面给出。

crud.py

from flask import *
import sqlite3

app = Flask(__name__)

@app.route("/")
def index():
	return render_template("index.html");

@app.route("/add")
def add():
	return render_template("add.html")

@app.route("/savedetails", methods = ["POST", "GET"])
def saveDetails():
	msg = "msg"
	if request.method == "POST":
		try:
			name = request.form["name"]
			email = request.form["email"]
			address = request.form["address"]
			with sqlite3.connect("employee.db") as con:
				cur = con.cursor()
				cur.execute("INSERT into Employees (name, email, address) values (?, ?, ?)", (name, email, address))
				con.commit()
				msg = "Employee successfully Added"
		except:
			con.rollback()
			msg = "We can not add the employee to the list"
		finally:
			return render_template("success.html", msg = msg)
			con.close()

@app.route("/view")
def view():
	con = sqlite3.connect("employee.db")
	con.row_factory = sqlite3.Row
	cur = con.cursor()
	cur.execute("select * from Employees")
	rows = cur.fetchall()
	return render_template("view.html", rows = rows)


@app.route("/delete")
def delete():
	return render_template("delete.html")

@app.route("/deleterecord", methods = ["POST"])
def deleterecord():
	id = request.form["id"]
	with sqlite3.connect("employee.db") as con:
		try:
			cur = con.cursor()
			cur.execute("delete from Employees where id = ?", id)
			msg = "record successfully deleted"
		except:
			msg = "can't be deleted"
		finally:
			return render_template("delete_record.html", msg = msg)

if __name__ == "__main__":
	app.run(debug = True)

在终端上使用以下命令运行python脚本EmployeeDB.py创建数据库和Employees表。