python - 如何使用 Python 将变量中的列值插入到 Postgres 中？

Question

我有一个json数据要插入到表中，这是 JSON 数据，

data1 = [
    {
        'Design Project': 'Not Available',
        'Design Target*': 'Not Available',
        'Median Property*': '50',
        'Metric': 'ENERGY STAR score (1-100)'
    },
    {   
        'Design Project': 'Not Available',
        'Design Target*': '35.4',
        'Median Property*': '141.4',
        'Metric': 'Source EUI (kBtu/ft\\u00b2)'
    },
    {   
        'Design Project': 'Not Available',
        'Design Target*': '15.8',
        'Median Property*': '63.1',
        'Metric': 'Site EUI (kBtu/ft\\u00b2)'
    },
    {   
        'Design Project': 'Not Available',
        'Design Target*': '3,536.0',
        'Median Property*': '14,144.1',
        'Metric': 'Source Energy Use (kBtu)'
    },
    {   
        'Design Project': 'Not Available',
        'Design Target*': '1,578.7',
        'Median Property*': '6,314.9',
        'Metric': 'Site Energy Use (kBtu)'
    },
    {   
        'Design Project': 'Not Available',
        'Design Target*': '34.61',
        'Median Property*': '138.44',
        'Metric': 'Energy Cost ($)'
    },
    {   
        'Design Project': '0.0',
        'Design Target*': '0.2',
        'Median Property*': '0.6',
        'Metric': 'Total GHG Emissions (Metric Tons CO2e)'
    }
]

我有表结构，

project_id | metric | design_target | median_property | design_project 
-----------+--------+---------------+-----------------+---------------

我有一个 project_id 变量，例如，

项目 ID = 400

我尝试将数据插入到 Postgres 中的表中的查询，

cur.executemany(
    """INSERT INTO Metric_Comparison(Project_id, Metric, Design_Target, Median_Property, Design_Project) 
       VALUES (%(PROJECT_ID)s, %(Metric)s, %(Design Target*)s, %(Median Property*)s, %(Design Project)s)""",
    data1
)

如何在插入表时附加项目 ID？

Answer 1

如何通过查询的字符串格式设置项目 ID？

PROJECT_ID = 400

cur.executemany("""
    INSERT INTO Metric_Comparison 
      (Project_id,Metric,Design_Target,Median_Property,Design_Project) 
      VALUES ({}, %(Metric)s, %(Design Target*)s, %(Median Property*)s, %(Design Project)s)
    """.format(PROJECT_ID), data1)