时间戳

首页 > 解决方案 > 用于 SQL 片段执行的字符串提取和格式化

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

问题描述

我在字符串中键入了一些参数，这些参数需要使用字符串format()函数进行提取和替换。代码片段如下：

# 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')

标签： pythonpython-3.xstring-formatting

解决方案

假设 sql 文件具有这样的内容并用于过滤信息格式，如图所示（作为一系列名称和值对，由逗号和空格字符分隔），您可以从后者创建字典，然后将其传递给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')

推荐阅读