title: How to create a large pandas dataframe from an sql query without running out of memory?
body: <p>I have trouble querying a table of > 5 million records from MS SQL Server database. I want to select all of the records, but my code seems to fail when selecting to much data into memory. </p>
  
  <p>This works:</p>
  
  <pre><code>import pandas.io.sql as psql
  sql = "SELECT TOP 1000000 * FROM MyTable" 
  data = psql.read_frame(sql, cnxn)
  </code></pre>
  
  <p>...but this does not work:</p>
  
  <pre><code>sql = "SELECT TOP 2000000 * FROM MyTable" 
  data = psql.read_frame(sql, cnxn)
  </code></pre>
  
  <p>It returns this error:</p>
  
  <pre><code>File "inference.pyx", line 931, in pandas.lib.to_object_array_tuples
  (pandas\lib.c:42733) Memory Error
  </code></pre>
  
  <p>I have read <a href="https://stackoverflow.com/questions/11622652/large-persistent-dataframe-in-pandas">here</a> that a similar problem exists when creating a <code>dataframe</code> from a csv file, and that the work-around is to use the 'iterator' and 'chunksize' parameters like this:</p>
  
  <pre><code>read_csv('exp4326.csv', iterator=True, chunksize=1000)
  </code></pre>
  
  <p>Is there a similar solution for querying from an SQL database?  If not, what is the preferred work-around?  Should I use some other methods to read the records in chunks?  I read a bit of discussion <a href="https://stackoverflow.com/questions/14262433/large-data-work-flows-using-pandas">here</a> about working with large datasets in pandas, but it seems like a lot of work to execute a SELECT * query.  Surely there is a simpler approach.</p>