Flask with MySQL - flask crud application

on

What is CRUD? 

CRUD means Create, Read, Update, and Delete operations. In this example, we will use the MySQL database to store data. 
We will use the Flask Module to handle the request and response from the end-user or client. So let's start implementing using Flask.

So, let's start - 
If you have installed all the requirements for this then continue otherwise click on Flask and MySQL Setup.

Database : 

Create a database in your MySQL :
 > Create database firstproject
Create Table :
CREATE TABLE products( 

  `id` int NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `description` longtext NOT NULL,
  `gender` enum('male','female','others') NOT NULL,
  `num_images` int DEFAULT NULL,
  `price` decimal(10,0) NOT NULL,
  `primary_color` varchar(45) DEFAULT NULL,
  `sku` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku_UNIQUE` (`sku`)
)

After the Database setup let's start with flask, So first we will try to build the connection between Flask and MySQL. For this, we are going to use the PyMySQL package. Follow the given steps from the picture -

After completing the above steps from the image, install the PyMySQL package.

> pip install PyMySQL

Here all the installation and setups are completed now we will move to the implementation. So first create a file with the name db_connection.py and start the code for the connection between Flask and MySQL -

Step - 1 :

import pymysql.cursors

# database connection setup
def mysqlCursor():
    connection = pymysql.connect(host='localhost',
                    user='username',
                    password='password',
                    database='dbname',
                    cursorclass=pymysql.cursors.DictCursor)
    return connection

Step - 2:

Create a file with the name models.py to manage all database operation functions. I'm writing to write code for Create, read, update, and delete operation functions. These all are command methods that will help you to perform CRUD operations in your application - 

from flask import Flask
from db_connection import *

app = Flask(__name__)

cursor = mysqlCursor().cursor()
connection = mysqlCursor()

# function to add new record in database
def insert(params, tablename):
    try:
        name =  params["name"]
        brand =  params["brand"]
        description =  params["description"]
        gender =  params["gender"]
        num_images =  params["num_images"]
        price =  params["price"]
        primary_color =  params["primary_color"]
        sku =  params["sku"]

        query = "INSERT INTO products(`name`,`brand`,`description`,`num_images`,`price`,`primary_color`,`gender`,`sku`) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(query,(name,brand,description,gender,num_images,price,primary_color,sku))
        connection.commit()
        return True
    except Exception as e:
        print(e)
        return e

# function to fetch all record from database
def fetchAll():
    
    query = "SELECT * FROM `products`"
    cursor.execute(query)
    rows = cursor.fetchall()
    if rows:
        return rows
    return False

# function to delete record based on id
def delete(id):
    try:
        query = "DELETE FROM `products` WHERE id =%s"
        cursor.execute(query,(id))
        connection.commit()
        return True
    except Exception as e:
        return False

# function to update record from database based on id
def update(params,id):
    try:
        name =  params["name"]
        brand =  params["brand"]
        description =  params["description"]
        gender =  params["gender"]
        num_images =  params["num_images"]
        price =  params["price"]
        primary_color =  params["primary_color"]
        sku =  params["sku"]

        query = "UPDATE `products` SET `name`=%s,`brand`=%s,`description`=%s,`gender`=%s,`num_images`=%s,`price`=%s,`primary_color`=%s,`sku`=%s WHERE id =%s"
        cursor.execute(query,(name,brand,description,gender,num_images,price,primary_color,sku,id))
        connection.commit()
        return True
    except Exception as e:
        return False

# function to fetch single row from the database based on id
def fetchRow(id):

    query = "SELECT * FROM `products` WHERE id=%s"
    cursor.execute(query,(id))
    row = cursor.fetchone()
    if row:
        return row
    return False

Step - 3:

After the above steps create a file with the name view.py. This file will be one type of controller of your application. Let's start the code of this file - 

from flask import Flask,render_template,request,redirect,session,url_for,flash
from models import *

app = Flask(__name__)
app.secret_key = 'secret key'

@app.route('/index')
def index():
   
    rows = fetchAll()
    return render_template('index.html',products = rows)

@app.route('/add',methods=["POST","GET"])
def add():
    try:
        if request.method == "POST":
            data = {
                "name":  request.form["name"],
                "brand":  request.form["brand"],
                "description":  request.form["description"],
                "gender":  request.form["gender"],
                "num_images":  request.form["num_images"],
                "price":  request.form["price"],
                "primary_color":  request.form["primary_color"],
                "sku":  request.form["sku"]
            }

            insert_data = insert(data)
            if insert_data:
                flash("Added successfully")
                return redirect(url_for('index'))
            
    except Exception as e:
        return flash("Something went wrong")

    return render_template('add.html')

@app.route('/edit')
@app.route('/edit/<id>',methods=["GET","POST"])
def edit(id=None):
    try:
        if request.method == "POST":
            data = {
                "name":  request.form["name"],
                "brand":  request.form["brand"],
                "description":  request.form["description"],
                "gender":  request.form["gender"],
                "num_images":  request.form["num_images"],
                "price":  request.form["price"],
                "primary_color":  request.form["primary_color"],
                "sku":  request.form["sku"]
            }
            product_update = update(data,id)
            if product_update:
                return redirect(url_for('index'))
        else:
            row = fetchRow(id)
            return render_template('edit.html',product=row)
    
    except Exception as e:
        return False


@app.route('/delete/<id>')
def delete(id):
    delete_data = delete(id)
    if delete_data:
        flash(f"User id {id} Deleted Successfully")
        return redirect(url_for('index'))
    else:
        flash(f"System is not able to delete user id {id} data")
        return redirect(url_for('index'))

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

Step - 4:

After adding the above code, create a template to perform all the operations efficiently with the help of UI.

index.html 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Grid</title>
</head>
<body>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Brand</th>
            <th>Description</th>
            <th>Gender</th>
            <th>Num_Images</th>
            <th>Price</th>
            <th>Primary_Color</th>
            <th>Sku</th>
        </tr>
        {% if not products %}
        <tr>
            <td colspan="7" class="text-center">No data available</td>
        </tr>
    {% else %}
        {% for product in products %}
            <tr>
                <td>{{ loop.index }}</td>
                <td>{{ product['name'] }}</td>
                <td>{{ product['brand'] }}</td>
                <td>{{ product['description'] }}</td>
                <td>{{ product['gender'] }}</td>
                <td>{{ product['num_images'] }}</td>
                <td>{{ product['price'] }}</td>
                <td>{{ product['primary_color'] }}</td>
                <td>{{ product['sku'] }}</td>
                <td>
                    <a href="{{ url_for('edit',id=product['id']) }}" class="btn text-primary"><i class="fa fa-pencil"></i></a>
                    <a href="{{ url_for('delete',id=product['id']) }}" class="btn text-danger" onclick="return confirm('Are you sure you want to delete this item?')"><i class="fa fa-trash"></i></a>
                </td>
            </tr>
        {% endfor%}
    {% endif %}
    </table>
</body>
</html>

add.html 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Add</title>
</head>
<body>
    <form action="/add" method="post">
        <input type="text" name="name" placeholder="Enter name details">
        <input type="text" name="brand" placeholder="Enter brand details">
        <input type="text" name="description" placeholder="Enter description details">
        <input type="text" name="gender" placeholder="Enter gender details">
        <input type="text" name="num_images" placeholder="Enter num_images details">
        <input type="text" name="price" placeholder="Enter price details">
        <input type="text" name="primary_color" placeholder="Enter primary_color details">
        <input type="text" name="sku" placeholder="Enter sku details">
        <button type="submit">Save</button>
    </form>
</body>
</html>

edit.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Add</title>
</head>
<body>
    <form action="{{ url_for('edit',id=product['id']) }}" method="post">
        <input type="text" name="name" placeholder="Enter name details" value="{{ product['name'] }}">
        <input type="text" name="brand" placeholder="Enter brand details" value="{{ product['brand'] }}">
        <input type="text" name="description" placeholder="Enter description details" value="{{ product['description'] }}">
        <input type="text" name="gender" placeholder="Enter gender details" value="{{ product['gender'] }}">
        <input type="text" name="num_images" placeholder="Enter num_images details" value="{{ product['num_images'] }}">
        <input type="text" name="price" placeholder="Enter price details" value="{{ product['price'] }}">
        <input type="text" name="primary_color" placeholder="Enter primary_color details" value="{{ product['primary_color'] }}">
        <input type="text" name="sku" placeholder="Enter sku details" value="{{ product['sku'] }}">
        <button type="submit">Save</button>
    </form>
</body>
</html>

This is the complete code of CRUD operation in the flask, after all the steps now your can run your application with the help of the given command -

> flask --app views run

Comments

Post a Comment