Rest API with Python Flask and MySQL – Part 2

In the previous tutorial, we learned how to create a basic Rest API using Python Flask. In this tutorial, we will learn how to integrate MySQL database with our Flask application to store and retrieve data.

Setting up MySQL Database

Before we can start integrating MySQL with our Flask application, we need to set up a MySQL database. You can use a local MySQL server or a cloud-based solution like Amazon RDS.

Installing MySQL Connector

To connect to MySQL from Python, we will need to install the mysql-connector-python package. You can install it using pip:

pip install mysql-connector-python

Connecting Flask Application to MySQL

Now that we have our MySQL database set up and the connector installed, we can connect our Flask application to the database. We will need to create a new Python file (e.g., database.py) where we will define the database connection.


from flask import Flask
from flask_mysqldb import MySQL

mysql = MySQL(app)


Creating API Endpoints to Interact with MySQL

Now that we have our database connection set up, we can create API endpoints to interact with the MySQL database. We can create endpoints to fetch data from the database, insert new data, update existing data, and delete data.

Fetching Data

We can fetch data from the database using a GET request. For example:


@app.route('/users', methods=['GET'])
def get_users():
cur = mysql.connection.cursor()
cur.execute('SELECT * FROM users')
data = cur.fetchall()
cur.close()

return jsonify(data)


Inserting Data

We can insert data into the database using a POST request. For example:


@app.route('/add_user', methods=['POST'])
def add_user():
cur = mysql.connection.cursor()
cur.execute('INSERT INTO users (name, email) VALUES (%s, %s)', ('John', 'john@example.com'))
mysql.connection.commit()
cur.close()

return 'User added successfully'


Conclusion

By following this tutorial, you have learned how to integrate MySQL database with a Python Flask application to create a Rest API. You can now build more complex applications that store and retrieve data from a database.