Create Tables and insert Data

import pyodbc

# Connect to the SQL Server database
conn_str = (
    r'DRIVER={SQL Server};'
    r'SERVER=your_server;'
    r'DATABASE=your_database;'
    r'UID=your_username;'
    r'PWD=your_password;'
)
conn = pyodbc.connect(conn_str)

# Create a cursor
cursor = conn.cursor()

# Create a table if it doesn't exist
cursor.execute('''
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Employees')
    BEGIN
        CREATE TABLE Employees (
            EmployeeID INT PRIMARY KEY,
            FirstName NVARCHAR(50),
            LastName NVARCHAR(50),
            Age INT,
            Department NVARCHAR(50)
        )
    END
''')

# Insert data into the table if it's empty
cursor.execute('SELECT COUNT(*) FROM Employees')
if cursor.fetchone()[0] == 0:
    employees = [
        (1, 'Michael', 'Johnson', 30, 'IT'),
        (2, 'Emily', 'Brown', 35, 'HR'),
        (3, 'Christopher', 'Davis', 40, 'Finance')
    ]
    cursor.executemany('INSERT INTO Employees VALUES (?, ?, ?, ?, ?)', employees)
    conn.commit()

# Fetch and print data from the table
print("EmployeeID | FirstName | LastName | Age | Department")
print("----------------------------------------------------")
cursor.execute('SELECT * FROM Employees')
rows = cursor.fetchall()
for row in rows:
    print("{:<11} | {:<9} | {:<8} | {:<3} | {:<10}".format(row.EmployeeID, row.FirstName, row.LastName, row.Age, row.Department))

# Update an employee's age
new_age = 45
cursor.execute('UPDATE Employees SET Age = ? WHERE LastName = ?', (new_age, 'Johnson'))
conn.commit()
print("\nEmployee 'Johnson' updated. New age:", new_age)

# Delete an employee
cursor.execute('DELETE FROM Employees WHERE LastName = ?', ('Brown',))
conn.commit()
print("\nEmployee 'Brown' deleted.")

# Fetch and print updated data from the table
print("\nUpdated Employee Data:")
print("EmployeeID | FirstName | LastName | Age | Department")
print("----------------------------------------------------")
cursor.execute('SELECT * FROM Employees')
rows = cursor.fetchall()
for row in rows:
    print("{:<11} | {:<9} | {:<8} | {:<3} | {:<10}".format(row.EmployeeID, row.FirstName, row.LastName, row.Age, row.Department))

# Close the cursor and connection
cursor.close()
conn.close()

Code Explanation

1. Connecting to the Database:
   • We establish a connection to the SQL Server database using the pyodbc.connect() function, passing connection parameters such as server name, database name, username, and password.
2. Creating a Cursor:
   • We create a cursor object using the cursor() method of the connection. The cursor allows us to execute SQL statements and interact with the database.
3. Creating the Employees Table (if not exists):
   • We execute a SQL query to create the Employees table if it doesn’t already exist in the database. This query is wrapped in a conditional statement to avoid errors if the table already exists.
4. Inserting Data into the Table (if empty):
   • We check if the Employees table is empty by executing a SELECT COUNT(*) query. If the count is 0, indicating no records are present, we insert sample employee data into the table using the executemany() method.
5. Fetching and Printing Data:
   • We execute a SELECT * FROM Employees query to fetch all records from the Employees table. We iterate over the fetched rows and print them in a tabular format for easy readability.
6. Updating Employee Data:
   • We update an employee’s age by executing an UPDATE SQL statement, setting the age to a new value (new_age) for the employee with the last name ‘Johnson’. We commit the transaction to save the changes to the database.
7. Deleting an Employee:
   • We delete an employee from the Employees table by executing a DELETE SQL statement, specifying the last name (‘Brown’) of the employee to be deleted. Again, we commit the transaction to save the changes.
8. Fetching and Printing Updated Data:
   • We fetch and print the updated data from the Employees table to reflect the changes made. This helps verify that the updates and deletions were successful.
9. Closing Cursor and Connection:
   • Finally, we close the cursor and connection using the close() method to release database resources and ensure proper cleanup.

Output

EmployeeID | FirstName | LastName | Age | Department
----------------------------------------------------
1          | Michael   | Johnson  | 30  | IT
2          | Emily     | Brown    | 35  | HR
3          | Christopher | Davis  | 40  | Finance

Employee 'Johnson' updated. New age: 45

Employee 'Brown' deleted.

Updated Employee Data:
EmployeeID | FirstName | LastName | Age | Department
----------------------------------------------------
1          | Michael   | Johnson  | 45  | IT
3          | Christopher | Davis  | 40  | Finance