title: psycopg2 Postgres COPY EXPERT to Pandas read_csv using memory buffer fails with ValueError
body: <p>So I'm running the following code using the psycopg2 driver in Python 3.5 to Pandas 19.x.</p>
  
  <pre><code> buf = io.StringIO()
   cursor = conn.cursor()
   sql_query = 'COPY ('+ base_sql + ' limit 100) TO STDOUT WITH CSV HEADER'
   cursor.copy_expert(sql_query, buf)
   df = pd.read_csv(buf.getvalue(),engine='c')
   buf.close()
  </code></pre>
  
  <p>The <strong>read_csv</strong> blows chunks when reading from the memory buffer:</p>
  
  <pre><code>pandas\parser.pyx in pandas.parser.TextReader.__cinit__ (pandas\parser.c:4175)()
  
  pandas\parser.pyx in pandas.parser.TextReader._setup_parser_source (pandas\parser.c:8333)()
  
  C:\Users\....\AppData\Local\Continuum\Anaconda3\lib\genericpath.py in exists(path)
       17     """Test whether a path exists.  Returns False for broken symbolic links"""
       18     try:
  ---&gt; 19         os.stat(path)
       20     except OSError:
       21         return False
  
  ValueError: stat: path too long for Windows
  </code></pre>
  
  <p>Uh..wot path? buf is in memory. What am I missing here? </p>
  
  <p>Just FYI, the Copy to seems to be working as expected.</p>
  
  <p><strong>SOLUTION CODE BELOW</strong></p>
  
  <p>Thanks to the answer below, My query speed doubled using this method and my memory use dropped by 500%. Here is my final test code included to help others resolve their performance issues. I'd love to see any code that improves this! Be sure to link back to this question in your question.</p>
  
  <pre><code># COPY TO CSV quick and dirty performance test
  import io
  import sys
  
  start = time.time()
  conn_str_copy= r'postgresql+psycopg2://' + user_id + r":" + pswd + r"@xxx.xxx.xxx.xxx:ppppp/my_database"
  result = urlparse(conn_str_copy)
  username = result.username
  password = result.password
  database = result.path[1:]
  hostname = result.hostname
  
  size = 2**30
  buf = io.BytesIO()
  # buf = io.StringIO()
  
  engine = create_engine(conn_str_copy)
  conn_copy= psycopg2.connect(
      database=database, user=username, password=password, host=hostname)
  
  cursor_copy = conn_copy.cursor()
  sql_query = 'COPY ('+ my_sql_query + ' ) TO STDOUT WITH CSV HEADER'
  cursor_copy.copy_expert(sql_query,  buf, size)
  print('time:', (time.time() - start)/60, 'minutes or ', time.time() - start, 'seconds')
  tmp = buf.seek(0)
  df = pd.read_csv(buf,engine='c', low_memory=False )
  buf.close()
  print('time:', (time.time() - start)/60, 'minutes or ', time.time() - start, 'seconds') 
  </code></pre>
  
  <p>Speed is ~4 minutes to copy the data from postgres and less than 30 seconds to load it into the the pandas dataframe. Note the copy command is a feature of the psycopg2 driver and may not work in other drivers.</p>