Answer to Question #188519 in Python for geetika

Question

Write Program to perform following tasks (10 Marks)

a. Create a database SELECTION_DB

b. Set connection with mysql.connector.connect.

c. Create a table EMP_SELECTION in database SELECTION_DB with following data FIRST_NAME,LAST_NAME,AGE,GENDER,INCOME.

d. change table structure / (add, edit, remove column of a table) at run time

i. add a column address in the EMP_SELECTIONtable.

ii. execute SQL INSERT statement to create a record into EMP_SELECTION table

iii. run the query to updates all the records having GENDER as 'M', and increase AGE of all the males by one year.

iv. delete all the records from EMP_SELECTION Table where AGE is less than 18 .

Answer 1

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE SELECTION_DB")


mycursor.execute("CREATE TABLE EMP_SELECTION (FIRST_NAME VARCHAR(255) ,LAST_NAME VARCHAR(255) ,AGE INT,GENDER VARCHAR(1),INCOME INT)")
mycursor.execute("ALTER TABLE EMP_SELECTION ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

mycursor.execute("ALTER TABLE EMP_SELECTION ADD COLUMN address VARCHAR(255)" )
  

sql = "INSERT INTO EMP_SELECTION (FIRST_NAME,LAST_NAME,AGE,GENDER,INCOME,address) VALUES (%s, %s,%d , %s, %d )"
val = ("John", "Highway",21,"M",1000,"221 bakers street")


mycursor.execute(sql, val)
mycursor.commit()

sql = "Update EMP_SELECTIOn set age=age +1. where GENDER='M'"
mycursor.execute(sql)
mycursor.commit()

sql = "Delete from EMP_SELECTIOn where GENDER='M' and age < 18"
mycursor.execute(sql)
mycursor.commit()