RESTFul API is an advance concept of web development that implemented at server and used with HTTP method (GET/ POST/ PUT/ DELETE) to handle the data. The HTTP request methods and resources are identified using URIs and handled functionality accordingly.
If you’re thinking about implementing Python RESTful API, then you’re here at right place. In our previous tutorial, you have learned about implementing RESTFul API with CodeIgniter. In this tutorial, you will learn how to create Python RESTFul API using MYSQL.
We will cover this tutorial with live example with HTTP methods like (GET/ POST/ PUT/ DELETE) to implement Python RESTFul API with CRUD operations.
We hope you have installed Python in Your Windows or Linux with Python and its packages. Here we are using Python 3.10.4 version. We will use flask
, flask-mysql
and flask-cors
module.
Also, read:
- Create RESTful API using CodeIgniter
- Creating REST API with Golang
- Create Simple REST API with PHP & MySQL
So let’s create RESTful API using Python and MySQL. We have project directory restful-api-python
and the major files are:
- config.py
- app.py
- main.py
Step1: Create MySQL Database Table
As we will implement RESTful API to perform CRUD operations, so we will create MySQL database table emp
to perform operations. So first we will create MySQL database rest-api
and then create table emp
using below table create statement.
CREATE TABLE `emp` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `phone` varchar(16) DEFAULT NULL, `address` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `emp` ADD PRIMARY KEY (`id`); ALTER TABLE `emp` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Step2: Import Flask Modules
As we handle handle REST API functionality using Flask
and MySQL
, so we will need both the modules. The module Flask
works as web framework while MySQL
module require to make connection with MySQL database.
So first we will create project directory restful-api-python
and move inside using cd
command.
Then we will install flask
module by running below command.
pip install Flask
we will also install flask-cors
extension for handling Cross Origin Resource Sharing (CORS), making cross-origin possible.
pip install -U flask-cors
So now we will create the app.py
Python script and import the flask
module and create the flask
instance to use with MySQL
module. We will also import code>flask-cors extension for cross-origin.
from flask import Flask from flask_cors import CORS, cross_origin app = Flask(__name__) CORS(app)
Ste3: Create MySQL Connection
We need to install Flask-MySQL
extension that allows you to access a MySQL database. We will install Flask-MySQL
extension using below command.
pip install flask-mysql
We will create config.py
Python file to initialize MySQL database connection details to make connection with MySQL database. We will import the app
script to handle MySQL database connection with Flask-MySQL
module.
As we have already created MySQL database rest-api
, so we will connect by providing connection details.
from app import app from flaskext.mysql import MySQL mysql = MySQL() app.config['MYSQL_DATABASE_USER'] = 'root' app.config['MYSQL_DATABASE_PASSWORD'] = '' app.config['MYSQL_DATABASE_DB'] = 'rest-api' app.config['MYSQL_DATABASE_HOST'] = 'localhost' mysql.init_app(app)
Step4: Create REST API CRUD Operation
We will create main.py
script and import the app
and config
modules. We will connect to MySQL database and implement CRUD operations by defining all REST URIs.
Here we have used POST
HTTP method to create new employee records to MySQL database. We have used GET
HTTP method to get all employee records or individual record and used PUT
HTTP method for employee record update. Also implemented DELETE
HTTP method to delete record. If there are no record found then defined 404 method to handle not found error.
import pymysql from app import app from config import mysql from flask import jsonify from flask import flash, request @app.route('/create', methods=['POST']) def create_emp(): try: _json = request.json _name = _json['name'] _email = _json['email'] _phone = _json['phone'] _address = _json['address'] if _name and _email and _phone and _address and request.method == 'POST': conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) sqlQuery = "INSERT INTO emp(name, email, phone, address) VALUES(%s, %s, %s, %s)" bindData = (_name, _email, _phone, _address) cursor.execute(sqlQuery, bindData) conn.commit() respone = jsonify('Employee added successfully!') respone.status_code = 200 return respone else: return showMessage() except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/emp') def emp(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT id, name, email, phone, address FROM emp") empRows = cursor.fetchall() respone = jsonify(empRows) respone.status_code = 200 return respone except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/emp/') def emp_details(emp_id): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT id, name, email, phone, address FROM emp WHERE id =%s", emp_id) empRow = cursor.fetchone() respone = jsonify(empRow) respone.status_code = 200 return respone except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/update', methods=['PUT']) def update_emp(): try: _json = request.json _id = _json['id'] _name = _json['name'] _email = _json['email'] _phone = _json['phone'] _address = _json['address'] if _name and _email and _phone and _address and _id and request.method == 'PUT': sqlQuery = "UPDATE emp SET name=%s, email=%s, phone=%s, address=%s WHERE id=%s" bindData = (_name, _email, _phone, _address, _id,) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sqlQuery, bindData) conn.commit() respone = jsonify('Employee updated successfully!') respone.status_code = 200 return respone else: return showMessage() except Exception as e: print(e) finally: cursor.close() conn.close() @app.route('/delete/', methods=['DELETE']) def delete_emp(id): try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM emp WHERE id =%s", (id,)) conn.commit() respone = jsonify('Employee deleted successfully!') respone.status_code = 200 return respone except Exception as e: print(e) finally: cursor.close() conn.close() @app.errorhandler(404) def showMessage(error=None): message = { 'status': 404, 'message': 'Record not found: ' + request.url, } respone = jsonify(message) respone.status_code = 404 return respone if __name__ == "__main__": app.run()
Step5: Run Application
Now we will go the project directory restful-api-python
and execute the the command python main.py
and the server will start on default port 5000. Now we will use Postman to run our Python RESTful API with (POST, GET, PUT or DELETE) methods to test it.
We will run the below URL with HTTP GET
method to get the all employee and display data in JSON format.
http://localhost:5000/emp
The following JSON data response will be returned:
We will get the employee record in JSON data with id 1 using below URL with GET
HTTP method.
http://localhost:5000/emp/1
The response will be JSON data:
We will create new employee record with POST
HTTP method.
http://localhost:5000/create
The request body will be following:
The response will JSON data employee add message.
We will update existing employee record with #1 using PUT
HTTP method.
http://localhost:5000/update
The request body will be following:
The response will employee record update message.
We will delete existing employee with id #3 using DELETE
HTTP method.
http://localhost:5000/delete/4
The response will employee record delete message.
You have completed tutorial about Python RESTFul API using Flask and MySQL with examples. You can further implement this into your project according to your requirement. If you have any query, you can submit your precious comments. Thanks!
You may also like:
- User Management System with PHP & MySQL
- Datatables Add Edit Delete with Ajax, PHP & MySQL
- Build Helpdesk System with jQuery, PHP & MySQL
- Build Online Voting System with PHP & MySQL
- School Management System with PHP & MySQL
- DataTables Add Edit Delete with CodeIgniter
- Create RESTful API using CodeIgniter
- Build Reusable Captcha Script with PHP
- Product Search Filtering using Ajax, PHP & MySQL
- Image Upload and Crop in Modal with jQuery, PHP & MySQL
- Build Push Notification System with PHP & MySQL
- Project Management System with PHP and MySQL
- Hospital Management System with PHP & MySQL
- Build Newsletter System with PHP and MySQL
- Skeleton Screen Loading Effect with Ajax and PHP
- Build Discussion Forum with PHP and MySQL
- Customer Relationship Management (CRM) System with PHP & MySQL
- Online Exam System with PHP & MySQL
- Expense Management System with PHP & MySQL
You can download full the script from the Download link below.
I didn’t Understand 1st step where to create mysql code. Do we need to download mysql or it comes with flask
you need this table to run this example code. thanks!
I had to change the finally and Im still geting a error on get/post
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 2464, in __call__
return self.wsgi_app(environ, start_response)
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 2450, in wsgi_app
response = self.handle_exception(e)
File “/usr/local/lib/python3.8/site-packages/flask_cors/extension.py”, line 165, in wrapped_function
return cors_after_request(app.make_response(f(*args, **kwargs)))
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File “/usr/local/lib/python3.8/site-packages/flask/_compat.py”, line 39, in reraise
raise value
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 2447, in wsgi_app
response = self.full_dispatch_request()
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 1953, in full_dispatch_request
return self.finalize_request(rv)
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 1968, in finalize_request
response = self.make_response(rv)
File “/usr/local/lib/python3.8/site-packages/flask/app.py”, line 2097, in make_response
raise TypeError(
TypeError: The view function did not return a valid response. The function either returned None or ended without a return statement.
When I try to add data to the table, NULL is getting appended instead of the data provided through Postman
I am checking this and update you soon, thanks!