flask - Display database from python to html

python html web flask server

61,304

Solution 1

You can pass your data using render_template() like this:

cur = con.cursor()
cur.execute("SELECT * FROM dataset")
data = cur.fetchall()
render_template('template.html', data=data)

Then in your template, iterate over the rows, for example you could render table rows for each row:

{% for item in data %}
<tr>
    <td>{{item[0]}}</td>
    <td>{{item[1]}}</td>
    ...
</tr>
{% endfor %}

Solution 2

render_template allows you to pass variables to html, and jinja2 help you to manipulate it. You only need to format your query result and send it within render_template

Example

app.py

@app.route('/test')
def test_route():
    user_details = {
        'name': 'John',
        'email': '[email protected]'
    }

    return render_template('test.html', user=user_details)

test.html

<!DOCTYPE html>
<html>
    <head>
        <title>test</title>
    </head>
    <body>
        <!-- use {{}} to access the render_template vars-->
        <p>{{user.name}}</p>
        <p>{{user.email}}</p>
    </body>
</html>

to make the most of jinja2, take a look at his Documentation

Solution 3

Suppose you have table_name = user_info and let's visualize it:

You can do something like this:

app_name.py

from flask import Flask, render_template
import mysql.connector

mydatabase = mysql.connector.connect(
    host = 'localhost(or any other host)', user = 'name_of_user',
    passwd = 'db_password', database = 'database_name')


mycursor = mydatabase.cursor()

#There you can add home page and others. It is completely depends on you

@app.route('/example.html')
def example():
   mycursor.execute('SELECT * FROM user_info')
   data = mycursor.fetchall()
   return render_template('example.html', output_data = data)

In the above code we use fetchall() method that's why ID is also included automatically

(Header html tag and others are ignored. I write only inside of body) example.html

--snip--

<table>
    <thead>
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>Phone</th>
    </tr>
    </thead>
    <tbody>
        {% for row in output_data %} <-- Using these '{%' and '%}' we can write our python code -->
            <tr>
                <td>{{row[0]}}</td>
                <td>{{row[1]}}</td>
                <td>{{row[2]}}</td>
                <td>{{row[3]}}</td>
            </tr>
        {% endfor %}        <-- Because it is flask framework there would be other keywords like 'endfor'   -->
    </tbody>
</table>

--snip--

And finally you get expected result

61,304

Author by

David Herlianto

Updated on July 05, 2022

Comments

David Herlianto almost 2 years

I have code like this to retrieve data from database and I want to display it in html.

This is app.py

@app.route('/news')
def news():
    import pymysql
    import re

    host='localhost'
    user = 'root'
    password = ''
    db = 'skripsi'

    try:
        con = pymysql.connect(host=host,user=user,password=password,db=db, use_unicode=True, charset='utf8')
        print('+=========================+')
        print('|  CONNECTED TO DATABASE  |')
        print('+=========================+')
     except Exception as e:
        sys.exit('error',e)

     cur = con.cursor()
     cur.execute("SELECT * FROM dataset")
     data = cur.fetchall()

     for row in data:
         id_berita = row[0]
         judul = row[1]
         isi = row[2]
         print('===============================================')
         print('BERITA KE', id_berita)
         print('Judul :', judul)
         print('Isi   :', isi)
         print('===============================================')

return render_template('home.html')

This is the result

This is berita.html. I want to display inside div class = output

<body>
<center>
<header style="padding-top: 10px; font-family: Calibri; font-size: 40pt;">WELCOME!</header><br>
<div class="nav">
  <a href="/home">Home
  <a href="/berita">Berita
  <a href="/preprocessing">Pre-Processing
  <a href="/feature">Fitur Ekstraksi
  <a href="/knn">KNN
</div>
<div class="output">
</div>

David Herlianto over 6 years

But I'll load 1000 datas from database.
DobleL over 6 years

then you need to use some Pagination technique, that's a design issue rather than tech used
bgse over 6 years

@DavidHerlianto For pagination, you can try and substitute cur.fetchall() with cur.fetchmany(20) in your code to test what your first page would look like when restricting to 20 items per page. Showing the following pages is then just a matter of passing a parameter for selected page with your request, and scrolling the cursor accordingly.
culter almost 6 years

Hi David, could you provide complete code where you pull the data from DB and especially how to push them to html, please?