DuckDB - How to iterate over the result returned from duckdb.sql command

Question

Using DuckDB Python client, I want to iterate over the results returned from query like

import duckdb
employees = duckdb.sql("select * from 'employees.csv' ")

Using type(employees) returns 'duckdb.duckdb.DuckDBPyRelation'. I'm able to get the number of rows with len(employees), however for-loop iteration seems to not work.

What is the correct way to process each row at a time?

Answer 1

Apparently, from DuckDB API, something like the following should work.
I unfortunately cannot test it because DuckDB's package won't install for whatever reason.
I hope this helps you get in the right path to finding your answer.

import duckdb

employees = duckdb.sql("select * from 'employees.csv'")
rows = employees.fetchall()

# Iterate over the rows
for row in rows:
    print(row)

Answer 2

Here is an example for batching the rows:

import duckdb
batch_size = 10

handle = duckdb.sql("select * from 'employees.csv'")

while batch := handle.fetchmany(batch_size):
    print(batch)