- select employees in descending order – salary
- select all employees from Mumbai
- select all employees having salary more than average salary
- select sum of salary from table
- select all unique address
- select details, salary from table
(details should be concatenation of name and address) - select names of employees having max salary
- select employees having 2nd max salary
- count employees by address, order by employee count
e.g. select count(name,address) from employee - show count of employees from nagpur only
- select all employees whose names starts or ends with vowels
- find employees having max salary in particular city
- select top 5 salaried employees
- select 2nd highest salaried employees
- show cities having total salary more than 200000
- Show all students who appeared for exam
- show student, subject and total marks of students whose total marks are more than 80
Author: admin
Python
Online Tool
Software to be installed for Python
Python
https://www.python.org/downloads/
XAMPP
https://www.apachefriends.org/download.html
Git for Windows
https://gitforwindows.org/
Nodepad++
https://notepad-plus-plus.org/downloads/
Heroku cli
https://devcenter.heroku.com/articles/heroku-cli#download-and-install
PHP CRUD CHEAT SHEET
MySQL Schema
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` int NOT NULL,
`city` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
REST API
rest_api.php
<?php
header('Access-Control-Allow-Origin: *');
$servername = "localhost";
$username = "diituser";
$password = "%TGBbgt5";
$dbname = "ecom";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
switch($_SERVER['REQUEST_METHOD']) {
case 'GET':
if(isset($_REQUEST['id']) && $_REQUEST['id'] != '') {
$id = $_REQUEST['id'];
$sql = "SELECT * FROM users WHERE id = $id";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$records = $stmt->fetch();
} else {
$sql = "SELECT * FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$records = $stmt->fetchAll();
}
echo json_encode($records);
case 'DELETE':
$data = json_decode(file_get_contents('php://input'), true);
$id = $data["id"];
$sql = "DELETE FROM users WHERE id = $id";
$stmt = $conn->prepare($sql);
$stmt->execute();
echo json_encode(["result" => "success"]);
case 'POST':
$data = json_decode(file_get_contents('php://input'), true);
$name = $data["name"];
$age = $data['age'];
$city = $data['city'];
$sql = "INSERT INTO `users` (`id`, `name`, `age`, `city`) VALUES (NULL, '$name', '$age', '$city'); ";
$stmt = $conn->prepare($sql);
$stmt->execute();
echo json_encode(["result" => "success"]);
case 'PUT':
$data = json_decode(file_get_contents('php://input'), true);
$id = $data['id'];
$name = $data["name"];
$age = $data['age'];
$city = $data['city'];
$sql = "UPDATE users SET `name` = '$name', age = '$age', city = '$city' WHERE id = $id";
$stmt = $conn->prepare($sql);
$stmt->execute();
echo json_encode(["result" => "success"]);
}
$conn = null;
FRONT END
script.js
const api_url = "rest_api.php";
function loadData(records = []) {
var table_data = "";
for (let i = 0; i < records.length; i++) {
table_data += `<tr>`;
table_data += `<td>${records[i].name}</td>`;
table_data += `<td>${records[i].age}</td>`;
table_data += `<td>${records[i].city}</td>`;
table_data += `<td>`;
table_data += `<a href="edit.php?id=${records[i].id}"><button class="btn btn-primary">Edit</button></a>`;
table_data += " ";
table_data += `<button class="btn btn-danger" onclick=deleteData('${records[i].id}')>Delete</button>`;
table_data += `</td>`;
table_data += `</tr>`;
}
//console.log(table_data);
document.getElementById("tbody").innerHTML = table_data;
}
function getData() {
fetch(api_url)
.then((response) => response.json())
.then((data) => {
console.table(data);
loadData(data);
});
}
function getDataById(id) {
fetch(`${api_url}?id=${id}`)
.then((response) => response.json())
.then((data) => {
console.log(data);
document.getElementById("id").value = data.id;
document.getElementById("name").value = data.name;
document.getElementById("age").value = data.age;
document.getElementById("city").value = data.city;
});
}
function postData() {
var name = document.getElementById("name").value;
var age = document.getElementById("age").value;
var city = document.getElementById("city").value;
data = { name: name, age: age, city: city };
fetch(api_url, {
method: "POST",
headers: {
Accept: "application/json",
"Content-Type": "application/json",
},
body: JSON.stringify(data),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
window.location.href = "index.php";
});
}
function putData() {
var id = document.getElementById("id").value;
var name = document.getElementById("name").value;
var age = document.getElementById("age").value;
var city = document.getElementById("city").value;
data = { id: id, name: name, age: age, city: city };
fetch(api_url, {
method: "PUT",
headers: {
Accept: "application/json",
"Content-Type": "application/json",
},
body: JSON.stringify(data),
})
.then((response) => response.json())
.then((data) => {
console.table(data);
window.location.href = "index.php";
});
}
function deleteData(id) {
user_input = confirm("Are you sure you want to delete this record?");
if (user_input) {
fetch(api_url, {
method: "DELETE",
headers: {
Accept: "application/json",
"Content-Type": "application/json",
},
body: JSON.stringify({ id: id }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
window.location.reload();
});
}
}
index.php
<html>
<head>
<title>Project</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>
<body class="d-flex flex-column h-100 container">
<header>
<nav class="navbar navbar-expand-lg navbar-light bg-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="navbarNavAltMarkup">
<div class="navbar-nav">
<a class="nav-link active" aria-current="page" href="#">Listing</a>
<a class="nav-link" href="add.php">Add New</a>
</div>
</div>
</div>
</nav>
</header>
<table class="table table-striped table-hover text-center">
<thead>
<th>Name</th>
<th>Age</th>
<th>City</th>
<th>Action</th>
</thead>
<tbody id="tbody">
</tbody>
<tfoot>
</tfoot>
</table>
<footer class="footer mt-auto py-3 bg-light">
<div class="container text-center">
<span class="text-muted">right © 2021</span>
</div>
</footer>
</body>
<script src="script.js"></script>
<script>
getData();
</script>
</html>
add.php
<html>
<head>
<title>Project</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>
<body class="d-flex flex-column h-100 container">
<header>
<nav class="navbar navbar-expand-lg navbar-light bg-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="navbarNavAltMarkup">
<div class="navbar-nav">
<a class="nav-link" href="index.php">Listing</a>
<a class="nav-link active" aria-current="page" href="add.php">Add New</a>
</div>
</div>
</div>
</nav>
</header>
<h3>Add Document</h3>
<form onsubmit="return false;">
<div class="mb-3">
<label for="name" class="form-label">Name</label>
<input type="text" class="form-control" id="name" autofocus>
</div>
<div class="mb-3">
<label for="exampleInputPassword1" class="form-label">Age</label>
<input type="text" class="form-control" id="age">
</div>
<div class="mb-3">
<label for="city" class="form-label">City</label>
<input type="text" class="form-control" id="city">
</div>
<button class="btn btn-primary" onclick="return postData()">Submit</button>
<a href="index.php" class="btn btn-primary">Cancel</a>
</form>
<footer class="footer mt-auto py-3 bg-light">
<div class="container text-center">
<span class="text-muted">right © 2021</span>
</div>
</footer>
</body>
<script src="script.js"></script>
<script>
</script>
</html>
edit.php
<html>
<head>
<title>Project</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>
<body class="d-flex flex-column h-100 container">
<header>
<nav class="navbar navbar-expand-lg navbar-light bg-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="navbarNavAltMarkup">
<div class="navbar-nav">
<a class="nav-link" href="index.php">Listing</a>
<a class="nav-link active" aria-current="page" href="add.php">Add New</a>
</div>
</div>
</div>
</nav>
</header>
<h3>Edit Document</h3>
<form onsubmit="return false;">
<input type="hidden" class="form-control" id="id">
<div class="mb-3">
<label for="name" class="form-label">Name</label>
<input type="text" class="form-control" id="name" autofocus>
</div>
<div class="mb-3">
<label for="exampleInputPassword1" class="form-label">Age</label>
<input type="text" class="form-control" id="age">
</div>
<div class="mb-3">
<label for="city" class="form-label">City</label>
<input type="text" class="form-control" id="city">
</div>
<button class="btn btn-primary" onclick="return putData()">Update</button>
<a href="index.php" class="btn btn-primary">Cancel</a>
</form>
<footer class="footer mt-auto py-3 bg-light">
<div class="container text-center">
<span class="text-muted">right © 2021</span>
</div>
</footer>
</body>
<script src="script.js"></script>
<script>
const urlParams = new URLSearchParams(window.location.search);
const id = urlParams.get('id');
getDataById(id);
</script>
</html>
Deploy Flask App on Heroku
mkdir app
cd app
app.py
from flask import Flask, jsonify, request
app = Flask(__name__)
@app.route("/")
def index():
return "Hello World!"
@app.route('/add')
def add():
num1 = int(request.args.get('num1'));
num2 = int(request.args.get('num2'));
return f"{num1} + {num2} = {num1 + num2}"
#if __name__ == "__main__":
#app.run(debug=True);
#app.run(host="0.0.0.0", port=int("1234"), debug=True)
runtime.txt
python-3.10.8
Procfile
web: gunicorn app:app
requirements.txt
click==8.0.3
colorama==0.4.4
Flask==2.0.2
Flask-Cors==3.0.10
gunicorn==20.1.0
itsdangerous==2.0.1
Jinja2==3.0.3
MarkupSafe==2.0.1
PyMySQL==1.0.2
six==1.16.0
Werkzeug==2.0.2
OR
You can output all dependencies using following command
python -m pip freeze > requirements.txt
heroku login
#onetime
heroku create <appname>
heroku git:remote -a <appname>
git init
#repeat whenever you make changes
git add .
git commit -m 'heroku push'
git push heroku master
Test API
https://myflaskapp2022.herokuapp.com/
https://myflaskapp2022.herokuapp.com/add?num1=5&num2=9
Troubleshoot
heroku logs --tail --app eflask-app-dusra
If still it is not working please do check spellings of files
React Cheat Sheet
React is a free and open-source front-end JavaScript library for building user interfaces based on UI components. It is maintained by Meta and a community of individual developers and companies. React can be used as a base in the development of single-page or mobile applications.
Create new react project
npm -v
npm i npx
npx create-react-app my-react-app
cd my-react-app
code .
Run from terminal
npm start
OR
npm start --port 8000
Open Browser and run the react app http://localhost:3000 OR http://ipaddress:3000
App.js
import logo from './logo.svg';
import './App.css';
function App() {
return (
<>
<h1>Test App</h1>
</>
);
}
export default App;
Create new component and use click and change event
src/components/Add.js
import { Fragment, useState } from "react";
function Add () {
const [ num1, setNum1 ] = useState(0);
const [ num2, setNum2 ] = useState(0);
const [ result, setResult ] = useState(0);
function addFun() {
setResult(num1 + num2);
}
return (<Fragment>
<h1>Addition of two numbers</h1>
<input type="text" onChange={ e => setNum1(parseInt(e.target.value)) }/>
<input type="text" onChange={ e => setNum2(parseInt(e.target.value)) } />
<button onClick={addFun}>Get Addtion</button>
<div>{result}</div>
</Fragment>);
}
export default Add;
Add this Add component in App component as tag <Add />
import './App.css';
import Add from './components/Add';
function App() {
return (
<>
<Add />
<Add />
<h1>Test App</h1>
</>
);
}
export default App;
React Properties
App.js
import './App.css';
import Add from './components/Add';
function App() {
return (
<>
<Add x="5" y="6"/>
<Add x="7" y="8"/>
<h1>Test App</h1>
</>
);
}
export default App;
Add.js
import { Fragment, useState } from "react";
function Add (props) {
const [ num1, setNum1 ] = useState(parseInt(props.x));
const [ num2, setNum2 ] = useState(parseInt(props.y));
const [ result, setResult ] = useState(0);
function addFun() {
setResult(num1 + num2);
}
return (<Fragment>
<h1>Addition of two numbers</h1>
<input type="text" onChange={ e => setNum1(parseInt(e.target.value)) }/>
<input type="text" onChange={ e => setNum2(parseInt(e.target.value)) } />
<button onClick={addFun}>Get Addtion</button>
<div>{result}</div>
</Fragment>);
}
export default Add;
Ajax call in React.js
Create new component Users
components/User.js
import { Fragment } from "react";
function User(props) {
//object destructoring assignment
const {id, name, username, email} = props.user;
return (
<Fragment>
<tr>
<td>{props.user.id}</td>
<td>{props.user.name}</td>
<td>{username}</td>
<td>{email}</td>
</tr>
</Fragment>
);
}
export default User;
components/UsersListing.js
import { Fragment, useState } from "react";
import User from "./User";
function UsersListing () {
const [ users, setUsers ] = useState([]);
function getUsers() {
fetch("https://jsonplaceholder.typicode.com/users")
.then(response => response.json())
.then(json => {
console.table(json)
var rows = [];
for(let i = 0; i < json.length; i++) {
rows.push(<User key={i} user={json[i]}/>);
}
setUsers(rows);
}
);
}
return (<Fragment>
<h1>Users Listing</h1>
<button onClick={getUsers}>Get Users</button>
<table border='1'>
<thead>
<th>Id</th>
<th>Name</th>
<th>Username</th>
<th>Email</th>
</thead>
<tbody>
{users}
</tbody>
</table>
</Fragment>);
}
export default UsersListing;
Use <UsersListing /> component in App.js JSX to show users list
Routing
https://www.w3schools.com/REACT/react_router.asp
Deploy React App on Github
Create repository on github
git init
git add .
git commit -m "first commit"
git branch -M main
git remote add origin https://github.com/<username>/<repositoryname>.git
git remote set-url origin https://<githubtoken>@github.com/<username>/<repositoryname>.git
#git clone https://<username>:<githubtoken>@github.com/<username>/<repositoryname>.git
To get github token Go To Profile Settings => Developer Settings => Personal Access Token => Generate New Token
npm install gh-pages --save-dev
vim package.json
{
"name": "my-app",
"version": "0.1.0",
"homepage": "https://gitname.github.io/repositoryname",
"private": true,
"scripts": {
"predeploy": "npm run build",
"deploy": "gh-pages -d build",
"start": "react-scripts start",
"build": "react-scripts build",
npm run deploy
git status
git add .
git commit -m 'deploy react on github'
git push
https://<username>.github.io/<repositoryname>/
To publish react app locally
npm run build
verify build directory and index.html file
build index.html
<link rel="manifest" href="manifest.json"/>
<title>React App</title>
<script defer="defer" src="./static/js/main.<yourhash>.js"></script>
<link href="./static/css/main.<yourhash>.css" rel="stylesheet">
You can publish this in xampp htdocs folder
index.html
<!DOCTYPE html>
<html>
<head>
<script src="https://unpkg.com/react@18/umd/react.development.js" crossorigin></script>
<script src="https://unpkg.com/react-dom@18/umd/react-dom.development.js" crossorigin></script>
<script src="https://unpkg.com/@babel/standalone/babel.min.js"></script>
<script src="https://unpkg.com/react-router@5.0.0/umd/react-router.min.js"></script>
<script src="https://unpkg.com/react-router-dom@5.0.0/umd/react-router-dom.min.js"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.1/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-iYQeCzEYFbKjA/T2uDLTpkwGzCiq6soy8tYaI1GyVh/UjpbCx/TYkiZhlZB6+fzT" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.1/dist/js/bootstrap.bundle.min.js"
integrity="sha384-u1OknCvxWvY5kfmNBILK2hRnQC3Pr17a+RTT6rIHI7NnikvbZlHgTPOOmMi466C8"
crossorigin="anonymous"></script>
</head>
<body class="container-fluid">
<div id="root"></div>
<script type="text/babel">
function Home() {
return <h1>This is home</h1>
}
function UserComponent() {
return <h1>This is user</h1>
}
function AdminComponent() {
return <h1>This is admin</h1>
}
const Router = window.ReactRouterDOM.BrowserRouter;
const Route = window.ReactRouterDOM.Route;
const Link = window.ReactRouterDOM.Link;
const Prompt = window.ReactRouterDOM.Prompt;
const Switch = window.ReactRouterDOM.Switch;
const Redirect = window.ReactRouterDOM.Redirect;
function App() {
return <>
<Router>
<nav>
<ul>
<li>
<Link to="/">Home</Link>
</li>
<li>
<Link to="/admin">Admin</Link>
</li>
<li>
<Link to="/user">Users</Link>
</li>
</ul>
</nav>
<Switch>
<Route path='/user' component={UserComponent} />
<Route path='/admin' component={AdminComponent} />
<Route exact path='/' component={Home} />
</Switch>
</Router>
</>;
}
ReactDOM.render(<App />, document.getElementById('root'))
</script>
</body>
</html>
Python CRUD App
Create CRUD Application using Python and MySQL connector
Show following options
1. List All Records
2. Delete
3. Insert
4. Update
5. Search
0. Exit
Write separate function for each functionality
Program should not get exited unless user select 0 option.
You can use any module to connect to MySQL from python
Blockchain Demo
main.js
const SHA256 = require('crypto-js/sha256');
class Block {
constructor(index, timestamp, data, prevHash = '') {
this.index = index;
this.timestamp = timestamp;
this.data = data;
this.prevHash = prevHash;
this.hash = this.calculateHash();
}
calculateHash() {
return SHA256(this.index + this.prevHash + this.timestamp + JSON.stringify(this.data)).toString();
}
}
class Blockchain {
constructor() {
this.chain = [this.createGenesisBlock()];
}
createGenesisBlock() {
return new Block(0, "01/01/2021", "Genesis block", "0");
}
getLatestBlock() {
return this.chain[this.chain.length - 1];
}
addBlock(newBlock) {
newBlock.prevHash = this.getLatestBlock().hash;
newBlock.hash = newBlock.calculateHash();
this.chain.push(newBlock);
}
isChainValid() {
for(let i=1; i < this.chain.length; i++) {
const currentBlock = this.chain[i];
const prevBlock = this.chain[i-1];
if(currentBlock.hash !== currentBlock.calculateHash()) {
return false;
}
if(currentBlock.prevHash != prevBlock.hash) {
return false;
}
}
return true;
}
}
let tcoin = new Blockchain();
tcoin.addBlock(new Block(1, "03/09/2021", {amt: 100}));
tcoin.addBlock(new Block(1, "04/09/2021", {amt: 200}));
console.log(tcoin.isChainValid());
console.log(JSON.stringify(tcoin, null, 4));
//tempering data
tcoin.chain[2].amt = 2000;
console.log(JSON.stringify(tcoin, null, 4));
console.log(tcoin.isChainValid());
Proof of Work
const SHA256 = require('crypto-js/sha256');
class Block {
constructor(index, timestamp, data, prevHash = '') {
this.index = index;
this.timestamp = timestamp;
this.data = data;
this.prevHash = prevHash;
this.hash = this.calculateHash();
this.nonce = 0;
}
calculateHash() {
return SHA256(this.index + this.prevHash + this.timestamp + JSON.stringify(this.data) + this.nonce).toString();
}
mineBlock(complexity) {
while(this.hash.substring(0, complexity) !== Array(complexity + 1).join("0")) {
this.nonce++;
//console.log(this.nonce);
this.hash = this.calculateHash();
}
console.log(`Block mined: ${this.hash}`);
}
}
class Blockchain {
constructor(complexity = 0) {
this.chain = [this.createGenesisBlock()];
this.complexity = complexity;
}
createGenesisBlock() {
return new Block(0, "01/01/2021", "Genesis block", "0");
}
getLatestBlock() {
return this.chain[this.chain.length - 1];
}
addBlock(newBlock) {
newBlock.prevHash = this.getLatestBlock().hash;
//newBlock.hash = newBlock.calculateHash();
newBlock.mineBlock(this.complexity);
this.chain.push(newBlock);
}
isChainValid() {
for(let i=1; i < this.chain.length; i++) {
const currentBlock = this.chain[i];
const prevBlock = this.chain[i-1];
if(currentBlock.hash !== currentBlock.calculateHash()) {
return false;
}
if(currentBlock.prevHash != prevBlock.hash) {
return false;
}
}
return true;
}
}
let tcoin = new Blockchain(5);
console.log("Mining Block 1...");
tcoin.addBlock(new Block(1, "03/09/2021", {amt: 100}));
console.log("Mining Block 2...");
tcoin.addBlock(new Block(1, "04/09/2021", {amt: 200}));
console.log(JSON.stringify(tcoin, null, 4));
RegEx
Playground for practice https://regexr.com/ https://regex101.com/
Pattern Matching
Regex Syntax
/<regex pattern>/
Starts with wild card character
^
Ends with wcc
$
set enclosed in
[set]
[^invert-set]
only numbers
^[0-9]+$
^\d+$
only characters
^[a-zA-Z]+$
^\w+$
occurance wcc
* => any number of occurrence
? => 0 or 1 occurrence
+ => 1 or many occurrence
e.g. adarsh mishra
to match 1 space /adarsh\smishra/
to match multiple space /adarsh\s+mishra/
to match 0 or many spaces /adarsh\s*mishra/
no special characters
^[0-9a-zA-Z\s]+$
limited characters or numbers
^[0-9]{6}$
^[a-z]{3}$
min / max range characters or numbers
^[0-9]{3,6}$
^[a-z]{3,6}$
OR clause
^(chacha|bhatija)$
case insensitive
//i
global check
//g
search any pattern
(.) e.g. “https://www.youtube.com/watch?v=hw_HpTI_Wkw”.match(/v=(.)/)[1]
will return video id hw_HpTI_Wkw
Assignment
- girls / boys name
- phone number
- find Indian zip code
- valid name
- email id
- 8-12 character password
- aadhaar number
- pancard
- name starts with vowels
- names ends with vowels
- names having only 5 characters
- credit card number
Sample Text
1234567890
123453
DAVPS0412P
127836320613
9876543210
098762
xyz@yahoo.com
smita
&UJMmju7
shailesh
143258761937
priyanka
abc@gmail.com
112096851365
%TGBbgt5
priya
DMA Project Cheat Sheet
To get the connection string
Atlas > Deployment > Databases > Connect > Connect your application > Driver Node.js > Version 4.1 or later
CRUD Application using Vanilla JS | HTML5 | CSS3 | Bootstrap5
NOTE: DON’T FORGET TO APPEND DATABASE NAME IN CONNECTION STRING URL
users_module.js
//Step 1: Database connection using connection string
const mongoose = require("mongoose");
//mongodb://127.0.0.1:27017/dbname
//const conn_str = "mongodb://localhost:27017/tcet";
const conn_str = "mongodb+srv://user:passwd@cluster0.gp5lcta.mongodb.net/<DBNAME>?retryWrites=true&w=majority"
mongoose.connect(conn_str, { useNewUrlParser: true, useUnifiedTopology: true })
.then(() => console.log("Connected successfully..."))
.catch( (error) => console.log(error) );
//Step 2: Create Schema (similar to Java Class)
const userSchema = new mongoose.Schema({
name: String,
age: Number,
city: String
})
//Step 3: Create collection Object (model)
// MAPPING
const userObject = new mongoose.model("users", userSchema);
exports.User = userObject;
Test Database Connection
index.js
const express = require("express");
const port = 8080;
const user_model = require("./users_module");
const User = user_model.User;
const app = express();
app.use(express.json());
var cors = require('cors');
app.use(cors());
app.get("/", (req, res) => {
res.send("Hello Friends..");
});
app.get("/user", async (req, res) => {
let data = await User.find().sort({_id:-1});
res.send(data);
});
app.get("/user/:id", async (req, res) => {
console.log(req.params.id);
let data = await User.find({"_id": req.params.id});
res.send(data[0]);
});
app.post("/user", async (req, res) => {
console.log(req.body)
let u = await User(req.body);
let result = u.save();
res.send(req.body);
});
app.put("/user", async (req, res) => {
console.log(req.body);
//User.updateOne({where}, {set});
let u_data = await User.updateOne({"_id": req.body._id}, {
"$set": {
"name" : req.body.name,
"age" : req.body.age,
"city" : req.body.city
}
});
res.send(u_data);
});
app.delete("/user", async(req, res) => {
let d_data = await User.deleteOne({"_id": req.body._id});
res.send(d_data);
});
app.listen(process.env.PORT || port, () => {
console.log(`Listening on port ${port}`);
});
Run index.js file (node .) and Test in Postman
GET Method
POST Method
POST Method
PUT Method
DELETE Method
Create Frontend to access all web services from Web Browser
script.js
//const api_url = "<heroku_app_url>"
const api_url = "http://localhost:8080/user"
function loadData(records = []) {
var table_data = "";
for(let i=0; i<records.length; i++) {
table_data += `<tr>`;
table_data += `<td>${records[i].name}</td>`;
table_data += `<td>${records[i].age}</td>`;
table_data += `<td>${records[i].city}</td>`;
table_data += `<td>`;
table_data += `<a href="edit.html?id=${records[i]._id}"><button class="btn btn-primary">Edit</button></a>`;
table_data += ' ';
table_data += `<button class="btn btn-danger" onclick=deleteData('${records[i]._id}')>Delete</button>`;
table_data += `</td>`;
table_data += `</tr>`;
}
//console.log(table_data);
document.getElementById("tbody").innerHTML = table_data;
}
function getData() {
fetch(api_url)
.then((response) => response.json())
.then((data) => {
console.table(data);
loadData(data);
});
}
function getDataById(id) {
fetch(`${api_url}/${id}`)
.then((response) => response.json())
.then((data) => {
console.log(data);
document.getElementById("id").value = data._id;
document.getElementById("name").value = data.name;
document.getElementById("age").value = data.age;
document.getElementById("city").value = data.city;
})
}
function postData() {
var name = document.getElementById("name").value;
var age = document.getElementById("age").value;
var city = document.getElementById("city").value;
data = {name: name, age: age, city: city};
fetch(api_url, {
method: "POST",
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
body: JSON.stringify(data)
})
.then((response) => response.json())
.then((data) => {
console.log(data);
window.location.href = "index.html";
})
}
function putData() {
var _id = document.getElementById("id").value;
var name = document.getElementById("name").value;
var age = document.getElementById("age").value;
var city = document.getElementById("city").value;
data = {_id: _id, name: name, age: age, city: city};
fetch(api_url, {
method: "PUT",
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
body: JSON.stringify(data)
})
.then((response) => response.json())
.then((data) => {
console.table(data);
window.location.href = "index.html";
})
}
function deleteData(id) {
user_input = confirm("Are you sure you want to delete this record?");
if(user_input) {
fetch(api_url, {
method: "DELETE",
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
body: JSON.stringify({"_id": id})
})
.then((response) => response.json())
.then((data) => {
console.log(data);
window.location.reload();
})
}
}
index.html
<!DOCTYPE html>
<html>
<head>
<title>CIA Institute - MongoDB Project</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"crossorigin="anonymous"></script>
</head>
<body class="d-flex flex-column h-100 container">
<header>
<nav class="navbar navbar-expand-lg navbar-expand-sm navbar-light bg-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="navbarNavAltMarkup">
<div class="navbar-nav">
<a class="nav-link active" aria-current="page" href="#">Listing</a>
<a class="nav-link" href="add.html">Add New</a>
</div>
</div>
</div>
</nav>
</header>
<table class="table table-striped table-hover text-center">
<thead>
<th>Name</th>
<th>Age</th>
<th>City</th>
<th>Action</th>
</thead>
<tbody id="tbody">
</tbody>
<tfoot>
</tfoot>
</table>
<footer class="footer mt-auto py-3 bg-light">
<div class="container text-center">
<span class="text-muted"> © CIA Institute 2022</span>
</div>
</footer>
</body>
<script src="script.js"></script>
<script>
getData();
</script>
</html>
Test in Browser
add.html
<html>
<head>
<title>CIA Institute - MongoDB Project</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"crossorigin="anonymous"></script>
</head>
<body class="d-flex flex-column h-100 container">
<header>
<nav class="navbar navbar-expand-lg navbar-expand-sm navbar-light bg-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="navbarNavAltMarkup">
<div class="navbar-nav">
<a class="nav-link" href="index.html">Listing</a>
<a class="nav-link active" aria-current="page" href="add.html">Add New</a>
</div>
</div>
</div>
</nav>
</header>
<h3>Add Document</h3>
<form onsubmit="return false;">
<div class="mb-3">
<label for="name" class="form-label">Name</label>
<input type="text" class="form-control" id="name" autofocus>
</div>
<div class="mb-3">
<label for="exampleInputPassword1" class="form-label">Age</label>
<input type="text" class="form-control" id="age">
</div>
<div class="mb-3">
<label for="city" class="form-label">City</label>
<input type="text" class="form-control" id="city">
</div>
<button class="btn btn-primary" onclick="return postData()">Submit</button>
<a href="index.html" class="btn btn-primary">Cancel</a>
</form>
<footer class="footer mt-auto py-3 bg-light">
<div class="container text-center">
<span class="text-muted"> © CIA Institute 2022</span>
</div>
</footer>
</body>
<script src="script.js"></script>
<script>
</script>
</html>
edit.html
<html>
<head>
<title>CIA Institute - MongoDB Project</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"crossorigin="anonymous"></script>
</head>
<body class="d-flex flex-column h-100 container">
<header>
<nav class="navbar navbar-expand-lg navbar-expand-sm navbar-light bg-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="navbarNavAltMarkup">
<div class="navbar-nav">
<a class="nav-link" href="index.html">Listing</a>
<a class="nav-link active" aria-current="page" href="add.html">Add New</a>
</div>
</div>
</div>
</nav>
</header>
<h3>Edit Document</h3>
<form onsubmit="return false;">
<input type="hidden" class="form-control" id="id">
<div class="mb-3">
<label for="name" class="form-label">Name</label>
<input type="text" class="form-control" id="name" autofocus>
</div>
<div class="mb-3">
<label for="exampleInputPassword1" class="form-label">Age</label>
<input type="text" class="form-control" id="age">
</div>
<div class="mb-3">
<label for="city" class="form-label">City</label>
<input type="text" class="form-control" id="city">
</div>
<button class="btn btn-primary" onclick="return putData()">Update</button>
<a href="index.html" class="btn btn-primary">Cancel</a>
</form>
<footer class="footer mt-auto py-3 bg-light">
<div class="container text-center">
<span class="text-muted"> © CIA Institute 2022</span>
</div>
</footer>
</body>
<script src="script.js"></script>
<script>
const urlParams = new URLSearchParams(window.location.search);
const id = urlParams.get('id');
getDataById(id);
</script>
</html>
Screenshots
Reference Link: https://gitlab.com/tcet/mongodb-july-21.git
Create REST Api using Node.js | Express | Mongoose
index.js
const port = 8080;
const mongoose = require("mongoose");
//const conn_str ="C"
const conn_str = "mongodb://<user>:<passwd>@cluster0-shard-00-00.dslyw.mongodb.net:27017,cluster0-shard-00-01.dslyw.mongodb.net:27017,cluster0-shard-00-02.dslyw.mongodb.net:27017/<databasename>?ssl=true&replicaSet=atlas-3xk2hf-shard-0&authSource=admin&retryWrites=true&w=majority";
mongoose.connect(conn_str, { useNewUrlParser: true , useUnifiedTopology: true})
.then( () => console.log("Connected successfully...") )
.catch( (err) => console.log(err) );
const userSchema = new mongoose.Schema({
name: String,
age: Number,
city: String
});
const user = new mongoose.model("users", userSchema);
/** Express Mongoose Integration **/
const express = require("express");
var cors = require('cors');
const app = express();
//add middlewares
app.use(express.json());
app.use(cors());
app.route("/user")
.get(async (req, res) => {
let data = await user.find();
res.send(data);
})
.post(async (req, res) => {
req_data = req.query;
let obj = new user(req.query)
let result = await obj.save();
res.send(result);
})
.put(async (req, res) => {
console.log(req.body);
//model.updateOne({where}, {set});
let u_data = await user.updateOne({"_id": req.body._id}, {
"$set": {
"name" : req.body.name,
"age" : req.body.age,
"city" : req.body.city
}
});
res.send(u_data);
})
.delete(async (req, res) => {
let d_data = await User.deleteOne({"_id": req.body._id});
res.send(d_data);
})
app.listen(process.env.PORT || port, () => {
console.log("listening 8080...");
});
Call REST Api using Vanilla JS
index.html
<script>
function getDataById(id) {
fetch(`http://localhost:8080/user/${id}`)
.then((response) => response.json())
.then((data) => { console.table(data); })
}
function getData() {
fetch("http://localhost:8080/user")
.then((response) => response.json())
.then((data) => { console.table(data); })
}
function postData(data) {
fetch("http://localhost:8080/user", {
method: "POST",
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
body: JSON.stringify(data)
})
.then((response) => response.json())
.then((data) => { console.table(data); })
}
function putData(data) {
fetch("http://localhost:8080/user", {
method: "PUT",
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
body: JSON.stringify(data)
})
.then((response) => response.json())
.then((data) => { console.table(data); })
}
function deleteData(id) {
fetch("http://localhost:8080/user", {
method: "DELETE",
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
body: JSON.stringify({"_id": id})
})
.then((response) => response.json())
.then((data) => { console.table(data); })
}
getDataById();
getData();
postData({"name": "Akshu", "age": 20, "city": "Koradi"});
putData({"_id" : "61082ae9cd4b7a0ccc39d377", "name": "chaitu"});
deleteData("61082b4ecd4b7a0ccc39d37a");
</script>
Deploy From Gitlab To FTP Server
Create file gitlab-ci.yml in gitlab
variables:
HOST: "yourFTPServer"
USERNAME: "yourFTPServerUsername"
PASSWORD: "yourFTPServerPassword"
deploy:
script:
- apt-get update -qq && apt-get install -y -qq lftp
- lftp -c "set ftp:ssl-allow no; open -u $USERNAME,$PASSWORD $HOST; mirror -Rnev ./ ./htdocs --ignore-time --parallel=10 --exclude-glob .git* --exclude .git/"
only:
- master
NOTE: Make sure to change host, username, password and directory name where to deploy code
Ref: https://stackoverflow.com/questions/49632077/use-gitlab-pipeline-to-push-data-to-ftpserver