body { font-family: Arial, sans-serif; line-height: 1.6; margin: 20px; } h1, h2, h3 { color: #333; } code { background-color: #f4f4f4; border: 1px solid #ccc; display: block; padding: 10px; margin: 10px 0; } pre { background-color: #f4f4f4; border: 1px solid #ccc; padding: 10px; overflow-x: auto; } a { color: #0066cc; }

How to Connect Flask with MySQL using Flask-SQLAlchemy

Introduction

Flask is a lightweight yet powerful Python web framework that allows developers to build dynamic web applications effortlessly. While integrating Flask with a MySQL database, using Flask-SQLAlchemy, a popular Object-Relational Mapping (ORM) library, can simplify and streamline the process of database management. In this blog post, we will guide you through the steps to connect Flask with MySQL using Flask-SQLAlchemy, perform CRUD (Create, Read, Update, Delete) operations, and manage the database using phpMyAdmin. Let’s dive into the details.

Prerequisites

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

• Python and Flask: Install Python and Flask to set up a Python development environment.
• MySQL Server: Install MySQL or have access to a MySQL server for database management.
• phpMyAdmin: Set up phpMyAdmin as your web-based database administration tool.
• Flask-SQLAlchemy and MySQL driver (PyMySQL): You will need to install these packages for the database interaction.

Step 1: Install Required Packages

First, you need to install Flask-SQLAlchemy and a MySQL driver. Flask-SQLAlchemy will help manage the connection and ORM functionalities, while the driver (PyMySQL) will allow Flask to communicate with MySQL.

Open a terminal or command prompt and run the following commands:

pip install Flask-SQLAlchemy
pip install PyMySQL

Step 2: Configure the Flask Application

Now that we have installed the necessary packages, we will create a basic Flask application and configure it to connect to a MySQL database.

1. Set Up Your Flask App:

   Start by creating a new Python file for your Flask application (e.g., app.py). Import the required modules and initialize the Flask app:

   from flask import Flask
   from flask_sqlalchemy import SQLAlchemy
   
   app = Flask(__name__)

2. Configure the SQLAlchemy Database URI:

   To establish a flask sqlalchemy database connection, you need to specify the flask sqlalchemy MySQL connection string. This connection string includes the username, password, host, and database name. Add the following configuration settings to your application:

   app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://your_mysql_username:your_mysql_password@localhost/your_database_name'
   app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
   
   db = SQLAlchemy(app)

   Replace your_mysql_username, your_mysql_password, and your_database_name with your actual MySQL credentials and the name of the database you want to use. This flask sqlalchemy MySQL connection string is crucial for connecting Flask with MySQL using SQLAlchemy.

Understanding the Flask-SQLAlchemy MySQL Connection String

The connection string used in the configuration is a standard format for defining how your Flask application will connect to the MySQL database. Here’s a breakdown of the components:

• mysql+pymysql: This specifies the database type (MySQL) and the driver (PyMySQL) used for the connection.
• your_mysql_username: Replace this with your MySQL username.
• your_mysql_password: Replace this with your MySQL password.
• localhost: This is the hostname where your MySQL server is running. If your MySQL server is running on a different host or port, replace localhost accordingly.
• your_database_name: The name of the database you wish to connect to.

This string forms the backbone of the flask sqlalchemy database connection, allowing the Flask application to interact with the MySQL database through SQLAlchemy.

Step 3: Define Database Models

In Flask-SQLAlchemy, you define your database schema using Python classes, where each class represents a table in the database. Let’s define a simple User model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return f'<User {self.name}>'

• id: A unique identifier for each user, set as the primary key.
• name: A string field to store the user’s name.
• email: A string field to store the user’s email, which must be unique.

Step 4: Create the Database and Tables

Before you can use the database, you need to create it along with the necessary tables based on the models defined. Add the following code to your application file, which will run when the script is executed:

if __name__ == '__main__':
    db.create_all()  # This command creates the database and tables based on the defined models
    app.run(debug=True)

Running this script will create the tables in the MySQL database as defined by the User model. This is a key part of setting up a flask sqlalchemy database connection.

Step 5: Perform Database Operations Using Flask-SQLAlchemy

With the database set up, you can now perform CRUD operations. Here’s how you can handle each type of operation using Flask-SQLAlchemy, leveraging the flask mysql sqlalchemy configuration.

1. Insert Data:

   Define a route to insert a new user into the database:

   @app.route('/add_user')
   def add_user():
       new_user = User(name='John Doe', email='johndoe@example.com')
       db.session.add(new_user)
       db.session.commit()
       return 'User added successfully!'

   This code creates a new User object, adds it to the session, and commits the transaction to save the changes to the database.

2. Select Data:

   Define a route to retrieve and display a list of users from the database:

   @app.route('/users')
   def get_users():
       users = User.query.all()
       user_list = [{'id': user.id, 'name': user.name, 'email': user.email} for user in users]
       return str(user_list)

   This code fetches all user records from the User table and returns them as a list of dictionaries.

3. Update Data:

   Define a route to update an existing user’s information:

   @app.route('/update_user/<int:id>')
   def update_user(id):
       user = User.query.get(id)
       if user:
           user.name = 'Jane Doe'
           db.session.commit()
           return 'User updated successfully!'
       return 'User not found!'

   This code retrieves a user by their ID, updates their name, and commits the changes to the database.

4. Delete Data:

   Define a route to delete a user from the database:

   @app.route('/delete_user/<int:id>')
   def delete_user(id):
       user = User.query.get(id)
       if user:
           db.session.delete(user)
           db.session.commit()
           return 'User deleted successfully!'
       return 'User not found!'

   This code fetches a user by their ID and deletes the user if found, then commits the transaction.

Step 6: Test Your Flask Application

Save your Flask application file and run it using the following command in your terminal:

python app.py

Once the application is running, you can access the defined routes in your web browser to test the database operations:

• Add a user: http://localhost:5000/add_user
• Display users: http://localhost:5000/users
• Update a user: http://localhost:5000/update_user/1
• Delete a user: http://localhost:5000/delete_user/1

Conclusion

In this comprehensive guide, we covered an alternative method to connect Flask with MySQL using Flask-SQLAlchemy. This method simplifies database interactions by using an ORM, which allows developers to work with database records as Python objects. By following the step-by-step instructions provided, you now have the necessary knowledge to integrate Flask with MySQL using Flask-SQLAlchemy, perform CRUD operations, and manage your database effectively using phpMyAdmin. This approach opens up possibilities for building robust and scalable web applications with Flask. Happy coding with Flask and Flask-SQLAlchemy!