Is connection.commit() needed after each cursor.execute()?

Question

Is it a valid PyMySQL operation to cursor.execute(…) multiple times before executing connection.commit(), or does connection.commit() need to occur after each execute statement for the results to be stored properly? The idea is to eliminate as many redundant statements as possible, as the process is long-running.

Code structure:

with connection.cursor() as cursor:
    …
    cursor.execute(SQLtype1, rowToInsert)

    cursor.execute(SQLtype2, otherToInsert)

    connection.commit() # does this account for both execute statements, or just the last?

I’ve reviewed the following:

PyMySQL execute/commit example, but with only one example having only one execute statement.

Python MySQLdb example, but with an example showing a commit after each execute statement

Python SQLite example, shows multiple execute statements before a commit, but it's uncertain whether SQLite handles differently

Note: As the SQL queries are different, executemany doesn’t appear to be an option.

Answer 1

No, that's the intended purpose of cursor.commit(). What you're describing is auto-committing, which may or may not be enabled for your database driver. Check with its documentation to be sure.

If your first query succeeds but the second one fails, you probably don't want your database to be left in a corrupt state where some rows were inserted but others were not. All the changes you perform are staged until you commit them to the database with cursor.commit(). This allows you to perform multiple queries at once and automatically roll back the changes if one of them fails.