Flask Setup and Basic

### Step1: Create virtualenv

>python -m virtualenv env

>virtualenv\scripts\activate

Create Virtual environment for ubuntu/linux machine and activate

>python -m venv env

>source env/bin/activate

### Step2: Creating Project

>mkdir demo_project

>cd demo_project

>

### Step3: Creating python file main.py


from flask import Flask

app = Flask(__name__)

@app.route("/")

def test():

return "Hello this is for testing and print in console!"

@app.route("/demo")

def harry():

return "this is second route url which in /demo"

app.run(debug=True)


### step5: Run server

>python main.py


##Tutorial2: Static and Templates folder

What is Static in flask?

This folder is public, client can access it. it may be images, css, javascript and other files.

These files can also be viewed using the www.website-url/static/filename address.

What is Templates in flask?

Templates folder is private, client can not access it. All the sensitive data is put in it.

Flask uses its template folder for storing the raw templates which can be filled through the python program.

Flask will look for templates in the templates folder.

So if your application is a module, this folder is next to that module,

if it’s a package it’s actually inside your package:

Case 1: a module:

/application.py

/templates

/index.html

Case 2: a module:

/application

/__init__.py

/templates

/index.html

Step1: Creating Static folder and templates

>mkdir static

>mkdir templates

What is render_template() function and where is use it?

render_template() function return a template(HTML page) through our python file.

Ex: render_template(‘html_file_name.html’, params={}) #as shown in the code below

return render_template('index.html', params={"title":"Home Page",

"keywords": "python, programming, numpy, pandas",

"description":"Python Tutorial for Beginners." },

"image_url": url_for("user_image", filename=user.image)

)

How can get Variable, images, css, js and include child html files in parent html?

Load Image

<img src="{{ url_for('static', filename='1.png') }}">

<img src="{{ url_for('static', filename='subfolder/2.png') }}">

<img src="/static/2.png">

Get python variable in html

<h1>Hi I am {{ variable_name }}!</h1>

<img src="{{ params.image_url }}">

<meta name="keywords" content="{{ params.keywords }}">

<meta name="Description" content="{{ params.description }}">

<title>{{ params.title }}</title>

For Loop In Template

{% for post in posts %}

#Slicing

{{post.content[0:101]}}...

{% endfor %}

How can set variable in Flask(Jinda) Template

{% set image_url = 'images/' + params.image_url %}

<img src="{{ url_for('static', filename=image_url) }}">

Inherit/include html files

{% include 'include/header.html' %}

{% include 'include/footer.html' %}

Load JS and CSS

<script src="/static/common/js/codemirror/lib/codemirror.js"></script>

<img src="{{ url_for('static', filename='js/jquery-3.5.1.min.js') }}">

<link rel="stylesheet" href="{{ url_for('static', filename='css/bootstrap.min.css') }}">

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">\

Variable Rules

You can add variable sections to a URL by marking sections with <variable_name>.

Your function then receives the <variable_name> as a keyword argument.

Optionally, you can use a converter to specify the type of the argument like <converter:variable_name>.

string (default): accepts any text without a slash

int :accepts positive integers

float: accepts positive floating point values

path: like string but also accepts slashes

uuid: accepts UUID strings

from markupsafe import escape

@app.route('/user/<username>')

def show_user_profile(username):

# show the user profile for that user

return 'User %s' % escape(username)

@app.route('/post/<int:post_id>')

def show_post(post_id):

# show the post with the given id, the id is an integer

return 'Post %d' % post_id

@app.route('/path/<path:subpath>')

def show_subpath(subpath):

# show the subpath after /path/

return 'Subpath %s' % escape(subpath)

Unique URLs / Redirection Behavio(Redirect with traling slash)

The following two rules differ in their use of a trailing slash.

#If you access the URL without a trailing slash, Flask redirects you to the canonical URL with the trailing slash.

@app.route('/projects/')

def projects():

return 'The project page'

@app.route('/about')

def about():

return 'The about page'

URL Building

To build a URL to a specific function, use the url_for() function.

It accepts the name of the function as its first argument and any number of keyword arguments, each corresponding to a variable part of the URL rule.

Unknown variable parts are appended to the URL as query parameters

test_request_context() method to try out url_for(). test_request_context() tells Flask to behave as though it’s handling a request even while we use a Python shell.

from flask import url_for

with app.test_request_context():

print(url_for('test'))

print(url_for('projects'))

print(url_for('projects', next='/'))

print(url_for('user', username='John Doe'))

HTTP Methods:

Web applications use different HTTP methods when accessing URLs.

You should familiarize yourself with the HTTP methods as you work with Flask.

By default, a route only answers to GET requests.

You can use the methods argument of the route() decorator to handle different HTTP methods.

from flask import request

@app.route('/login', methods=['GET', 'POST'])

def login():

if request.method == 'POST':

return do_the_login()

else:

return show_the_login_form()

The Request Object:

from flask import request

def log_the_user_in(username):

print(username)

return username

def valid_login(username, password):

print(username, password)

return True

@app.route('/user-login', methods=['POST', 'GET'])

def login():

error = None

if request.method == 'POST':

if valid_login(request.form['username'],

request.form['password']):

return log_the_user_in(request.form['username'])

else:

error = 'Invalid username/password'

# the code below is executed if the request method

# was GET or the credentials were invalid

return render_template('user-login.html', error=error)

To access parameters submitted in the URL (?key=value) you can use the args attribute:

searchword = request.args.get('key', '')

File Uploads:

set the enctype="multipart/form-data" attribute on your HTML form,

otherwise the browser will not transmit your files at all.

Uploaded files are stored in memory or at a temporary location on the filesystem.

You can access those files by looking at the files attribute on the request object.

Each uploaded file is stored in that dictionary.

It behaves just like a standard Python file object, but it also has a save() method that allows you to store that file on the filesystem of the server.

Here is a simple example showing how that works:

from os.path import join, dirname, realpath

from werkzeug.utils import secure_filename

@app.route('/upload', methods=['GET', 'POST'])

def upload_file():

if request.method == 'POST':

file = request.files['my-file']

filename = secure_filename(file.filename)

UPLOADS_PATH = join(dirname(realpath(__file__)), f'static/uploads/{filename}')

#file.save(UPLOADS_PATH, filename)

file.save(UPLOADS_PATH)

return render_template('file-upload.html' , message="File Uploaded Successfully.")

if request.method == 'GET':

return render_template('file-upload.html' , message="")

Cookies:

To access cookies you can use the cookies attribute.

To set cookies you can use the set_cookie method of response objects.

The cookies attribute of request objects is a dictionary with all the cookies the client transmits.

If you want to use sessions, do not use the cookies directly but instead

use the Sessions in Flask that add some security on top of cookies for you.

@app.route('/set-cookies')

def set_cookies():

username = request.cookies.get('username')

# use cookies.get(key) instead of cookies[key] to not get a

# KeyError if the cookie is missing.

render_template('cokkies.html', username=username)

#Storing cookies:

from flask import make_response

@app.route('/get-cookies')

def get_cookies():

resp = make_response(render_template('cokkies.html'))

resp.set_cookie('username', 'the username')

return resp

Redirects and Errors:

To redirect a user to another endpoint, use the redirect() function;

to abort a request early with an error code, use the abort() function:

from flask import abort, redirect, url_for

def this_is_never_executed():

return "This is never executed"

@app.route('/redirect')

def redirect():

return redirect(url_for('redirect-login'))

@app.route('/redirect-login')

def redirect_login():

abort(401)

this_is_never_executed()

Sessions:

In order to use sessions you have to set a secret key. Here is how sessions work:

from flask import Flask, session, redirect, url_for, request

from markupsafe import escape

app = Flask(__name__)

# Set the secret key to some random bytes. Keep this really secret!

app.secret_key = b'_5#y2L"F4Q8z\n\xec]/'

@app.route('/')

def index():

if 'username' in session:

return 'Logged in as %s' % escape(session['username'])

return 'You are not logged in'

@app.route('/login', methods=['GET', 'POST'])

def login():

if request.method == 'POST':

session['username'] = request.form['username']

return redirect(url_for('index'))

return '''

<form method="post">

<p><input type=text name=username>

<p><input type=submit value=Login>

</form>

'''

@app.route('/logout')

def logout():

# remove the username from the session if it's there

session.pop('username', None)

return redirect(url_for('index'))

# Logging

This might be caused by a user tampering with the data, or the client code failing.

Most of the time it’s okay to reply with 400 Bad Request in that situation,

but sometimes that won’t do and the code has to continue working.

app.logger.debug('A value for debugging')

app.logger.warning('A warning occurred (%d apples)', 42)

app.logger.error('An error occurred')

SQLAlchemy:

#pip install flask-sqlalchemy

from flask_sqlalchemy import SQLAlchemy

from datetime import datetime

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:@localhost/test_db'

db = SQLAlchemy(app)

class Contacts(db.Model):

sno = db.Column(db.Integer, primary_key=True)

name = db.Column(db.String(80), nullable=False)

phone_num = db.Column(db.String(12), nullable=False)

msg = db.Column(db.String(120), nullable=False)

date = db.Column(db.String(12), nullable=True)

email = db.Column(db.String(20), nullable=False)

@app.route("/contact", methods = ['GET', 'POST'])

def contact():

if(request.method=='POST'):

'''Add entry to the database'''

name = request.form.get('name')

email = request.form.get('email')

phone = request.form.get('phone')

message = request.form.get('message')

entry = Contacts(name=name, phone_num = phone, msg = message, date= datetime.now(),email = email )

db.session.add(entry)

db.session.commit()

return render_template('contact.html')

Admin DashBoard Access

@app.route('/dashboard', methods=['GET', 'POST'])

def dashboard():

if 'user' in session AND session['user']==params['admin_user']:

// Access dashboard template

if request.method=='POST':

username= request.form.get('username')

password= request.form.get('password')

if username==params['admin_user'] AND password==params['password']:

session['user']==username

// Access dashboard template

// Access Login Template

else:

// access Login template

@app.route('/edit/<string:sno>', methods=['GET', 'POST'])

def edit(sno):

if 'user' in session AND session['user']==params['admin_user']:

if request.method=='POST':

username= request.form.get('username')

password= request.form.get('password')

if username==params['admin_user'] AND password==params['password']:

title= request.form.get('title')

content= request.form.get('content')

.....

@app.route('/upload', methods=['GET', 'POST'])

def upload():

app.config['UPLOAD_FOLDER']='D:\\flask_demo\\static'

if request.method=='POST':

f=request.files['file_name']

f.save(os.path.join(app.config['UPLOAD_FOLDER'], secure_filename(f.filename)))

return "File has been uploaded successfully"

@app.route('/pagination', methods=['GET', 'POST'])

def pagination():

all_post

last_post=math.floor(len(all_post)/int(no_of_post))

page=int(request.arg.get['page'])

if (not str(page).isnumeric()):

page =1

all_post=all_post[(page-1)*no_of_post: (page-1)*no_of_post+no_of_post]

if page==1:

prev="#"

next="next?"+str(page+1)

elif page==last_post:

prev="next?"+str(page-1)

next="#"

else:

prev="next?"+str(page-1)

next="next?"+str(page+1)

return render_template("post.html", params=params, posts=posts, prev=prev, next=next)

View More...

Django Python Command

Verify Python Installation

>python

Check Python Version

>python --version

shortcut command

>python -V

>python3 -V

Check if PIP is Already Installed

>pip help

Installing PIP On Windows Machine

Download PIP get-pip.py

Copy PIP code and save file name is get-pip.py

PIP is a command-line program. When you install PIP, the PIP command is added to your system.

>python  get-pip.py

Check PIP Version

>pip --version

shortcut command

>pip -V

Upgrading PIP for Python on Windows Machine

>python -m pip install --upgrade pip

Downgrade PIP Version

>python -m pip install pip==18.1

Creating virtual environment for window Machine

>python -m virtualenv env

Activate virtual environment for window Machine

>env\scripts\activate

Create Virtual environment for ubuntu/linux machine

>python -m venv env

Activate virtual environment for ubuntu/linux machine

>source env/bin/activate

Deactivate virtual environment

>deactivate

Install django and mysqlclient sh > pip install django > pip install mysqlclient

Install Django

>python -m pip install django

shortcut command

>pip install django

If you wante to install Specific version of django

>pip install django==3.1.7

Check Django Version

>django-admin --version

Install mysqlclient for MYSQL Database

>pip install mysqlclient

if you got installation error in window 

 then download whl files from here

Download :  https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient

for window 32 bit

>pip install mysqlclient-1.4.6-cp38-cp38-win32.whl

for window 64 bit

>pip install mysqlclient-1.4.6-cp38-cp38-win_amd64.whl

Creating Django Project

>django-admin startproject demo_project

Creating Django APP

>django-admin startapp demo_app

Note: django-admin is equal to python manage.py command

i.e django-admin = python manage.py

>python manage.py startapp demo_app

Django Migration Command

>python manage.py makemigrations

>python manage.py migrate

Create Super User for Django admin

>python manage.py createsuperuser

Run Server for Django Project

>python manage.py runserver

Exporting data from a Django project

>python manage.py dumpdata > vishavjeet_data.json

If you want the exported file to have a readable JSON,

 then you can use "--indent".

>python manage.py dumpdata --indent=4 > dumped_data.json

Importing data from a Django project

>python manage.py loaddata < vishavjeet_data.json

View More...

Upload CSV file using Django

Djnago Project Setup for upload CSV file

Import CSV data into mysql database using django

# Step1: Installation or Setup

Creating virtual environment for window and activate

>python -m virtualenv env

>env\scripts\activate

Create Virtual environment for ubuntu/linux and activate

>python -m venv env

>source env/bin/activate

Install django and mysqlclient

> pip install django

> pip install mysqlclient

Creating Project and App

> django-admin startproject test_demo

> django-admin startapp dump_csv_import

#Step2: Change database connection in settings.py for mysql instead of default sqllite


DATABASES = {

'default':{

'ENGINE':'django.db.backends.mysql',

'NAME':'dump_tender',

'USER':'root',

'PASSWORD':'',

'HOST':'localhost',

'PORT':3306

}

}


#Step3: Create a model name DumpTender, which code given below


from django.db import models

import random

import string

from django.db.models.signals import pre_save

from django.utils.text import slugify

# Create your models here.

STATUS = (

('Active', 'Active'),

('Deactive', 'Deactive')

)

def random_string_generator(size=10, chars=string.ascii_lowercase + string.digits):

rand_str = ''.join(random.choice(chars) for _ in range(size))

return rand_str

def unique_slug_generator(instance, new_slug):

if new_slug is not None:

slug = new_slug.replace(" ", "-")

else:

slug = slugify(instance.product_title)

klass = instance.__class__

qs_exists = klass.objects.filter(product_slug=slug).exists()

if qs_exists:

rand_str = random_string_generator(4)

new_slug = f"{slug}-{rand_str}"

return unique_slug_generator(instance, new_slug)

return slug

class DumpTender(models.Model):

id = models.AutoField(primary_key=True, unique=True)

product_id = models.IntegerField()

product_sku = models.CharField(max_length=64)

product_title = models.CharField(max_length=128)

product_slug = models.SlugField(max_length=256, default='', null=True, blank=True)

product_price = models.FloatField(default=0.0)

quantity = models.IntegerField()

total_price = models.FloatField(default=0.0)

created_datetime = models.DateTimeField(auto_now_add=True)

updated_datetime = models.DateTimeField(auto_now=True)

status = models.CharField(max_length=32, default='Deactive', choices=STATUS)

def __str__(self):

return self.product_title

class Meta:

db_table = 'products'

ordering = ('-created_datetime',)

def dumptender_pre_save_receiver(sender, instance, *args, **kwargs):

if not instance.product_slug:

instance.product_slug = unique_slug_generator(instance, instance.product_title)

klass = instance.__class__

qs_exists = klass.objects.filter(product_slug=instance.product_slug).exists()

if qs_exists:

instance.product_slug = unique_slug_generator(instance, instance.product_title)

pre_save.connect(dumptender_pre_save_receiver, sender=DumpTender)


# Step4: Register our model in the admin.py file:

from django.contrib import admin

# Register your models here.

from dump_csv_import.models import DumpTender

admin.site.register(DumpTender)

# Step5: Let's migrate our model:

>python manage.py makemigrations

>python manage.py migrate

>python manage.py createsuperuser

# Step6: Now, let's write on our views.py file to create a function-based view:

import csv

from datetime import datetime

import io

from django.contrib import messages

from django.shortcuts import render

# Create your views here.

from dump_csv_import.models import DumpTender

def dump_tender(request):

prompt = {

'order': 'Product of the CSV should be product_id, product_sku, product_title, product_slug,'

'product_price, quantity, total_price, created_datetime, updated_datetime, status',

'products': DumpTender.objects.all()

}

if request.method == "GET":

return render(request, "dump_tender.html", prompt)

if not request.FILES['file'].name.endswith('.csv'):

messages.error(request, 'THIS IS NOT A CSV FILE')

if request.FILES['file'].name.endswith('.csv'):

data_set = request.FILES['file'].read().decode('UTF-8')

io_string = io.StringIO(data_set)

next(io_string)

i=0

for column in csv.reader(io_string, delimiter=',', quotechar="|"):

i+=1

_created_at = datetime.strptime(column[6].replace('/','-'), "%m-%d-%Y %H:%M")

_updated_at = datetime.strptime(column[6].replace('/', '-'), "%m-%d-%Y %H:%M")

_, created = DumpTender.objects.update_or_create(

product_id=int(column[0]),

product_sku=column[1],

product_title=column[2],

product_slug=column[3],

product_price=int(column[4]),

quantity=int(column[5]),

total_price=int(column[4]) * int(column[5]),

created_datetime=_created_at,

updated_datetime=_updated_at,

status=column[8]

)

context = {

'messages': [f' {i} Products uploaded successfully'],

'products': DumpTender.objects.all()

}

return render(request, "dump_tender.html", context)

# Step7: Create a templates folder in the app dump_csv_import

 directory and a new HTML file named dump_tender.html (dump_csv_import/templates/dump_tender.html)



<!DOCTYPE html>

<html lang="en">

<head>

<title>Dump Tender</title>

<meta charset="utf-8">

<meta name="viewport" content="width=device-width, initial-scale=1">

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

</head>

<body>

<div class="container">

<h2>Upload Dump Tender Products</h2>

<p>Only CSV File accept</p>

{% if messages %}

{% for message in messages %}

<!-- | means OR operator-->

<div class="alert alert-success alert-dismissible">

<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>

<strong>{{message|safe}}</strong>

</div>

{% endfor %}

{% else %}

<div class="alert alert-success alert-dismissible">

<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>

<strong>{{order}}</strong>

</div>

<form class="form-inline" action="" method="POST" enctype="multipart/form-data">

{% csrf_token %}

<div class="form-group">

<label class="sr-only" for="file1">Upload a file:</label>

<input type="file" class="form-control" id="file1" name="file">

</div>

<button type="submit" class="btn btn-default">Upload</button>

</form>

{% endif %}

<h2>Products List</h2>

<table class="table">

<thead>

<tr>

<th>S.No</th>

<th>Name</th>

<th>Slug</th>

<th>Price</th>

<th>QTY</th>

<th>Total Price</th>

<th>Created Date</th>

<th>Updated Date</th>

<th>Status</th>

</tr>

</thead>

<tbody>

{% for product in products %}

<tr>

<td>{{ forloop.counter }} </td>

<td>{{product.product_title}}</td>

<td>{{product.product_slug}}</td>

<td>{{product.product_price}}</td>

<td>{{product.quantity}}</td>

<td>{{product.total_price}}</td>

<td>{{product.created_datetime}}</td>

<td>{{product.updated_datetime}}</td>

<td>{{product.status}}</td>

</tr>

{% endfor %}

</tbody>

</table>

</div>

</body>

</html>


#Step8: Add Templates directory in settings.py

TEMPLATES = [

{

......

'DIRS': [os.path.join(BASE_DIR, "templates"), ],

......

},

]

#Step9: Create urls.py files in app directory and write code which are given below.

from django.urls import path

from dump_csv_import import views

urlpatterns = [

path('', views.dump_tender),

]

#Step10: include urls path in projcect's urls.py(test_demo/urls.py)


from django.contrib import admin

from django.urls import path, include

urlpatterns = [

path('admin/', admin.site.urls),

path('dump-tender', include('dump_csv_import.urls')),

]


# Step11: Now, create a new product_demo.csv file and try to upload it:

product_id, product_sku, product_title, product_slug, product_price, quantity, total_price, created_datetime, updated_datetime, status


View More...

DataBase Connection and Run SQL

#Create Connection

import mysql.connector

#Exercise 1: Show Databse And Create Database

# db = mysql.connector.connect( host="localhost", user="root", password="")

# print(db)

# cursor = db.cursor()

# cursor.execute("SHOW DATABASES")

# for x in cursor:

# print(x)

#

# cursor.execute("CREATE DATABASE python_demo_db")

#Exercise 2: Create Table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

#cursor.execute("CREATE TABLE players (name VARCHAR(255), country VARCHAR(64), total_run int(1), total_match int(1), player_type VARCHAR(64))")

#Exercise 3: use the ALTER TABLE keyword:

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

#sql="ALTER TABLE players ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY"

#sql="ALTER TABLE players DROP id"

#sql="ALTER TABLE players ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY AFTER total_match"

#sql="ALTER TABLE players ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST"

# cursor.execute(sql)

#Exercise 4: Insert data into playes table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

# sql = "INSERT INTO players (name, country, total_run, total_match, player_type) VALUES (%s, %s, %s, %s, %s)"

# val = ("Sourav Ganguli", "INIDIA" ,11363 ,311, "India")

# cursor.execute(sql, val)

#print("1 record inserted, ID:", cursor.lastrowid)

#Exercise 5: Insert Many data into playes table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor= db.cursor()

# sql = "INSERT INTO players (name, country, total_run, total_match, player_type) VALUES (%s, %s, %s, %s, %s)"

# val = [

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ]

# cursor.executemany(sql, val)

#print("1 record inserted, ID:", cursor.lastrowid)

#Exercise 6: Dynamic Sql Qquery run by input

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

# sql=input("ENTER SQL COMMAND HERE:> ")

# try:

# cursor.execute(sql)

# print(f"Total {cursor.rowcount} record inserted in players table.")

# except :

# print("Wrong Command, Please type correct SQL command")

# print(f"Total {cursor.rowcount} record inserted in players table.")

#Exercise 7: Fetch all row from playes table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor= db.cursor()

# cursor.execute("SELECT * FROM players")

# result = cursor.fetchall()

#

# for x in result:

# print(x)

# Exercise 8: Fetch one row from playes table

db = mysql.connector.connect(host="localhost", user="root", password="", database="python_demo_db")

cursor = db.cursor()

cursor.execute("SELECT * FROM players")

result = cursor.fetchone()

print(result)

db.commit()

print(f"Total {cursor.rowcount} record inserted in players table.")

View More...

Basic SQL/MYSQL QUERY


SHOW DATABASES;

#Delete table

DROP TABLE IF EXISTS test_db;

#DROP TABLE based on a pattern

DROP TABLE like '%test_%'

#Delete database

DROP DATABASE test_db;

#Table structure for table `old_test_table`

#Create Table;

CREATE TABLE `old_test_table` (

`id` int(11) NOT NULL,

`user` varchar(64) NOT NULL,

`mobile` varchar(10) NOT NULL,

`email` varchar(120) NOT NULL,

`address` varchar(128) DEFAULT NULL,

`gender` enum('male','female') NOT NULL DEFAULT 'male',

`created_on` timestamp NOT NULL DEFAULT current_timestamp(),

`updated_at` datetime NOT NULL

)

#Rename Table old_test_table to test_table

RENAME TABLE old_test_table to test_table;

#Rename Colunm

ALTER TABLE test_table CHANGE created_at created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `test_table` CHANGE `address1` `address` VARCHAR(128) DEFAULT NULL;

ALTER TABLE test_table ADD PRIMARY KEY (`id`);

#Indexes  AUTO_INCREMENT for table test_table

ALTER TABLE test_table MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

#Add Colunm salary

ALTER TABLE test_table ADD salary INT(1) NOT NULL AFTER updated_at;


#INSERT INTO Statement

INSERT INTO test_table (user, mobile, email, address, gender, created_on, updated_at, salary) VALUES (NULL, 'Anupam', '8368689068', 'singh.anupam@gmail.com', 'Lucknow', 'male', '2021-03-24 10:10:42', '2021-03-26 10:10:42', 140000);

# Insert multiple row data in single query

INSERT INTO test_table (user, mobile, email, address, gender, created_on, updated_at, salary) VALUES

('vishavjeet', '8448813565', 'singh.vishavjeet11@gmail.com', 'test', 'male', '2021-03-11 04:42:22', '2021-03-26 10:10:42', 40000),

('Anupam', '8368689068', 'singh.anupam@gmail.com', 'Lucknow', 'male', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 140000),

('Nimesh', '9335208575', 'singh.nimesh@gmail.com', 'Ray Bareilly', 'male', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 120000),

('Syad Gulfam Abbas Naqvi', '8368688443', 'syed.gulfamabbas786@gmail.com', 'Azamgarh', 'male', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 95000),

('Sara', '8368688443', 'sara.khan@gmail.com', 'Varansi', 'female', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 91000),

('Nishu', '8368688443', 'nishu.anupam@gmail.com', 'Rampur', 'female', '2021-03-24 04:40:42', '2021-03-26 10:10:42', 110000);

#UPDATE Statement

UPDATE `test_table` SET `mobile` = '8448813565' WHERE `test_table`.`id` = 1;

UPDATE `test_table` SET `salary` = '30000' WHERE `test_table`.`id` = 1;

UPDATE `test_table` SET `salary` = '140000' WHERE `test_table`.`id` = 2;

# DELETE Statement

DELETE FROM `test_table` WHERE `test_table`.`id` = 3"

#For Fetching/Filter data from table

#SELECT Statement

SELECT user, email FROM test_table;

SELECT * FROM `test_table` WHERE user like '%visha%'

select * from test_table where user like 'sara'

select * from test_table where salary>95000

#DISTINCT Statement

SELECT DISTINCT user, email FROM test_table;

#WHERE Clause

select * from test_table where user='sara'

select * from test_table where user like 'anupam'

select * from test_table where salary>95000

# ORDER BY Keyword

select * from test_table order by created_on DESC

select * from test_table where id >1 order by created_on DESC

#LIKE Operator

select * from test_table where email like '%singh%'

select * from test_table where email like '%singh%' order by created_on DESC

#NULL Values

select * from test_table where address IS NULL;

select * from test_table where address IS NOT NULL;

#AND, OR and NOT Operators

select * from test_table where address='lucknow' AND salary>40000

select * from test_table where address='lucknow' OR salary>40000

select * from test_table where (address='lucknow' AND salary>40000) OR (address='rampur' AND salary<200000)

#Aggregate Functions: MIN(), Max(), COUNT(), AVG() and SUM()

select min(salary) from test_table

select max(salary) from test_table

select sum(salary) from test_table

select avg(salary) from test_table

select count(id) from test_table

select count(id) as total from test_table GROUP BY address

#IN Operator

select * from test_table where id IN (1,3,6)

#Alternative Second way

select * from test_table where id=1 OR id=3 OR id=6

#BETWEEN Operator

select * from test_table where created_on BETWEEN "2021-03-24" AND "2021-03-27"

#Alternative Second way

select * from test_table where created_on >= "2021-03-24" AND <="2021-03-27"

#JOIN comming Soon...

View More...

First Previous 6 7 8 9 10 Next Last