Data_Analyser_using_python/tableCreator.py at master · itsdeepak-kumar/Data_Analyser_using_python · GitHub

History

76 lines (49 loc) · 2.1 KB

Raw

import mysql.connector as sql

from mysql.connector import errorcode

import json

import sys

database = 'STASTISTICAL_DATA_ANALYSIS'

user = 'user_name' # <<<<--- Change here

password = 'your_password' # <<<<--- Change here

branchName = "Chemical_2019" # Table Name # <<<<--- Change here

path_of_json_file = "path/to/json/file" # <<<<--- Change here

# creating table

def tableCreation():

try:

cursor.execute(f"Create table {branchName}(Rank_No int, RollNo int, Name varchar(30), Cgpa float, Sgpa float, Points int)")

except sql.Error as e:

if e.errno == 1050:

cursor.execute(f"Drop Table {branchName};")

print(f"Existing table {branchName} removed")

cursor.execute(f"Create table {branchName}(Rank_No int, RollNo int, Name varchar(30), Cgpa float, Sgpa float, Points int)")

def valueAdder():

for i, item in enumerate(data):

Rank = item.get("Rank", None)

RollNo = item.get("Rollno", None)

Name = item.get("Name", None)

Cgpa= item.get("Cgpa", None)

Sgpa= item.get("Sgpa", None)

Points = item.get("Points", None)

cursor.execute(f"INSERT INTO {branchName} (Rank_No, RollNo, Name, Cgpa, Sgpa, Points) VALUES (%s, %s, %s, %s, %s, %s)", (Rank, RollNo, Name, Cgpa, Sgpa, Points))

# Removing Duplicates Items

def duplicteValueRemover():

cursor.execute(f"CREATE TABLE copy_of_{branchName} SELECT DISTINCT Rank_no, RollNo, Name, Cgpa, Sgpa, Points FROM {branchName}")

cursor.execute(f"Drop Table {branchName};")

cursor.execute(f"Alter Table copy_of_{branchName} rename to {branchName}")

print("Duplite Value Removed.")

if __name__ == "__main__":

try:

conn = sql.connect(host='localhost',database=database,user=user,password=password)

if conn.is_connected():

print("Connected to MySql Server\n\n")

cursor = conn.cursor()

except Exception as e:

sys.exit("Sorry, Couldn't Connect to Server. Please try again later.")

with open (path_of_json_file, "r") as file:

data = json.load(file)

tableCreation()

valueAdder()

duplicteValueRemover()

conn.commit()

conn.close()

print(f"{branchName} table created.")

Data_Analyser_using_python/tableCreator.py at master · itsdeepak-kumar/Data_Analyser_using_python · GitHub

Expand file tree

Breadcrumbs

tableCreator.py

Latest commit

History

Breadcrumbs

tableCreator.py

File metadata and controls