Fetch Customer Orders with pyodbc

This function connects to a database using pyodbc, executes a SQL query to fetch order information for a specific customer, and converts the query result into a list of dictionaries containing order details.

Technology Stack : pyodbc, SQL Server, database connection, SQL query

Code Type : Database query

Code Difficulty : Intermediate

                
                    
import pyodbc

def fetch_customer_orders(customer_id):
    """
    Fetches customer orders from a database using pyodbc.

    Parameters:
    customer_id (int): The ID of the customer for whom to fetch orders.

    Returns:
    list: A list of dictionaries, each representing an order with details like order_id, product_name, quantity, and price.
    """
    # Establishing a connection to the database
    connection = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_database;UID=your_username;PWD=your_password')
    cursor = connection.cursor()

    # SQL query to fetch orders for a specific customer
    query = "SELECT o.OrderID, p.ProductName, o.Quantity, o.Price FROM Orders o INNER JOIN Products p ON o.ProductID = p.ProductID WHERE o.CustomerID = ?"
    cursor.execute(query, (customer_id,))

    # Fetching all the rows from the result
    orders = cursor.fetchall()

    # Converting the result to a list of dictionaries
    order_list = []
    for order in orders:
        order_dict = {
            'OrderID': order.OrderID,
            'ProductName': order.ProductName,
            'Quantity': order.Quantity,
            'Price': order.Price
        }
        order_list.append(order_dict)

    # Closing the connection
    cursor.close()
    connection.close()

    return order_list