Inserting rows while looping over result set

Question

I am working on a program to clone rows in my database from one user to another. It works my selecting the rows, editing a few values and then inserting them back.

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

My code looks like the following:

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()

When running this code, I get the following error:

mysql.connector.errors.InternalError: Unread result found

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

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

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()

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

Is there a way to insert rows while looping over a result set without fetching the entire result set?

Answer 1

Is there a way to insert rows while looping over a result set without fetching the entire result set?

Yes. Use two MySQL connections: one for reading and the other for writing.

The performance impact isn't too bad, as long as you don't have thousands of instances of the program trying to connect to the same MySQL server.

One connection is reading a result set, and the other is inserting rows to the end of the same table, so you shouldn't have a deadlock. It would be helpful if the WHERE condition you use to read the table could explicitly exclude the rows you're inserting, if there's a way to tell the new rows apart from the old rows.

At some level, the performance impact of two connections doesn't matter because you don't have much choice. The only other way to do what you want to do is slurp the whole result set into RAM in your program, close your reading cursor, and then write.