A database cursor acts as a pointer into a query result, advancing as rows are retrieved. Streaming avoids loading a large result set into application memory, though it increases database round trips. section 5.7
With asyncpg, a streaming cursor supports several retrieval patterns:
- Iterate one row at a time with async for. section 5.7.2
- Move forward with forward and retrieve a batch with fetch. section 5.7.2
- Adjust prefetching when creating the cursor directly; awaitable cursor creation does not accept prefetch. section 5.7.2
Listing 5.15 Streaming results one by one
import asyncpg
import asyncio
import asyncpg
async def main():
connection = await asyncpg.connect(host='127.0.0.1',
port=5432,
user='postgres',
database='products',
password='password')
query = 'SELECT product_id, product_name FROM product'
async with connection.transaction():
async for product in connection.cursor(query):
print(product)
await connection.close()
asyncio.run(main())
Streams product rows inside a transaction and prints each row without retaining the entire result set. section 5.7.2
