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:

Basic understanding of Python
Installation – Python 3.0, Postman
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.

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