title: Nested queries using MySQLdb
body: <p>I am trying to achieve the following using Python and the MySQLdb interface:</p>
  
  <ol>
  <li>Read the contents of a table that has a few million rows.</li>
  <li>Process and modify the output of every row.</li>
  <li>Put the modified rows into another table.</li>
  </ol>
  
  <p>It seems sensible to me to iterate over each row, process on-the-fly and then insert each new row into the new table on-the-fly.</p>
  
  <p>This works:</p>
  
  <pre><code>import MySQLdb
  import MySQLdb.cursors
  
  conn=MySQLdb.connect(
      host="somehost",user="someuser",
      passwd="somepassword",db="somedb")
  
  cursor1 = conn.cursor(MySQLdb.cursors.Cursor)
  query1 = "SELECT * FROM table1"
  cursor1.execute(query1)
  
  cursor2 = conn.cursor(MySQLdb.cursors.Cursor)
  
  for row in cursor1:
      values = some_function(row)
      query2 = "INSERT INTO table2 VALUES (%s, %s, %s)"
      cursor2.execute(query2, values)
  
  cursor2.close()
  cursor1.close()
  conn.commit()
  conn.close()
  </code></pre>
  
  <p>But this is slow and memory-consuming since it's using a client-side cursor for the <code>SELECT</code> query. If I instead use a server-side cursor for the <code>SELECT</code> query:</p>
  
  <pre><code>cursor1 = conn.cursor(MySQLdb.cursors.SSCursor)
  </code></pre>
  
  <p>Then I get a 2014 error:</p>
  
  <pre><code>Exception _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") in &lt;bound method SSCursor.__del__ of &lt;MySQLdb.cursors.SSCursor object at 0x925d6ec&gt;&gt; ignored
  </code></pre>
  
  <p>So it doesn't seem to like starting another cursor while iterating over a server-side cursor. Which seems to leave me stuck with a very slow client-side iterator.</p>
  
  <p>Any suggestions?</p>