如何通过psycop2g库，烧瓶和ajax从PostgreSQL数据库中获取和绘制数据？
您好，亲爱的Stackoverflow社区，我创建了一个名称为Epex的HREF链接（在我的HTML文件中），一个名称为Show Cartern。单击EPEX应抓取构建图形的X，y值的绘制图像（请参阅第一个PIC上传）。 X轴应表示时间轴，Y轴代表每1兆瓦的电力价格。
该脚本从我的PostgreSQL数据库（带有PsyCop2G）中获取文件，该文件最终保存在我的静态文件夹中new_plot.png。这里第一个图像链接：（ 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()
所以我的第一步是呈现我的页面上的Python脚本：
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)
这是我的.html代码：
{% extends "base.html" %}
{% block content %}
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/[email protected]/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 %}
我的问题是我渲染一个页面，其中包含此图片中所示的所有内容（ https://ibb.co/0vqh7fl ）。 我将所有3条路线作为图片上传，以解释它们应该如何显示：
- 输入/postgresql路线（ https：// qjy6rsq ），让我使用Ajax请以右列中为中心。
- 单击EPEX应打开/postgresql/epex路由，并仅显示我的情节为图片（ https://ibb.co/km5bjrjrj> ）。其余的像“让我使用Ajax请”和“ Elektrolyseur Tabelle”消失了。
- 通过单击合作伙伴，U在链接/PostgreSQL/Partners上，所有红色都应被划出，例如此处（ htttps：// ibb.co/bk4t5tz ）。
有人可以帮我吗？再次感谢。
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/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/[email protected]/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:
- Entering /postgresql route (https://ibb.co/qjY6RSq), let me use ajax please centered in the right column.
- 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.
- 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将向您展示一个示例，其中每小时将显示“ ID完整（/MWH）”字段。我将制作一个像bar这样的图表。
输出：
或您可以获取表结果：
输出：
如果需要SQL，则可以轻松地将数据框架保存到SQL -
I will show you an example in which I will display the field 'ID Full(€/MWh)' for each hour. And I will make a chart like bar.
OUTPUT:
Or you can get table result:
OUTPUT:
If SQL is required, you can easy save dataframe to SQL - DataFrame.to_sql()
好的，也许我的问题很难理解，但是我想最终发布一个解决方案，该解决方案使用Ajax回答了我的问题。
为此，我必须更改我的HTML代码并使用JavaScript。
无论如何。谢谢谢尔盖，你也向我展示了一些技巧:-)。
Ok, maybe my question was too hard to understand, but I wanted to post finally a solution, which answers my question using AJAX.
And for this i had to change my html code and use javascript.
Anyways. Thank you Sergey, you showed me also me some trick :-).