Joining tables and executing parameterized queries

This example demonstrates how to use the pyodbc module to connect to a SQL Server database and execute a parameterized query that joins two tables. The script starts by establishing a connection to the database using connection string parameters, such as the server name, database name, user ID, and password. Within a try-except block for error handling, a cursor is created to execute an SQL query that retrieves employee details from the ‘IT’ department. The query joins the Employees and Departments tables and uses a parameterized query to ensure security against SQL injection. The results are fetched and printed in a formatted output, and finally, the cursor and connection are closed.

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)

try:
    # Create a cursor
    cursor = conn.cursor()

    # Joining tables and executing parameterized query
    department = 'IT'
    cursor.execute('''
        SELECT e.FirstName, e.LastName, e.Age, d.DepartmentName
        FROM Employees e
        INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
        WHERE d.DepartmentName = ?
    ''', (department,))

    # Fetch and print the result
    print(f"Employees in the '{department}' department:")
    print("----------------------------------------------------------")
    for row in cursor.fetchall():
        print("{:<15} | {:<15} | {:<5} | {:<10}".format(row.FirstName, row.LastName, row.Age, row.DepartmentName))

except pyodbc.Error as e:
    print("An error occurred:", e)

finally:
    # 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. Using a Try-Except-Finally Block:
   • We use a try-except-finally block to handle exceptions and ensure proper cleanup of resources even if an error occurs during execution.
3. Creating a Cursor:
   • Inside the try block, 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.
4. Executing a Parameterized Query:
   • We execute a parameterized SQL query using the execute() method of the cursor. The query selects employee details (first name, last name, age, and department name) from the Employees table based on a specified department.
   • The department name is passed as a parameter to the query to prevent SQL injection attacks and ensure safe and efficient execution.
5. Fetching and Printing the Result:
   • We fetch the result of the query using the fetchall() method of the cursor. The result is an iterable containing rows of data retrieved from the database.
   • We iterate over the fetched rows and print employee details in a tabular format for easy readability.
6. Error Handling:
   • Any errors that occur during execution of the SQL query are caught in the except block. We print the error message to the console for debugging and error reporting purposes.
7. Closing Cursor and Connection:
   • In the finally block, we close the cursor and connection using the close() method to release database resources and ensure proper cleanup, regardless of whether an error occurred or not.

Output

The output of the code example would depend on the data present in the Employees and Departments tables in your SQL Server database. However, let’s assume the following data is present:

Employees Table:
+------------+-----------+----------+--------+
| FirstName  | LastName  | Age      | DeptID |
+------------+-----------+----------+--------+
| John       | Doe       | 30       | 1      |
| Jane       | Smith     | 35       | 2      |
| Tom        | Jones     | 40       | 1      |
+------------+-----------+----------+--------+

Departments Table:
+--------------+-----------+
| DepartmentID | DeptName  |
+--------------+-----------+
| 1            | IT        |
| 2            | HR        |
+--------------+-----------+

The output of the code would be:

Employees in the 'IT' department:
----------------------------------------------------------
John           | Doe            | 30   | IT
Tom            | Jones          | 40   | IT

• We executed a SQL query to select employee details (FirstName, LastName, Age) from the Employees table who belong to the ‘IT’ department, joining the Departments table on the DepartmentID.
• The query retrieved two rows of data: one for John Doe and another for Tom Jones, who both belong to the ‘IT’ department.
• We then printed the fetched data in a tabular format, displaying the first name, last name, age, and department name for each employee.