How to retreive records from the table based on input 'id' using Python?

Question

I have table1 which is like ,

project_id |                 metric                 | design_target | median_property | design_project 
------------+----------------------------------------+---------------+-----------------+----------------
 300        | ENERGY STAR score (1-100)              | Not Available | 50              | Not Available
 300        | Source EUI (kBtu/ft\u00b2)             | 35.4          | 141.4           | Not Available
 300        | Site EUI (kBtu/ft\u00b2)               | 15.8          | 63.1            | Not Available
 300        | Source Energy Use (kBtu)               | 3,536.0       | 14,144.1        | Not Available
 300        | Site Energy Use (kBtu)                 | 1,578.7       | 6,314.9         | Not Available
 300        | Energy Cost ($)                        | 34.61         | 138.44          | Not Available
 300        | Total GHG Emissions (Metric Tons CO2e) | 0.2           | 0.6             | 0.0
 400        | ENERGY STAR score (1-100)              | Not Available | 50              | Not Available
 400        | Source EUI (kBtu/ft\u00b2)             | 35.4          | 141.4           | Not Available
 400        | Site EUI (kBtu/ft\u00b2)               | 15.8          | 63.1            | Not Available
 400        | Source Energy Use (kBtu)               | 3,536.0       | 14,144.1        | Not Available
 400        | Site Energy Use (kBtu)                 | 1,578.7       | 6,314.9         | Not Available
 400        | Energy Cost ($)                        | 34.61         | 138.44          | Not Available
 400        | Total GHG Emissions (Metric Tons CO2e) | 0.2           | 0.6             | 0.0

I have table2 which is like,

 project_id |                                                       energy_type                                                       | uses |              target              |            title             
------------+-------------------------------------------------------------------------------------------------------------------------+------+----------------------------------+------------------------------
 300        | {"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"} |      |  Target % Better than Median: 75 | About this Property's Design
 400        | {"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"} |      |  Target % Better than Median: 75 | About this Property's Design

I have table3 which is like,

 project_id | your_design_score 
------------+-------------------
 300        | N/A
 400        | N/A

I am joining table based project_id using python,

import psycopg2

con = psycopg2.connect(dbname="db",
      user="postgres", host="localhost",
      password="")
d1 = "select t1.project_id,t1.Metric, t1.Design_Target, t1.Median_Property, t1.Design_Project, t2.Energy_Type, t2.Uses, t2.Target, t2.Title, t3.your_design_score from metric_comparison t1, property_design t2, design_score t3 where t1.project_id = t2.project_id and t2.project_id = t3.project_id"
cursor.execute(d1)
d2 = cursor.fetchall()
print d2
con.commit()

I have got the output like ,

[('300', 'ENERGY STAR score (1-100)', 'Not Available', '50', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('300', 'Source EUI (kBtu/ft\\u00b2)', '35.4', '141.4', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('300', 'Site EUI (kBtu/ft\\u00b2)', '15.8', '63.1', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('300', 'Source Energy Use (kBtu)', '3,536.0', '14,144.1', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('300', 'Site Energy Use (kBtu)', '1,578.7', '6,314.9', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('300', 'Energy Cost ($)', '34.61', '138.44', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('300', 'Total GHG Emissions (Metric Tons CO2e)', '0.2', '0.6', '0.0', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'ENERGY STAR score (1-100)', 'Not Available', '50', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'Source EUI (kBtu/ft\\u00b2)', '35.4', '141.4', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'Site EUI (kBtu/ft\\u00b2)', '15.8', '63.1', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'Source Energy Use (kBtu)', '3,536.0', '14,144.1', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'Site Energy Use (kBtu)', '1,578.7', '6,314.9', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'Energy Cost ($)', '34.61', '138.44', 'Not Available', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A'), ('400', 'Total GHG Emissions (Metric Tons CO2e)', '0.2', '0.6', '0.0', '{"Energy Not Entered","Assumed Mix Based on State & Property Type:","","Electric - Grid (56.9%)","Natural Gas (43.1%)"}', None, ' Target % Better than Median: 75', "About this Property's Design", 'N/A')]

Now am trying to display the records based on project_id input,

PROJECT_ID = '300' #declared in python code Based on the input of PROJECT_ID, i have to display the records from the above output.

I have to display the records where the project_id is matched to PROJECT_ID.

Answer 1

Filter at the SQL side passing the paremeter in the execute method:

d1 = """
    select
        project_id,
        t1.Metric,
        t1.Design_Target,
        t1.Median_Property,
        t1.Design_Project,
        t2.Energy_Type,
        t2.Uses, t2.Target,
        t2.Title,
        t3.your_design_score
    from
        metric_comparison t1
        inner join
        property_design t2 using (project_id)
        inner join
        design_score t3 using (project_id)
    where project_id = %s
"""
project_id = '300'
cursor.execute(d1, [project_id])