Stream results to disk rather than memory?

[shaunlandau1973]

My app needs to write a large result to file. Is there a way to do this without capturing results in memory?

[hashhar]

Yes, instead of using fetchall you can use fetchmany and set cursor.arraysize to whatever value you want.

e.g.

cursor.arraysize = 100
# only 100 rows will be fetched at a time
cursor.execute("SELECT * FROM tpch.sf1.orders")
with open(csv_file, 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    rows = cursor.fetchmany()
    # get column names
    headers = [desc[0] for desc in cursor.description]
    write.writerow(headers)
        
    # write rows
    while len(rows) > 0:
        writer.writerows(rows)
        rows = cursor.fetchmany()