The following Python code demonstrates how to generate HTML report based on PostgreSQL queries.
It can be adjusted to any number of queries and customized with fonts, colors and how data is placed on the HTML page.
import psycopg2
import pandas as pd
import plotly.graph_objects as go
from IPython.display import HTML
db_name="postgres"
db_user="postgres"
db_port="5432"
db_host="localhost"
db_pwd="123456"
pd.set_option('colheader_justify', 'center')
html_string = '''
<html>
<head>
<title>My Report</title>
<style>
.mystyle {{
font-size: 10pt;
font-family: Verdana;
border-collapse: collapse;
border: 1px solid silver;
}}
.mystyle td, th {{
padding: 5px;
}}
.mystyle tr:nth-child(even) {{
background: #E0E0E0;
}}
.mystyle tr:hover {{
background: silver;
cursor: pointer;
}}
</style>
</head>
<body>
<h4>Output #1</h4>
{table_1}
<h4>Output #2</h4>
{table_2}
<h4>Output #3</h4>
{table_3}
</body>
</html>
'''
sql_1="""
SELECT datname "DB Name", clock_timestamp()-query_start "RunTime", query "The Query"
from pg_stat_activity
order by 2
limit 25;
"""
sql_2="""
SELECT datname "DB_Name"
from pg_database
order by 1
limit 25;
"""
sql_3="""
select state "State", count(1) "Number of Connections"
from pg_stat_activity
group by 1
order by 2 desc;
"""
con=psycopg2.connect(database=db_name, user=db_user, password=db_pwd, host=db_host, port=db_port)
df_1 = pd.read_sql_query(sql_1,con)
df_2 = pd.read_sql_query(sql_2,con)
df_3 = pd.read_sql_query(sql_3,con)
with open('myhtml2.html', 'w') as f:
f.write(html_string.format(table_1=df_1.to_html(classes='mystyle'), table_2=df_2.to_html(classes='mystyle'), table_3=df_3.to_html(classes='mystyle')))
f.close()
con.close()
Top comments (0)