Introduction:
Flask, a powerful Python web framework, allows developers to build dynamic web applications with ease. Integrating Flask with phpMyAdmin, a popular web-based MySQL administration tool, enables efficient database management. In this blog post, we will walk you through the process of establishing a connection between Flask and phpMyAdmin. Additionally, we will demonstrate how to perform essential database operations, such as select, insert, update, and delete, using Flask and MySQL. Let’s dive into the step-by-step guide below.

Prerequisites: Before proceeding, ensure you have the following prerequisites in place:

  1. Python and Flask: Install Python and Flask to set up a Python development environment.
  2. MySQL Server: Install MySQL or have access to a MySQL server for database management.
  3. phpMyAdmin: Set up phpMyAdmin as your web-based database administration tool.

Step 1: Create a Flask Application:
To begin, let’s create a basic Flask application. Open your favorite text editor or integrated development environment (IDE) and follow these steps:

Import the necessary Flask modules and initialize the Flask app.

from flask import Flask

app = Flask(__name__)

Define a route and a function to handle the route. For example, let’s create a route to display a list of users.

@app.route('/users')
def display_users():
    # Add code to fetch user data from the database
    # Return the user data as a response
    return 'User List'

Run the Flask application.

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

Step 2: Install Required Packages:
To connect Flask with phpMyAdmin, we need to install the necessary packages. Open a terminal or command prompt and execute the following command to install Flask-MySQLdb:

pip install Flask-MySQLdb

This package allows Flask to communicate with MySQL.

Step 3: Configure the Database Connection: In your Flask application, you need to configure the MySQL database connection. Add the following lines of code to your Flask application file, preferably before the app.run() line:

from flask_mysqldb import MySQL

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'your_mysql_username'
app.config['MYSQL_PASSWORD'] = 'your_mysql_password'
app.config['MYSQL_DB'] = 'your_database_name'

mysql = MySQL(app)

Replace 'localhost' with the appropriate MySQL host, 'your_mysql_username' and 'your_mysql_password' with your MySQL credentials, and 'your_database_name' with the name of your MySQL database.

Step 4: Connect Flask with phpMyAdmin:
Now that we have the Flask-MySQLdb package installed and the database configuration set up, let’s establish the connection between Flask and phpMyAdmin.

  1. Open phpMyAdmin in your web browser by accessing the appropriate URL (e.g., http://localhost/phpmyadmin).
  2. Create a new database in phpMyAdmin that matches the database name you specified in the Flask application configuration ('your_database_name').

Step 5: Perform Database Operations in Flask:
With the connection established, you can now perform select, insert, update, and delete operations using Flask and MySQL.

Select Query: To retrieve data from the database, modify the display_users function in your Flask application as follows:

@app.route('/users')
def display_users():
    cur = mysql.connection.cursor()
    cur.execute("SELECT * FROM users")
    result = cur.fetchall()
    cur.close()

    # Process the user data and return it as a response
    return str(result)

This code fetches all the records from the “users” table and stores them in the result variable. You can process the data as per your application’s requirements and return it as a response.

Insert Query: To insert data into the database, modify the display_users function in your Flask application as follows:

@app.route('/users')
def display_users():
    cur = mysql.connection.cursor()
    cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("John Doe", "johndoe@example.com"))
    mysql.connection.commit()
    cur.close()

    return "User inserted successfully"

This code inserts a new user with the name “John Doe” and email “johndoe@example.com” into the “users” table.

Update and Delete Queries: Similarly, you can modify the display_users function to perform update and delete operations by using appropriate SQL queries. Here are the code snippets for reference:

Update Query:

cur.execute("UPDATE users SET name = %s WHERE id = %s", ("Jane Smith", 1))

Delete Query:

cur.execute("DELETE FROM users WHERE id = %s", (2,))

Explanation:

  1. DELETE FROM users: This is the standard SQL syntax for deleting records from the “users” table.
  2. WHERE id = %s: This is the condition specified using the WHERE clause. It specifies that only rows with a specific ID should be deleted. The %s acts as a placeholder for the value we will provide when executing the query.
  3. (2,): This is a tuple containing the value we want to substitute for the %s placeholder. In this case, we are deleting the row with the ID equal to 2. The trailing comma is necessary to indicate that it’s a tuple with a single element.

Step 6: Test the Flask Application: Save your Flask application file and run it using the python command in the terminal or command prompt. Once the application is running, access the defined routes (e.g., http://localhost:5000/users) in your web browser to test the database operations.

Conclusion:
In this comprehensive guide, we covered the process of establishing a connection between Flask and phpMyAdmin, allowing you to perform essential database operations. By following the step-by-step instructions provided, you now have the necessary knowledge to integrate Flask with phpMyAdmin, retrieve data from the database, and execute select, insert, update, and delete queries. This opens up a world of possibilities for building robust web applications with Flask and managing databases effectively. Happy coding with Flask and phpMyAdmin!

Categorized in:

Flask

Tagged in:

, , , ,