如何将原始SQL查询转换为gino ORM查询？

Question

我在一个postgreSQL数据库中安装并启用了这个表，并安装了postGIS扩展。

                                         Table "public.crime_data"


   Column    |            Type             | Collation | Nullable |                Default                 
-------------|-----------------------------|-----------|----------|----------------------------------------
 id          | integer                     |           | not null | nextval('crime_data_id_seq'::regclass)
 state       | character varying           |           |          | 
 district    | character varying           |           |          | 
 location    | character varying           |           |          | 
 sub_type_id | integer                     |           |          | 
 date_time   | timestamp without time zone |           |          | 
 latitude    | double precision            |           |          | 
 longitude   | double precision            |           |          | 
 geom_point  | geography(Point,4326)       |           |          | 


Indexes:
    "crime_data_pkey" PRIMARY KEY, btree (id)
    "idx_crime_data_geom_point" gist (geom_point)
Foreign-key constraints:
    "crime_data_sub_type_id_fkey" FOREIGN KEY (sub_type_id) REFERENCES sub_type(id)

我正在使用Sanic web框架，同时使用Gino ORM，因为它是异步的。

我能够在命令行中并使用Gino编写和运行原始SQL查询。我只想知道是否有可能将某个查询转换为ORM语法。

这是正在工作的原始查询。此代码片段位于异步视图函数中，这将返回预期的结果。

data_points = await db.status(db.text('''
    SELECT 
        location, 
        sub_type_id, 
        latitude, 
        longitude, 
        date_time
    FROM 
        crime_data
    WHERE 
        ST_Distance(
        geom_point,
        ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)
    ) <= 5 * 1609.34;
'''), {
    'lat': lat,
    'lng': lng,
})

这是我尝试将其转换为ORM查询的尝试，而不是工作的。

data_points = await CrimeData.query.where(
    geo_func.ST_Distance(
        'geom_point',
        geo_func.ST_SetSRID(
            geo_func.ST_MakePoint(lng, lat),
            4326
        )
    ) <= (5 * 1609.34)
).gino.all()

在尝试运行此查询并以text的形式返回响应时，我将得到此错误。

⚠️ 500 — Internal Server Error
parse error - invalid geometry HINT: "ge" <-- parse error at position 2 within geometry

Traceback of __main__ (most recent call last):
InternalServerError: parse error - invalid geometry HINT: "ge" <-- parse error at position 2 within geometry
File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/sanic/app.py, line 973, in handle_request

response = await response

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/backend/services/crime_plot.py, line 30, in test

data_points = await CrimeData.query.where(

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/api.py, line 127, in all

return await self._query.bind.all(self._query, *multiparams, **params)

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/engine.py, line 740, in all

return await conn.all(clause, *multiparams, **params)

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/engine.py, line 316, in all

return await result.execute()

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/dialects/base.py, line 214, in execute

rows = await cursor.async_execute(

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/dialects/asyncpg.py, line 184, in async_execute

result, stmt = await getattr(conn, "_do_execute")(query, executor, timeout)

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/asyncpg/connection.py, line 1433, in _do_execute

result = await executor(stmt, None)

File asyncpg/protocol/protocol.pyx, line 196, in bind_execute


InternalServerError: parse error - invalid geometry HINT: "ge" <-- parse error at position 2 within geometry while handling path /crime-plot/test1

我知道ORM查询是一个SELECT *，只要我实际得到结果就行了。我不明白我做错了什么。我正在完成这项工作，但我只想确保ORM也是可能的。

这是视图函数的代码，如果它是相关的话。

@app.route('/test')
async def test(request):
    """
    /test?lng=88.21927070000001&lat=23.9130464
    """
    lat = request.args.get('lat')
    lng = request.args.get('lng')
    if lat and lng:
        lat = float(lat)
        lng = float(lng)

        data_points = ...  # either of the above mentioned queries
        return text(data_points)
    else:
        return text('ERROR: lat or lng value missing')

Answer 1

由于您使用的是ORM，所以需要使用模型类的属性而不是列名的字符串。将ORM查询更改为此，它应该可以工作。

data_points = await CrimeData.query.where(
    geo_func.ST_Distance(
        CrimeData.geom_point,
        geo_func.ST_SetSRID(
            geo_func.ST_MakePoint(lng, lat),
            4326
        )
    ) <= (5 * 1609.34)
).gino.all()