Skip to main content
 首页 » 编程设计

python之flask +SQLAlchemy : How to show the latest entry in 1-to-many database

2024年11月01日8grandyang

抱歉,我想不出一个明确的标题,我不太确定我想要完成的是什么。另外,英语不是我的第一语言,所以请多多包涵。

我已经建立了一个包含两个表“Sensors”和“SensorValues”的数据库。 应该存在一对多关系,这样一个传感器可以有多个读数,但读数只能属于一个传感器。

我正在使用 Flask 和 flask-sqlalchemy。

数据库模型:

class Sensor(db.Model): 
    id = db.Column(db.Integer, primary_key=True) 
    name  = db.Column(db.String(64), unique=True, nullable=False,) 
    gpiopin  = db.Column(db.INT, unique=False, nullable=False,) 
    stype  = db.Column(db.String(20), unique=False, nullable=False,) 
    values = db.relationship('SensorValues', backref='author', lazy=True) #not a column in table, just backreference 
    def __repr__(self): 
        return f"Sensor('{self.name}', '{self.gpiopin}', '{self.stype}')" 
 
class SensorValues(db.Model): 
    id = db.Column(db.Integer, primary_key=True) 
    reading = db.Column(db.String(64), unique=False) 
    timestamp = db.Column(db.DateTime, unique=False, nullable=False, default=datetime.utcnow) 
    sensor_id = db.Column(db.Integer, db.ForeignKey('sensor.id'), nullable=False) 
    def __repr__(self): 
        return f"Value('{self.reading}', '{self.timestamp}')" 

这是 home.html 的 flask 路径:

@app.route("/", methods=['POST', 'GET']) 
def home(): 
  sensors = Sensor.query.all() 
  posts = SensorValues.query.all() 
  return render_template('home.html', posts=posts, sensors=sensors, db=db) 

我如何在 home.html 中生成表格

  <tbody> 
            {% for sensor in sensors %} 
                <tr> 
                <td>{{ sensor.name }}</td> 
                <td>{{ sensor.gpiopin }}</td> 
                <td>{{ sensor.stype }}</td> 
                <td>{{ sensor.values.reading }}</td> <- Where I want the latest reading of the specific sensor in the SensorValues to appear 
                </tr> 
            {% endfor %} 
        </tbody> 

现在,我有另一个表格,其中列出了所有读数及其所属传感器的名称。 它基本上是一个历史记录,并且以这种方式工作,因为我直接循环 SensorValues 表。

  {% for post in posts %} 
                <tr> 
                <td>{{ post.author.name }}</td> 
                <td>{{ post.author.stype }}</td> 
                <td>{{ post.reading }}</td> 
                <td>{{ post.timestamp }}</td> 
                </tr> 
  {% endfor %} 

我试图将第一个循环更改为:

{% for sensor in sensors %} 
                <tr> 
                <td>{{ sensor.name }}</td> 
                <td>{{ sensor.gpiopin }}</td> 
                <td>{{ sensor.stype }}</td> 
                {{ lastvalue = SensorValues.filter_by(sensor_id=sensor.id).last()}} 
                <td>{{ lastvalue.reading }}</td> 
                </tr> 
{% endfor %} 

我尝试使用当前 sensor.id(for 循环当前所在的传感器的 ID)对 SensorValues 表进行筛选查询,逻辑上我认为这可行,但语法不正确。我收到 Jinja 错误:

expected token 'end of print statement', got '=' 
 

我什至可以在 home.html 中进行查询,还是必须在页面呈现之前在路由中进行查询?

请您参考如下方法:

View 函数中的查询需要更新以包含有关传感器最新读数的信息,然后再将其传递到模板上下文中。

首先用 SQL 编写查询,然后将其转换为 SQLAlchemy 变体。

查找最后一个传感器读数的 SQL 查询如下:

https://www.db-fiddle.com/f/5fXZyPiPYawc22ffMi2tYk/0

SELECT DISTINCT  
    s.*,  
    FIRST_VALUE(sv.reading) OVER ( 
  PARTITION BY sv.sensor_id ORDER BY sv.timestamp DESC 
) AS last_reading 
FROM sensors AS s 
JOIN sensor_values AS sv ON s.id = sv.sensor_id 

现在,让我们构建 SQLAlchemy 变体

import sqlalchemy as sa 
 
sensors = Sensor.query.join( 
              SensorValues).with_entities( 
                  Sensor, 
                      sa.func.first_value( 
                          SensorValues.reading).over( 
                              partition_by=SensorValues.sensor_id, 
                              order_by=SensorValues.timestamp.desc 
                          ).label('latest_reading')) 

上述查询返回的结果行是 KeyTuple 的实例latest_reading 标签应该作为传感器的直接属性在 Jinja2 模板中提供。

{% for sensor in sensors %} 
    <tr> 
        <td>{{ sensor.Sensor.name }}</td> 
        <td>{{ sensor.Sensor.gpiopin }}</td> 
        <td>{{ sensor.Sensor.stype }}</td> 
        <td>{{ sensor.latest_reading }}</td> 
    </tr> 
{% endfor %}