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:
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
Hmza
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
Hmza
