Inserting rows while looping over result set
<p>I am working on a program to <em>clone</em> rows in my database from one user to another.  It works my selecting the rows, editing a few values and then inserting them back.</p>

<p>I also need to store the newly inserted rowIDs with their existing counterparts so I can clone some other link tables later on.</p>

<p>My code looks like the following:</p>

<pre><code>import mysql.connector
from collections import namedtuple

con = mysql.connector.connect(host='127.0.0.1')
selector = con.cursor(prepared=True)
insertor = con.cursor(prepared=True)

user_map = {}
selector.execute('SELECT * FROM users WHERE companyID = ?', (56, ))
Row = namedtuple('users', selector.column_names)

for row in selector:
    curr_row = Row._make(row)
    new_row = curr_row._replace(userID=None, companyID=95)

    insertor.execute('INSERT INTO users VALUES(?,?,?,?)', tuple(new_row))
    user_map[curr_row.userID] = insertor.lastrowid

selector.close()
insertor.close()
</code></pre>

<p>When running this code, I get the following error:</p>

<blockquote>
  <p>mysql.connector.errors.InternalError: Unread result found</p>
</blockquote>

<p>I'm assuming this is because I am trying to run an <code>INSERT</code> while I am still looping over the <code>SELECT</code>, <strong>but</strong> I thought using two cursors would fix that.  Why do I still get this error with multiple cursors?</p>

<p>I found a solution using <code>fetchall()</code>, but I was afraid that would use too much memory as there could be thousands of results returned from the <code>SELECT</code>.</p>

<pre><code>import mysql.connector
from collections import namedtuple

con = mysql.connector.connect(host='127.0.0.1')
cursor = con.cursor(prepared=True)

user_map = {}
cursor.execute('SELECT * FROM users WHERE companyID = ?', (56, ))
Row = namedtuple('users', cursor.column_names)

for curr_row in map(Row._make, cursor.fetchall()):
    new_row = curr_row._replace(userID=None, companyID=95)

    cursor.execute('INSERT INTO users VALUES(?,?,?,?)', tuple(new_row))
    user_map[curr_row.userID] = cursor.lastrowid

cursor.close()
</code></pre>

<p>This works, but it's not very fast.  I was thinking that <em>not</em> using <code>fetchall()</code> would be quicker, but it seems if I do not fetch the full result set then MySQL yells at me.</p>

<p>Is there a way to insert rows <em>while</em> looping over a result set <em>without</em> fetching the entire result set?</p>