如何将嵌套循环生成的SQL WHERE子句存储为单个拼接字符串？

希望将下方函数的输出存储为单个字符串，以便通过F-string传入查询语句。目前函数的输出内容正确，但仅为分散的print语句，调用函数后变量x未存储任何值，请问如何实现将输出保存为单个字符串？

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

你的函数目前只是将内容打印到控制台，没有返回任何值，因此变量x会是None。要实现将输出保存为单个字符串，需要构建字符串并通过return返回，而非用print输出。

修改后的代码 #

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: list):
    # 为每个条目生成对应的SQL条件片段
    clause_list = []
    for item in view_dictionary:
        # 给时间字符串添加单引号，避免SQL语法错误
        clause = f'''(b.id = {item['id']}
            and b.viewed_at between coalesce('{item['viewtime_min']}',published_at) and '{item['viewtime_max']}')'''
        clause_list.append(clause)
    # 用' or '拼接所有片段，生成完整的WHERE子句
    return ' or '.join(clause_list)

x = get_where_clause(view_dict)
# 此时x已存储完整的字符串，可以直接用于F-string查询
print(x)

关键修改说明 #

1. 替换print为字符串构建：不再直接打印内容，而是将每个条件片段存入列表，最后用join方法拼接，比逐个判断是否为最后一条更简洁高效
2. 添加return返回结果：函数最后返回拼接好的完整字符串，调用时变量x就能接收该字符串
3. 修正SQL语法问题：给时间字段值添加单引号，避免SQL解析时出现语法错误
4. 修正参数类型注解：原函数标注参数为dict，但实际传入的是列表list，修改后避免类型提示错误

内容的提问来源于stack exchange，提问作者Doug