How to Build a Simple Python Flask Application with MySQL Database: Create, Delete, Update, and Display

Leave a Comment / How To, MySQL, Python, Web Development / By

In this tutorial, we will cover the basics of using Flask and MySQL together. Specifically, we will cover how to add, delete, update, and view data in a MySQL database using Flask.

Prerequisites:

Before we begin, make sure you have the following installed:

  • Python 3
  • Flask
  • MySQL

You should also have some knowledge of Python, Flask, and MySQL.

Creating a MySQL Database

First, let’s create a MySQL database. Open up your MySQL command line interface and run the following commands.

CREATE DATABASE mydatabase;
USE mydatabase;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

You may also use PHPMyAdmin to create a database.

This will create a new database called mydatabase and a table called users with three columns: id, name, and email.

Creating a Flask Application

Create a new file called app.py and add the following code.

from flask import Flask, render_template, request, redirect
import mysql.connector

app = Flask(__name__)

# MySQL Configuration
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="mydatabase"
)

@app.route("/")
def index():
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users")
    users = cursor.fetchall()
    return render_template("index.html", users=users)

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

This code creates a Flask application and connects to our MySQL database. It also defines a route for the root URL (“/”) that fetches all users from the database and renders an HTML template called “index.html“.

Adding Data

Add a route for adding data to the database. Add the following code to your Flask application.

@app.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "POST":
        name = request.form["name"]
        email = request.form["email"]
        cursor = db.cursor()
        query = "INSERT INTO users (name, email) VALUES (%s, %s)"
        values = (name, email)
        cursor.execute(query, values)
        db.commit()
        return redirect("/")
    else:
        return render_template("add.html")

This code defines a new route for the URL “/add”. If the request method is “POST“, it fetches the name and email from the form data and inserts a new user into the database using an SQL query. If the request method is not “POST“, it renders a template called “add.html“.

Creating the “add.html” Template

Create the “add.html” template. Create a new file called “add.html” in the “templates” folder and add the following code.

<!DOCTYPE html>
<html>
    <head>
        <title>Add User</title>
    </head>
    <body>
        <h1>Add User</h1>
        <form action="{{ url_for('add') }}" method="post">
            <label for="name">Name:</label>
            <input type="text" id="name" name="name"><br>
            <label for="email">Email:</label>
            <input type="text" id="email" name="email"><br>
            <input type="submit" value="Add">
        </form>
    </body>
</html>

This code defines a form that allows the user to enter a name and email address.

Deleting Data

Now let’s add a route for deleting data from the database. Add the following code to your Flask application.

@app.route("/delete/<int:id>")
def delete(id):
    cursor = db.cursor()
    query = "DELETE FROM users WHERE id = %s"
    values = (id,)
    cursor.execute(query, values)
    db.commit()
    return redirect("/")

This code defines a new route for the URL “/delete/id”, where “id” is the ID of the user to be deleted. It deletes the user from the database using an SQL query and then redirects the user back to the home page (“/”).

Updating Data

Now let’s add a route for updating data in the database. Add the following code to your Flask application.

@app.route("/update/<int:id>", methods=["GET", "POST"])
def update(id):
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (id,))
    user = cursor.fetchone()
    if request.method == "POST":
        name = request.form["name"]
        email = request.form["email"]
        query = "UPDATE users SET name = %s, email = %s WHERE id = %s"
        values = (name, email, id)
        cursor.execute(query, values)
        db.commit()
        return redirect("/")
    else:
        return render_template("update.html", user=user)

This code defines a new route for the URL “/update/id”, where “id” is the ID of the user to be updated. If the request method is “POST“, it fetches the name and email from the form data and updates the user in the database using an SQL query. If the request method is not “POST“, it renders a template called “update.html” with the current user data.

Creating the “update.html” Template

Create the “update.html” template. Create a new file called “update.html” in the “templates” folder and add the following code.

<!DOCTYPE html>
<html>
    <head>
        <title>Update User</title>
    </head>
    <body>
        <h1>Update User</h1>
        <form action="{{ url_for('update') }}" method="post">
            <label for="name">Name:</label>
            <input type="text" id="name" name="name" value="{{ user[1] }}"><br>
            <label for="email">Email:</label>
            <input type="text" id="email" name="email" value="{{ user[2] }}"><br>
            <input type="submit" value="Update">
        </form>
    </body>
</html>

This code defines a form that displays the current user data and allows the user to update the name and email.

Viewing Data

Add a route for viewing data from the database. Add the following code to your Flask application.

@app.route("/")
def index():
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users")
    users = cursor.fetchall()
    return render_template("index.html", users=users)

This code defines the home page (“/“) route, which fetches all users from the database using an SQL query and then renders a template called “index.html” with the list of users.

Creating the “index.html” Template

Now let’s create the “index.html” template. Create a new file called “index.html” in the “templates” folder and add the following code.

<!DOCTYPE html>
<html>
    <head>
        <title>User List</title>
    </head>
    <body>
        <h1>User List</h1>
        <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>
                {% for user in users %}
                    <tr>
                        <td>{{ user[1] }}</td>
                        <td>{{ user[2] }}</td>
                        <td>
                            <a href="/update/{{ user[0] }}">Edit</a>
                            <a href="/delete/{{ user[0] }}">Delete</a>
                        </td>
                    </tr>
                {% endfor %}
            </tbody>
        </table>
        <a href="/add">Add User</a>
    </body>
</html>

This code defines a table that displays the list of users, along with links to edit and delete each user. It also includes a link to add a new user.

Conclusion

In this tutorial, we have learned how to create a simple Python Flask application that interacts with a MySQL database. We have covered how to add, delete, update, and view data in the database using SQL queries and Flask routes. By following this tutorial, you should now have a basic understanding of how to build a web application using Flask and MySQL.

Leave a Comment

Your email address will not be published. Required fields are marked *