Posts By vishavjeet (23)

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


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.")

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...

How to Install XAMPP on Ubuntu/Linux?

Installing XAMPP on Ubuntu/Linux

Step 1: Download the installation package

Link: https://www.apachefriends.org/index.html

Step 2: Make the installation package executable

> cd /home/vishavjeet/Downloads

>sudo su

Download#>chmod 755 xampp-linux-x64-7.2.10-0-installer.run

Step 3: Confirm execute permission

Download#> ls -l xampp-linux-x64-7.2.10-0-installer.run

Step 4: Launch the Setup Wizard

Download#>  ./xampp-linux-7.2.10-0-installer.run

Step 5: Work through the graphical setup wizard

>

Step 6: Launch XAMPP through the Terminal

Download#>  /opt/lampp/lampp start

This output shows that XAMPP is started and already running. Please note that you need to manually start XAMPP each time you restart your system.

If you get the following output after starting XAMPP, it means that Net Tools are not installed on your system:

In order to install Net Tools, run the following command as root:

Download#> apt install net-tools

Step 7: Verify Installation

http://localhost

http://localhost/phpmyadmin

Uninstall XAMPP

Download#>cd /opt/lampp

Download#> ./uninstall

Download#> rm -r /opt/lampp

Why use Numpy in python?

Use of Numpy in linear algebra, fourier transform, and matrices.

Python have no array, that  lists serve the purpose of arrays.

NumPy is  provide an array  which is up to 50 times(50x) faster than traditional Python lists.

NumPy is called ndarray.

NumPy  are stored data in local memory unlike lists

So processes can access and manipulate them very efficiently

Numpy Library written in partially in python but most part is written in C or C++ because require fast computation

First Previous 1 2 3 4 5 Next Last