db1.py

import mysql.connector


# Establishing connection
def connect_db():
    return mysql.connector.connect(
        host="127.0.0.1", user="root", password="", database="examdb"
    )


# Insert a new user
def insert_user(username, password):
    db = connect_db()
    cursor = db.cursor()
    try:
        query = "INSERT INTO users (username, password) VALUES (%s, %s)"
        cursor.execute(query, (username, password))
        db.commit()
        print(f"User '{username}' inserted successfully.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        cursor.close()
        db.close()


# Delete a user
def delete_user(username):
    db = connect_db()
    cursor = db.cursor()
    try:
        query = "DELETE FROM users WHERE username = %s"
        cursor.execute(query, (username,))
        db.commit()
        if cursor.rowcount > 0:
            print(f"User '{username}' deleted successfully.")
        else:
            print(f"User '{username}' not found.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        cursor.close()
        db.close()


# Update a user's password
def update_user_password(username, new_password):
    db = connect_db()
    cursor = db.cursor()
    try:
        query = "UPDATE users SET password = %s WHERE username = %s"
        cursor.execute(query, (new_password, username))
        db.commit()
        if cursor.rowcount > 0:
            print(f"Password for user '{username}' updated successfully.")
        else:
            print(f"User '{username}' not found.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        cursor.close()
        db.close()


# Login testing
def login_user(username, password):
    db = connect_db()
    cursor = db.cursor()
    try:
        query = "SELECT * FROM users WHERE username = %s AND password = %s"
        cursor.execute(query, (username, password))
        result = cursor.fetchone()
        if result:
            print(f"Login successful for user '{username}'.")
        else:
            print(f"Invalid credentials for user '{username}'.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        cursor.close()
        db.close()


# Show all users
def show_users():
    db = connect_db()
    cursor = db.cursor()
    try:
        query = "SELECT * FROM users"
        cursor.execute(query)
        users = cursor.fetchall()
        numb = 1
        if users:
            print("\n--- Registered Users ---")
            for user in users:
                print(f"{numb} - {user[0]} - {user[1]}")
                numb += 1
        else:
            print("No users found.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        cursor.close()
        db.close()


# Menu-driven interface
def main():
    while True:
        print("\n--- User Management ---")
        print("1. Insert User")
        print("2. Delete User")
        print("3. Update User Password")
        print("4. Login Testing")
        print("5. Show Users")
        print("6. Exit")
        choice = input("Enter your choice: ")

        if choice == "1":
            username = input("Enter username: ")
            password = input("Enter password: ")
            insert_user(username, password)
        elif choice == "2":
            username = input("Enter username to delete: ")
            delete_user(username)
        elif choice == "3":
            username = input("Enter username: ")
            new_password = input("Enter new password: ")
            update_user_password(username, new_password)
        elif choice == "4":
            username = input("Enter username: ")
            password = input("Enter password: ")
            login_user(username, password)
        elif choice == "5":
            show_users()
        elif choice == "6":
            print("Exiting program.")
            break
        else:
            print("Invalid choice! Please try again.")


if __name__ == "__main__":
    main()