Computer Science
Schema of table EMPL is shown below :
EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Design a Python application to obtain a search criteria from user and then fetch records based on that from empl table. (given in chapter 13, Table 13.5)
Python MySQL
1 Like
Answer
Table Empl
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 8369 | SMITH | CLERK | 8902 | 1990-12-18 | 800 | NULL | 20 |
| 8499 | ANYA | SALESMAN | 8698 | 1991-02-20 | 1600 | 300 | 30 |
| 8521 | SETH | SALESMAN | 8698 | 1991-02-22 | 1250 | 500 | 30 |
| 8566 | MAHADEVAN | MANAGER | 8839 | 1991-04-02 | 2985 | NULL | 20 |
| 8654 | MOMIN | SALESMAN | 8698 | 1991-09-28 | 1250 | 1400 | 30 |
| 8698 | BINA | MANAGER | 8839 | 1991-05-01 | 2850 | NULL | 30 |
| 8839 | AMIR | PRESIDENT | NULL | 1991-11-18 | 5000 | NULL | 10 |
| 8844 | KULDEEP | SALESMAN | 8698 | 1991-09-08 | 1500 | 0 | 30 |
| 8882 | SHIAVNSH | MANAGER | 8839 | 1991-06-09 | 2450 | NULL | 10 |
| 8886 | ANOOP | CLERK | 8888 | 1993-01-12 | 1100 | NULL | 20 |
| 8888 | SCOTT | ANALYST | 8566 | 1992-12-09 | 3000 | NULL | 20 |
| 8900 | JATIN | CLERK | 8698 | 1991-12-03 | 950 | NULL | 30 |
| 8902 | FAKIR | ANALYST | 8566 | 1991-12-03 | 3000 | NULL | 20 |
| 8934 | MITA | CLERK | 8882 | 1992-01-23 | 1300 | NULL | 10 |
import mysql.connector
db_con = mysql.connector.connect(host = "localhost",
user = "root",
passwd = "fast",
database = "employeedb")
cursor = db_con.cursor()
search_criteria = input("Enter search criteria : ")
sql1 = "SELECT * FROM EMPL WHERE {}".format(search_criteria)
cursor.execute(sql1)
records = cursor.fetchall()
print("Fetched records:")
for record in records:
print(record)
db_con.close()
Output
Enter search criteria : job = 'clerk'
Fetched records:
(8369, 'SMITH', 'CLERK', 8902, datetime.date(1990, 12, 18), 800.0, None, 20)
(8886, 'ANOOP', 'CLERK', 8888, datetime.date(1993, 1, 12), 1100.0, None, 20)
(8900, 'JATIN', 'CLERK', 8698, datetime.date(1991, 12, 3), 950.0, None, 30)
(8934, 'MITA', 'CLERK', 8882, datetime.date(1992, 1, 23), 1300.0, None, 10)
Answered By
1 Like
Related Questions
Explain what the following query will do ?
import mysql.connector db = mysql.connector.connect(....) cursor = db.cursor() person_id = input("Enter required person id") lastname = input("Enter required lastname") db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}')".format(person_id, lastname)) db.commit() db.close()Explain what the following query will do ?
import mysql.connector db = mysql.connector.connect(....) cursor = db.cursor() db.execute("SELECT * FROM staff WHERE person_id in {}".format((1, 3, 4))) db.commit() db.close()Design a Python application that fetches all the records from Pet table of menagerie database.
Design a Python application that fetches only those records from Event table of menagerie database where type is Kennel.