mysql - 通过两个表连接查询 Flask-SQLAlchemy - 连接不起作用

Question

我正在尝试加入两个表。

这是我的简化表格

class Spots(db.Model):
    __tablename__ = "spots"

    geobaseid = db.Column((db.Integer), primary_key=True,autoincrement=True)
    spot = db.Column(db.String(50))
    url_slug = db.Column(db.String(50))
    region = db.Column(db.String(50))
    country = db.Column(db.String(50))
    chop = db.Column(db.Integer)
    small_wave = db.Column(db.Integer)
    flat = db.Column(db.Integer)
    big_wave = db.Column(db.Integer)
    west_direction = db.Column(db.Integer)
    southwest_direction = db.Column(db.Integer)
    amount = db.Column(db.Integer)
    url_slug = db.Column(db.String(50))

    forcast = db.relationship('Forcast_short')

class Forcast_short(db.Model):
    __tablename__ = "forcast_short"

    id = db.Column((db.Integer), primary_key=True,autoincrement=True)
    date = db.Column(db.Date)
    geobaseid = db.Column((db.Integer), ForeignKey('spots.geobaseid'))
    spot = db.Column(db.String(50))
    region = db.Column(db.String(50))
    country = db.Column(db.String(50))
    latitude = db.Column(db.Numeric(10,8))
    longitude = db.Column(db.Numeric(10,8))
    deg = db.Column(db.Numeric(65,1))
    mps = db.Column(db.Numeric(65,1))

这是我的查询不起作用

forcast_query = Forcast_short.query.join(Spots, Spots.geobaseid==Forcast_short.geobaseid).filter(Forcast_short.region == region).all()

我究竟做错了什么？

当我运行查询时，我只能从带有和不带有过滤器的 Forcast_short 获得结果。

<tbody>
                  <tr>{%for row in forcast_query%}
                    <td><a href="/spots/{{row.url_slug}}"> {{row.spot}} </a></td>
                    <td>{{row.mps}} </td>
                    <td>{{row.url_slug}} </td>

                  </tr>
                  {%endfor%}
                </tbody>

此查询适用于 Mysql 工作台。

select * from
(
SELECT * FROM sc_db2.forcast_short

) a
 join 
(
select * from sc_db2.spots
) b
on a.geobaseid = b.geobaseid

;

标签： mysqlflask-sqlalchemy

Answer 1

这是一个较短的示例，它说明了点和预测之间的一对多关系（我认为这是您想要做的）：

from app import db

class Spot(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    geobaseid = db.Column(db.Integer)
    forecasts = db.relationship('Forecast', backref='spot', lazy='dynamic')


class Forecast(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    region = db.Column(db.String(50))
    spot_id = db.Column(db.Integer, db.ForeignKey('spot.id'))

该db对象在app包初始化程序中设置，遵循 Miguel Grinberg 的模式：

from flask import Flask
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import models

请注意，设置了外键关系后，您不需要显式连接即可从子对象访问父字段 - SQLAlchemy 允许您只使用点表示法：

>>> from app import db
>>> from app.models import Spot, Forecast
>>> for spot in Spot.query.all():
...     print(spot.id, spot.geobaseid)
...
1 1
2 2
>>> for forecast in Forecast.query.all():
...     print(forecast.id, forecast.region, forecast.spot_id, forecast.spot.geobaseid)
...
1 Scotland 2 2
2 England 2 2