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.
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)