如何利用Pandas字典批量生成指定Schema的SQL建表语句？

你可以通过Python遍历字典中的Schema和表名，结合字符串格式化批量生成目标SQL语句，具体实现如下：

代码实现 #

# 定义你的Schema-表名字典
my_dict = {
    'ETL_A': ['TABLE_001', 'TABLE_002', 'TABLE_003'],
    'TEST': ['TABLE_TEST', 'TEST001', 'SAMPLE_TEST'],
    'MY_PRIVATE_SHEMA': ['WWW_001', 'LTT_222']
}

def generate_sql_ddl(schema_table_dict, target_timestamp):
    # 定义SQL模板，确保时间戳被单引号包裹
    sql_template = "CREATE TABLE {schema}.{table} SELECT * FROM {schema}.{table} at(timestamp => '{timestamp}'::timestamp);"
    sql_list = []
    # 遍历每个Schema及其下属表
    for schema_name, table_names in schema_table_dict.items():
        for table in table_names:
            # 填充模板变量
            sql_statement = sql_template.format(
                schema=schema_name,
                table=table,
                timestamp=target_timestamp
            )
            sql_list.append(sql_statement)
    return sql_list

# 示例：生成时间戳为'10-06-26 02:31:29'的所有SQL语句
target_ts = '10-06-26 02:31:29'
all_sql = generate_sql_ddl(my_dict, target_ts)

# 输出所有生成的SQL
for stmt in all_sql:
    print(stmt)

输出示例 #

运行上述代码后，会生成以下SQL语句：

CREATE TABLE ETL_A.TABLE_001 SELECT * FROM ETL_A.TABLE_001 at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE ETL_A.TABLE_002 SELECT * FROM ETL_A.TABLE_002 at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE ETL_A.TABLE_003 SELECT * FROM ETL_A.TABLE_003 at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE TEST.TABLE_TEST SELECT * FROM TEST.TABLE_TEST at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE TEST.TEST001 SELECT * FROM TEST.TEST001 at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE TEST.SAMPLE_TEST SELECT * FROM TEST.SAMPLE_TEST at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE MY_PRIVATE_SHEMA.WWW_001 SELECT * FROM MY_PRIVATE_SHEMA.WWW_001 at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE MY_PRIVATE_SHEMA.LTT_222 SELECT * FROM MY_PRIVATE_SHEMA.LTT_222 at(timestamp => '10-06-26 02:31:29'::timestamp);

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