Flask API Part 2 Product List: Create Database & Flask App API | Create Json API Using Flask

• What will we Learn In This Video:-
• Learn Python Flask
• Learn Database MySQL Connection Using Flask
• Learn flask Web
• Learn Flask Table And Insert Data In Database
• Generate URLS
• Get Responce And Return The Data
• HTTP Methods ( GET / POST / PATCH / DELETE / PUT)
• Operations On Database Using flask Python
• CREATE / SELECT / UPADATE / DELETE
• Return Json Data RestAPI

In This Video We Are Lerning

• Creating database in mysql
• create Products.py for product api
• Creating API Call Url in this video

Create Database in Mysql

Create a database in MySQL:

• Connect to your MySQL server using the mysql command-line client or a GUI tool like phpMyAdmin.
• Run the following command to create a new database called “ecommerce”: CREATE DATABASE ecommerce;
• Create a table called “products” with the following columns: id, name, price, quantity, description
• Run a SQL query like this to create the table:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    description TEXT
);

Create a Flask API in your app.py file:

• Import the required libraries and modules:

from flaskext.mysql import MySQL
from flask import Flask

Connect to the MySQL database using the following code:

app = Flask(__name__)
app.config['MYSQL_HOST'] = 'host'
app.config['MYSQL_USER'] = 'user'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'ecommerce'
mysql = MySQL(app)

Define a route for the product list:

@app.route('/products', methods=['GET'])
def get_products():
    cur = mysql.connection.cursor()
    cur.execute('''SELECT * FROM products''')
    rv = cur.fetchall()
    return jsonify(rv)

1. Create a JSON API using Flask:
   • In the above-defined route return the fetched data in JSON format using the jsonify() method.
   • To test the API, run the Flask application by running flask run in the command line, and then make a GET request to the /products endpoint using a tool like Postman.

Note: This is a basic setup, you can add more functionality like pagination, filtering, adding products, updating and deleting products, and more features according to your requirement. Also, You should also use ORM(Object-relational mapping) or SQLAlchemy to interact with the Database, it will make your code more readable and maintainable.

Lets Create Product database and connection in Python Flask

connection.py

from flaskext.mysql import MySQL
from flask import Flask

app=Flask(__name__)

mysql=MySQL()
app.config['MYSQL_HOST']="localhost"
app.config['MYSQL_DATABASE_USER']="admin"
app.config['MYSQL_DATABASE_PASSWORD']="ADMIN"
app.config['MYSQL_DATABASE_DB']="productdb"
app.config['MYSQL_DATABASE_CURSOR']='DictCursor'

mysql.init_app(app)

You Can also Save Credentials in db.yaml file for security

Now Lets Create The product list from database

Product.py

from connectdb import mysql
import uploadfiles
class Products:
    def __init__(self):
        self.cursor=mysql.connect().cursor()

    def getProducts(self,limit):
        #write query for get data from database table view/show data
        self.cursor.execute("SELECT * FROM product ORDER BY id ASC LIMIT %s",limit)
        aData=self.cursor.fetchall()
        payload=[]
        content={}
        for result in aData:
            content = {
                'id':result[0],
                'title' :result[1],
                'description': result[2],
                'image_url':result[3],
                'price':result[4],
                'review':result[5],
                'rating':result[6],
                'link':result[7],
                'category':result[8],
            }
            payload.append(content)
            content={}
        return payload

    def createProduct(self,data):
        id=data['id']
        title=data['title'],
        description=data['description'],
        price=data['price'],
        review=data['review'],
        rating=data['rating'],
        category=data['category']
        if(data['image_url']==''):
            image_url=""
        else:
            # fileurl=uploadfiles.files_image(data['image_url'])
            image_url=data['image_url']
        
            sql="INSERT INTO `product`(`title`,`description`,`image_url`, `price`, `review`,`rating`,'category'  ) VALUES (%s,%s,%s,%s,%s,%s,%s)"
            rez=self.cursor.execute(sql,(title,description,image_url,price,review,rating,category))   
            self.cursor.connection.commit()
        if(rez==1):
            return 200
        else:
            return 400

    def updateProduct(self,data,pid):
        id=data['id'],
        title=data['title'],
        description=data['description'],
        image_url=data['image_url'],
        price=data['price'],
        review=data['review'],
        rating=data['rating'],
        link=data['link'],
        category=data['category']
        sql="UPDATE `product` SET `title`=%s,`description`=%s ,`image_url`=%s,`price`=%s,`review`=%s, `rating`=%s, `link`=%s, category=%s where id=%s"
        rez=self.cursor.execute(sql,(title,description,image_url,price,review,rating,link,category,pid))
        if(rez==1):
            return 200
        else:
            return 400
    
    def deleteProduct(self,id):
        sql="DELETE from product where id=%s"
        rez=self.cursor.execute(sql,id)
        if(rez==1):
            return 200
        else:
            return 400
    def getSingleProduct(self,id:int):
        self.cursor.execute("SELECT * FROM product where id=%s",id)
        aData=self.cursor.fetchall()
        payload=[]
        content={}
        for result in aData:
            content = {
                'id':result[0],
                'title' :result[1],
                'description': result[2],
                'image_url':result[3],
                'price':result[4],
                'review':result[5],
                'rating':result[6],
                'link':result[7],
                'category':result[8],
            }
            payload.append(content)
            content={}
        return payload
    def __del__(self):
        self.cursor.close()