How to Convert SQL in Lambda Expressions?

Lambda expressions make it simple to define anonymous functions in computer languages. Functions can be used as arguments or returned as values in functional programming paradigms, where they are treated as first-class citizens.

Lambda expressions let you declare a plain function “inline”—without giving it a name—when it has to be written and will only be called once. They are also a great way to streamline your code and make it clearer and more expressive.

In many programming languages, lambda expressions are distinguished by the “lambda” keyword, a list of parameters that are separated by commas, a colon, and the expression that will be evaluated.

Knowing SQL Query Language

Understanding the syntax of SQL queries is important before we get into the specifics of turning them into Lambda expressions. Most SQL queries are made up of various components, including:

1. SELECT: This clause defines which database columns should be retrieved.
2. From: The FROM clause defines which table or tables should be used to retrieve the data.
3. WHERE: This clause outlines any prerequisites that must be satisfied in order for a row to be returned by the query.
4. GROUP BY: Using one or more columns, this clause groups the result’s rows.
5. HAVING: This clause outlines any prerequisites that groups in the outcome must satisfy.
6. ORDER BY: This phrase outlines the sequence in which the result’s rows should be displayed.

Connecting to the Database

• We begin by importing the pyodbc library, which enables database interaction with Python programmes.

pip install pyodbc

• once the library has been installed. The following code can be used to establish a connection to the AdventureWorks2016 database 
• Then, by providing the server name, database name, login, and password, we set up the connection to the SQL Server database.

import pyodbc

conn_str = (
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=server_name;'
    r'DATABASE=AdventureWorks2016;'
    r'UID=username;'
    r'PWD=password;'
)

with pyodbc.connect(conn_str) as cnxn:
    cursor = cnxn.cursor()

The raw string (r”) that makes up the connection string spans several lines. The server name, database name, username, and password are hardcoded values in the string.

The with statement enables the establishment of the connection and cursor objects. The pyodbc.connect() method receives the connection string as input, and the generated cnxn object is used as a context manager. This ensures that the connection is properly terminated when the block is exited.

Using the cnxn.cursor() method inside the block produces the cursor object. This cursor can be used to execute SQL queries against the database.

Query:

SELECT * FROM Production.Product
 WHERE ListPrice > 3000;

The following code can be used to run this query using the cursor.execute() method:

cursor.execute('SELECT * FROM 
Production.Product WHERE ListPrice > 3000')
products = cursor.fetchall()

we execute an SQL query using the cursor.execute() method. The query retrieves all the products from the Production.Product table with a list price greater than 3000. This code executes the SQL query and stores the results in the products variable as a list of dictionaries.

Converting SQL to Lambda Expressions

Now that we have retrieved the data from the database, we can convert the SQL query to lambda expressions in Python. The equivalent lambda expression for the SQL query would be

list(filter(lambda p: p['ListPrice'] > 3000, products))

The list(filter()) function takes a lambda function as its first argument and filters the products list according to the lambda expression. The lambda function takes a single argument, p, which represents each product in the list, and returns True if the ListPrice field of the product is greater than 3000.

Finally, we print the results of the SQL query and the lambda expression to the console. The for loop iterates over each product in the products list and prints it to the console.

print('SQL query:')
for product in products:
    print(product)
    
print('Lambda expression:')
for product in list(filter(lambda p: 
p['ListPrice'] > 3000, products)):
    print(product)

Output of the SQL Query:

Output of the Lambda Expression:

{'ProductID': 749, 'Name': 'Road-150 Red, 62', 'ProductNumber': 'BK-R93R-62
', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Red', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 2171.2942, 'ListPrice': 3578.27
,  'Size': '62', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '15', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 750, 'Name': 'Road-150 Red, 44
', 'ProductNumber': 'BK-R93R-44', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Red', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 2171.2942, 'ListPrice': 3578.27
,  'Size': '44', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '13.77
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 751, 'Name': 'Road-150 Red, 48
', 'ProductNumber': 'BK-R93R-48, 38', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Red', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 2171.2942, 'ListPrice': 3578.27
, 'Size': '48', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '14.13
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 752, 'Name': 'Road-150 Red, 52
', 'ProductNumber': 'BK-R93R-52, 42', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Red', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 2171.2942, 'ListPrice': 3578.27
, 'Size': '52', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '14.42
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 753, 'Name': 'Road-150 Red, 56
', 'ProductNumber': 'BK-R93R-56, 38', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Red', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 2171.2942, 'ListPrice': 3578.27
, 'Size': '56', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '14.68
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 778, 'Name': 'Mountain-100 Silver, 38', 'ProductNumber': 
'BK-M82S-38', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Silver
', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1912.1544, 'ListPrice': 3399.99
, 'Size': '38', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '20.35
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 771, 'Name': 'Mountain-100 Silver, 42', 'ProductNumber': 
'BK-M82S-42', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Silver', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1912.1544, 'ListPrice': 3399.99, 'Size': '42', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '20.77
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 772, 'Name': 'Mountain-100 Silver, 44', 'ProductNumber': 
'BK-M82S-44', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Silver', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1912.1544, 'ListPrice': 3399.99, 'Size': 
'44', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '21.13
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 773, 'Name': 'Mountain-100 Silver, 48', 'ProductNumber':
 'BK-M82S-48', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Silver', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1912.1544, 'ListPrice': 3399.99, 'Size':
 '48', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '21.42
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 774, 'Name': 'Mountain-100 Silver, 38', 'ProductNumber': 
'BK-M82S-38', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Black
', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1898.0944, 'ListPrice': 3374.99
, 'Size': '38', 'WeightUnitMeasureCode
' : 'LB', 'Weight': '20.35
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 775, 'Name': 'Mountain-100 Silver, 42', 'ProductNumber'
: 'BK-M82S-42', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Black', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1898.0944, 'ListPrice': 3374.99, 'Size': '42', 
'WeightUnitMeasureCode
' : 'LB', 'Weight': '20.77
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 776, 'Name': 'Mountain-100 Silver, 44', 'ProductNumber': 
'BK-M82S-44', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Black', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1898.0944, 'ListPrice': 3374.99, 'Size': '44',
 'WeightUnitMeasureCode
' : 'LB', 'Weight': '21.13
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}
{'ProductID': 777, 'Name': 'Mountain-100 Silver, 48', 'ProductNumber': 
'BK-M82S-48', 'MakeFlag': 1, 'FinishedGoodsFlag
': 1, 'Color
': 'Black', 'SafetyStockLevel
': 100, 'ReorderPoint
' : 75, 'StandardCost': 1898.0944, 'ListPrice': 3374.99, 'Size': '48', 
'WeightUnitMeasureCode
' : 'LB', 'Weight': '21.42
', 'ProductCategoryID': 'U', 'ProductModelID': 'HF-0578'}