Getting Large Dataset Out of MySQL into Pandas Dataframe keeps Failing , Even With Chunksize
<p>I am trying to pull ~700k rows out of mysql into a Pandas dataframe.</p>

<p>I kept getting the same error over and over again:</p>

<p>Traceback (most recent call last):</p>

<blockquote>
  <p>File "C:\Anaconda3\lib\site-packages\mysql\connector\network.py", </p>
  
  <p>line 245, in recv_plain read = self.sock.recv_into(packet_view, rest)
  ConnectionResetError: [WinError 10054]</p>
</blockquote>

<p>An existing connection was forcibly closed by the remote host</p>

<p>Searching on StackOverflow, I found <a href="https://stackoverflow.com/questions/18107953/how-to-create-a-large-pandas-dataframe-from-an-sql-query-without-running-out-of">a great suggestion from ThePhysicist</a> in another thread, so I modified my code as below.  It won't run at all if the chunk size is over 200, and even if it is 200, the process will eventually throw up the "forcibly closed" error.</p>

<p>If anybody has any idea as to how to fix this, it would be very much appreciated.  It is extremely frustrating as I never have similar issues when using R</p>

<p>Thanks</p>

<p>My current code (apologies for the formatting - can't figure out how to do it here):</p>

<pre><code>from pandas import DataFrame
import time
import mysql.connector
import pandas as pd
import pandas.io.sql as psql

chunk_size = 300
offset = 0

cnx = mysql.connector.connect(user='xxx', password='xxx', database='xxx', host='xxx')
dfs = []

while True:
  print(offset)
  sql = "select a,b,c,d,e,f,g,h from table where a='xyz' order by b,c,d limit %d offset %d " % (chunk_size,offset) 
  dfs.append(psql.read_sql(sql, cnx))
  offset += chunk_size
  if len(dfs[-1]) &lt; chunk_size:
    print("break")
    break

full_df = pd.concat(dfs)
</code></pre>

<p>Explain Extended Returns:</p>

<pre><code>select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
SIMPLE  table   ref idx_clientid,idx_char   idx_char    48  const   1173586 100 Using index condition
</code></pre>

<p>When I move the code to the AWS server where the database resides, it runs fine with no issues.  The issue seems to be when I run the code and the machine is not residing on AWS...</p>