Create RESTful API Using Python Flask & MySQL

Creating Flask RESTful API Using Python & MySQL

This would be a 3 part tutorial series. In this part, we’ll use Python to create Flask RESTful API. We’ll finish the series by creating an AngularJS web application using these RESTful APIs.

We’ll make use of MySQL for the back end for the APIs. Hence, this tutorial assumes the reader to have the basic knowledge of Python programming language and MySQL database.

Flask is a framework for creating web applications using Python programming language. From the officials docs,

Flask is a microframework for Python based on Werkzeug, Jinja 2 and good intentions. And before you ask: It’s BSD licensed!

Getting Started

Make sure you have python installed in your system by typing

python --version

We’ll make use of PIP python package manager to install Flask-RESTful. If you don’t have PIP installed, I would recommend installing it. Using pip install Flask-RESTful

pip install flask-restful

We’ll be creating a simple application where the user would be able to register, sign  in, add items and sign out. So, we’ll need the following RESTful APIs :

  • CreateUser API
  • AuthenticateUser API
  • AddItems

Flask RESTful API For User Creation

We’ll start by creating the CreateUser API. Create a project directory called RestAPI. Inside RestAPI, create a file called api.py. Import flask and flask-restful and create the minimal API as shown below:


from flask import Flask
from flask_restful import Resource, Api

app = Flask(__name__)
api = Api(app)

class CreateUser(Resource):
    def post(self):
        return {'status': 'success'}

api.add_resource(CreateUser, '/CreateUser')

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

As seen in the above code, we have imported flask framework and flask-restful. Using the Resource and Api from the flask-restful, we have hooked up the CreateUser to the /CreateUser endpoint. Now, if we we try to send a POST request to the /CreateUser endpoint, we’ll receive the success response.


{
    "status": "success"
}

When trying to create a new user, we’ll pass in an email address and password. So, let’s parse the arguments in the CreateUser API endpoint. For this purpose, we’ll make use of library called reqparse provided by flask-restful. So, import the library from flask-restful.


from flask_restful import reqparse

Now create a parser from the reqparse libray.


parser = reqparse.RequestParser()

Using the parser, add the expected arguments.


parser.add_argument('email', type=str, help='Email address to create user')
parser.add_argument('password', type=str, help='Password to create user')

Now parse the arguments and take them out into separate variables.


args = parser.parse_args()

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

Here is the modified CreateUser API endpoint.


class CreateUser(Resource):
    def post(self):
        try:
            # Parse the arguments
            parser = reqparse.RequestParser()
            parser.add_argument('email', type=str, help='Email address to create user')
            parser.add_argument('password', type=str, help='Password to create user')
            args = parser.parse_args()

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

            return {'Email': args['email'], 'Password': args['password']}

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

Save the above changes and execute the python code. I recommend using the Postman chrome extension to test the API. So, now our API is running on the localhost server at port 5000 and if we post a request to the CreateUser endpoint, we should get the posted user email address and password.

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

Connect Python to MySQL

Using the email address and password received in the CreateUser endpoint, we’ll create  a user in the MySQL database.

First let’s create a database called ItemListDb. Inside ItemListDb create a table called tblUser.


CREATE TABLE `ItemListDb`.`tblUser` (
`UserId` INT NOT NULL AUTO_INCREMENT,
`UserName` VARCHAR(45) NULL,
`Password` VARCHAR(45) NULL,
PRIMARY KEY (`UserId`));

Next create a stored procedure called spCreateUser to create a user.


USE `ItemListDb`;

DROP procedure IF EXISTS `spCreateUser`;

DELIMITER $$
USE `ItemListDb`$$
CREATE PROCEDURE `spCreateUser` (
IN p_Username varchar(50),
IN p_Password varchar(50)
)
BEGIN

if ( select exists (select 1 from tblUser where UserName = p_username) ) THEN

    select 'Username Exists !!';

ELSE

insert into tblUser
(
    UserName,
    Password
)
values
(
    p_Username,
    p_Password
);

END IF;

END$$

DELIMITER ;

I won’t be going into much details of the above stored procedures, since I’m assuming they are quite self explanatory and simple. Still if you face any doubts, do drop in a comment and I’ll take it from there.

Next, in order to connect from Python Flask to MySQL we’ll make use of the Flask-MySQL connector. Start by installing flask-mysql using pip.


pip install flask-mysql

Once you are done with the installation, import the flask-mysql.


from flask.ext.mysql import MySQL

Create a MySQL instance and set the app configurations required to connect to the MySQL database.


mysql = MySQL()

# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'jay'
app.config['MYSQL_DATABASE_PASSWORD'] = 'jay'
app.config['MYSQL_DATABASE_DB'] = 'ItemListDb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'

After the flask app has been initialized, make the MySQL init call and you should be good to go.


mysql.init_app(app)


Using the mysql object make a connection to the MySQL database.


conn = mysql.connect()

We’ll make use of MySQL cursors to interact with the database. So create a cursor.


cursor = conn.cursor()

Making user of the above created cursor, make a call to the MySQL stored procedure spCreateUser with the required parameters.


cursor.callproc('spCreateUser',(_userEmail,_userPassword))
data = cursor.fetchall()

If everything went well, we’ll return the user creation success message. If any error occurred during user creation, we’ll send the error message with the error code.


if len(data) is 0:
   conn.commit()
   return {'StatusCode':'200','Message': 'User creation success'}
else:
   return {'StatusCode':'1000','Message': str(data[0])}

Save the above changes and execute the python code. Try to post a email and password to create a new user and you should be get the user creation success message.

Create Flask RESTful API using Python & MySQL - User Creation Success
Create Flask RESTful API using Python & MySQL – User Creation Success

But when trying to create a new user using the existing username or email address, it will throw an error saying the username exists.

Create Flask RESTful API using Python & MySQL - User Creation Error
Create Flask RESTful API using Python & MySQL – User Creation Error

Wrapping It Up

I kind of believe that stretching a tutorial too long makes the user loose interest, so I’m going to wrap it up here. But it’s not the end. In the  next tutorial, we’ll create the other two APIs: Authenticate User API and the Add Item API.

In this tutorial, we saw how to get started with creating a Flask RESTful API using Python and MySQL. We created and tested a User creation API. Source code from this tutorial is available on GitHub.

Do drop in a comment in case of any doubts, suggestions, tutorial requests or corrections.

Happy Coding 🙂