Store results of nested for loop as single concatenated string

Question

I'm trying to store the values of the function below to a single string that I can input into a query leveraging an F-string. The output looks correct but is really just a few separated print statements.

How can I store the output of the below to a single string?

import pandas as pd
view_dict = [{'id':'168058','viewtime_min':'2023-01-26 21:00:59.435 -0600','viewtime_max':'2023-01-26 21:59:59.435 -0600'},
                 {'id':'167268','viewtime_min':'2023-01-26 21:59:59.435 -0600','viewtime_max':'2023-01-26 21:59:59.435 -0600'},
                 {'id':'167268','viewtime_min':'2023-01-26 21:59:59.435 -0600','viewtime_max':'2023-01-26 21:59:59.435 -0600'}] 

def get_where_clause(view_dictionary: dict):
    where_clause = " "
    for index in range(len(view_dictionary)): 
        if index != max(range(len(view_dictionary))):
            print(f'''(b.id = {view_dictionary[index]['id']}
                and b.viewed_at between coalesce({view_dictionary[index]['viewtime_min']},published_at) and {view_dictionary[index]['viewtime_max']})
                or''')
        else:
            print(f'''(b.id = {view_dictionary[index]['id']}
                and b.viewed_at between coalesce({view_dictionary[index]['viewtime_min']},published_at) and {view_dictionary[index]['viewtime_max']})''')

x = get_where_clause(view_dict)

x

I'm expecting this to store to a value but when accessing the value nothing is stored.

get_where_clause does not return anything and does not change the dict — RomanPerekhrest
– RomanPerekhrest, Commented Feb 6, 2023 at 20:36
The output is the following... I'm trying to save it as a single string rather than 3 separate print statements. ``` (b.id = 168058 and b.viewed_at between coalesce(2023-01-26 21:00:59.435 -0600,published_at) and 2023-01-26 21:59:59.435 -0600) or (b.id = 167268 and b.viewed_at between coalesce(2023-01-26 21:59:59.435 -0600,published_at) and 2023-01-26 21:59:59.435 -0600) or (b.id = 167268 and b.viewed_at between coalesce(2023-01-26 21:59:59.435 -0600,published_at) and 2023-01-26 21:59:59.435 -0600) ``` — Doug
– Doug, Commented Feb 6, 2023 at 20:38

C.Nivs · Accepted Answer · 2023-02-06 20:55:31Z

You aren't actually returning or storing anything, print simply writes to the console. Ideally, you'd collect these into something like a list to be returned, which you can then use str.join to concatenate:

view_dict = [{'id':'168058','viewtime_min':'2023-01-26 21:00:59.435 -0600','viewtime_max':'2023-01-26 21:59:59.435 -0600'},
                 {'id':'167268','viewtime_min':'2023-01-26 21:59:59.435 -0600','viewtime_max':'2023-01-26 21:59:59.435 -0600'},
                 {'id':'167268','viewtime_min':'2023-01-26 21:59:59.435 -0600','viewtime_max':'2023-01-26 21:59:59.435 -0600'}] 

def get_where_clause(view_dictionary: dict):
    # I've changed this to a list
    where_clause = []

    for index in range(len(view_dictionary)): 
        if index != max(range(len(view_dictionary))):
            where_clause.append(f'''(b.id = {view_dictionary[index]['id']}
                and b.viewed_at between coalesce({view_dictionary[index]['viewtime_min']},published_at) and {view_dictionary[index]['viewtime_max']})
                or''')
        else:
            where_clause.append(f'''(b.id = {view_dictionary[index]['id']}
                and b.viewed_at between coalesce({view_dictionary[index]['viewtime_min']},published_at) and {view_dictionary[index]['viewtime_max']})''')
    
    # join together here
    return ' '.join(where_clause)



x = get_where_clause(view_dict)

print(x)

I know it isn't asked for, but this could be cleaned up a little more with some basic iteration techniques:

def get_where_clause(view_dictionary: list):
    # I've changed this to a list
    where_clause = []

    # grab the last element and iterate over a slice
    # rather than checking an index
    last = view_dictionary[-1]

    # iterate over the views directly, don't use an index
    for item in view_dictionary[:-1]:
        where_clause.append(f'''(b.id = {item['id']}
                and b.viewed_at between coalesce({item['viewtime_min']},published_at) and {item['viewtime_max']})
                or''')
    
     where_clause.append(f'''(b.id = {last['id']}
                and b.viewed_at between coalesce({last['viewtime_min']},published_at) and {last['viewtime_max']})''')
    
    # join together here
    return ' '.join(where_clause)

And to simplify formatting, you can indent a single string by using parentheses:

    for item in view_dictionary:
        where_clause.append(
            f"(b.id = {item['id']} "
            "and b.viewed_at between "
            f"coalesce({item['viewtime_min']},published_at) "
            f"and {item['viewtime_max']})"
        )
    
    # rather than checking for first/last, you can join on
    # 'or' 
    return ' or '.join(where_clause)

Saaru Lindestøkke · Accepted Answer · 2023-02-06 20:52:47Z

1

The print command just writes the text to your screen, it does not return a value. Your function needs to return a value for it to be "storable" in a variable.

I rewrote, formatted and commented your code, it now returns a string containing the where clause:

view_dict = [
    {
        "id": "168058",
        "viewtime_min": "2023-01-26 21:00:59.435 -0600",
        "viewtime_max": "2023-01-26 21:59:59.435 -0600",
    },
    {
        "id": "167268",
        "viewtime_min": "2023-01-26 21:59:59.435 -0600",
        "viewtime_max": "2023-01-26 21:59:59.435 -0600",
    },
    {
        "id": "167268",
        "viewtime_min": "2023-01-26 21:59:59.435 -0600",
        "viewtime_max": "2023-01-26 21:59:59.435 -0600",
    },
]


def get_where_clause(view_dictionary: list[dict]) -> str:
    # This is where all the outputs will be stored
    outputs = []
    # Loop over the view_dictionary, which is actually a list of dicts
    for vd in view_dictionary:
        # Just for readability: get the relevant parts out of each dict
        bid = vd['id']
        tmin = vd['viewtime_min']
        tmax = vd['viewtime_max']
        # Place everything in an f-string
        output = f"(b.id = {bid} and b.viewed_at between coalesce('{tmin}',published_at) and '{tmax}') or"
        # Print, just for fun, you won't use this output
        print(output)
        # Place every output in the outputs list
        outputs.append(output)
    # Create the actual WHERE clause by concatenating all outputs into a single string
    # the [:-3] makes sure the last 'or' keyword is removed
    where_clause = " ".join(outputs)[:-3]
    return where_clause


x = get_where_clause(view_dict)

edited Feb 6, 2023 at 20:52

answered Feb 6, 2023 at 20:41

Saaru Lindestøkke

2,6051 gold badge33 silver badges64 bronze badges

4 Comments

Doug Over a year ago

this is super helpful but I do have a remaining question. The output is returns '\n' and some random spaces in a dict format. Copy + pasting below. Do you know how I can make the output SQL legible? ``` "['(b.id = 168058\\n and b.viewed_at between coalesce(2023-01-26 21:00:59.435 -0600,published_at) and 2023-01-26 21:59:59.435 -0600)\\n or', '(b.id = 167268\\n and b.viewed_at between coalesce(2023-01-26 21:59:59.435 -0600,published_at) and 2023-01-26 21:59:59.435 -0600)\\n ```

Sahil Yadav Over a year ago

What is the use of where_clause in this snippet? and instead of returning outputs return "".join(outputs) . You can replace the empty string with whatever you want to join the strings like "\n".join(outputs) if you need the strings in new line.

Saaru Lindestøkke Over a year ago

@Doug, see my edit, you now get a single string without \n or other characters which you should be able to paste int your SQL query.

Doug Over a year ago

thanks @SaaruLindestøkke! This is the answer I went with but I made a slight edit. Rather than removing the last 3 characters, I changed that line to the following: where_clause = " or ".join(outputs)

Collectives™ on Stack Overflow

Store results of nested for loop as single concatenated string

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related