How to display database content in your flask website
Flask,PostgreSQL
File structure
Hi,I have a table called v_timestamp1 made in PostgreSQL. Lets see how we can display it in the Flask UI. First I m establishing connection with pgadmin in the try statement. Put your database name instead of silverTip, that is name of my database also my user is postgres, my password is sT789. Replace those with your specific credentials.Here is my app.py code
from flask import Flask, request,render_template
import psycopg2
try:
conn = psycopg2.connect(database="silverTip", user="postgres",
password="sT789", host="localhost")
print("connected")
except:
print ("I am unable to connect to the database")
mycursor =conn.cursor()
app = Flask(__name__)@app.route('/')
def index():return render_template('index.html')@app.route('/v_timestamp')
def v_timestamp():
mycursor.execute("SELECT * FROM v_timestamp1")
data = mycursor.fetchall()
return render_template('v_timestamp.html', data=data)
If you guys havent downloaded psycopg2, please do so before running the code, in the terminal. Please make sure you have made a database made in the postgreSQL.
pip install psycopg2Code in index.html.In the body section I have just included a button, upon clicking that button lead to v_timestamp page.
<!DOCTYPE html>
<html>
<head>
<title></title>
<link rel="stylesheet" href="{{ url_for('static', filename='home.css') }}">
</head>
<body><h2>Vehicle info dashboard</h2><p align="center"><a href=v_timestamp ><button class=grey style="height:75px;width:150px">vmd timestamp</button></a></p></body>
</html>
The index.html has the code for arrangement of styles for the main page(app.py). How the front page looks like in port 5000.
The code in the v_timestamp.html is given below. I have given the text alignment as center, and padding and border as specified in the code.My table column headings are vehicle ID, current timestamp, expiry timestamp.As specified under <th> element, those fonts appear in bold. The <tr> elements font is normal(not bold) .In the <td> elements, the instruction {{row[0]}} means to capture the first row details in the v_timestamp1 PostgrSQL db and so on. Do not get confused, v_timestamp1 is the db at postgreSQL. v_timestamp is the page I’m routing the main page upon a button click event.
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<style>
td {
width: 150px;
text-align: center;
border: 1px solid black;
padding: 5px;
}
</style>
<table>
<thead>
<tr>
<th>vehicle ID</th>
<th>current timestamp</th>
<th>expiry timestamp</th>
</tr>
</thead>
<tbody>
{% for row in data %}
<tr>
<td>{{row[0]}}</td>
<td>{{row[1]}}</td>
<td>{{row[2]}}</td>
</tr>
{% endfor %}
</tbody>
</table></body>
</html>
How the UI looks for v_timestamp page.
Thanks for reading. Have fun coding!!!
