Create RESTful API Using Python Flask & MySQL

Creating RESTful API Using Python Flask & MySQL – Part 2

In the first part of this tutorial series, we saw how to get started with creating a RESTful API using Python Flask and MySQL. We already create a CreateUser API for registering a new user into our application.

In this tutorial, we’ll take it to the next level and implement the next two APIs: AuthenticateUser and AddItem API.

Getting Started

To get started, clone the previous tutorial source code from GitHub.

git clone https://github.com/codehandbook/RESTfulApiUsingPythonFlask

Navigate to the project directory RESTfulApiUsingPythonFlask and run the api.py.

cd RESTfulApiUsingPythonFlask
python api.py

You should have the RESTful API running at http://localhost:5000.

AuthenticateUser RESTful API Using Python

We’ll use this API to authenticate the user. If the user exists we’ll return the relevant details associated with the user otherwise we’ll return an error that the user doesn’t exist. So, let’ get started.

We’ll start by creating a MySQL stored procedure to authenticate the user based on an email address and password. The stored procedure would simply look in the database and get the details of the particular username. Here is the sp_AuthenticateUser stored procedure.

USE `ItemListDb`;
DROP procedure IF EXISTS `sp_AuthenticateUser`;

DELIMITER $$
USE `ItemListDb`$$
CREATE PROCEDURE `sp_AuthenticateUser` (
IN p_username VARCHAR(20)
)
BEGIN

     select * from tblUser where UserName = p_username;

END$$

DELIMITER ;

Next, create a new class in the api.py file. Let’s name the class as AuthenticateUser. First, we’ll parse the data posted to the AuthenticateUser API.

class AuthenticateUser(Resource):
    def post(self):
        try:
            # Parse the arguments

            parser = reqparse.RequestParser()
            parser.add_argument('email', type=str, help='Email address for Authentication')
            parser.add_argument('password', type=str, help='Password for Authentication')
            args = parser.parse_args()

            _userEmail = args['email']
            _userPassword = args['password']

        except Exception as e:
            return {'error': str(e)}

Once we have parsed the username or email address and the password, we’ll make a call to our sp_AuthenticateUser stored procedure.

 

conn = mysql.connect()
cursor = conn.cursor()
cursor.callproc('sp_AuthenticateUser',(_userEmail,))
data = cursor.fetchall()

The above code is quite similar to what we had done earlier. We created a connection and cursor and then called the MySQL stored procedure using the cursor.

We’ll be getting the password in the data object. So, we’ll compare the password returned from the stored procedure with the one passed into the service url.

 

if(len(data)>0):
    if(str(data[0][2])==_userPassword):
        return {'status':200,'UserId':str(data[0][0])}
    else:
        return {'status':100,'message':'Authentication failure'}

As seen in the above code, if the password matches the API returns the user ID and the status code. If the authentication fails, an appropriate message and status code is returned. Here is the complete AuthenticateUser API.

class AuthenticateUser(Resource):
    def post(self):
        try:
            # Parse the arguments

            parser = reqparse.RequestParser()
            parser.add_argument('email', type=str, help='Email address for Authentication')
            parser.add_argument('password', type=str, help='Password for Authentication')
            args = parser.parse_args()

            _userEmail = args['email']
            _userPassword = args['password']

            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_AuthenticateUser',(_userEmail,))
            data = cursor.fetchall()

            
            if(len(data)>0):
                if(str(data[0][2])==_userPassword):
                    return {'status':200,'UserId':str(data[0][0])}
                else:
                    return {'status':100,'message':'Authentication failure'}

        except Exception as e:
            return {'error': str(e)}

Hook up the above AuthenticateUser resource to an endpoint.

api.add_resource(AuthenticateUser, '/AuthenticateUser')

Save the above changes and execute the python script.

From PostMan, try to make a call the AuthenticateUser API with a valid credentials and you should be able to view the success response.

Create RESTful API using Python Flask & MySQL - AuthenticateUserAPI
Create RESTful API using Python Flask & MySQL - AuthenticateUserAPI

Similarly, when invalid credentials are used an error message would be returned.

Create RESTful API using Python Flask & MySQL - AuthenticateUserAPI
Create RESTful API using Python Flask & MySQL - AuthenticateUserAPI

AddItems RESTful API Using Python

We’ll be following a similar approach in creating the AddItems API as we did earlier. We’ll start by writing the stored procedure for inserting items to database table. But before that we need to create a table in the database.


CREATE TABLE `ItemListDb`.`tblItem` (
  `Id` INT NULL AUTO_INCREMENT,
  `UserId` VARCHAR(45) NULL,
  `ItemName` VARCHAR(45) NULL,
  PRIMARY KEY (`Id`));

Now write a procedure called sp_AddItems to insert entry into the tblItem table.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_AddItems`(
in p_userId int,
in p_item varchar(25)
)
BEGIN
    insert into tblItem(
        UserId,
        ItemName
    )
    values(
        p_userId,
        p_item
    );
END

Next, we’ll create a new resource in our api.py file. We’ll parse the parameters passed into the REST API endpoint and call the add items stored procedure. I won’t be going into the details of the code as it should be quite self explanatory and it’s quite similar to what we have done for the CreateUser and AuthenticateUser API. So, here is the AddItem resource:

class AddItem(Resource):
    def post(self):
        try: 
            # Parse the arguments
            parser = reqparse.RequestParser()
            parser.add_argument('id', type=str)
            parser.add_argument('item', type=str)
            args = parser.parse_args()

            _userId = args['id']
            _item = args['item']

            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_AddItems',(_userId,_item))
            data = cursor.fetchall()

            conn.commit()
            return {'StatusCode':'200','Message': 'Success'}

        except Exception as e:
            return {'error': str(e)}

GetAllItems RESTful API Using Python

Once we have added items into the database, we would require an endpoint to list all the items added. So for that purpose, we’ll create an API called GetAllItems.
First, write a stored procedure to select all the items from the database based on a particular user ID.


USE `ItemListDb`;
DROP procedure IF EXISTS `sp_GetAllItems`;

DELIMITER $$
USE `ItemListDb`$$
CREATE PROCEDURE `sp_GetAllItems` (
in p_userId int
)
BEGIN
    select Id, ItemName from tblItem where UserId = p_userId; 
END$$

DELIMITER ;

Next, we’ll create a resource in our api.py file called GetAllItems. Similar to what we had done in other APIs, we’ll parse the parameter, connect to database and execute the stored procedure. In response we’ll get the list of items based on the user ID.


class GetAllItems(Resource):
    def post(self):
        try: 
            # Parse the arguments
            parser = reqparse.RequestParser()
            parser.add_argument('id', type=str)
            args = parser.parse_args()

            _userId = args['id']

            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_GetAllItems',(_userId,))
            data = cursor.fetchall()

            return {'StatusCode':'200'}

        except Exception as e:
            return {'error': str(e)}

Now once we have the items in the data object, we need to iterate it and put it into an array. Then then we simply return the array list.

items_list=[];
            for item in data:
                i = {
                    'Id':item[0],
                    'Item':item[1]
                }
                items_list.append(i)

Here is the complete GetAllItems resource.

class GetAllItems(Resource):
    def post(self):
        try: 
            # Parse the arguments
            parser = reqparse.RequestParser()
            parser.add_argument('id', type=str)
            args = parser.parse_args()

            _userId = args['id']

            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_GetAllItems',(_userId,))
            data = cursor.fetchall()

            items_list=[];
            for item in data:
                i = {
                    'Id':item[0],
                    'Item':item[1]
                }
                items_list.append(i)

            return {'StatusCode':'200','Items':items_list}

        except Exception as e:
            return {'error': str(e)}

Wrapping It Up

In this tutorial, we complete creating the required RESTful APIs using Python Flask and MySQL for our AngularJS app. In the coming parts, we’ll use the above created RESTful APIs to power up our AngularJS app.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts, suggestion and any corrections in the comments below !