Lesson 8 – Working with SQL Server Databases for HR Systems

Welcome back to the Python from 0 to Hero series! In this lesson, we’ll shift from using file-based databases like SQLite to a more powerful relational database system: SQL Server. This is especially useful for handling large-scale HR systems and payroll management, where data integrity and performance are critical.

We will cover:

1. Setting up a connection to a SQL Server database using Python.
2. Performing CRUD operations (Create, Read, Update, Delete) on employee data.
3. Practical examples for managing employee data in an HR system using SQL Server.

By the end of this lesson, you will have the foundation to integrate a SQL Server database into your HR applications.

1. Why Use SQL Server?

For larger organizations, SQL Server is a robust, enterprise-grade database solution that offers several advantages over file-based databases like SQLite:

• Scalability: It can handle millions of records efficiently.
• Data Security: SQL Server provides advanced security features such as encryption and role-based access control.
• Integration: It integrates well with other Microsoft services, often used in corporate HR systems.
• Performance: Optimized for large datasets and complex queries.

2. Setting Up Python to Work with SQL Server

To connect Python with SQL Server, we'll use the pyodbc library, which provides an easy interface to connect to ODBC databases, including SQL Server.

Step 1: Installing pyodbc

First, you need to install the pyodbc package. You can do this using pip:

pip install pyodbc

Step 2: Setting Up a Connection

You’ll need the server name, database name, and login credentials (username and password) for your SQL Server instance.

Here’s how to set up a connection in Python:

import pyodbc

# Set up connection parameters
server = 'your_server_name'  # Example: 'localhost\SQLEXPRESS'
database = 'your_database_name'  # Example: 'HRDatabase'
username = 'your_username'  # Example: 'sa'
password = 'your_password'

# Establish connection to SQL Server
connection = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};'
                            f'SERVER={server};DATABASE={database};'
                            f'UID={username};PWD={password}')

# Create a cursor object
cursor = connection.cursor()

Make sure you replace your_server_name, your_database_name, your_username, and your_password with the actual details of your SQL Server instance.

3. Creating an Employee Table in SQL Server

Now that we're connected, let’s create a table for employees in SQL Server. This will store basic employee data like ID, name, position, and salary.

# Create the employees table
cursor.execute('''
CREATE TABLE employees (
    id INT PRIMARY KEY IDENTITY(1,1),
    name NVARCHAR(100) NOT NULL,
    position NVARCHAR(100) NOT NULL,
    salary FLOAT NOT NULL
)
''')

# Commit the changes to the database
connection.commit()

In this query:

• id is the primary key, auto-incremented.
• name, position, and salary store the employee details.

4. Inserting Employee Data into SQL Server

Let’s add a few employee records to the employees table.

# Insert employee data
cursor.execute('''
INSERT INTO employees (name, position, salary)
VALUES (?, ?, ?)
''', ('John Doe', 'HR Manager', 5500.00))

# Commit the changes
connection.commit()

In this example:

• We use parameterized queries (?) to prevent SQL injection attacks.
• This query inserts an employee named John Doe with the position of HR Manager and a salary of 5500.00.

5. Reading Employee Data from SQL Server

You can retrieve employee data using the SELECT statement. For example, let’s fetch all employee records:

# Fetch all employee records
cursor.execute('SELECT * FROM employees')

# Fetch and print each row
employees = cursor.fetchall()
for employee in employees:
    print(employee)

This retrieves and prints all rows from the employees table. Each employee is stored as a tuple with the values in the order they appear in the table (e.g., id, name, position, salary).

6. Updating Employee Records in SQL Server

Let’s say you need to update an employee’s salary. You can do this with the UPDATE statement:

# Update the salary of an employee
cursor.execute('''
UPDATE employees
SET salary = ?
WHERE name = ?
''', (6000.00, 'John Doe'))

# Commit the changes
connection.commit()

Here, we updated John Doe’s salary to 6000.00.

7. Deleting Employee Records from SQL Server

To delete an employee from the database, use the DELETE statement:

# Delete an employee record
cursor.execute('''
DELETE FROM employees
WHERE name = ?
''', ('John Doe',))

# Commit the changes
connection.commit()

This query deletes the employee named John Doe from the employees table.

8. Practical Example: Building an Employee Management System with SQL Server

Now let’s combine everything into a simple employee management system using SQL Server.

import pyodbc

# Connection setup
server = 'your_server_name'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'

connection = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};'
                            f'SERVER={server};DATABASE={database};'
                            f'UID={username};PWD={password}')
cursor = connection.cursor()

# Create the employees table
cursor.execute('''
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY IDENTITY(1,1),
    name NVARCHAR(100),
    position NVARCHAR(100),
    salary FLOAT
)
''')
connection.commit()

# Function to add a new employee
def add_employee(name, position, salary):
    cursor.execute('''
    INSERT INTO employees (name, position, salary)
    VALUES (?, ?, ?)
    ''', (name, position, salary))
    connection.commit()

# Function to fetch and display all employees
def display_employees():
    cursor.execute('SELECT * FROM employees')
    employees = cursor.fetchall()
    for emp in employees:
        print(emp)

# Function to update an employee's salary
def update_salary(employee_name, new_salary):
    cursor.execute('''
    UPDATE employees
    SET salary = ?
    WHERE name = ?
    ''', (new_salary, employee_name))
    connection.commit()

# Function to delete an employee
def delete_employee(employee_name):
    cursor.execute('''
    DELETE FROM employees
    WHERE name = ?
    ''', (employee_name,))
    connection.commit()

# Example usage
add_employee('Alice Smith', 'Software Engineer', 7000.00)
add_employee('Bob Johnson', 'Product Manager', 8000.00)

print("All employees:")
display_employees()

update_salary('Alice Smith', 7500.00)

print("\nUpdated employees:")
display_employees()

delete_employee('Bob Johnson')

print("\nAfter deletion:")
display_employees()

# Close the connection
connection.close()

How It Works:

1. We connect to SQL Server and create an employees table (if it doesn’t already exist).
2. We define functions to add, display, update, and delete employee records.
3. The script adds two employees, updates one of their salaries, and then deletes an employee from the database.

9. Best Practices for Working with SQL Server in Python

1. Use Parameterized Queries: Always use parameterized queries to avoid SQL injection attacks.
2. Handle Exceptions: Implement proper error handling using try-except blocks, especially when connecting to the database or executing queries.
3. Connection Management: Ensure that connections are properly closed after queries are executed, or use context managers (with statements) for automatic handling.
4. Indexing: For large datasets, consider adding indexes to frequently queried columns (e.g., employee name or position) to speed up data retrieval.

Conclusion

In this lesson, you learned how to:

• Connect Python to SQL Server using the pyodbc library.
• Perform CRUD operations (Create, Read, Update, Delete) on an employee database.
• Build a basic employee management system that stores employee records in SQL Server.

Using a robust database like SQL Server makes your HR or payroll system scalable and secure, especially when managing large datasets or integrating with other systems.

Keep Coding :)