如何将Python字典转换为带过滤条件的动态SQL查询？

需要将给定的Python字典转换为SQL查询，字典结构如下：

dict_info = {
    "Tables": [
        "product",
        "sales"
    ],
    "Columns": {
        "product": [
            "prod_id",
            "prod_desc",
        ],
        "sales": [
            "prod_id",
            "prod_sales",
            "prod_qty"
        ]
    },
    "Filter": {
        "region_id": "1,2",
        "state_id": "4,7,9",
        "store_id": "14",
    }
}

要求为每个表生成独立的SELECT查询，期望结果示例：

Query1= " SELECT product.prod_id, product.prod_desc FROM product WHERE product.region_id in 
          (1,2) AND product.state_id in (4,7,9) AND product.store_id in (14);

Query2= " SELECT sales.prod_id, sales.prod_sales, sales.prod_qty FROM sales WHERE
          sales.region_id in (1,2) AND sales.state_id in (4,7,9) AND sales.store_id in (14);

已尝试基础代码实现查询生成，但需要完善添加过滤语句：

import pandas as pd
import numpy as np

get_tables = dict_info.get("Tables", [])
get_columns = dict_info.get("Columns", [])

for i in tables:
    query = "SELECT " + ", ".join(columns[i]) + ' FROM ' + i ";"

需要完善代码，添加过滤语句，为每个选中的表生成动态SQL查询。

以下是完善后的代码，解决了变量名错误、过滤条件缺失等问题，完全匹配需求格式：

dict_info = {
    "Tables": [
        "product",
        "sales"
    ],
    "Columns": {
        "product": [
            "prod_id",
            "prod_desc",
        ],
        "sales": [
            "prod_id",
            "prod_sales",
            "prod_qty"
        ]
    },
    "Filter": {
        "region_id": "1,2",
        "state_id": "4,7,9",
        "store_id": "14",
    }
}

# 提取字典中的核心数据
get_tables = dict_info.get("Tables", [])
get_columns = dict_info.get("Columns", {})
get_filters = dict_info.get("Filter", {})

# 遍历每个表生成对应SQL查询
for idx, table in enumerate(get_tables, start=1):
    # 生成带表前缀的列名（如product.prod_id）
    prefixed_cols = [f"{table}.{col}" for col in get_columns.get(table, [])]
    select_part = ", ".join(prefixed_cols)
    
    # 生成带表前缀的过滤条件
    filter_parts = []
    for field, vals in get_filters.items():
        filter_str = f"{table}.{field} in ({vals})"
        filter_parts.append(filter_str)
    where_part = " AND ".join(filter_parts)
    
    # 拼接完整查询语句
    full_query = f"SELECT {select_part} FROM {table} WHERE {where_part};"
    print(f"Query{idx}= \" {full_query}")

关键优化点 #

1. 变量名修正：修复了原代码中tables、columns未定义的错误，统一使用从字典中提取的变量
2. 前缀自动添加：为列名和过滤字段自动添加表前缀，符合示例格式要求
3. 过滤条件动态生成：遍历Filter字典自动生成IN子句，无需硬编码每个条件
4. 查询编号匹配：用enumerate从1开始生成查询编号，和示例格式完全一致

运行代码后将直接输出符合要求的两条SQL查询语句。

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