Program for an employee database using python

Question

In this program I'm defining functions to perform different tasks on a data record book for employees.

Here is a summary of the code:

Intro() to make the presentation of the home page.
create_record() to create records of employees.
search(name) to look for any employee and their details.
display_all() to show details of all the employees.
delete_records to delete record of any employee by searching for their name.
modify_records(name) to modify previous employee records.
main () to call all the defined functions as provided by the options for the functioning of the program.

The code must have functions and libraries that are there in the CBSE class 12 Computer Science Syllabus.

import mysql.connector

import time

db = mysql.connector.connect(
host="localhost",
user="root",
password="admin",
database="contact"
)

cursor = db.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS book (
name char(30) primary key,
address char(100),
mobile char(15),
email char(30)
);
""")

def intro():

    print("=" * 80)

    print("{:^80s}".format("EMPLOYEE"))

    print("{:^80s}".format("CONTACT REGISTER"))

    print("{:^80s}".format("PROJECT"))

    print("{:^80s}".format("MADE BY: BMMSchool.com"))

    print("=" * 80)

    print()

    time.sleep(2)

def create_record():

    name = input("Enter Employee name: ")

    address = input("Enter address: ")

    mobile = input("Enter mobile: ")

    email = input("Enter email: ")

    sql = "INSERT INTO book(name,address,mobile,email) VALUES (%s,%s,%s,%s)"

    record = (name, address, mobile, email)

    cursor.execute(sql, record)

    db.commit()

    print("Record Entered Successfully\n")

def search(name):

    sql = "SELECT * FROM book WHERE name = %s"

    value = (name,)


    cursor.execute(sql, value)

    record = cursor.fetchone()

    if record is None:

        print("No such record exists")

    else:

        print('Name:', record[0])

        print('Address:', record[1])

        print('Mobile:', record[2])

        print('E-mail:', record[3])

def display_all():

    cursor.execute("SELECT * FROM book")

    print('{0:20}{1:30}{2:15}{3:30}'.format('NAME', 'ADDRESS', 'MOBILE NO', 'E-MAIL'))

    for record in cursor:

        print('{0:20}{1:30}{2:15}{3:30}'.format(record[0], record[1], record[2],record[3]))

def delete_record(name):

    sql = "DELETE FROM book WHERE name = %s"

    value = (name,)

    cursor.execute(sql, value)

    db.commit()

    if cursor.rowcount == 0:

        print("Record not found")

    else:

        print("Record deleted successfully")

def modify_record(name):

    sql = "SELECT * FROM book WHERE name = %s"

    value = (name,)

    cursor.execute(sql, value)

    record = cursor.fetchone()

    if record is None:

        print("No such record exists")

    else:

        while True:

            print("\nPress the option you want to edit: ")

            print("1. Employee Name")

            print("2. Address")

            print("3. Mobile")

            print("4. BACK")

            print()

            ch = int(input("Select Your Option (1-4): "))

            if ch == 1:

                new_name = input("Enter new name: ")

                sql = "UPDATE book SET name = %s WHERE name = %s"

                values = (new_name, name)

                cursor.execute(sql, values)

                db.commit()

                print(cursor.rowcount, "record updated successfully")

            elif ch == 2:

                new_address = input("Enter new address: ")

                sql = "UPDATE book SET address = %s WHERE name = %s"

                values = (new_address, name)

                cursor.execute(sql, values)

                db.commit()

                print(cursor.rowcount, "record updated successfully")

            elif ch == 3:

                new_mobile = input("Enter new mobile : ")

                sql = "UPDATE book SET mobile = %s WHERE name = %s"

                values = (new_mobile, name)

                cursor.execute(sql, values)

                db.commit()

                print(cursor.rowcount, "record updated successfully")

            elif ch == 4:

                break

            else:

                print("Invalid choice !!!\n")
def main():

    intro()

    while True:

        print("\nMAIN MENU ")

        print("1. ADD NEW RECORD")

        print("2. SEARCH RECORD")

        print("3. DISPLAY ALL RECORDS")

        print("4. DELETE RECORD")

        print("5. MODIFY RECORD")

        print("6. EXIT")

        print()

        ch = int(input("Select Your Option (1-6): "))

        print()

        if ch == 1:

            print("ADD NEW RECORD")


            create_record()

        elif ch == 2:

            print("SEARCH RECORD BY NAME")

            name = input("Enter name: ")

            search(name)


        elif ch == 3:


            print("DISPLAY ALL RECORDS")

            display_all()

        elif ch == 4:

            print("DELETE RECORD")

            name = input("Enter name: ")

            delete_record(name)

        elif ch == 5:

            print("MODIFY RECORD")

            name = input("Enter name: ")

            modify_record(name)

        elif ch == 6:

            print("Thanks for using Contact Book")

            db.close()

            break

        else:

            print("Invalid choice")

main()

toolic · Accepted Answer · 2024-10-09 14:51:32Z

Overview

You did a good job of partitioning the code into functions, and you used meaningful names for the functions.

Layout

The code is difficult to understand because you added a blank line between every line of code. You need to use blank lines much more sparingly. I suggest only using a blank line between functions in this code. For example:

def intro():
    print("=" * 80)
    print("{:^80s}".format("EMPLOYEE"))
    print("{:^80s}".format("CONTACT REGISTER"))
    print("{:^80s}".format("PROJECT"))
    print("{:^80s}".format("MADE BY: BMMSchool.com"))
    print("=" * 80)
    print()
    time.sleep(2)

def create_record():
    name = input("Enter Employee name: ")
    address = input("Enter address: ")
    mobile = input("Enter mobile: ")
    email = input("Enter email: ")
    sql = "INSERT INTO book(name,address,mobile,email) VALUES (%s,%s,%s,%s)"
    record = (name, address, mobile, email)
    cursor.execute(sql, record)
    db.commit()
    print("Record Entered Successfully\n")

def search(name):
    sql = "SELECT * FROM book WHERE name = %s"
    value = (name,)

I recommend placing all the function definitions immediately after the import lines. Place all the procedural code at the bottom.

Also, indent the multi-line function calls:

db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="admin",
    database="contact"
)
cursor = db.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS book (
    name char(30) primary key,
    address char(100),
    mobile char(15),
    email char(30)
    );
""")
main()

Input checking

Check all input, especially before entering the input data into the database. Don't be Little Bobby Tables :)

Naming

Give all variables meaningful names. For example, ch should be choice.

Documentation

Add a doctring to the top of the code to summarize its purpose:

"""
Data record book for employees using MySql

Here are some more details...

"""

DRY

The following lines are repeated a few times (except for "name"):

new_name = input("Enter new name: ")
sql = "UPDATE book SET name = %s WHERE name = %s"
values = (new_name, name)
cursor.execute(sql, values)
db.commit()
print(cursor.rowcount, "record updated successfully")

You could factor that code out into another function:

def do_update(name):

Some justification on why to drop the blank lines. You could argue that blank lines make it more readable, but screen space is valuable. Less of the program fits on the screen, so it makes it harder to navigate between different parts of your code. That said, blank lines can add a lot to readibility. They are often added between two "blocks" of code, where each block does something different from the previous block. This is similar to how paragraphs are used in normal text. — AccidentalTaylorExpansion, Commented Oct 10, 2024 at 8:21
MySQL connector escapes parameters. So there's no risk of SQL injection with this code, because execute() is used correctly. Of course there are other reasons why you'd likely wish to check the inputs. And offer the record to be reviewed by the user before inserting into the database. — TrayMan, Commented Oct 10, 2024 at 19:04
@toolic , I am new to this web page. So I was unaware of the fact that double spacing actually left a blank line space . Because in my preview window the code lines seemed to be attached together ... I'll keep it in mind from the next time . Thanks a lot for helping me out 😊😊 — Abhisikta Ray, Commented Oct 11, 2024 at 15:31
@AbhisiktaRay: Welcome to the site! A lot of people who are new to the site have trouble posting code, so don't feel bad. — toolic, Commented Oct 11, 2024 at 15:54

Chris · Accepted Answer · 2024-10-09 16:23:44Z

In addition to what toolic has brought up, in intro you have the magic number 80. It would be advisable to factor this out as a variable or as an argument that intro takes.

Any change in width can now be made in one place rather than several.

def intro():
    width = 80
    print("=" * width)
    print("EMPLOYEE".rjust(width))
    print("CONTACT REGISTER".rjust(width))
    print("PROJECT".rjust(width))
    print("MADE BY: BMMSchool.com".rjust(width))
    print("=" * width)
    time.sleep(2)

def intro(width=80):
    print("=" * width)
    print("EMPLOYEE".rjust(width))
    print("CONTACT REGISTER".rjust(width))
    print("PROJECT".rjust(width))
    print("MADE BY: BMMSchool.com".rjust(width))
    print("=" * width)
    time.sleep(2)

2 revs · Accepted Answer · 2024-10-10 03:07:31Z

6

This does nothing useful:

def main():
    ...

main()

The idiom to use is:

def main():
    ...

if __name__ == "__main__":
    main()

In accordance with this, these top-level statements should be moved into main() and the functions be redesigned so as not to rely on global constructs:

db = mysql.connector.connect(...)
cursor = db.cursor()
cursor.execute(...)

See also: What does if __name__ == "__main__": do?.

edited Oct 10, 2024 at 3:07

community wiki

2 revs
InSync

3

\$\begingroup\$ Yes! Suppose a unit test has def test_search() and def test_delete_record(). It should be able to import this module without side effects. \$\endgroup\$
– J_H
Commented Oct 10, 2024 at 3:04

Add a comment |

Booboo · Accepted Answer · 2024-10-14 17:29:13Z

You have statement sequences such as:

ch = int(input("Select Your Option (1-6): "))

If I were to enter, for example, 'one' in stead of '1', your program would raise a ValueError exception in trying to convert that string to an int. You should either be validating the input before attempting the conversion or catching the exception. I would suggest you create a function whose sole purpose is to input and return an int value. For example:

def input_choice(prompt: str, max_choice: int) -> int:
    """Input an integer between 1 and max_choice inclusive
    and return its value."""

    INVALID_CHOICE_MSG = 'Invalid choice'

    while True:
        try:
            choice = int(input(prompt))
        except ValueError:
            pass
        else:
            if 1 <= choice <= max_choice:
                return choice
        print(INVALID_CHOICE_MSG)

Stack Exchange Network

Program for an employee database using python

4 Answers 4

Overview

Layout

Input checking

Naming

Documentation

DRY

Hot Network Questions

4 Answers 4

Overview

Layout

Input checking

Naming

Documentation

Related