Exporting data via VS code using python but data export keeps cycling

Question

I'm a newbie and have cobbled loads of code from various sections from this site to get to where I have got to and am now stuck. I'm simply trying to export data from an Oracle DB which seems to work on aggregated table of data, around 11 rows, but as soon as I try to export a large number of rows, the export seems to cycle continuously. I can see the csv file starting off around 16kb, then rise to arond 6 mb in increments, then fall back down to around 8 ro 12 kb and then rise again. I've left it to run for several hours and keeps looping.

Below is the code, some code I've commented out that didn't work.

This is the code.

If I change the SQL to an aggregated code then it works (about 11 rows), but fails if it's like a full table export. Normal export from SQL Developer is max 6MB so not a huge amount of data at best, but fails using this code.

Am I missing something?

import oracledb
oracledb.init_oracle_client(lib_dir=r"C:\Program Files (x86)\Oracle\InstantClient_11gr2",
                           config_dir=r"C:\Users\Public\PY\TNS")

#import connection

#cur, conn = connection.get_connection()

#import config
#login(config.un, config.pw)

#exec(open("connection.py").read())
#exec(open("C:\Users\Public\PY\connection.py").read())

import sys
import os
import pandas as pd  # Calculations and data handling.
import csv

from datetime import date
from datetime import datetime
from datetime import timedelta

un = 'username'
pw = 'password'
cs = 'DL'


os.chdir(r"C:\Users\Public\PY\Measures\Ethernet\Output") # Change the working directory to be the script location.

conn = oracledb.connect(user=un, password=pw, dsn=cs)

sql = """Select * from CFA.MV_WEEKLY_ETH_OP_DATA
 where 1=1

 """

cur = conn.cursor()
cur.execute(sql)
res = cur.fetchall()

for row in res:
    #print(row)

 with open("output5.csv", "w", encoding='utf-8') as outputfile:
         writer = csv.writer(outputfile, lineterminator="\n")
         results = cur.execute(sql)
         headers = [i[0] for i in cur.description]
         writer.writerow(headers)
         writer.writerows(results)

print(res[6])

Answer 1

I've just been scrolling through this site and found this article where I copied part of this code and commented out this:

added

with connection.cursor() as cursor:
    cursor.arraysize = 5000

and commented out this code in my original code and the output worked.

commented out

#for row in res:
    #print(row)

so it now looks like this :

sql = """Select * from CFA.MV_WEEKLY_ETH_OP_DATA
 where 1=1

 """ 

cur = conn.cursor()
cur.execute(sql)
res = cur.fetchall()

#for row in res:
    #print(row)
with conn.cursor() as cursor:
    cursor.arraysize = 5000

    with open("output5.csv", "w", encoding='utf-8') as outputfile:
         writer = csv.writer(outputfile, lineterminator="\n")
         results = cur.execute(sql)
         headers = [i[0] for i in cur.description]
         writer.writerow(headers)
         writer.writerows(results)

Question is, what is array size and how do I define what this should be?

emphasized text