How to correctly compose a join request in a Flask application?

Question

Flask

Models:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    username = db.Column(db.String(120), unique=True)
    password = db.Column(db.String(120))

    def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = password

class Weather(db.Model):
    __tablename__ = 'weather'
    id = db.Column(db.Integer, primary_key=True)
    ray_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    ray = db.relationship('User', backref=db.backref('weather_current', uselist=False))   
    putdate = db.Column(db.DateTime)
    sost = db.Column(db.String(250))
    temp = db.Column(db.String(150))

    def __init__(self, ray_id, putdate, sost, temp):
        self.ray_id = ray_id
        self.putdate = putdate
        self.sost = sost
        self.temp = temp

View:

@mod.route('/', methods = ['GET', 'POST'])
def weather():
    rows = Weather.query.outerjoin(User, Weather.ray_id==User.id).order_by(User.name.asc())
    return render_template("weather/weather.html",
        rows = rows)

Sample:

{% for row in rows %}
  <tr>
  <td align=left>{{ row.name }}</td>
  <td>{{ row.putdate }}</td>
  <td>{{ row.sost }}</td>
  <td>{{ row.temp }}</td>
  </tr>
 {% endfor %}

Data is displayed only on the Weather model, how to display user data, for example, {{ row.username }}

The request in views corresponds to the sql query

SELECT * FROM weather LEFT JOIN users ON weather.id_ray = users.id ORDER BY users.name asc"

Everything works great in PHP.

Answer 1

{{ row.ray.username }}
And instead
You can just write
Full example:

from flask import Flask, render_template
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tst.db'
db = SQLAlchemy(app)

import datetime

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    username = db.Column(db.String(120), unique=True)
    password = db.Column(db.String(120))

    def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = password

class Weather(db.Model):
    __tablename__ = 'weather'
    id = db.Column(db.Integer, primary_key=True)
    ray_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    ray = db.relationship('User', backref=db.backref('weather_current', uselist=False))   
    putdate = db.Column(db.DateTime)
    sost = db.Column(db.String(250))
    temp = db.Column(db.String(150))

    def __init__(self, ray_id, putdate, sost, temp):
        self.ray_id = ray_id
        self.putdate = putdate
        self.sost = sost
        self.temp = temp
db.drop_all()
db.create_all()

u = User("usertest2", 'testuser2', '123')

db.session.add(u)
db.session.commit()
db.session.add(Weather(u.id, datetime.datetime.now(), 'test', '123'))
db.session.add(Weather(None, datetime.datetime.now(), 'test', '123'))
db.session.commit()
        
@app.route('/', methods = ['GET', 'POST'])
def weather():
    rows = Weather.query.outerjoin(User).order_by(User.name.asc())
    return render_template("weather.html",
        rows = rows)
if __name__ == "__main__":
    app.run(debug=True)

Sample:

<table>
{% for row in rows %}
  <tr>
  <td align=left>{{ row.ray.name }}</td>
  <td>{{ row.putdate }}</td>
  <td>{{ row.sost }}</td>
  <td>{{ row.temp }}</td>
  <td>{{ row.ray.username }}</td>
  </tr>
 {% endfor %}
</table>

Answer 2

I would suggest paying attention to add_entity, but I'm not sure how correct this solution is, because I myself only understand the flask.