1

I'm trying to take the results from a very large mysql query and write them to a file in Python, but I keep running into memory overload errors. I've easily climbed up to 60+ GB ram utilization until I run out of memory and it crashes. How do I do this effectively?

Here's my code snippet:

with connection.cursor() as cursor:
    cursor.execute(my_query)
    row = cursor.fetchone()
    while row is not None:
        with open(my_file,'a+') as f:
            f.writelines(row)
        row = cursor.fetchone()

This still overloads my memory even though I would have thought it would only have one line at a time in memory.

I've also tried using f.flush() at every line, as well as reopening the file and then running f.close() on every line.

Thank you so much!!

wilsonwatson
  • 21
  • 4
  • 1
    Probably more of a memory leak somewhere else - have you tried closing the cursor every now and then? – Willyzekid May 19 '20 at 22:41
  • Thanks for the help! Do you know how I would close the cursor without losing the results of the query? – wilsonwatson May 19 '20 at 22:48
  • 1
    You would partition your query using which ever index you have (by dates, etc.) or could use a cursor on the server side. But what mysql connector are you using? There must be a way to avoid this and leverage some options in the query/cursor setup (and use the doc to verify my assumption on cursor memory issue) – Willyzekid May 19 '20 at 23:14
  • 2
    Why are you opening the file every time you want to append to it? Shouldn't the `open` statement be outside the loop? – Amitai Irron May 19 '20 at 23:17
  • I was putting the open statement inside the loop so as to not load all the contents being written to the file to memory, hence this issue being about memory overload. Yes, if I wasn't facing memory issues that would make the most sense, but the contents being written to the file are too large. – wilsonwatson May 20 '20 at 16:01

1 Answers1

0

Okay, @Willyzekid pointed me in the right direction and I figured it out. In addition to there being a cursor memory issue, which I figured out using this post: MySQLdb - cursor - memory leak?

I also didn't realize that above my code snippet I had already taken the output from one query and used it as input for this query in the snippet. I was loading that output from the first query into memory, which was also overloading it. Same problem highlighted by @Willyzekid, just in a different spot in the code.

Thanks!

wilsonwatson
  • 21
  • 4