如何基于JSON-Schema文件自动生成PostgreSQL建表SQL代码

我有如下JSON Schema文件，需要基于它自动在PostgreSQL中创建数据表，已经尝试用Python脚本实现，但需要帮助。

JSON Schema 文件内容 #

{
  "$schema": "https://json-schema.org/draft/2019-09/schema",
  "$defs": {
    "FC1": {
      "$anchor": "FC1",
      "allOf": [
        {
          "$ref": "../Th1/th1s.json"
        },
        {
          "type": "object",
          "properties": {
            "properties": {
              "type": "object",
              "properties": {
                "@type": {
                  "type": "string"
                },
                "Attr1": {
                  "type": "string"
                },
                "Attr2": {
                  "type": "string"
                },
                "attr3": {
                  "$ref": "#/$defs/Attr3Value"
                },
                "attr4": {
                  "type": "array",
                  "items": {
                    "type": "string",
                    "format": "uri"
                  },
                  "uniqueItems": true
                }
              },
              "required": [
                "@type",
                "attr3"
              ]
            }
          },
          "required": [
            "properties"
          ]
        }
      ]
    },
    
    "FC2": {
      "$anchor": "FC2",
      "allOf": [
        {
          "$ref": "../FC21.json"
        },
        {
          "type": "object",
          "properties": {
            "geometry": {
              "$ref": "https://geojson.org/schema/Point.json"
            },
            "properties": {
              // 省略内容
            }
          },
          "required": [
            "properties"
          ]
        }
      ]
    }
  }
}

尝试的Python脚本 #

import json
from jsonschema2db import JSONSchemaToPostgres
import psycopg2

# 加载JSON Schema文件
schema = json.load(open('jsonschema.json'))
print(schema)

# 初始化Schema转PostgreSQL转换器
translator = JSONSchemaToPostgres(
    schema,
    postgres_schema='s1',  # PostgreSQL中的目标schema
    item_col_name='file_id',  # 用于关联数据的列名
    item_col_type='string',  # 关联列的数据类型
    abbreviations={
        'AbbreviateThisReallyLongColumn': 'AbbTRLC',  # 长列名缩写配置
    }
)

# 连接PostgreSQL数据库
con = psycopg2.connect('host=myhostname dbname=db user=psqluser password=psqlpw')
# 执行数据表创建操作
translator.create_tables(con)

# 提交数据库更改
con.commit()

关键解决要点 #

• 处理外部Schema引用：你的Schema包含本地文件引用（../Th1/th1s.json、../FC21.json）和远程GeoJSON引用，jsonschema2db无法自动解析这些外部依赖，需要将外部Schema内容合并到主Schema中，或者确保转换器能访问到对应文件路径。
• 补充缺失定义：Schema中#/$defs/Attr3Value未给出具体结构，必须补充完整定义，否则转换器无法识别对应数据类型。
• 空间类型映射：PostgreSQL支持空间数据类型（如POINT），需自定义类型映射规则，将GeoJSON的Point类型转换为PostgreSQL的geometry(Point, 4326)这类空间类型。
• 调试SQL语句：添加print(translator.get_create_table_statements())查看生成的SQL，验证是否符合预期结构。
• 权限验证：确认数据库用户psqluser拥有在s1 schema下创建表的权限。

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