How to insert a column value from a variable into Postgres using Python?

Question

I have a json data to be inserted into the table, Here is the JSON data,

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)'
    }
]

I have the Table structure,

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

I have a project_id variable like,

PROJECT_ID = 400

My query to insert the data into the table in Postgres which I tried like,

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
)

How can I append the project id while inserting into the table?

may be you can use bind variables in this case

pankaj mishra
– pankaj mishra

2017-12-01 12:21:23 +00:00
Commented Dec 1, 2017 at 12:21 — pankaj mishra
– pankaj mishra, Commented Dec 1, 2017 at 12:21

SchEma · Accepted Answer · 2017-12-01 12:23:43Z

1

What about incting the project id by string formatting of the query?

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)

answered Dec 1, 2017 at 12:23

SchEma

941 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcin Pietraszek · Accepted Answer · 2017-12-01 12:19:48Z

0

You could create new dict and add your project id to it:

enriched_data = {}
project_id = 100
for key, value in data1:
    enriched_data[key] = dict(value)
    enriched_data[key]["PROJECT_ID"] = project_id
sql_query = "..."
cur.executemany(sql_query, enriched_data)

answered Dec 1, 2017 at 12:19

Marcin Pietraszek

3,2221 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to insert a column value from a variable into Postgres using Python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related