How to display data from a postgresql database in a html table that uses flask as backend

Question

• Connected the postgresql database to flask using psycopg adapter.
• When using the jinja for loop template to fetch data the data is not displayed.
• This is my app.py code

Here is my app.py code

@app.route("/schemes")
def schemes():
    return render_template("schemes.html")

conn = psycopg2.connect(database="schemes", 
                        user="sukku",
                        password="schemes", 
                        host="localhost", port="5432")

cur = conn.cursor()

cur.execute('''SELECT * FROM schemes_data''')

data = cur.fetchall()

conn.commit()

cur.close()
conn.close()

here is my schemes.html

    <table class="fl-table">
        <thead>
        <tr>
            <th>Year</th>
            <th>No.of projects sanctioned</th>
            <th>Cost of Project.( Rs in lakhs)</th>
            <th>TIFS Share sanctioned.( Rs in lakhs)</th>
        </tr>
        </thead>
        <tbody>
        <tr>
            <td>2008-09</td>
            <td>12</td>
            <td>8441.10</td>
            <td>747.86</td>
        </tr>
        <tr>
            <td>2009-10</td>
            <td>22</td>
            <td>28686.27</td>
            <td>26339.86</td>
            
        </tr>
        <tr>
            <td>2010-11</td>
            <td>30</td>
            <td>47484.60</td>
            <td>10284.10</td>
            
        </tr>
        <tr>
            <td>2011-12</td>
            <td>09</td>
            <td>86491.97</td>
            <td>6810.00</td>
            
        </tr>
        <tbody>
    </table>

Since the for loop did not work I have loaded the table data as static. I want to load the data from postgresql instead.Please tell me how to do it.

Answer 1

   Flask

    @app.route("/schemes")
     def schemes():
        cur = conn.cursor()
        cur.execute('''SELECT * FROM schemes_data''')
        data = cur.fetchall()
        conn.commit()
        cur.close()
        conn.close()
        return render_template('schemes.html',data1=data)
 
   schemes.html

                 {% for item in data1 %}
                  <tbody>
                     <tr>
                         <td>{{item[0]}}</td>
                         <td>{{item[1]}}</td>
                         <td>{{item[2]}}</td>
                         <td>{{item[3]}}</td>
                         <td>{{item[4]}}</td>
                     </tr>
                  </tbody>
                  {% endfor %}`

Do check whether the dB is connected properly .and apply the above code as per you need