SQLAlchemy Harvesine大圆计算

Question

我有下表：

id     zip    city     state    longitude    latitude  timezone    dst
3817   99950    Ketchikan    AK    -131.46633      55.875767    -9           1
....

在我的SQLALchemy模型中，我有以下@classmethod 我想返回给定纬度X英里范围内的所有邮政编码 我创建了两个查询，但是下面的两个查询都失败了query1，query2

@classmethod
def getZipsWithinXMiles(cls, lat1: str, lon1: str, dst: str):
    """ Get zip codes within 'dst' miles of lat1,lon1"""

    print(lat1, lon1, dst)
    breakpoint()
    query1 = "SELECT * ," \
        "( 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 " \
        "FROM ZipCode " \
        "HAVING distanceInMiles < dst " \
        "ORDER BY distanceInMiles" \
        % {'lat1': float(lat1), 'lon1': float(lon1), 'dst': int(dst)}

    query2 = "SELECT * FROM " \
        "(SELECT id, city, state, zip, " \
        "( 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 " \
        "FROM ZipCode " \
        " ) inner_query " \
        "HAVING distanceInMiles < dst " \
        "ORDER BY distanceInMiles" \
        % {'lat1': float(lat1), 'lon1': float(lon1), 'dst': int(dst)}

    print(query1)
    print(query2)
    breakpoint()
    zipCodes = cls.query.from_statement(query2).all()
    return zipCodes

这就是我得到的错误：

cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "zipcode" does not exist
LINE 1: ...57.2958 - lon1/57.2958))) as distanceInMiles FROM ZipCode  )...

这是我用来创建表的模型

class ZipCode(db.Model):

    __tablename__ = "zipCode"

    id = db.Column(db.Integer, primary_key=True)
    zip = db.Column(db.String(5), nullable=False)  # 23567
    city = db.Column(db.String(50), nullable=False)  # New York
    state = db.Column(db.String(2), nullable=False)  # Ex: NY
    longitude = db.Column(db.String(15), nullable=False)
    latitude = db.Column(db.String(15), nullable=False)
    timezone = db.Column(db.String(3), nullable=False)  # Ex: -5,-10
    dst = db.Column(db.String(2))  # Ex: 0,1

这个代码怎么了？ 我尝试了两个查询，但都失败了

Answer 1

给予

__tablename__ = "zipCode"

必须使用带引号的标识符来引用该表：

query1 = "..." \
    ...
    'FROM "zipCode" ' \
    ...

因为mixed case：

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other.

如果给定的table name包含大写字符、保留字或包含特殊字符，SQLAlchemy将使用带引号的标识符。你知道吗

---

至于其余的，使用HAVING子句根据select列表项进行过滤是Postgresql不支持的MySQL“扩展”。query2在使用子查询时是正确的，但是应该使用WHERE子句。你知道吗

似乎已经将latitude和longitude列定义为字符串，因此必须使用CAST对它们进行算术运算。考虑改用适当的类型。你知道吗

您还使用了%-格式，但是在字符串中没有任何转换说明符-这是一件好事，因为您无论如何都不应该使用字符串格式。而是使用绑定参数/占位符并将参数传递给SQL驱动程序或库。你知道吗

总之，类方法可以如下所示：

def getZipsWithinXMiles(cls, lat1: str, lon1: str, dst: str):
    """ Get zip codes within 'dst' miles of lat1,lon1"""

    query = db.text("""
        SELECT *
        FROM (
            SELECT "zipCode".*
                 , (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 "zipCode") zc
        WHERE zc.distanceInMiles < :dst
        ORDER BY zc.distanceInMiles
        """)

    zipCodes = cls.query.\
        from_statement(query).\
        params(lat1=float(lat1),
               lon1=float(lon1),
               dst=int(dst)).\
        all()

    return zipCodes

尽管我对距离计算的正确性不承担任何责任。你知道吗