python - 使用子查询对 SQLAlchemy (flask-sqlalchemy) 进行 SQL 查询

问题描述

我有这个 SQL 查询我想转换为 SLQAlchemy (flask-sqlalchemy) 查询使用分页

SQL查询

queryPostgres = db.text("""
    SELECT *, COUNT(*) OVER () as RowCount
    FROM (
        SELECT * ,
            ( 3958.75 *
            acos(sin(:lat1 / 57.2958) * sin( cast(latitude as double precision) / 57.2958) +
                 cos(:lat1 / 57.2958) * cos( cast(latitude as double precision) / 57.2958) *
                 cos( cast(longitude as double precision) / 57.2958 - :lon1/57.2958)))
            as distanceInMiles
        FROM "job" ) zc
    WHERE zc.distanceInMiles < :dst
    ORDER BY zc.distanceInMiles
    LIMIT :per_page
    OFFSET :offset
""")

以 SQLALchemy 为

 @classmethod
    def getJobsWithinXMiles2(cls, lat1: str, lon1: str, dst: str):

        return cls.query.filter(
            (3958.75 *
                acos(sin(lat1 / 57.2958) * sin(latitude / 57.2958) +
                cos(lat1 / 57.2958) * cos(latitude / 57.2958) *
                cos(longitude / 57.2958 - lon1/57.2958))) as distanceInMiles)  \
            .where(distanceInMiles < dst) \
            .order_by(distanceInMiles) \
            .paginate(page=_page, per_page=_per_page)

但我在 SQLAlchemy 中遇到错误

这是澄清的模式

class Job(db.Model):

    __tablename__ = "job"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80), nullable=False)
    city = db.Column(db.String(250), nullable=False)
    state = db.Column(db.String(10), nullable=False)
    zipCode = db.Column(db.String(10), nullable=False)
    latitude = db.Column(db.String(10), nullable=False)
    longitude = db.Column(db.String(10), nullable=False)
    description = db.Column(db.String(200), nullable=False)
    narrative = db.Column(db.String(250), nullable=False)
    companyLogo = db.Column(db.String(250), nullable=False)
    companyName = db.Column(db.String(250), nullable=False)
    companyURL = db.Column(db.String(100), nullable=False)
    time_created = db.Column(db.DateTime, nullable=False)

但我无法让它发挥作用。有人可以帮助将此 sql 查询转换为 SQLAlchemy 吗？

标签： pythonpostgresqlsqlalchemyflask-sqlalchemy

python - 使用子查询对 SQLAlchemy (flask-sqlalchemy) 进行 SQL 查询

问题描述

解决方案

推荐阅读