How to Connect MySQL to Your Flask App, a CRUD example (Python Tutorial)

How to Connect MySQL to Your Flask App, a CRUD example (Python Tutorial)
Photo by NEOM / Unsplash

In this tutorial, we will guide you through how to install and use MySQL with the Flask framework easily.

The first requirement is to have Python and Flask installed at your system, as we assume that you already do, let's dive directly to installing flask-mysql package.

If not, follow this tutorial:

How to Install and Create Flask Web App in Mins (Python Tutorial 2024)
Flask is a popular web framework written in Python that allows developers to build web applications quickly and easily. It is known for its simplicity, flexibility, and scalability. Advantages of using Flask Here are the advantages of using Flask: 1. Simplicity: Flask has a simple and intuitive API that makes

Install flask-mysql package

pip install flask-mysql

Import flask-mysql

In your flask app file, let's import the flask-mysql package:

from flask import Flask
from flask_mysql import MySQL

app.py

Now, we can connect to the MySQL database using the following, do not forget to replace the connection data with your own.

app = Flask(__name__)
app.config['MYSQL_HOST'] = 'localhost'  # Replace with your MySQL host
app.config['MYSQL_USER'] = 'username'  # Replace with your MySQL username
app.config['MYSQL_PASSWORD'] = 'password'  # Replace with your MySQL password
app.config['MYSQL_DB'] = 'database_name'  # Replace with your MySQL database name

mysql = MySQL(app)

app.py

Retrieve and display data from MySQL using Flask

Now we can start using the database, let's say that you have a list of users in the users table, in the following snippet, we will reterive all users and render their information in users.html.

from flask import Flask, render_template
from flask_mysql import MySQL

app = Flask(__name__)
app.config['MYSQL_HOST'] = 'localhost'  # Replace with your MySQL host
app.config['MYSQL_USER'] = 'username'  # Replace with your MySQL username
app.config['MYSQL_PASSWORD'] = 'password'  # Replace with your MySQL password
app.config['MYSQL_DB'] = 'database_name'  # Replace with your MySQL database name

mysql = MySQL(app)

@app.route('/users')
def get_users():
    cursor = mysql.connection.cursor()
    cursor.execute('SELECT * FROM users')
    users = cursor.fetchall()
    cursor.close()
    return render_template('users.html', users=users)

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

app.py

The following is the users.html template, which will render the data from get_users function.

<!DOCTYPE html>
<html>
<head>
    <title>Users</title>
</head>
<body>
    <h1>Users</h1>
    <ul>
        {% for user in users %}
        <li>{{ user.name }}</li>
        {% endfor %}
    </ul>
</body>
</html>

users.html

Create and Insert Records into database

Here is how to use Flask to insert a new record into the users table, using an HTML form.

@app.route('/insert', methods=['POST'])
def insert_data():
    name = request.form['name']
    age = request.form['age']

    cursor = mysql.connection.cursor()
    query = f"INSERT INTO users (name, age) VALUES ('{name}', {age})"
    cursor.execute(query)
    mysql.connection.commit()

    cursor.close()

    return 'Data inserted successfully!'

<!DOCTYPE html>
<html>
<head>
    <title>Insert Data</title>
</head>
<body>
    <h1>Insert Data</h1>
    <form method="POST" action="/insert">
        <label for="name">Name:</label>
        <input type="text" name="name" required>

        <label for="age">Age:</label>
        <input type="number" name="age" required>

        <button type="submit">Submit</button>
    </form>
</body>
</html>

insert.html


Delete records from a MySQL database with Flask

@app.route('/delete/<int:id>')
def delete_data(id):
    cursor = mysql.connection.cursor()
    query = f"DELETE FROM table_name WHERE id = {id}"
    cursor.execute(query)
    mysql.connection.commit()
    cursor.close()
    return 'Data deleted successfully!'


Edit records with MySQL database with Flask

@app.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit_data(id):
    data = Data.query.get_or_404(id)
    if request.method == 'POST':
        data.name = request.form['name']
        data.age = request.form['age']
        db.session.commit()
        return redirect('/')
    return render_template('edit.html', data=data)

app.py

<!DOCTYPE html>
<html>
<head>
    <title>Edit Data</title>
</head>
<body>
    <h1>Edit Data</h1>
    <form method="POST">
        <label for="name">Name:</label>
        <input type="text" name="name" value="{{ data.name }}" required>

        <label for="age">Age:</label>
        <input type="number" name="age" value="{{ data.age }}" required>

        <button type="submit">Save</button>
    </form>
</body>
</html>

edit.html


Flask Python Tutorial: Upload CSV file and Insert rows into MySQL Database
Python is a cool language, soon as you finish your first projects with it, you will be hocked. It’s a general purpose language and it’s gaining popularity for the recent years among data scientists and data engineers. Personally, I recommend Python to newbies especially teens, not just because it has

Read more