How to use server-side SQL cursors with Python and QuestDB?

Community
,
1

I’m trying to fetch a few million rows from QuestDB with psycopg2, but I’m getting an SQL error:

2024-02-01T07:09:55.952335Z I i.q.g.SqlCompilerImpl parse [fd=24, thread=31, q=DECLARE "test" CURSOR FOR SELECT * FROM test;]

That’s probably because my code is trying to create a server-side cursor to avoid fetching all records into RAM in one go:

sql = "SELECT * FROM test;"
with self._conn.cursor('test') as cur:
    cur.itersize = 1000
    cur.execute(sql)
    while _records := cur.fetchmany(size=1000):
        ...

Does QuestDB support server-side cursors?

2

psycopg2 uses so-called scrollable cursors (PostgreSQL: Documentation: 16: 43.7. Cursors), i.e. cursors that have to be created explicitly with DECLARE CURSOR and that can go forward and backward. QuestDB doesn’t support these cursors, but it supports so-called non-scrollable cursors, i.e. forward-only cursors, and that’s what you need.

asyncpg driver supports non-scrollable cursors: API Reference — asyncpg Documentation

Here is a snippet that would prefetch rows in 1K batches and print them:

import asyncio
import asyncpg
import datetime

async def main():
    conn = await asyncpg.connect(dsn='postgresql://admin:quest@localhost:8812/qdb',ssl=False)

    async with conn.transaction():
        async for record in conn.cursor('SELECT * FROM test;', prefetch=1000):
            print(record)

    await conn.close()

asyncio.get_event_loop().run_until_complete(main())