Question

Create the database with at least 10 employees and 2 managers are populated. Important: your name should be in the employee table.

Write a Python code that can do the following:

    1) Take an employee ID from user

    2) Display the output of the SQL, select * from employee;

Improve the Python code above that can do the following:

    1) Take an employee ID from the user. Let it be idEntered.

    2) Get the departmentID for the given employeeID, idEntered. Let it be deptFound.

    3) Check if idEntered is in the table manager

    4) If yes, then execute: query = ("select * from employee where dID = %s; "). Note that since a variable deptFound is included in SQL, if it should be included, then use %s in SQL. And then declare a variable, data_tuple = (deptFound, ) in a separate line. Finally, execute the query cursor.execute(query, data_tuple).

    5) if No, simply execute the SQL: select * from employee where eid = %s; do the similarly way for idEntered as above

Answer 1

Hello there,Thanks For asking this question.I am here providing you all the necessary details if need more help or any Clarification. Feel free to contact me by any means necessary.

Creating Db:-

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE python")

Creating table:-

import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="python"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (id VARCHAR(255), deptid VARCHAR(255), name VARCHAR(255))")

Code 1&2:-

import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="python"
)

mycursor = mydb.cursor()
identered= input("Enter Your Id:")
mycursor.execute("SELECT * FROM customers WHERE id LIKE %s", ("%" + user_input + "%",))

myresult = mycursor.fetchall()

for x in myresult:
print(x)

Code part 2(1,2,3&4):-

import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="python"
)

mycursor = mydb.cursor()
identered = input("Enter Your Id:-")
deptfound = input("Enter Your deptid:-")
sql="SELECT * FROM customers WHERE deptid LIKE %s"
mycursor.execute(sql,("%" + deptfound + "%",))

myresult = mycursor.fetchall()

if(mycursor.rowcount == 0):
    print("No records found for deptid Showing Result For id:- "+identered)
    sql="SELECT * FROM customers WHERE id LIKE %s"
    mycursor.execute(sql,("%" + identered + "%",))
    myresult = mycursor.fetchall()
    for x in myresult:
        print(x)
else:
    for x in myresult:
        print(x)
  

Hope it helped you,If there is any kind of error in code or it needs any modification do let me know,And please give your upvote to this it''ll encourage to do my work more efficiently.

Happy Learning!!!.