employees_sql.py

import cx_Oracle
from cx_Oracle import DatabaseError

# Connect to Oracle database
def create_connection():
    try:
        connection = cx_Oracle.connect(
            user='user name',
            password='password',
            dsn='localhost:1521/xe'
        )
        if connection is not None:
            return connection
    except DatabaseError as e:
        print(f"Error while connecting to Oracle: {e}")
        return None

# Create the employees1 table
def create_table():
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("""
                CREATE TABLE employees1 (
                    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
                    name VARCHAR2(25) NOT NULL,
                    salary FLOAT NOT NULL,
                    phone VARCHAR2(20) NOT NULL
                )
            """)
            print("employees1 table created successfully")
        except DatabaseError as e:
            print(f"Error while creating table: {e}")
        finally:
            cursor.close()
            connection.close()

# Create a new employee
def create_employee(employee):
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("""
                INSERT INTO employees1 (name, salary, phone)
                VALUES (:name, :salary, :phone)
            """, employee)
            connection.commit()
            print(f"Employee created with ID: {cursor.lastrowid}")
        except DatabaseError as e:
            print(f"Error while creating employee: {e}")
        finally:
            cursor.close()
            connection.close()

# Create multiple employees
def create_multiple_employees(employees):
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.executemany("""
                INSERT INTO employees1 (name, salary, phone)
                VALUES (:name, :salary, :phone)
            """, employees)
            connection.commit()
            print(f"{cursor.rowcount} employees created successfully")
        except DatabaseError as e:
            print(f"Error while creating multiple employees: {e}")
        finally:
            cursor.close()
            connection.close()

# Read one employee by ID
def read_one_employee(id):
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("SELECT * FROM employees1 WHERE id = :id", {'id': id})
            employee = cursor.fetchone()
            if employee:
                print(employee)
            else:
                print(f"No employee found with ID: {id}")
        except DatabaseError as e:
            print(f"Error while reading employee: {e}")
        finally:
            cursor.close()
            connection.close()

# Read all employees
def read_all_employees():
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("SELECT * FROM employees1")
            employees = cursor.fetchall()
            for employee in employees:
                print(employee)
        except DatabaseError as e:
            print(f"Error while reading all employees: {e}")
        finally:
            cursor.close()
            connection.close()

# Update an employee by ID
def update_employee(id, update_fields):
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            update_query = "UPDATE employees1 SET " + ", ".join([f"{field} = :{field}" for field in update_fields.keys()]) + " WHERE id = :id"
            cursor.execute(update_query, {**update_fields, 'id': id})
            connection.commit()
            if cursor.rowcount > 0:
                print(f"Employee with ID: {id} has been updated.")
            else:
                print(f"No employee found with ID: {id} or no changes made.")
        except DatabaseError as e:
            print(f"Error while updating employee: {e}")
        finally:
            cursor.close()
            connection.close()

# Delete an employee by ID
def delete_one_employee(id):
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("DELETE FROM employees1 WHERE id = :id", {'id': id})
            connection.commit()
            if cursor.rowcount > 0:
                print(f"Employee with ID: {id} has been deleted.")
            else:
                print(f"No employee found with ID: {id}")
        except DatabaseError as e:
            print(f"Error while deleting employee: {e}")
        finally:
            cursor.close()
            connection.close()

# Delete multiple employees by a filter
def delete_many_employees(salary_threshold):
    connection = create_connection()
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("DELETE FROM employees1 WHERE salary < :salary", {'salary': salary_threshold})
            connection.commit()
            print(f"{cursor.rowcount} employees have been deleted.")
        except DatabaseError as e:
            print(f"Error while deleting multiple employees: {e}")
        finally:
            cursor.close()
            connection.close()


# Create the employees1 table
# create_table()

# Create example employee
# employee_example = {
#         "name": "John Doe",
#         "salary": 50000,
#         "phone": "123-456-7890"
#     }
# create_employee(employee_example)

# # Create multiple employees
# employees_list = [
#         {"name": "Alice Smith", "salary": 60000, "phone": "555-1234"},
#         {"name": "Bob Johnson", "salary": 70000, "phone": "555-5678"},
#         {"name": "Charlie Brown", "salary": 55000, "phone": "555-8765"}]
# create_multiple_employees(employees_list)

# # Read example employee
# example_id = 1  # Replace with the actual ID of an existing employee
# read_one_employee(example_id)

# # Read all employees
# read_all_employees()

# # Update example employee
# example_id =1
# update_fields = {"salary": 55000, "phone": "098-765-4321"}
# update_employee(example_id, update_fields)

# Delete example employee
example_id =1
delete_one_employee(example_id)

# # Delete multiple employees
delete_many_employees(60000)