def window_function():
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
cursor.execute('SELECT stud_name,subject,marks, SUM(marks) OVER (partition by subject) Total_marks FROM student_info')
result = cursor.fetchall()
for i in result:
logger.info(i)
except:
logger.exception("Function Aborted")
def drop_indexing():
'''
Description:
To Drop a Index in MySQL
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
indexName = input("Enter index name: ")
cursor.execute("DROP INDEX {} ON employee_details".format(indexName))
connect._CRUD__db.commit()
logger.info("Index Dropped: {}".format(indexName))
except:
logger.error("Dropping Index Aborted")
def create_indexing():
'''
Description:
To Create a Index in MySQL
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
indexName = input("Enter index name: ")
cursor.execute("CREATE INDEX {} ON employee_details(name, salary)".format(indexName))
connect._CRUD__db.commit()
logger.info("Index Created: {}".format(indexName))
except:
logger.error("Creating Index Aborted")
def drop_view():
'''
Description:
Drop View
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
viewName = input("Enter view name: ")
cursor.execute("DROP VIEW {}".format(viewName))
connect._CRUD__db.commit()
logger.info("View Dropped: {}".format(viewName))
except:
logger.error("Dropping View Aborted")
def show_view():
'''
Description:
Show View
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
viewName = input("Enter view name: ")
cursor.execute("SELECT * FROM {}".format(viewName))
result = cursor.fetchall()
for i in result:
logger.info(i)
except:
logger.error("Show View Aborted")
def cross_join():
'''
Description:
Cross Join
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
cursor.execute(
"SELECT * FROM employee_details ed CROSS JOIN payroll_details pd")
result = cursor.fetchall()
for i in result:
logger.info(i)
except:
logger.error("CROSS JOIN Aborted")
def search_by_name():
'''
Description:
To Search by name in MySQL
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
name = input("Enter name: ")
cursor.execute("SELECT * FROM employee_details WHERE name = '{}'".format(name))
result = cursor.fetchall()
for i in result:
logger.info(i)
except:
logger.error("Retrieve by data Aborted")
def alter_view():
'''
Description:
Alter View
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
viewName = input("Enter view name: ")
cursor.execute(
"CREATE OR REPLACE VIEW {} AS SELECT name, salary FROM employee_details"
.format(viewName))
connect._CRUD__db.commit()
logger.info("View Altered: {}".format(viewName))
except:
logger.error("Altering View Aborted")
def create_view():
'''
Description:
Create a view in MySQL
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
viewName = input("Enter view name: ")
cursor.execute(
"CREATE VIEW {} AS SELECT * FROM employee_details".format(
viewName))
connect._CRUD__db.commit()
logger.info("View Created: {}".format(viewName))
except:
logger.error("Creating View Aborted")
def right_outer_join():
'''
Description:
Outer Right Join
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
cursor.execute(
"SELECT * FROM employee_details ed RIGHT OUTER JOIN payroll_details pd ON ed.id = pd.employee_id"
)
result = cursor.fetchall()
for i in result:
logger.info(i)
except:
logger.error("RIGHT OUTER JOIN Aborted")
def self_join():
'''
Description:
Cross Join
Parameter:
None
Return:
None
'''
try:
connect = CRUD()
cursor = connect._CRUD__db.cursor()
cursor.execute(
"SELECT * FROM employee_details ed1, employee_details ed2 WHERE ed1.salary = ed2.salary AND ed2.salary <30000"
)
result = cursor.fetchall()
for i in result:
logger.info(i)
except:
logger.error("SELF JOIN Aborted")
def __init__(self):
self.connect = CRUD()
def features():
'''
Description:
Performs CRUD Operation on MySql
Parameter:
None
Return:
None
'''
try:
logger.info("Program start")
user_input = ""
while user_input != 'q':
print("Default table selected employee_table")
print("1 - Create Table")
print("2 - Drop Table")
print("3 - Alter Table to add more fields")
print("4 - Insert record to employee_table")
print("5 - Retrieve records from employee_table")
print("6 - Update record from employee_table")
print("7 - Delete record from employee_table")
print("q - Quit")
user_input = input("Select Option: ")
connect = CRUD()
if (user_input == "1"):
logger.info("Choosen to create a new table")
connect.create_table()
elif (user_input == "2"):
logger.info("Choosen to drop table")
connect.drop_table()
elif (user_input == "3"):
logger.info("Choosen to alter table")
connect.alter_table()
elif (user_input == "4"):
logger.info("Choosen to insert record")
connect.insert()
elif (user_input == "5"):
logger.info("Choosen to retrieve records")
connect.retrieve()
elif (user_input == "6"):
logger.info("Choosen to update record")
connect.update()
elif (user_input == "7"):
logger.info("Choosen to delete record")
connect.retrieve()
connect.delete()
elif user_input == "q":
logger.info("Choosen to quit")
break
else:
print("Please Select Proper Option")
except:
raise Exception("Program Stopped")