Python DB-API（PyMySQL、PostgreSQL等）游标：单游标复用还是每查询新建？

Great question! Let's break down the two approaches you've outlined, and talk about which is more idiomatic for PyMySQL (and most Python DB-API compliant libraries like psycopg2 for PostgreSQL).

Option 1: Reuse a Single Cursor #

This is the more common and recommended approach for most everyday scenarios. Here's why:

• Resource efficiency: Creating and destroying cursors is lightweight, but there’s no need to repeat the process unnecessarily when a single cursor can handle sequential queries cleanly.
• Cleaner code: You cut down on redundant cursor = connect.cursor() and cursor.close() calls, making your code more concise and easier to follow.
• Safe (when handled properly): As long as you’ve fully processed the results from the previous query (e.g., called fetchall(), fetchone(), or exhausted the result set), executing a new query on the same cursor will automatically clear the old result set in PyMySQL. There’s no risk of leftover state messing with your next operation.

Your example code for Option 1 is totally valid and idiomatic:

connect = pymysql.connect()
cursor = connect.cursor()
cursor.execute('INSERT INTO user(name) VALUE(%s)', ('John',))
last_id = cursor.lastrowid
cursor.execute('SELECT name FROM user')
result = cursor.fetchall()
cursor.close()

Option 2: Create a New Cursor for Each Query #

This approach is less common in standard use cases, but it has niche value:

• Strict isolation: If you need to hold onto a result set while executing another query (e.g., iterating through a large dataset and running a side query mid-iteration), separate cursors let you keep both result sets active at once. This is a rare scenario, though.
• Avoiding accidental state leaks: In complex, branching code paths where you might forget to clear a result set, separate cursors eliminate the risk of leftover data interfering with subsequent operations.

That said, this approach adds unnecessary boilerplate for most simple, sequential database tasks.

Pro Tip: Use Context Managers #

No matter which approach you choose, using Python's with statement (context managers) is a better practice than manually calling close(). It ensures cursors and connections are properly cleaned up even if an error occurs:

with pymysql.connect(host='your_host', user='your_user', password='your_pass', db='your_db') as conn:
    with conn.cursor() as cursor:
        # Reuse this cursor for all your queries
        cursor.execute('INSERT INTO user(name) VALUE(%s)', ('John',))
        last_id = cursor.lastrowid
        cursor.execute('SELECT name FROM user')
        result = cursor.fetchall()
# Both cursor and conn are automatically closed here

Final Verdict #

For most day-to-day work with PyMySQL, reusing a single cursor (Option 1) is the more idiomatic and efficient choice. Only reach for per-query cursors if you have a specific need for parallel result set handling.

内容的提问来源于stack exchange，提问作者user9583104