Task Assigned in Full Stack Data Science Class -- 20-08-2022.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c545f76c",
   "metadata": {},
   "source": [
    "### Task Assigned in Full Stack Data Science Class -- 20-08-2022"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce531e43",
   "metadata": {},
   "source": [
    "#### Mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff100924",
   "metadata": {},
   "outputs": [],
   "source": [
    "from flask import Flask ,request,jsonify\n",
    "import  mysql.connector as conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7306c46c",
   "metadata": {},
   "outputs": [],
   "source": [
    "app = Flask(__name__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e02b1069",
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = conn.connect(host = \"localhost\" , user = 'root' , passwd = \"password\")\n",
    "cursor = mydb.cursor()\n",
    "cursor.execute(\"create database if not exists api_task\")\n",
    "cursor.execute(\"create table if not exists api_task.api_table (name varchar(30) , number int)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa5dbded",
   "metadata": {},
   "outputs": [],
   "source": [
    "if __name__ == '__main__':\n",
    "    app.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b3c0716",
   "metadata": {},
   "source": [
    "#### 1. Write a program to insert a record in sql table via api database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da3c7b3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route('/insert/mysql',methods = ['POST'])\n",
    "def insert_record():\n",
    "    if request.method=='POST':\n",
    "        name = request.json['name']\n",
    "        number = request.json['number']\n",
    "        cursor.execute(\"insert into api_task.api_table values(%s , %s)\",(name ,number))\n",
    "        mydb.commit()\n",
    "        return jsonify(str('successfully inserted'))  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "041916b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "\n",
    "{\n",
    "    \"name\": \"xyz\",\n",
    "    \"number\":1232\n",
    "}\n",
    "\n",
    "\n",
    "{\n",
    "    \"name\": \"abc\",\n",
    "    \"number\":1456\n",
    "}\n",
    "\n",
    "\n",
    "{\n",
    "    \"name\": \"zzz\",\n",
    "    \"number\":8906\n",
    "}\n",
    "\n",
    "\n",
    "{\n",
    "    \"name\": \"yooy\",\n",
    "    \"number\":6758\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1b58ed8",
   "metadata": {},
   "source": [
    "#### 2. Write a program to update a record via api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26683bc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/update/mysql\" , methods= ['POST'])\n",
    "def update_record():\n",
    "    if request.method=='POST':\n",
    "         get_name = request.json['get_name']\n",
    "        cursor.execute(\"update api_task.api_table set number = number + 500 where name = %s \",(get_name,))\n",
    "         mydb.commit()\n",
    "        return jsonify(str(\"successfully updated\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bc08f1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "\n",
    "{\n",
    "    \"get_name\":\"xyz\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c3c37e8",
   "metadata": {},
   "source": [
    "#### 3. Write a program to delete a record via api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "428f686e",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/delete/mysql\" , methods= ['POST'])\n",
    "def delete_record():\n",
    "    if request.method == 'POST':\n",
    "        name_del = request.json['name_del']\n",
    "        cursor.execute(\"delete from api_task.api_table where name = %s\",(name_del,))\n",
    "        mydb.commit()\n",
    "        return jsonify(str(\"successfully deleted\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7435ea14",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "\n",
    "{\n",
    "    \"name_del\":\"abc\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18154da4",
   "metadata": {},
   "source": [
    "#### 4. Write a program to fetch a record via api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10091da0",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/fetch/mysql\",methods = ['POST','GET'])\n",
    "def fetch_record():\n",
    "    cursor.execute(\"select * from api_task.api_table\")\n",
    "    l = []\n",
    "    for i in cursor.fetchall():\n",
    "        l.append(i)\n",
    "    return jsonify(str(l))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b291693d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "# The below output is displayed in postman\n",
    "\n",
    "\"[('xyz', 1732), ('zzz', 8906), ('yooy', 6758)]\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b53ec0d",
   "metadata": {},
   "source": [
    "#### Mongodb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b7adaa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from flask import Flask , request,jsonify\n",
    "import pymongo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0541cdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "app = Flask(__name__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "384934f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "client = pymongo.MongoClient(\"mongodb+srv://username:<password>@cluster.link.mongodb.net/?retryWrites=true&w=majority\")\n",
    "db = client.test\n",
    "\n",
    "database = client['api_task']\n",
    "collection= database['api_collection']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1f18b7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "if __name__=='__main__':\n",
    "    app.run(port=5001)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89aa782e",
   "metadata": {},
   "source": [
    "#### 1. Write a program to insert a record in mongodb collection via api database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cda3deb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/insert/mongo\" , methods=['POST'])\n",
    "def insert_rec():\n",
    "    if request.method == 'POST':\n",
    "        name = request.json['name']\n",
    "        age = request.json['age']\n",
    "        collection.insert_one({name:age})\n",
    "        return jsonify(str(\"successfully inserted \"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4bb7d3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "\n",
    "{\n",
    "    \"name\": \"abc\",\n",
    "    \"age\": 21\n",
    "}\n",
    "\n",
    "\n",
    "{\n",
    "    \"name\": \"xyz\",\n",
    "    \"age\": 23\n",
    "}\n",
    "\n",
    "\n",
    "{\n",
    "    \"name\": \"zzyy\",\n",
    "    \"age\": 27\n",
    "}\n",
    "\n",
    "\n",
    "{\n",
    "    \"name\": \"abyz\",\n",
    "    \"age\": 30\n",
    "}\n",
    "\n",
    "{\n",
    "    \"name\": \"purple\",\n",
    "    \"age\": 27\n",
    "}\n",
    "\n",
    "{\n",
    "    \"name\": \"grey\",\n",
    "    \"age\": 143\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d703cf28",
   "metadata": {},
   "source": [
    "#### 2. Write a program to update a record via api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fecb81c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/update/mongo\", methods=['POST'])\n",
    "def update_mongo():\n",
    "    if request.method == 'POST':\n",
    "        get_age = request.json['get_age']\n",
    "        set_age = request.json['set_age']\n",
    "        collection.update_one(get_age, set_age)\n",
    "        return jsonify(str(\"successfully updated\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf1b9cb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "\n",
    "{\n",
    "    \"get_age\" : { \"age\": 27 },\n",
    "    \"set_age\" : {\"$set\": {\"age\": 28 }}\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8afdab5c",
   "metadata": {},
   "source": [
    "#### 3. Write a program to delete a record via api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24100f71",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/delete/mongo\", methods=['POST'])\n",
    "def delete_mongo():\n",
    "    if request.method == 'POST':\n",
    "        rec_del = request.json['rec_del']\n",
    "        collection.delete_one(rec_del,)\n",
    "        return jsonify(str(\"successfully deleted\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d95de47",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "\n",
    "{\n",
    "    \"rec_del\" :{\"name\":\"purple\"}\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5623759c",
   "metadata": {},
   "source": [
    "#### 4. Write a program to fetch a record via api."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51e6d600",
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route(\"/fetch/mongo\", methods=['POST'])\n",
    "def fetch_mongo():\n",
    "    if request.method == 'POST':\n",
    "        x = collection.find()\n",
    "        l = []\n",
    "        for i in x:\n",
    "            l.append(i)\n",
    "        return jsonify(str(l))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91cc3a42",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Postman\n",
    "# The following output is displayed in postman\n",
    "\n",
    "\"\"\"[{'_id': ObjectId('6305f02abc4bae2282a37674'), 'abc': 21}, {'_id': ObjectId('6305f046bc4bae2282a37675'), 'xyz': 23}, \n",
    "{'_id': ObjectId('6305f061bc4bae2282a37676'), 'zzyy': 28}, {'_id': ObjectId('6305f081bc4bae2282a37677'), 'abyz': 30}, \n",
    ", {'_id': ObjectId('630630f5e5956eb544391393'), 'grey': 143}]\"\"\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}