Problems to generate a unique json using python

Question

After several try and research I have been trying how to solve the issue of the error memory by using cur.itersize but still having problems as it not generating what I'm expecting a unique json file with all the rows, what I'm getting is that is generating the same file once after other, not sure how to do it to make the chunks being appended within a unique json file. I have tried to run it in a unique query without

for row in cur:

and and changing fetchmany() and for fetchall() but the table querying is huge and pop errors of out of memory in PostgreSQL, I need the whole dataset.

for y in x:
    cur = connection.cursor('test')
    cur.itersize = 2000
    cur.execute(
        " SELECT * FROM table "
    print("fetching data for " + y)
    for row in cur:
        rows = cur.fetchmany(2000)
        print("Generating json")
        rowarray_list = []
        print("parsing rows to json file")
        json_data = json.dumps(rows)
        filename = '%s.json' % y
        print('File: ' + filename + ' created')
        with open(filename, "w") as f:
            f.write(json_data)

Answer 1

there are some things you could do to solve the memory problem, and bugs, and also increase performance.

• limit the result in the query using limit/offset and use fetchall instead of using fetchmany. i.e. "SELECT * FROM table LIMIT 10 OFFSET 0";
• set the mode in open to "a" (append) instead of "w" (write);

my suggested code:

limit = 10

for y in x:
    print("fetching data for " + y)
    filename = '%s.json' % y

    page = 0

    while 1:
        offset = limit * page

        with connection.cursor() as cur:
            cur.execute("SELECT * FROM table LIMIT %s OFFSET %s", (limit, offset))
            rows = cur.fetchall()

        if not rows:
            break

        with open(filename, "a") as f:
            f.write(json.dumps(rows))

        page += 1