[FIXED] How to grab and plot data from a PostgreSQL database via psycop2g library, FLASK and AJAX?

Issue

Hello dear Stackoverflow community, I have created a href link (in my html file) with the name EPEX and one with the name Show partner. Clicking on EPEX should grab of a ploted image, which are building the x,y values of my graph (see first pic upload). The x axis should represent the timeline and the y axis is representing the price for electricity per 1 Megawatt.

This script grabs files out of my postgresql database (with psycop2g) which saves at the end a new_plot.png in my static folder. Here the first image link: (https://ibb.co/bN29Bv1)

import psycopg2
#import numpy as np
import matplotlib.pyplot as plt

con = None
con = psycopg2.connect("dbname='Demonstratoren Statische Daten' user='postgres' password='NRL-HAW-2022' host='localhost'")
cur = con.cursor()
alles = ' SELECT * from "EPEX_2022" WHERE "id"=1 '

cur.execute(alles) 
rows = cur.fetchall()

for y in rows:
    #print("Id = ", y[0], )
    #print("Handelstag = ", y[1])
    #print("Durchschnittliche MWh Preise  = ", "\n", y[2:26] )
    #print(type(y[2:26]))
    print("Strompreise erfolreich abgerufen und abgespeichert!")
y_val = y[2:26]
#print ("zuletzt:", y_val)


# erhalte y[3:26] tuple, welches die stündlichen Strompreise pro MWh (als float numbers in postgresql hinterlegt)
# cursor abbruch, neue Abfrage der column namen aus postgresql, die unsere Abzisse darstellen mit x_val
con = None
con = psycopg2.connect("dbname='Demonstratoren Statische Daten' user='postgres' password='NRL-HAW-2022' host='localhost'")
con.autocommit = True
    
with con:
    with con.cursor() as cursor:
        cursor.execute(
            "select COLUMN_NAME from information_schema.columns\
            where table_name='EPEX_2022'")
        x0 = [row[0] for row in cursor] 
        x1= x0[2:]
        # Using list comprehension + list slicing method, um Zeitraum aus der x1 Liste zu löschen
        x_val = [sub[9 : ] for sub in x1]
        #print (x_val)
        
# giving a title to my graph
plt.title('EPEX-SPOTPRICE-DE - ' + str(y[1]))
plt.xlabel('Zeitraum (h)')
plt.ylabel('Durchschnittlicher Strompreis pro MWh (in €)')
plt.xticks(rotation=45, fontsize=3 )
plt.grid(True) 
plt.plot(x_val,y_val)
plt.plot(x_val,y_val,'or')
plt.savefig("EPEX-SPOTPRICE-DE - on "+ str(y[1]), dpi=300)
plt.show()

So my first step is to render my page with this python script:

import psycopg2
import psycopg2.extras
from flask import (Flask,
    Blueprint,
    render_template
)
from flask_login import login_required, current_user
app = Flask(__name__)

import pandas as pd
import matplotlib.pyplot as plt
import os


server = Blueprint('server', __name__)
connectionString = "dbname='Demonstratoren Statische Daten' user='postgres' password='NRL-HAW-2022' host='localhost'"
conn = psycopg2.connect(connectionString)
try:
    conn = psycopg2.connect(connectionString)
 
    if conn is not None:
        print('Connection established to PostgreSQL.')
    else:
        print('Connection not established to PostgreSQL.')
         
except (Exception, psycopg2.DatabaseError) as error:
    print(error)


        
@server.route('/postgresql')
@login_required
def postgresql():
    return render_template('db.html', user=current_user)

        
@server.route('/postgresql/')
@login_required
def post():
  """rows returned from postgres are just an ordered list"""

  with conn:
      cur = conn.cursor()
      try:
          alles = ' Select "AG/TV", "Partner" from "Elektrolyseure" '
          cur.execute(alles)
      except:
              print("Error executing select")
  results = cur.fetchall()
  return render_template('db.html', Elektrolyseure=results,user=current_user)



    
@server.route('/postgresql/epex')
@login_required
def epex():
    return render_template('db.html', name = 'EPEX-SPOTPRICE-DE- 20 June 2022', url ='/static/Bilder/new_plot.png', user=current_user)

Here is my .html code:

{% extends "base.html" %}

{% block content %}


  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
     
    <!-- Postgresqlabfragen -->
  </head>
  <body>
<div class="container-fluid">
  <div class="row">
    <nav id="sidebarMenu" class="col-md-3 col-lg-2 d-md-block bg-light sidebar collapse">
      <div class="position-sticky pt-3">
        <ul class="nav flex-column">
          
          <li class="nav-item">
            <a class="nav-link active" aria-current="page" href="{{url_for('server.epex')}}">
              <span data-feather="file"></span>
              EPEX
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link active" aria-current="page" href="{{url_for('server.post')}}">
              <span data-feather="file"></span>
              Show Partner
            </a>
          </li>
          
        </ul>
      </div>
    </nav>

    <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">
      <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
        <h1 class="h2">EPEX Strompreise</h1>
        <div class="btn-toolbar mb-2 mb-md-0">
          <div class="btn-group me-2">
          </div>
          
  
  <div class="dropdown">
    <button type="button" class="btn btn-primary dropdown-toggle" data-bs-toggle="dropdown">
      Aktueller Stand
    </button>
    <ul class="dropdown-menu">
      <li><a class="dropdown-item" href="#">Letzte Woche</a></li>
      <li><a class="dropdown-item" href="#">Maximum</a></li>
    </ul>
  </div>
  
  
  
  
        </div>
      </div>
      
      
   

      <img src={{url}} width="900" height="600" >
      <br>
      <br>
      <h1>Elektrolyseur Tabelle</h1>

      <table>
      <tr><th>AG/TV</th><th>Partner</th></tr>
      {% for partner in Elektrolyseure %}
      <tr><td>{{partner[0]}}</td><td>{{partner[1]}}</td></tr>
      {% endfor %}
      </table>
      <br>
      <br>
      <head>
    <!--  </script><script src="{{ url_for('static', filename='EPEX_dashboard.js') }}"></script> -->
  </body>

{% endblock %}

My problem is that I render a page with everything like here seen in this picture (https://ibb.co/0Vqh7fL). I uploaded all 3 routes as pictures, to explain how they should appear:

1. Entering /postgresql route (https://ibb.co/qjY6RSq), let me use ajax please centered in the right column.
2. Clicking on EPEX should open /postgresql/epex route and display only my plot as a picture (https://ibb.co/kM5BJRj). The rest like "let me use ajax please" and "Elektrolyseur Tabelle" is vanished.
3. By Clicking partners, u are on the link /postgresql/partners and all red should be crossed out, like here (https://ibb.co/BK4T5tZ).

Can someone help me with it? Thanks again.

Solution

Ok, maybe my question was too hard to understand, but I wanted to post finally a solution, which answers my question using AJAX.

import psycopg2
#import numpy as np
import matplotlib.pyplot as plt



def generate_epex_plot(id: str):
    print("generate_epex_plot called, id=", id)
    con = None
    con = psycopg2.connect("dbname='Demonstratoren Statische Daten' user='postgres' password='NRL-HAW-2022' host='localhost'")
    cur = con.cursor()
    query =  f' SELECT * from "EPEX_2022" WHERE "id"={id}'
    if id =="0":
        query = ' SELECT * from "EPEX_2022" ORDER by id DESC LIMIT 1'
    #print("query:", query)
    cur.execute(query) 
    rows = cur.fetchall()
    #print(rows)
    #y_val = None
    y = rows[0]
    #print("Id = ", y[0], )
    #print("Handelstag = ", y[1])
    #print("Durchschnittliche MWh Preise  = ", "\n", y[2:26] )
    print(type(y[2:26]))
    print(y)
    print("Strompreise erfolgreich abgerufen und abgespeichert!")
    y_val = y[2:26]
    #print ("zuletzt:", y_val)
    
    
    # erhalte y[3:26] tuple, welches die stündlichen Strompreise pro MWh (als float numbers in postgresql hinterlegt)
    # cursor abbruch, neue Abfrage der column namen aus postgresql, die unsere Abzisse darstellen mit x_val
    con = None
    con = psycopg2.connect("dbname='Demonstratoren Statische Daten' user='postgres' password='NRL-HAW-2022' host='localhost'")
    con.autocommit = True
        
    with con:
        with con.cursor() as cursor:
            cursor.execute(
                "select COLUMN_NAME from information_schema.columns\
                where table_name='EPEX_2022'")
            x0 = [row[0] for row in cursor] 
            print( "x0", x0)
            x1= x0[2:26]
            print( "x1", x1)
            # Using list comprehension + list slicing method, um Zeitraum aus der x1 Liste zu löschen
            x_val = [sub[9 : ] for sub in x1]
            print ( "xval", x_val)
            
    # giving a title to my graph
    plt.title('EPEX-SPOTPRICE-DE - ' + str(y[1]))
    plt.xlabel('Zeitraum (h)')
    plt.ylabel('Durchschnittlicher Strompreis pro MWh (in €)')
    plt.xticks(rotation=45, fontsize=3 )
    plt.grid(True) 
    plt.plot(x_val,y_val)
    plt.plot(x_val,y_val,'or')
    #plt.savefig("static/Bilder/new_plot.png", dpi = 150) 
    plt.savefig("website/static/Bilder/new_plot.png", dpi = 150)
    plt.close()
    
    #plt.savefig("EPEX-SPOTPRICE-DE - on "+ str(y[1]), dpi=300)
    #plt.show()

if __name__ == "__main__":
    generate_epex_plot("0")
    print("Plot created!")

And for this i had to change my html code and use javascript.

{% extends "base.html" %}

{% block content %}


  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
     
    <!-- Postgresqlabfragen -->
  </head>
  <body>
<div class="container-fluid">
  <div class="row">
    <nav id="sidebarMenu" class="col-md-3 col-lg-2 d-md-block bg-light sidebar collapse">
      <div class="position-sticky pt-3">
        <ul class="nav flex-column">
        <ul>
          <li> <strong>Verfügbare Abfragen:</strong> </li>
          </ul>
          <li class="nav-item">
            <a class="nav-link active" aria-current="page" href="{{url_for('server.epex')}}">
              EPEX
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link active" aria-current="page" href="{{url_for('server.post')}}">
              Show Partner
            </a>
          </li>
          
        </ul>
      </div>
    </nav>

    <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">
      <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
        <h1 class="h2">EPEX Strompreise</h1>
        <div class="btn-toolbar mb-2 mb-md-0">
          <div class="btn-group me-2">
          </div>
          
 
  <div class="dropdown">
    <button type="button" class="btn btn-primary dropdown-toggle" data-bs-toggle="dropdown">
      Aktueller Stand
    </button>
    <ul class="dropdown-menu">
      <li><a class="dropdown-item" onclick="loadNewGraph(1)">Aktueller Stand</a></li>
      <li><a class="dropdown-item" onclick="loadNewGraph(1)">Letzte Woche</a></li>
      <li><a class="dropdown-item" onclick="loadNewGraph(2)">Maximum</a></li>
    </ul>
  </div>
  
        </div>
      </div>

      <img id="graph" src="" width="900" height="600" >
      
    <!-- <script src="{{ url_for('static', filename='EPEX_dashboard.js') }}"></script>-->
    <script>
    function loadNewGraph(id){
    
    let req_url = '/postgresql/epex/get_graph/'
    //if (window.location.href.includes("partner")){
    //    req_url = '/postgresql/partner/get_graph/'
    //}
    
    
    // ajax call to url get_graph, cf.: https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest/Using_XMLHttpRequest
    const xhttp = new XMLHttpRequest();
    xhttp.open("GET", req_url + id, true);
    xhttp.send();
    
    xhttp.onload = function(e){
        imgurl = document.getElementById('graph')
        imgurl.src = '/static/Bilder/new_plot.png' + "?ts=" + Date.now()
        
    }

    
    }
    loadNewGraph(0)
    </script>
  </body>

{% endblock %}

Anyways. Thank you Sergey, you showed me also me some trick :-).

Answered By - Olej

Answer Checked By - Clifford M. (PHPFixing Volunteer)