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()
pyodbc.connect()
function, passing connection parameters such as server name, database name, username, and password.try-except-finally
block to handle exceptions and ensure proper cleanup of resources even if an error occurs during execution.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.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.fetchall()
method of the cursor. The result is an iterable containing rows of data retrieved from the database.except
block. We print the error message to the console for debugging and error reporting purposes.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.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
FirstName
, LastName
, Age
) from the Employees
table who belong to the ‘IT’ department, joining the Departments
table on the DepartmentID
.