Create A CRUD Based App using Flask, Python & MySql

In this article, we will create a CRUD Based App Restful API of the important tasks such as Create, Read, Update, Delete, Select or simply CRUD operations.

We are going to use Python as our language, MySql as a database and Flask as our python framework.

If you wish to perform CRUD using MongoDB then write in the comment section below

We are not going to build any front-end like Angular or VueJs for flask to serve it, rather we are going to use Postman to test our API’s. If you wish to create a Front end to request from the Flask API’s then please comment down below

Pre-requisties:

  1. Basic understanding of Python
  2. Installation – Python 3.0, Postman
  3. MySql Database

Please run the following commands on your terminal for flask and mysql

pip install flask
pip install pymysql

In order to test if the Flask application is running or not, we can create a simple Hello World, for testing.

from flask import Flask

app = Flask(__name__)

@app.route('/index')
def index():
    return "Hello, World!"

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

Now run this file as py app.py in your terminal (app.py is the name I given to this script, you can name whatever you want)

At http://localhost:5000/ you should get Hello, World!

Now that we have our Flask all set, we shall move to database and create a simple table.

create table FlaskMySql(
NameID int AUTO_INCREMENT primary key ,
firstname varchar(30),
lastname varchar(30)
)

Now we have our table created in MySql, please bear in my mind that we need few details of our database for connectivity of it with Flask. For example – password, user, database name and so on.

Now we are going to create 3 different file conn.py (For our MySql connectivity), app.py (Entry point of application) and api.py (Our actual CRUD API’s)

Create a CRUD Based App with code.

  1. app.py File
from flask import Flask

app = Flask(__name__)

# For Testing Only

# @app.route('/')
# @app.route('/index')
# def index():
#     return "Hello, World!"


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

2. MySql Connectivity filename : conn.py

from app import app
from flaskext.mysql import MySQL

mysql = MySQL()

# MySQL configurations
app.config["MYSQL_DATABASE_USER"] = "root"
app.config["MYSQL_DATABASE_PASSWORD"] = "sql@2019"
app.config["MYSQL_DATABASE_DB"] = "hybrid"
app.config["MYSQL_DATABASE_HOST"] = "localhost"
app.config['MYSQL_DATABASE_SOCKET'] = None

mysql.init_app(app)

3. api.py

import pymysql
from app import app
from conn import mysql
from flask import jsonify, request, flash


#GET ALL
@app.route('/')
@app.route('/select', methods=['GET'])
def user():
    try:
        conn = mysql.connect()
        cur = conn.cursor(pymysql.cursors.DictCursor)
        cur.execute("SELECT * FROM FlaskMysql")
        rows = cur.fetchall()
        resp=jsonify(rows)
        resp.status_code = 200
        return resp
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


#GET ONE
@app.route('/select/<id>', methods=['GET'])
def userone(id):
    try:
        conn = mysql.connect()
        cur = conn.cursor(pymysql.cursors.DictCursor)
        cur.execute("SELECT * FROM FlaskMysql WHERE NAMEID ="+id)
        rows = cur.fetchall()
        resp=jsonify(rows)
        resp.status_code = 200
        return resp
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#INSERT
@app.route('/insert', methods=['POST'])
def inst():
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    firstname = request.json['firstname']
    lastname = request.json['lastname']
    query = "insert into FlaskMysql (firstname, lastname) values ('"+ firstname +"', '"+ lastname +"')"
    cur.execute(query)
    conn.commit()
    cur.close()
    output = {'firstname' : request.json['firstname'], 'lastname' : request.json['lastname'], 'Message': 'Success'}


    return jsonify({'result' : output})

#Update
@app.route('/update/<id>', methods=['PUT'])
def updates(id):
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    firstname = request.json['firstname']
    lastname = request.json['lastname']
    query = "update FlaskMysql set firstname = '"+ firstname +"', lastname = '"+ lastname +"' Where NameId = '"+ id +"'"
    cur.execute(query)
    conn.commit()
    cur.close()
    output = {'firstname' : request.json['firstname'], 'lastname' : request.json['lastname'], 'Message': 'Success'}


    return jsonify({'result' : output})

#DELETE
@app.route('/delete/<id>', methods=['DELETE'])
def delete(id):
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    firstname = request.json['firstname']
    lastname = request.json['lastname']
    query = "DELETE FROM FLASKMYSQL Where NameId = '"+ id +"'"
    cur.execute(query)
    conn.commit()
    cur.close()
    output = {'firstname' : request.json['firstname'], 'lastname' : request.json['lastname'], 'Message': 'DELETED'}
    return jsonify({'result' : output})
    
if __name__ == "__main__":
    app.debug = True
    app.run()
   

Now run api.py in your terminal, and it should succesfully work

Testing

Insert API

Testing

Select All API

Select All API

Select By Id

Select By Id

Update By Id API

Update By Id API

Delete By Id

Delete By Id

All API’s has been tested succesfully

If you Have any queries or you are facing bugs/error or you want the source code to this project just write down on the comment section below

THANKS !!!

About the author

Siddharth Singha

View all posts
4 1 vote
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback

[…] If you are interested in knowing about how to create back-end API using python & flask check this link http://datacyper.com/index.php/2020/03/15/create-a-crud-based-app-using-flask-python-mysql/ […]

python Online Training

I’m a Python Developer, Giving Python Online Training