Efficiently Managing Employee Records Using Azure SQL and Python

Building an Employee Management System with Azure SQL Database and Python

Objective

Develop a Python application that connects to an Azure SQL Database to perform basic CRUD (Create, Read, Update, Delete) operations on a database. This project aims to demonstrate how to set up an Azure SQL Database, integrate it with a Python application, and perform database operations efficiently.

Requirements

1. Setup Azure SQL Database SQL Database Configuration: Create an Azure SQL Database instance. Instance Details: Configure instance settings including pricing tier, compute size, storage, and networking. Database Setup: Create a new database within the Azure SQL instance and configure user roles and permissions.
2. Python Application Development Environment Setup: Set up a Python development environment with necessary libraries (e.g., pyodbc or pymssql for database connections, SQLAlchemy for ORM). Database Connection: Implement code to connect the Python application to the Azure SQL Database using database credentials. CRUD Operations: Develop functions to perform the following database operations: Create: Insert new records into the database. Read: Retrieve data from the database based on specific queries. Update: Modify existing records in the database. Delete: Remove records from the database.
3. Security and Compliance Data Encryption: Ensure data encryption both at rest and in transit. Access Control: Implement role-based access control and use Azure AD for secure access to the SQL Database. Backup and Recovery: Set up automated backups, snapshots, and database recovery mechanisms.
4. Testing and Validation Unit Testing: Write unit tests for CRUD functions to ensure they perform as expected. Integration Testing: Test the integration between the Python application and the SQL Database. Performance Testing: Validate the performance of the SQL Database under different loads and optimize as necessary.
5. Documentation and Training Code Documentation: Document the codebase, including setup instructions, API endpoints, and usage examples. User Guide: Create a user guide for end-users detailing how to use the application, including screenshots and step-by-step instructions. Training Sessions: Conduct training sessions for team members to ensure they understand how to deploy and use the application.

Detailed Solution with Code Documentation

Environment Setup

Ensure you have Python installed and set up a virtual environment:

python -m venv venv
source venv/bin/activate  # On Windows use `venv\Scripts\activate`
pip install pyodbc sqlalchemy flask
        

Database Connection

Create a Python script (e.g., app.py) and add code to connect to the Azure SQL Database:

python

import pyodbc

# Database connection parameters
server = 'your-server-name.database.windows.net'
database = 'your-database-name'
username = 'your-username'
password = 'your-password'
driver = '{ODBC Driver 17 for SQL Server}'

# Connect to the database
connection = pyodbc.connect(f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}')
print("Connected to the database successfully!")
        

CRUD Operations

1. Create: Insert new records into the database.
2. Read: Retrieve data from the database based on specific queries.
3. Update: Modify existing records in the database.
4. Delete: Remove records from the database.

Here's the complete code with CRUD operations:

python

import pyodbc

class DatabaseManager:
    def __init__(self, server, database, username, password):
        self.connection = pyodbc.connect(
            f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}'
        )
        self.cursor = self.connection.cursor()

    def create_table(self):
        create_table_query = """
        CREATE TABLE IF NOT EXISTS employees (
            id INT IDENTITY(1,1) PRIMARY KEY,
            name NVARCHAR(255) NOT NULL,
            position NVARCHAR(255) NOT NULL,
            salary DECIMAL(10, 2) NOT NULL
        )
        """
        self.cursor.execute(create_table_query)
        self.connection.commit()

    def insert_employee(self, name, position, salary):
        insert_query = """
        INSERT INTO employees (name, position, salary) VALUES (?, ?, ?)
        """
        self.cursor.execute(insert_query, (name, position, salary))
        self.connection.commit()

    def read_employees(self):
        read_query = "SELECT * FROM employees"
        self.cursor.execute(read_query)
        return self.cursor.fetchall()

    def update_employee(self, employee_id, name, position, salary):
        update_query = """
        UPDATE employees SET name = ?, position = ?, salary = ? WHERE id = ?
        """
        self.cursor.execute(update_query, (name, position, salary, employee_id))
        self.connection.commit()

    def delete_employee(self, employee_id):
        delete_query = "DELETE FROM employees WHERE id = ?"
        self.cursor.execute(delete_query, (employee_id,))
        self.connection.commit()

    def close_connection(self):
        self.connection.close()

if __name__ == "__main__":
    # Database connection parameters
    server = 'your-server-name.database.windows.net'
    database = 'your-database-name'
    username = 'your-username'
    password = 'your-password'

    # Create a DatabaseManager instance
    db_manager = DatabaseManager(server, database, username, password)

    # Create employees table
    db_manager.create_table()

    # Insert new employee
    db_manager.insert_employee("Jane Doe", "Data Scientist", 85000.00)

    # Read employees
    employees = db_manager.read_employees()
    for emp in employees:
        print(emp)

    # Update employee
    db_manager.update_employee(1, "Jane Doe", "Senior Data Scientist", 95000.00)

    # Delete employee
    db_manager.delete_employee(1)

    # Close the database connection
    db_manager.close_connection()
        

let's integrate the Python application code with the Employee Management System. This comprehensive example will include secure login, CRUD operations, and templates for the user interface.

Complete Python Application: Employee Management System

Install Required Libraries

Ensure you have the necessary libraries installed:

bash

pip install flask pyodbc flask-bcrypt flask-login
        

Application Structure

Create a project directory structure as follows:

app/
├── templates/
│   ├── index.html
│   ├── login.html
│   ├── add_employee.html
│   └── update_employee.html
├── __init__.py
├── app.py
├── auth.py
├── config.py
├── forms.py
└── models.py
        

config.py

This file contains the configuration for the Flask application.

python

import os

class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'supersecretkey'
    SQLALCHEMY_DATABASE_URI = 'mssql+pyodbc://<username>:<password>@<server>:1433/<database>?driver=ODBC+Driver+17+for+SQL+Server'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
        

models.py

This file contains the database models.

python

from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt
from flask_login import LoginManager

db = SQLAlchemy()
bcrypt = Bcrypt()
login_manager = LoginManager()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(150), unique=True, nullable=False)
    password = db.Column(db.String(150), nullable=False)

    def set_password(self, password):
        self.password = bcrypt.generate_password_hash(password).decode('utf-8')

    def check_password(self, password):
        return bcrypt.check_password_hash(self.password, password)

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150), nullable=False)
    position = db.Column(db.String(150), nullable=False)
    salary = db.Column(db.Float, nullable=False)
        

forms.py

This file contains the form definitions.

python

from flask_wtf import FlaskForm
from wtforms import StringField, PasswordField, SubmitField, FloatField
from wtforms.validators import DataRequired, Length

class LoginForm(FlaskForm):
    username = StringField('Username', validators=[DataRequired(), Length(min=2, max=150)])
    password = PasswordField('Password', validators=[DataRequired()])
    submit = SubmitField('Login')

class EmployeeForm(FlaskForm):
    name = StringField('Name', validators=[DataRequired(), Length(min=2, max=150)])
    position = StringField('Position', validators=[DataRequired(), Length(min=2, max=150)])
    salary = FloatField('Salary', validators=[DataRequired()])
    submit = SubmitField('Submit')
        

auth.py

This file contains the authentication routes.

python

from flask import Blueprint, render_template, redirect, url_for, flash
from flask_login import login_user, current_user, logout_user
from .models import User
from .forms import LoginForm
from . import db

auth = Blueprint('auth', __name__)

@auth.route('/login', methods=['GET', 'POST'])
def login():
    if current_user.is_authenticated:
        return redirect(url_for('main.index'))
    
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user and user.check_password(form.password.data):
            login_user(user)
            return redirect(url_for('main.index'))
        else:
            flash('Login Unsuccessful. Please check username and password', 'danger')
    
    return render_template('login.html', form=form)

@auth.route('/logout')
def logout():
    logout_user()
    return redirect(url_for('auth.login'))
        

app.py

This file contains the main application logic.

python

from flask import Flask, render_template, redirect, url_for, flash, request
from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt
from flask_login import LoginManager, login_required, login_user, current_user, logout_user
from config import Config
from forms import LoginForm, EmployeeForm
from models import db, bcrypt, login_manager, User, Employee

app = Flask(__name__)
app.config.from_object(Config)
db.init_app(app)
bcrypt.init_app(app)
login_manager.init_app(app)
login_manager.login_view = 'auth.login'

@app.route('/')
@login_required
def index():
    employees = Employee.query.all()
    return render_template('index.html', employees=employees)

@app.route('/add', methods=['GET', 'POST'])
@login_required
def add_employee():
    form = EmployeeForm()
    if form.validate_on_submit():
        employee = Employee(name=form.name.data, position=form.position.data, salary=form.salary.data)
        db.session.add(employee)
        db.session.commit()
        flash('Employee added successfully!', 'success')
        return redirect(url_for('index'))
    return render_template('add_employee.html', form=form)

@app.route('/update/<int:employee_id>', methods=['GET', 'POST'])
@login_required
def update_employee(employee_id):
    employee = Employee.query.get_or_404(employee_id)
    form = EmployeeForm()
    if form.validate_on_submit():
        employee.name = form.name.data
        employee.position = form.position.data
        employee.salary = form.salary.data
        db.session.commit()
        flash('Employee updated successfully!', 'success')
        return redirect(url_for('index'))
    elif request.method == 'GET':
        form.name.data = employee.name
        form.position.data = employee.position
        form.salary.data = employee.salary
    return render_template('update_employee.html', form=form)

@app.route('/delete/<int:employee_id>', methods=['POST'])
@login_required
def delete_employee(employee_id):
    employee = Employee.query.get_or_404(employee_id)
    db.session.delete(employee)
    db.session.commit()
    flash('Employee deleted successfully!', 'success')
    return redirect(url_for('index'))

if __name__ == "__main__":
    app.run(debug=True)
        

index.html

This file contains the main page template.

html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Employee Management System</title>
</head>
<body>
    <h1>Employee Management System</h1>
    <a href="{{ url_for('add_employee') }}">Add Employee</a>
    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Position</th>
                <th>Salary</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            {% for employee in employees %}
                <tr>
                    <td>{{ employee.id }}</td>
                    <td>{{ employee.name }}</td>
                    <td>{{ employee.position }}</td>
                    <td>{{ employee.salary }}</td>
                    <td>
                        <a href="{{ url_for('update_employee', employee_id=employee.id) }}">Update</a>
                        <form action="{{ url_for('delete_employee', employee_id=employee.id) }}" method="POST" style="display:inline;">
                            <button type="submit">Delete</button>
                        </form>
                    </td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>
        

login.html

This file contains the login page template.

html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Employee Management System - Login</title>
</head>
<body>
    <h1>Login</h1>
    <form method="POST">
        {{ form.hidden_tag() }}
        <div>
            {{ form.username.label }} {{ form.username }}
        </div>
        <div>
            {{ form.password.label }} {{ form.password }}
        </div>
        <div>
            {{ form.submit }}
        </div>
    </form>
    {% for message in get_flashed_messages(with_categories=true) %}
        <div class="{{ message[0] }}">{{ message[1] }}</div>
    {% endfor %}
</body>
</html>
        

add_employee.html

This file contains the add employee page template.

html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Employee Management System - Add Employee</title>
</head>
<body>
    <h1>Add Employee</h1>
    <form method="POST">
        {{ form.hidden_tag() }}
        <div>
            {{ form.name.label }} {{ form.name }}
        </div>
        <div>
            {{ form.position.label }} {{ form.position }}
        </div>
        <div>
            {{ form.salary.label }} {{ form.salary }}
        </div>
        <div>
            {{ form.submit }}
        </div>
    </form>
    {% for message in get_flashed_messages(with_categories=true) %}
        <div class="{{ message[0] }}">{{ message[1] }}</div>
    {% endfor %}
</body>
</html>
        

update_employee.html

Conclusion

The project "Using Azure SQL Database for Databases in a Python Application" equips learners with practical skills in cloud computing and database management. By setting up an Azure SQL Database instance and integrating it with a Python application, users can perform basic CRUD operations in a secure and efficient manner. This hands-on experience is invaluable for understanding key concepts such as database connectivity, data encryption, role-based access control, and automated backups.

The application, titled Employee Management System, provides a real-world business scenario for managing employee data. It covers essential functionalities like secure user login, adding new employees, viewing, updating, and deleting records, with clear notifications for all operations. This ensures that the system is user-friendly, reliable, and meets the business requirements effectively.

Through this project, learners will not only gain technical proficiency but also understand the importance of security, compliance, and performance optimization in cloud-based applications. The comprehensive approach, from initial setup to testing and documentation, prepares them to handle similar challenges in real-world business environments.

Job Coaching

At VSKumar Coaching, we build similar experiences by having demos for your profile. These demonstrations help scale up your profile faster and make job interviews easier. For some of the participants' demos, visit: vskumarcoaching.com. These practical demonstrations provide a hands-on learning experience, equipping you with the skills needed to excel in your career and stand out in the job market.

The Aspired AI professionals are suggested to see my live tasks related digital courses initially to learn Clou/DevOps/AI/ML/Gen AI/DevOps

How our coaching program works, learn from the following profiles:

See the Cloud/DevOps/IAC/AI/ML/Gen AI Built experience profile demos [14 plus years exp IT professional:

https://vskumarcoaching.com/srivallis-demos1

A 10 plus years ETL/QA Background IT Professional built for Cloud/DevOps/IAC/Terraform/Python experiences:

https://vskumarcoaching.com/hemanth-azure-demos-1

Ongoing Participants demos [yet to be completed the Gen AI scaling up]:

Rahul Patil an ML engineer with 4 plus years experience getting converted into Gen AI through Cloud/DevOps/IAC/AI/ML/Gen AI Experiences building:

https://vskumarcoaching.com/rahul-phase1-demos

Ravi Kumar An insurance Claims Analyst [with 4 plus years experience NONIT] getting converted into Gen AI through Cloud/DevOps/IAC/AI/ML/Gen AI Experiences building:

https://vskumarcoaching.com/ravi-kumar-p1-demos

If you are keen your profile evaluation call is mandated for us to design your scale up program roadmap.

Book a call [its a paid call] from Linkedin.

https://www.dhirubhai.net/in/vskumaritpractices/

AI Practices in Cybersecurity: Safeguarding the Digital World:

In the digital era, where cyber threats are continually evolving, the intersection of Artificial Intelligence (AI) and cybersecurity has emerged as a critical frontier.

AI practices in cybersecurity are revolutionizing how we detect, respond to, and predict cyber threats, offering unprecedented opportunities to enhance digital security. Let's delve into the key AI practices that are shaping the cybersecurity landscape.

Building an Employee Management System with AWS RDS and Python | LinkedIn