Get cursor

[josejachuf]

Hi. How do I get a cursor from the DB?

I tried something like this, but it doesn't work

cur = db._adapter.cursor       
cur.execute(query, (xmin, ymin, xmax, ymax))
tile = bytes(cur.fetchone()[0])
cur.close()

File "/venv/lib/python3.8/site-packages/pydal/helpers/_internals.py", line 28, in init
self.cursor = connection.cursor()

This works, but I have to create a new connection:

pg_uri = 'dbname=db user=usr password=123 host=localhost'
conn = psycopg2.connect(pg_uri)              
cur = conn.cursor()

Jose

[gi0baro]

@josejachuf db._adapter.cursor should be the opened cursor within the connection. It should be working. Do you have the full stack-trace?

Also, mind that db.executesql() should be fine for your use-case.

[josejachuf]

This works fine (without using the Emmett data layer)

import os
import math
import psycopg2
from emmett import App, response
from emmett.orm import Database

app = App(__name__)
app.config.db.uri = 'postgres://usr_1:pass_1@localhost/db_1'
db = Database(app, pool_size=3, auto_migrate=False)


CACHE_DIR = os.path.join(app.root_path, 'vt-cache')

def tile_ul(x, y, z):
    n = 2.0 ** z
    lon_deg = x / n * 360.0 - 180.0
    lat_rad = math.atan(math.sinh(math.pi * (1 - 2 * y / n)))
    lat_deg = math.degrees(lat_rad)
    return lon_deg, lat_deg

def get_tile(z, x, y):
    xmin, ymin = tile_ul(x, y, z)
    xmax, ymax = tile_ul(x + 1, y + 1, z)

    tile = None
    tilefolder = "{}/{}/{}".format(CACHE_DIR, z, x)
    tilepath = "{}/{}.pbf".format(tilefolder, y)
    if not os.path.exists(tilepath):

        query = """
            SELECT
                ST_AsMVT(tile, 'points')
            FROM
                (SELECT
                    id,
                    nombre,
                    ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, 0.1),
                        ST_Makebox2d(ST_MakePoint(%s, %s),
                                     ST_MakePoint(%s, %s)
                                     ), 4096, 0, false) AS geom
                FROM vw_points) AS tile
        """
        
        # BEGIN QUERY
        pg_uri = 'dbname=db_1 user=usr_1 password=pass_1 host=localhost'
        conn = psycopg2.connect(pg_uri)
        cur = conn.cursor()
        cur.execute(query, (xmin, ymin, xmax, ymax))
        tile = bytes(cur.fetchone()[0])
        cur.close()       
        conn.close()
        # END QUERY

        if not os.path.exists(tilefolder):
            os.makedirs(tilefolder)
        with open(tilepath, 'wb') as f:
            f.write(tile)
            f.close()
    else:
        tile = open(tilepath, 'rb').read()

    return tile

#@app.route("/")
#async def index():
#    return dict(cd=CACHE_DIR)

@app.route("/leaflet")
async def leaflet():
    return dict()

@app.route('/tiles')
@app.route('/tiles/<int:z>/<int:x>/<int:y>.pbf', output='bytes')
async def tiles(z=0, x=0, y=0):
    # print(dir(db.connection))
    tile = get_tile(z, x, y)
    response.headers['Content-Type'] = "application/octet-stream"
    response.headers['Access-Control-Allow-Origin'] = "*"
    return tile

Using Emmett Database

        # BEGIN QUERY
        query = query % (xmin, ymin, xmax, ymax)       
        r = db.executesql(query)
        tile = bytes(r[0])
        # END QUERY    

ERROR in handlers [/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/asgi/handlers.py:340]:
Application exception:
Traceback (most recent call last):
File "/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/asgi/handlers.py", line 325, in dynamic_handler
http = await self.router.dispatch(ctx.request, ctx.response)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/routing/router.py", line 249, in dispatch
return await match.dispatch(reqargs, response)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/routing/dispatchers.py", line 44, in dispatch
return self.response_builder(await self.f(**reqargs), response)
File "/pruebas-mvt/src-emmett/app.py", line 128, in tiles
tile = get_tile(z, x, y)
File "/pruebas-mvt/src-emmett/app.py", line 101, in get_tile
r = db.executesql(query)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/pydal/base.py", line 769, in executesql
adapter.execute(query)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/pydal/adapters/init.py", line 65, in wrap
raise ValueError(args[1])
ValueError:
SELECT
ST_AsMVT(tile, 'points')
FROM
(SELECT
id,
nombre,
ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, 0.1),
ST_Makebox2d(ST_MakePoint(-63.28125, -31.952162238024968),
ST_MakePoint(-62.578125, -32.54681317351515)
), 4096, 0, false) AS geom
FROM vw_points) AS tile

This if I execute it directly in PostgreSql works fine, there is no error

            SELECT
                ST_AsMVT(tile, 'points')
            FROM
                (SELECT
                    id,
                    nombre,
                    ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, 0.1),
                        ST_Makebox2d(ST_MakePoint(-63.28125, -31.952162238024968),
                                     ST_MakePoint(-62.578125, -32.54681317351515)
                                     ), 4096, 0, false) AS geom
                FROM vw_points) AS tile

using db._adapter.cursor

        # BEGIN QUERY 
        cur = db._adapter.cursor
        cur.execute(query, (xmin, ymin, xmax, ymax))
        tile = bytes(cur.fetchone()[0])
        cur.close()
         # END QUERY 

ERROR in handlers [/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/asgi/handlers.py:340]:
Application exception:
Traceback (most recent call last):
File "/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/asgi/handlers.py", line 325, in dynamic_handler
http = await self.router.dispatch(ctx.request, ctx.response)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/routing/router.py", line 249, in dispatch
return await match.dispatch(reqargs, response)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/emmett/routing/dispatchers.py", line 44, in dispatch
return self.response_builder(await self.f(**reqargs), response)
File "/pruebas-mvt/src-emmett/app.py", line 135, in tiles
tile = get_tile(z, x, y)
File "/pruebas-mvt/src-emmett/app.py", line 100, in get_tile
cur = db._adapter.cursor
File "/pruebas-mvt/venv/lib/python3.8/site-packages/pydal/connection.py", line 65, in cursor
return self._get_or_build_free_cursor().cursor
File "/pruebas-mvt/venv/lib/python3.8/site-packages/pydal/connection.py", line 61, in _get_or_build_free_cursor
return self._build_cursor()
File "/pruebas-mvt/venv/lib/python3.8/site-packages/pydal/connection.py", line 53, in _build_cursor
rv = Cursor(self.connection)
File "/pruebas-mvt/venv/lib/python3.8/site-packages/pydal/helpers/_internals.py", line 28, in init
self.cursor = connection.cursor()
AttributeError: 'NoneType' object has no attribute 'cursor'

[gi0baro]

I suspect the theme here is that you're not using the connection manager from emmett ORM.

Adding db.pipe to tiles route should fix the db.executesql approach.
This is true also for manually using the cursor, you need connection by pipe or to be manually open inside your get_tile function, just use a with db.connection(): block. Also, there's no need to call cursor.close once you use the connection handling by emmett.

[josejachuf]

Thanks @gi0baro

This works fine:

    with db.connection():
            cur = db._adapter.cursor
            cur.execute(query, (xmin, ymin, xmax, ymax))
            tile = bytes(cur.fetchone()[0])
            cur.close()

Using executesql not works for me. I am going to try some more things and notify you, but with the cursor with the connection it is useful to me