Database:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(15)
);
CRUD operation
CRUD operation in flask using raw MySQL
db_config.py:
# db_config.py
from flask_mysqldb import MySQL
def init_mysql(app):
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'flask_crud_db'
mysql = MySQL(app)
return mysql
app.py:
# app.py
from flask import Flask, render_template, request, redirect, url_for, flash
from db_config import init_mysql
app = Flask(__name__)
app.secret_key = "secret123"
mysql = init_mysql(app)
# 🔽 READ
@app.route('/')
def index():
cur = mysql.connection.cursor()
cur.execute("SELECT * FROM students")
data = cur.fetchall()
cur.close()
return render_template('index.html', students=data)
# 🔽 CREATE
@app.route('/add', methods=['GET', 'POST'])
def add():
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
cur = mysql.connection.cursor()
cur.execute("INSERT INTO students (name, email, phone) VALUES (%s, %s, %s)", (name, email, phone))
mysql.connection.commit()
flash('Student Added Successfully!')
return redirect(url_for('index'))
return render_template('add.html')
# 🔽 UPDATE
@app.route('/update/<int:id>', methods=['GET', 'POST'])
def update(id):
cur = mysql.connection.cursor()
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
cur.execute("""
UPDATE students
SET name=%s, email=%s, phone=%s
WHERE id=%s
""", (name, email, phone, id))
mysql.connection.commit()
flash('Student Updated Successfully!')
return redirect(url_for('index'))
cur.execute("SELECT * FROM students WHERE id = %s", [id])
student = cur.fetchone()
return render_template('update.html', student=student)
# 🔽 DELETE
@app.route('/delete/<int:id>', methods=['GET'])
def delete(id):
cur = mysql.connection.cursor()
cur.execute("DELETE FROM students WHERE id = %s", [id])
mysql.connection.commit()
flash('Student Deleted Successfully!')
return redirect(url_for('index'))
if __name__ == '__main__':
app.run(debug=True)
HTML templates:
index.html:-
<!DOCTYPE html>
<html>
<head>
<title>Student List</title>
</head>
<body>
<h2>Student Records</h2>
<a href="{{ url_for('add') }}">Add New</a>
<br><br>
{% with messages = get_flashed_messages() %}
{% if messages %}
{% for message in messages %}
<p style="color: green;">{{ message }}</p>
{% endfor %}
{% endif %}
{% endwith %}
<table border="1" cellpadding="10">
<tr><th>ID</th><th>Name</th><th>Email</th><th>Phone</th><th>Action</th></tr>
{% for row in students %}
<tr>
<td>{{ row[0] }}</td>
<td>{{ row[1] }}</td>
<td>{{ row[2] }}</td>
<td>{{ row[3] }}</td>
<td>
<a href="{{ url_for('update', id=row[0]) }}">Edit</a> |
<a href="{{ url_for('delete', id=row[0]) }}" onclick="return confirm('Are you sure?')">Delete</a>
</td>
</tr>
{% endfor %}
</table>
</body>
</html>
add.html:-
<!DOCTYPE html>
<html>
<head><title>Add Student</title></head>
<body>
<h2>Add New Student</h2>
<form method="POST">
Name: <input type="text" name="name" required><br><br>
Email: <input type="email" name="email" required><br><br>
Phone: <input type="text" name="phone"><br><br>
<button type="submit">Add</button>
</form>
</body>
</html>
update.html:-
<!DOCTYPE html>
<html>
<head><title>Update Student</title></head>
<body>
<h2>Edit Student</h2>
<form method="POST">
Name: <input type="text" name="name" value="{{ student[1] }}" required><br><br>
Email: <input type="email" name="email" value="{{ student[2] }}" required><br><br>
Phone: <input type="text" name="phone" value="{{ student[3] }}"><br><br>
<button type="submit">Update</button>
</form>
</body>
</html>
CRUD operation using SQLAlchemy
app.py:
from flask import Flask, render_template, request, redirect, url_for, flash
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.secret_key = 'secret123'
# ✅ Configure MySQL connection using SQLAlchemy
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:@localhost/flask_crud_db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
# ✅ Define the Student model
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(100), nullable=False)
phone = db.Column(db.String(15))
# 🔽 CREATE DB TABLES (only run once)
@app.before_first_request
def create_tables():
db.create_all()
# 🔽 READ - Show all students
@app.route('/')
def index():
students = Student.query.all()
return render_template('index.html', students=students)
# 🔽 CREATE - Add new student
@app.route('/add', methods=['GET', 'POST'])
def add():
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
new_student = Student(name=name, email=email, phone=phone)
db.session.add(new_student)
db.session.commit()
flash('Student Added Successfully!')
return redirect(url_for('index'))
return render_template('add.html')
# 🔽 UPDATE - Edit student
@app.route('/update/<int:id>', methods=['GET', 'POST'])
def update(id):
student = Student.query.get_or_404(id)
if request.method == 'POST':
student.name = request.form['name']
student.email = request.form['email']
student.phone = request.form['phone']
db.session.commit()
flash('Student Updated Successfully!')
return redirect(url_for('index'))
return render_template('update.html', student=student)
# 🔽 DELETE - Delete student
@app.route('/delete/<int:id>')
def delete(id):
student = Student.query.get_or_404(id)
db.session.delete(student)
db.session.commit()
flash('Student Deleted Successfully!')
return redirect(url_for('index'))
if __name__ == '__main__':
app.run(debug=True)
HTML templates:
index.html:-
<!DOCTYPE html>
<html>
<head><title>Student List</title></head>
<body>
<h2>Student Records</h2>
<a href="{{ url_for('add') }}">Add New</a><br><br>
{% with messages = get_flashed_messages() %}
{% if messages %}
{% for message in messages %}
<p style="color: green;">{{ message }}</p>
{% endfor %}
{% endif %}
{% endwith %}
<table border="1" cellpadding="10">
<tr><th>ID</th><th>Name</th><th>Email</th><th>Phone</th><th>Action</th></tr>
{% for s in students %}
<tr>
<td>{{ s.id }}</td>
<td>{{ s.name }}</td>
<td>{{ s.email }}</td>
<td>{{ s.phone }}</td>
<td>
<a href="{{ url_for('update', id=s.id) }}">Edit</a> |
<a href="{{ url_for('delete', id=s.id) }}" onclick="return confirm('Are you sure?')">Delete</a>
</td>
</tr>
{% endfor %}
</table>
</body>
</html>
add.html:-
<!DOCTYPE html>
<html>
<head><title>Add Student</title></head>
<body>
<h2>Add New Student</h2>
<form method="POST">
Name: <input type="text" name="name" required><br><br>
Email: <input type="email" name="email" required><br><br>
Phone: <input type="text" name="phone"><br><br>
<button type="submit">Add</button>
</form>
</body>
</html>
update.html:-
<!DOCTYPE html>
<html>
<head><title>Update Student</title></head>
<body>
<h2>Edit Student</h2>
<form method="POST">
Name: <input type="text" name="name" value="{{ student.name }}" required><br><br>
Email: <input type="email" name="email" value="{{ student.email }}" required><br><br>
Phone: <input type="text" name="phone" value="{{ student.phone }}"><br><br>
<button type="submit">Update</button>
</form>
</body>
</html>
