topic Re: How to store SQL query output columns as variables to be used as parameters for API data call in in Data Engineering

How to store SQL query output columns as variables to be used as parameters for API data call in DAT

LearnDB1234 — Thu, 20 Feb 2025 19:53:26 GMT

I have a sql query which provides me with the below output :

Select FirstName,LastName,Title From Default.Name

Tony         Gonzalez Mr
Tom          Brady    Mr
Patricia     Carroll  Miss

I would like to store FirstName, LastName & title column output rows as variable so i can use these variables as an input to my API data call which would take the names and return me with the data

Re: How to store SQL query output columns as variables to be used as parameters for API data call in

Brahmareddy — Fri, 21 Feb 2025 03:49:20 GMT

Hi @LearnDB1234 ,

How are you doing today? Try storing the SQL query result into variables and then using them as input for your API call. This approach ensures that you dynamically fetch data from SQL and use it as input for your API calls efficiently.

I hope this helps! give a try and let me know.

Regards,

Brahma

Re: How to store SQL query output columns as variables to be used as parameters for API data call in

LearnDB1234 — Fri, 21 Feb 2025 03:54:12 GMT

Adding more information that needs to be done in below :

My API Call looks something like this: ( Ignore the format & coding as this is a test code the actual code i have works fine)

from zeep import Client
from zeep.transports import Transport
from requests import Session
from requests.auth import HTTPBasicAuth
from pyspark.sql.types import StructType, StructField, StringType, BooleanType
from pyspark.sql.functions import lit
from datetime import datetime

wsdl_url =  "Test.xml"

session = Session()
session.auth = HTTPBasicAuth('abc','password')

transport = Transport(session=session)

client = Client(wsdl=wsdl_url, transport=transport)

# List all available services and ports
print(client.wsdl.services)

# Ensure the service and port names are correct
service_name = 'Test'
port_name = 'TestPort'

# Verify the available services and ports
for service in client.wsdl.services.values():
    print(f"Service: {service.name}")
    for port in service.ports.values():
        print(f"  Port: {port.name}")

# Bind to the correct service and port
service = client.bind("Test", "BasicHttpBinding_TestService")

request_data = {
    'userAuth': {
        'Nickname':"Test"
    },
    'requestReference': 'test',
    'request': {
        'Subject': {
            'Forename': 'Patricia',
            'Surname': 'Carroll'
        },
        'Address': {
            'AddressLine1': '123 Test Street',
            'AddressLine2': '',
            'AddressLine3': 'LONDON',
            'Postcode': 'ABC 123'
        },
        'ConsentFlag': True
    }
}

if service:
    response = service.PerformIDCheckV2(**request_data)
    
    # Define the schema explicitly
    schema = StructType([
        StructField("FirstName", StringType(), True),
        StructField("SurName", StringType(), True),
        StructField("response", StringType(), True),
        StructField("ETLApplyDateTime", StringType(), True)
    ])

We can see above where i have hardcoded Patricia Name, i want those names to be passed as input from the above sql output

Re: How to store SQL query output columns as variables to be used as parameters for API data call in

LearnDB1234 — Fri, 21 Feb 2025 03:56:44 GMT

Hi Brahma,

Thanks for the response, i am new to this so if you can please provide me with the pseudo code that helps.

Re: How to store SQL query output columns as variables to be used as parameters for API data call in

Brahmareddy — Fri, 21 Feb 2025 04:02:21 GMT

Hi @LearnDB1234 ,

Here is the approach - You can make your API call dynamic by first running your SQL query and storing the results in a DataFrame. Then, you can loop through each row in the DataFrame and extract the FirstName and LastName values, passing them into your API request instead of hardcoding them.

Here’s a simple way to do it -

1. Run your SQL query to get FirstName, LastName, and Title into a DataFrame.

2. Convert the DataFrame into a list so you can iterate through each row.

3. Inside a loop, dynamically insert FirstName and LastName into your API request.

4. Call the API for each person and collect responses.

5. Store responses in a new DataFrame for further processing.

This way, every API request will use actual names from your SQL output, removing the need for hardcoded values. It keeps things clean, scalable, and automated.

Regards,

Brahma