Should I re-use cursor object or create a new one with mysql.connector?

Question

Should I reuse the cursor object or create a new one for each query?

Reusing the cursor:

    # we're going to connect to s3 and mysql
    db = mysql_connect(host="localhost",
                       user="user",
                       passwd="pass",
                       database="db")

    # Reusing the cursor
    cursor = db.cursor()

    # loop through all the files in the bucket one by one
    for key in bucket.get_all_keys():

        # the document id is the filename in the s3 bucket
        doc_id = key.name.split("/")[-1]
        cursor.execute("SELECT document_name FROM laws_docs WHERE id = %i", (doc_id,))
        doc_name = cursor.fetchone()[0]

    cursor.close()

- or -

New cursor every time:

    # we're going to connect to s3 and mysql
    db =  mysql_connect(host="localhost",
                       user="user",
                       passwd="pass",
                       database="db")

    # loop through all the files in the bucket one by one
    for key in bucket.get_all_keys():

        # new cursor
        cursor = db.cursor()

        # the document id is the filename in the s3 bucket
        doc_id = key.name.split("/")[-1]
        cursor.execute("SELECT document_name FROM laws_docs WHERE id = %i", (doc_id,))
        doc_name = cursor.fetchone()[0]

         ursor.close()

Does it even matter? That loop is going to run at least 50,000 times.

Answer 1

If you're using MySQL Connector/Python, cursor = db.cursor() will create a new CMySQLCursor instance (or MySQLCursor if you're using the pure Python version). Thus for your second example, you will create 50,000 cursor instances.

You only need one cursor. Open and close the cursor outside the for loop (use your first example).