Home » Python » Python programs

Fetch employee details from the database whose salary lies within a certain range in Python

Here, we are going to learn how to fetch employee details from the database whose salary lies within a certain range in Python?
Submitted by Shivang Yadav, on February 19, 2021

Problem Statement: We need to fetch the details of employees from the database whose salaries are within a certain range in Python.

Solution:

We will use python's pymysql library to work with the database. This library provides the programmer the functionality to run MySQL query using Python.

Algorithm:

• Step 1: Take the maximum and minimum salary from the user.
• Step 2: Create a MySQL query, to select data within the range from the database. Refer: SQL tutorial for help.
• Step 3: Connect to database and run the query. Using the execute command.
• Step 4: Store all fetched employee details in row variable.
• Step 5: print details.

Python program to fetch details from the database

import pymysql as mysql

try:
    conn=mysql.connect(host='localhost',port=3306,user='root',password='123',db='myschool')
    cmd=conn.cursor()
    
    min=input("Enter Min Salary : ")
    max=input("Enter Max Salary : ")
    
    q="select * from faculties where salary between {} and {}".format(min,max)
    cmd.execute(q)
    
    rows=cmd.fetchall()
    
    #print(rows)
    for row in rows:
        print(row[1],row[4])
    
    conn.close()
except Exception as e:
    print("Error:",e)

Output:

Enter Min Salary : 25000
Enter Max Salary : 45000
34	34000
65	29500

Python database (SQL/MySQL) programs »

---