下面我将介绍如何使用 Flask 框架和 MySQL 数据库实现基本的 CRUD(创建、读取、更新、删除)操作。
首先确保已安装必要的 Python 包:
pip install flask flask-mysqldb
from flask import Flask, render_template, request, redirect, url_for, flash
from flask_mysqldb import MySQL
app = Flask(__name__)
# MySQL 配置
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'your_password'
app.config['MYSQL_DB'] = 'flask_crud'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor' # 返回字典格式的游标
mysql = MySQL(app)
# 设置密钥用于会话
app.secret_key = 'your_secret_key'
在 MySQL 中创建一个简单的表用于演示:
CREATE DATABASE IF NOT EXISTS flask_crud;
USE flask_crud;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20)
);
@app.route('/add_user', methods=['GET', 'POST'])
def add_user():
if request.method == 'POST':
# 获取表单数据
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
# 创建游标
cur = mysql.connection.cursor()
try:
# 执行 SQL 插入
cur.execute("INSERT INTO users(name, email, phone) VALUES(%s, %s, %s)",
(name, email, phone))
# 提交到数据库
mysql.connection.commit()
# 关闭游标
cur.close()
flash('用户添加成功', 'success')
return redirect(url_for('index'))
except Exception as e:
mysql.connection.rollback()
flash(f'添加用户失败: {str(e)}', 'danger')
return redirect(url_for('add_user'))
return render_template('add_user.html')
@app.route('/')
def index():
# 创建游标
cur = mysql.connection.cursor()
# 执行查询
cur.execute("SELECT * FROM users")
# 获取所有记录
users = cur.fetchall()
# 关闭游标
cur.close()
return render_template('index.html', users=users)
@app.route('/edit_user/<int:id>', methods=['GET', 'POST'])
def edit_user(id):
if request.method == 'POST':
# 获取表单数据
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
# 创建游标
cur = mysql.connection.cursor()
try:
# 执行更新
cur.execute("""
UPDATE users
SET name=%s, email=%s, phone=%s
WHERE id=%s
""", (name, email, phone, id))
# 提交到数据库
mysql.connection.commit()
# 关闭游标
cur.close()
flash('用户更新成功', 'success')
return redirect(url_for('index'))
except Exception as e:
mysql.connection.rollback()
flash(f'更新用户失败: {str(e)}', 'danger')
return redirect(url_for('edit_user', id=id))
# 获取当前用户信息
cur = mysql.connection.cursor()
cur.execute("SELECT * FROM users WHERE id = %s", (id,))
user = cur.fetchone()
cur.close()
if not user:
flash('用户不存在', 'danger')
return redirect(url_for('index'))
return render_template('edit_user.html', user=user)
@app.route('/delete_user/<int:id>', methods=['POST'])
def delete_user(id):
# 创建游标
cur = mysql.connection.cursor()
try:
# 执行删除
cur.execute("DELETE FROM users WHERE id = %s", (id,))
# 提交到数据库
mysql.connection.commit()
# 关闭游标
cur.close()
flash('用户删除成功', 'success')
except Exception as e:
mysql.connection.rollback()
flash(f'删除用户失败: {str(e)}', 'danger')
return redirect(url_for('index'))
<!DOCTYPE html>
<html>
<head>
<title>Flask CRUD</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-4">
{% with messages = get_flashed_messages(with_categories=true) %}
{% if messages %}
{% for category, message in messages %}
<div class="alert alert-{{ category }} alert-dismissible fade show">
{{ message }}
<button type="button" class="close" data-dismiss="alert">
<span>×</span>
</button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
{% block content %}{% endblock %}
</div>
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</body>
</html>
{% extends 'base.html' %}
{% block content %}
<h2>用户列表</h2>
<a href="{{ url_for('add_user') }}" class="btn btn-primary mb-3">添加用户</a>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>姓名</th>
<th>邮箱</th>
<th>电话</th>
<th>操作</th>
</tr>
</thead>
<tbody>
{% for user in users %}
<tr>
<td>{{ user.id }}</td>
<td>{{ user.name }}</td>
<td>{{ user.email }}</td>
<td>{{ user.phone }}</td>
<td>
<a href="{{ url_for('edit_user', id=user.id) }}" class="btn btn-sm btn-warning">编辑</a>
<form action="{{ url_for('delete_user', id=user.id) }}" method="post" style="display: inline;">
<button type="submit" class="btn btn-sm btn-danger" onclick="return confirm('确定删除吗?')">删除</button>
</form>
</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endblock %}
{% extends 'base.html' %}
{% block content %}
<h2>添加用户</h2>
<form method="post">
<div class="form-group">
<label for="name">姓名</label>
<input type="text" class="form-control" id="name" name="name" required>
</div>
<div class="form-group">
<label for="email">邮箱</label>
<input type="email" class="form-control" id="email" name="email" required>
</div>
<div class="form-group">
<label for="phone">电话</label>
<input type="text" class="form-control" id="phone" name="phone">
</div>
<button type="submit" class="btn btn-primary">提交</button>
<a href="{{ url_for('index') }}" class="btn btn-secondary">取消</a>
</form>
{% endblock %}
{% extends 'base.html' %}
{% block content %}
<h2>编辑用户</h2>
<form method="post">
<div class="form-group">
<label for="name">姓名</label>
<input type="text" class="form-control" id="name" name="name" value="{{ user.name }}" required>
</div>
<div class="form-group">
<label for="email">邮箱</label>
<input type="email" class="form-control" id="email" name="email" value="{{ user.email }}" required>
</div>
<div class="form-group">
<label for="phone">电话</label>
<input type="text" class="form-control" id="phone" name="phone" value="{{ user.phone }}">
</div>
<button type="submit" class="btn btn-primary">更新</button>
<a href="{{ url_for('index') }}" class="btn btn-secondary">取消</a>
</form>
{% endblock %}
在文件末尾添加:
if __name__ == '__main__':
app.run(debug=True)
然后运行:
python app.py
希望这个指南能帮助你开始使用 Flask 和 MySQL 实现 CRUD 操作!