用于 SQL 代码片段执行的字符串提取和格式化

Question

我在一个字符串中键入了一些需要提取的参数&使用字符串CCD_ 1函数替换。代码片段如下：

# Filter list (output from another script)
filters ='recipient_id=100, reporting_date=2020-10-12'
# Fetch SQL file from location
sql_file = 'configs/sql_files/{sql_name}.sql'.format(sql_name=sql_name)
file_path = os.path.realpath(__file__)
final_file_path = "/".join(file_path.split("/")[:-2]) + "/" + sql_file
with open(final_file_path) as sql_file:
# Pass in filters to the SQL snippet. The SQL snippet has two parameters
# recipient_id & reporting_date
sql = sql_file.read().format(filters)
try:
sf = get_sql_client()
except Exception as e:
print("Error connecting to the DB!")
sys.exit()
df = sf.fetch_df(sql)

代码片段失败为"；过滤器"；正在作为字符串传递。

sql_file.read().format('recipient_id=100, reporting_date=2020-10-12')

相反，它应该通过如下：

sql_file.read().format(recipient_id=100, reporting_date='2020-10-12')

有没有一种方法可以提取"；过滤器"；字符串&按照上面的格式格式化？

SQL文件示例：

SELECT columns..
FROM A
join B on <condition>
WHERE true
AND REPORTING_LEVEL_1_ID = '{recipient_id}'  
AND date_trunc('day', delivered_date_pt) >= DATEADD(day, -7, last_day('{reporting_date}'::date, 'week') + 1)
AND date_trunc('day', delivered_date_pt) <= last_day('{reporting_date}'::date, 'week')

Answer 1

尝试使用双引号，如so…

filters ="recipient_id=100, reporting_date='2020-10-12'"

Answer 2

假设sql文件包含类似的内容，并用于过滤如图所示的信息格式(作为一系列名称和值对，用逗号和空格字符分隔(，您可以从后者创建dictionary，然后将其传递给format()方法，如图所述(使用**前缀来解压缩其中的项(。

filter_data = 'recipient_id=100, reporting_date=2020-10-12'
final_file_path = 'sql_file'
with open(final_file_path) as sql_file:
pairs = (pair.split('=') for pair in filter_data.replace(',', '').split())
mapping = {k: v for (k, v) in pairs}
sql = sql_file.read().format(**mapping)
print(sql)

输出：

SELECT columns..
FROM A
join B on <condition>
WHERE true
AND REPORTING_LEVEL_1_ID = '100'
AND date_trunc('day', delivered_date_pt) >= DATEADD(day, -7, last_day('2020-10-12'::date, 'week') + 1)
AND date_trunc('day', delivered_date_pt) <= last_day('2020-10-12'::date, 'week')