时间戳

首页 > 解决方案 > Flask+SQLAlchemy：如何在一对多数据库中显示最新条目

python - Flask+SQLAlchemy：如何在一对多数据库中显示最新条目

问题描述

抱歉，我无法想出一个明确的标题，我不确定我想要完成的名称是什么。此外，英语不是我的第一语言，所以请多多包涵。

我已经建立了一个包含两个表“Sensor”和“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 的烧瓶路线：

@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 循环当前所在的 Sensor 的 id）对 SensorValues 表进行过滤查询，逻辑上我认为这可行，但语法不正确。我得到了 Jinja 错误：

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

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

标签： pythonflasksqlalchemyjinja2

解决方案

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

首先用 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'))

从上述查询返回的 Result 行是 的实例，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 %}

推荐阅读