Python REST API CRUD Example using Flask and MySQL

Introduction

Here we will see Python REST API CRUD Example using Flask and MySQL. We have also seen how to create web application CRUD example using Python, Flak and MySQL.

You may also like to read REST APIs in Java and Spring Technologies.

What is REST or RESTful?

The most important concept in REST is resources, which are identified by global IDs — typically using URIs. Client applications use HTTP methods (GET/ POST/ PUT/ DELETE) to manipulate the resource or collection of resources. A RESTful Web service is implemented using HTTP and the principles of REST. Typically, a RESTful Web service should define the following aspects:

  • The base/root URI for the Web service such as http://<host>/<appcontext/contextpath>/<url pattern>/<resources>.
  • The MIME type of the response data supported, which are JSON/XML/TEXT/HTML etc.
  • The set of operations supported by the service. (for example, POST, GET, PUT or DELETE).

REST or RESTful Methods

HTTP methods are mapped to CRUD (create, read, update and delete) actions for a resource. Although you can make slight modifications such as making the PUT method to be create or update, the basic patterns are listed as follows.

  • GET: Get/List/Retrieve an individual resource or a collection of resources.
  • POST: Create a new resource or resources.
  • PUT: Update an existing resource or collection of resources.
  • DELETE: Delete a resource or collection of resources.

Prerequisites

Have Python installed in Windows (or Unix)
Pyhton version and Packages
Here I am using Python 3.6.6 version

Unlike our previous tutorial on Python web application CRUD example using Flask and MySQL, we do not have front-end or User Interface (UI) here. We are building REST or RESTful web services which could be consumed by any consumer. These services are decoupled from consumer. You can use any UI technologies, such as, ReactJS, AngularJS or even jQuery, HTML to showcase your data for your users.

Preparing your workspace

Preparing your workspace is one of the first things that you can do to make sure that you start off well. The first step is to check your working directory.

When you are working in the Python terminal, you need first navigate to the directory, where your file is located and then start up Python, i.e., you have to make sure that your file is located in the directory where you want to work from.

For this Python REST API CRUD Example using Flask and MySQL, we need modules, such as, flask and mysql. The module flask works as a web framework and mysql module is required to establish connection with MySQL database and query the database using Python programming language.

Example and Source Code

In the below image you see I have opened a cmd prompt and navigated to the directory where I have to create Python script for implementing Python web application CRUD example using Flask MySQL.

python rest api crud example using flask and mysql

Please go through the following steps in order to implement Python web application CRUD example using Flask MySQL:

Step 1. Create the below app.py script(py is the extension to indicate Python script) where we import the flask module. This file should be created under user_crud directory. Notice how we create flask instance.

from flask import Flask

app = Flask(__name__)

Step 2. We create the below db_config.py Python script under user_crud to setup the MySQL database configurations for connecting to database. We need to configure database connection with flask module and that’s why we have imported app module and setup the MySQL configuration with flask module.

from app import app
from flaskext.mysql import MySQL

mysql = MySQL()
 
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'roytuts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Step 3. Next we need main.py script under user_crud directory. This script is the perfect instance of Python REST API CRUD Example using Flask and MySQL. It defines all REST URIs for performing CRUD operations. It will also connect to MySQL database server and query the database to read, insert, update and delete.

Here you can use http PUT method and http DELETE method for updating and deleting users respectively. I have defined only 404 method to handle not found error. You should basically handle required errors, such as, server errors for http responses 500, occurred during the REST API calls.

Notice also we have configured the MySQL connection with pymysql.cursors.DictCursor to fetch rows as a data dictionary so that we retrieve each column value as a key/value pair (column name/column value) that will help us to display data in json format using flask’s jsonify API.

import pymysql
from app import app
from db_config import mysql
from flask import jsonify
from flask import flash, request
from werkzeug import generate_password_hash, check_password_hash
		
@app.route('/add', methods=['POST'])
def add_user():
	try:
		_json = request.json
		_name = _json['name']
		_email = _json['email']
		_password = _json['pwd']
		# validate the received values
		if _name and _email and _password and request.method == 'POST':
			#do not save password as a plain text
			_hashed_password = generate_password_hash(_password)
			# save edits
			sql = "INSERT INTO tbl_user(user_name, user_email, user_password) VALUES(%s, %s, %s)"
			data = (_name, _email, _hashed_password,)
			conn = mysql.connect()
			cursor = conn.cursor()
			cursor.execute(sql, data)
			conn.commit()
			resp = jsonify('User added successfully!')
			resp.status_code = 200
			return resp
		else:
			return not_found()
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.route('/users')
def users():
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		cursor.execute("SELECT * FROM tbl_user")
		rows = cursor.fetchall()
		resp = jsonify(rows)
		resp.status_code = 200
		return resp
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.route('/user/<int:id>')
def user(id):
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		cursor.execute("SELECT * FROM tbl_user WHERE user_id=%s", id)
		row = cursor.fetchone()
		resp = jsonify(row)
		resp.status_code = 200
		return resp
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()

@app.route('/update', methods=['POST'])
def update_user():
	try:
		_json = request.json
		_id = _json['id']
		_name = _json['name']
		_email = _json['email']
		_password = _json['pwd']		
		# validate the received values
		if _name and _email and _password and _id and request.method == 'POST':
			#do not save password as a plain text
			_hashed_password = generate_password_hash(_password)
			# save edits
			sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s"
			data = (_name, _email, _hashed_password, _id,)
			conn = mysql.connect()
			cursor = conn.cursor()
			cursor.execute(sql, data)
			conn.commit()
			resp = jsonify('User updated successfully!')
			resp.status_code = 200
			return resp
		else:
			return not_found()
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.route('/delete/<int:id>')
def delete_user(id):
	try:
		conn = mysql.connect()
		cursor = conn.cursor()
		cursor.execute("DELETE FROM tbl_user WHERE user_id=%s", (id,))
		conn.commit()
		resp = jsonify('User deleted successfully!')
		resp.status_code = 200
		return resp
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()
		
@app.errorhandler(404)
def not_found(error=None):
    message = {
        'status': 404,
        'message': 'Not Found: ' + request.url,
    }
    resp = jsonify(message)
    resp.status_code = 404

    return resp
		
if __name__ == "__main__":
    app.run()

Step 4. Create MySQL database table – tbl_user with the following structure.

CREATE TABLE `tbl_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Enough coding… let’s move on to testing…

Related Posts:

Testing the Application

Now navigate to the user_crud directory and execute the command python main.py as shown in the below image, your server will start on default port 5000. If you want to change the port then you can change the line app.run() to app.run(port=50001), where 5001 is the new port.

python crud example using flask and mysql

You can use Postman, REST Client etc. to test your REST or RESTful APIs. Here I used Postman to test the above REST APIs.

Display all users

GET  http://localhost:5000/users

[
    {
        "user_email": "[email protected]",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    }
]

Assuming I already had one user in the MySQL table.

Add new user

POST  http://localhost:5000/add

Request Body:

{
	"name":"Soumitra",
	"email":"[email protected]",
	"pwd":"pwd"
}

Response:

“User added successfully!”

Display all users

GET  http://localhost:5000/users

[
    {
        "user_email": "[email protected]",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    },
    {
        "user_email": "[email protected]",
        "user_id": 2,
        "user_name": "Soumitra",
        "user_password": "pbkdf2:sha256:50000$JAYj9hea$ce6e0589539eeebba812bf2a7e732afcbe2a3a298248f7f94878804b40709743"
    }
]

Update user

POST  http://localhost:5000/update

Request Body:

{
	"id":2,
	"name":"Soumitra Roy",
	"email":"[email protected]",
	"pwd":"pwd"
}

Response:

“User updated successfully!”

Display all users

GET  http://localhost:5000/users

[
    {
        "user_email": "[email protected]",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    },
    {
        "user_email": "[email protected]",
        "user_id": 2,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$16NmgrDj$910f8a743ca9e1df81718bb25b1cfadcd3d3e31050234b86ea622fa47818fea7"
    }
]

Display single user

GET  http://localhost:5000/user/2

{
    "user_email": "[email protected]",
    "user_id": 2,
    "user_name": "Soumitra Roy",
    "user_password": "pbkdf2:sha256:50000$16NmgrDj$910f8a743ca9e1df81718bb25b1cfadcd3d3e31050234b86ea622fa47818fea7"
}

Delete a user

GET  http://localhost:5000/delete/2

Response:

“User deleted successfully!”

Display all users

GET  http://localhost:5000/users

[
    {
        "user_email": "[email protected]",
        "user_id": 1,
        "user_name": "Soumitra Roy",
        "user_password": "pbkdf2:sha256:50000$zQLJxLem$40a50a43592f9643a335ef53dc1cdfe312292c428dd4f20c36f8627ef40ee43b"
    }
]

Source Code

download source code

Thanks for reading.

Related posts

30 Thoughts to “Python REST API CRUD Example using Flask and MySQL”

  1. rethenya

    I’m dealing with the same problem as Divya does, what can I do now?

  2. LunatikCoder

    Thanks for this tutorial, everything works perfectly ! Just made my first api with python, it’s really cool :)

  3. Tanay

    I am getting the same error like Santiago even after putting those 2 lines above the try block. Please help.

  4. kindlebit

    awesome tutorial for new bie :)

  5. Divya

    When i try to send get/post request in postman i receive this message below. Can anyone help me out with this?

    500 Internal Server Error
    Internal Server Error
    The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

  6. joey

    thanks man!
    but in case i have to connect to a database out of my local host what should i do?

    1. you need to replace localhost by your remote address. also you need to update port if it is different.

  7. joey

    @app.route(‘/user/’)
    def user(id):
    try:
    conn = mysql.connect()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(“SELECT * FROM rfid_log WHERE ser_no=%s”, id)
    row = cursor.fetchone()
    resp = jsonify(row)
    resp.status_code = 200
    return resp
    except Exception as e:
    print(e)
    finally:
    cursor.close()
    conn.close()

    this is the code when i run it with another database i have it gives me 404 error but when i pass the argument as http://localhost/users the code works fine but when i try to run the above function it shows me 404 is ther any problem with fetching one single row?

    1. Please check the updated endpoint.

  8. Amar Badani

    I am getting a error on running http://127.0.0.1:5000/. This is the error in console – 127.0.0.1/:1 Failed to load resource: the server responded with a status of 404 (NOT FOUND)

  9. Wahba

    It is working guys, you should specify “Content-Type: application/json” header in your POST request.

    1. Wensong Liu

      wow, this helps a lot! Thank u very much!!!

  10. Louis

    flaskext is not available for pyton 3.6.8

  11. stephen

    I Got an a error … will you help me.

  12. destroyer

    hi everything is working except delete and user .can anyone help me

    1. I retested, the code working fine. Please don’t misguide the other people.

      1. fresh

        its not on python3 and latest version of flask.
        there is need to change router to:

        @app.route(‘/test/’)

      2. That’s why Python version is already mentioned in prerequisites section.

  13. Joseph

    my app can’t run, the Traceback message is:
    Traceback (most recent call last):
    File “main.py”, line 3, in
    from db_config import mysql
    ModuleNotFoundError: No module named ‘db_config’.

    i installed flask, flask-mysql, falsk-tables modules and used a virtual environment

  14. Kuldeep Singh

    Very informative post. Every things are working.
    Thanks Soumitra.

  15. Sonu

    Can you please share the correct code as /add, /update,/detele is not working and throwing the error.

    1. what error ? if you face the same error as Santiago then please see my previous reply.

  16. Rahul

    Hi Soumitra, Your article is very good except the point that none of the method for add, delete and updates are working and giving the error as mentioned by Santiago. It would be helpful if you share the updated version of the code.

    1. I told to move the below lines before try block:

      conn = mysql.connect()
      cursor = conn.cursor()

    2. Kuldeep Singh

      Dear Rahul,
      All methods are working,just you have to modify your request.
      Change parameter type text to json in boby->raw.

  17. Santiago

    Hi Soumitra

    I tried, with postman, to add users! But i have this error

    UnboundLocalError: local variable ‘cursor’ referenced before assignment

    http://localhost:5000/add?name=usuario&[email protected]&password=123456

    1. you can try to define cursor before try block

    2. Kuldeep Singh

      Replace json code :

      _json = request.json
      _name = _json[‘name’]
      _email = _json[’email’]
      _password = _json[‘pwd’]

      With request args:

      _name = request.args.get(‘name’)
      _email = request.args.get(’email’)
      _password =request.args.get(‘pwd’)

    3. Nirmal balaji.S

      @app.route(‘/delete/’) here is the error pls change it
      def delete_user(id):
      try:
      conn = mysql.connect()
      cursor = conn.cursor()
      cursor.execute(“DELETE FROM tbl_user WHERE user_id=%s”, (id,))
      conn.commit()
      resp = jsonify(‘User deleted successfully!’)
      resp.status_code = 200
      return resp
      except Exception as e:
      print(e)
      finally:
      cursor.close()
      conn.close()

      1. I retested, the code working fine. Please don’t misguide the other people.

Leave a Comment