Construct a dynamic HTML content using Database values [duplicate]

Question

I want to create a dynamic HTML page with the values from the PostgresQL database applied in necessary position of the HTML template.

The below image is a part of HTML page, i want that areas to be filled from the values from the database,Is there a way to do that? or is there any other better way to do that.

Im using Python and PostgreSQL database.

Thanks in Advance

  import psycopg2
  import jinja2
  from collections import namedtuple
  TEMPLATE="""
     <!DOCTYPE html>
       <html><head><title>Jinja Template Example</title>
       <meta name="viewport" content="width=device-width, initial- 
  scale=1.0">
       <link href="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" 
   rel="stylesheet" media="screen">
       <style type="text/css">
       .container {max-width: 500px;padding-top: 100px;}</style></head>
       <body>
        <div class="container">
         <p>My string: {{my_string}}</p>
         <p>Value from the list:</p>
         <p>Loop through the list:</p>
         <p style="line-height: 14px; text-align: center; font-size: 12px; margin: 0;"> Database Value </p><br>
         <p style="line-height: 14px;font-weight:Bold; text-align: center; font-size: 12px; margin: 0;">Database value</p>

         <ul>
           {% for row in test_records %}
           <li>{{ test_records.column1 }}</li>
           {% endfor %}
         </ul>
        </div>
          <script src="http://code.jquery.com/jquery-1.10.2.min.js"> 
          </script>
          <script src="http://netdna.bootstrapcdn.com/bootstr

          ap/3.0.0/js/bootstrap.min.js"></script>
          </body>
      </html>
      """
  def dB_Fetch():
  try:
   connection = psycopg2.connect(user="sandeep",
                                  password="postgres",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres")
   cursor = connection.cursor()
   postgreSQL_select_Query = "select * from test"
   cursor.execute(postgreSQL_select_Query)
   print("Selecting rows from test table using cursor.fetchall")
   test_records = cursor.fetchall()
   a = list();
   print("Print each row and it's columns values")
   for row in test_records:
       a.append(test_records);
       #print("column1 = ", row[0] )
       #print("column2 = ", row[1])
       print(a[0])

   env = jinja2.Environment()
   template = env.from_string(TEMPLATE)
   row_tuple = namedtuple("Row", [col[0] for col in cursor.description])
   result = template.render(rows=[row_tuple(row) for row in cursor.fetchall()])
   print (TEMPLATE)
   print (result)  

 except (Exception, psycopg2.Error) as error:
     print ("Error while fetching data from PostgreSQL", error)
 finally:
     if(connection):
         cursor.close()
         connection.close()
         print("PostgreSQL connection is closed")
   if __name__ == '__main__':
    dB_Fetch()
    enter code here

I'm getting an error in this code can you please show me?

brunns · Accepted Answer · 2019-04-26 15:35:42Z

1

Best way to approach this would be to read the data using Psycopg, and then some templating engine - jinja2, for instance - to generate the HTML given data from the database.

Update:

A sketch of a solution might look like this (untested).

import psycopg2
import jinja2
from collections import namedtuple

TEMPLATE="""
<html><head></head><body>
<ul>
{% for row in rows %}
  <li>{{ row.column1 }}, {{ row.column2 }}</li>
{% endfor %}
</ul>
</body></html>
"""

env = jinja2.Environment()
template = env.from_string(TEMPLATE)

with psycopg2.connect("dbname=test user=postgres") as conn:
    with conn.cursor() as curs:
        curs.execute("SELECT column1, column2 FROM test;")
        row_tuple = namedtuple("Row", [col[0] for col in curs.description])
        print(template.render(rows=[row_tuple(row) for row in curs.fetchall()]))

edited Apr 26, 2019 at 15:35

answered Apr 17, 2019 at 12:48

brunns

2,7641 gold badge15 silver badges25 bronze badges

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

21 Comments

Hafsa Elif Özçiftci Over a year ago

I think you should show some example to beginners for good understanding. :)

Hafsa Elif Özçiftci Over a year ago

Yes that isn't before my comment.

Sandeep Over a year ago

@Hafza, Can you show me where i'm getting the error in the above code?

brunns Over a year ago

What is the error you are getting?

Sandeep Over a year ago

The values from the database is not rendered to the HTML.

|

Hafsa Elif Özçiftci · Accepted Answer · 2019-04-17 13:02:52Z

Code style must be like below. You can search DB application with jinja2. Only changes, you give your data from list after execution of database query.

It's a simple example for beginners:

    <!DOCTYPE html>
<html>
  <head>
    <title>Flask Template Example</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet" media="screen">
    <style type="text/css">
      .container {
        max-width: 500px;
        padding-top: 100px;
      }
    </style>
  </head>
  <body>
    <div class="container">
      <p>My string: {{my_string}}</p>
      <p>Value from the list: {{my_list[3]}}</p>
      <p>Loop through the list:</p>
      <ul>
        {% for n in my_list %}
        <li>{{n}}</li>
        {% endfor %}
      </ul>
    </div>
    <script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
    <script src="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/js/bootstrap.min.js"></script>
  </body>
</html>

Collectives™ on Stack Overflow

Construct a dynamic HTML content using Database values [duplicate]

2 Answers 2

21 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

21 Comments

Comments

Linked

Related