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 PyMySQLStep 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.
-
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__) -
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, andyour_database_namewith 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, replacelocalhostaccordingly.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.
-
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
Userobject, adds it to the session, and commits the transaction to save the changes to the database. -
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
Usertable and returns them as a list of dictionaries. -
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.
-
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.pyOnce 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!
Comments