如何通过PostgreSQL生成指定格式的表选择规则JSON

针对从test表提取表名，批量生成目标格式JSON规则的需求，提供两种实用方案：

方法一：直接用SQL生成JSON片段 #

利用数据库的字符串拼接和行号函数，直接输出符合格式的内容，适合快速生成结果。

MySQL 实现 #

SELECT CONCAT(
    '    {',
    '      "rule-type": "selection",',
    '      "rule-id": "100', ROW_NUMBER() OVER (ORDER BY name), '",',
    '      "rule-name": "100', ROW_NUMBER() OVER (ORDER BY name), '",',
    '      "object-locator": {',
    '        "schema-name": "%",',
    '        "table-name": "', name, '"',
    '      },',
    '      "rule-action": "include",',
    '      "filters": []',
    '    }',
    -- 最后一条规则不加逗号，避免JSON语法错误
    CASE WHEN ROW_NUMBER() OVER (ORDER BY name) < (SELECT COUNT(*) FROM test) THEN ',' ELSE '' END
) AS json_rule
FROM test
ORDER BY name;

执行后将结果复制，前后包裹[和]即可得到合法JSON数组。

PostgreSQL 实现 #

SELECT STRING_AGG(
    format(
        '    {
      "rule-type": "selection",
      "rule-id": "%s",
      "rule-name": "%s",
      "object-locator": {
        "schema-name": "%%",
        "table-name": "%s"
      },
      "rule-action": "include",
      "filters": []
    }',
    '100' || row_number() OVER (ORDER BY name),
    '100' || row_number() OVER (ORDER BY name),
    name
    ), ',
'
) AS json_rules
FROM test;

此语句直接生成完整的JSON元素集合，同样只需前后加[]即可。

方法二：Python脚本批量生成（适合导出文件） #

如果需要直接生成JSON文件，或处理带特殊字符的表名，用脚本更可靠：

import json
import pymysql  # MySQL用这个，PostgreSQL替换为psycopg2

# 数据库连接配置
conn = pymysql.connect(
    host="你的数据库地址",
    user="用户名",
    password="密码",
    database="库名"
)
cursor = conn.cursor()

# 读取所有表名
cursor.execute("SELECT name FROM test ORDER BY name")
table_list = cursor.fetchall()

# 生成规则列表
rules = []
for idx, (table_name,) in enumerate(table_list, start=1):
    rule_id = f"100{idx}"
    rules.append({
        "rule-type": "selection",
        "rule-id": rule_id,
        "rule-name": rule_id,
        "object-locator": {
            "schema-name": "%",
            "table-name": table_name
        },
        "rule-action": "include",
        "filters": []
    })

# 写入JSON文件（自动处理转义和格式）
with open("table_rules.json", "w", encoding="utf-8") as f:
    json.dump(rules, f, indent=4)

# 关闭连接
cursor.close()
conn.close()

关键注意事项 #

• 若表名包含双引号等特殊字符，Python的json模块会自动转义，SQL方案则需手动添加转义逻辑（如MySQL用REPLACE(name, '"', '\\"')）
• 规则ID起始值可通过调整ROW_NUMBER()或enumerate的start参数修改
• 生成的JSON可通过jsonlint等工具验证语法，避免格式错误

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