How to Connect MySQL to Your Flask App, a CRUD example (Python Tutorial)
Table of Content
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:
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
