降低Cursor循环复杂度：万级数据循环代码优化求助

Hey there! Let's tackle this loop optimization problem. When dealing with 10,000 rows, the original approach of appending to a list one item at a time can feel slow because Python has to handle extra overhead for each append call. Plus, there's a possible bug in your code that might be adding unnecessary slowdowns—let's fix that and optimize at the same time.

First, Fix the Potential Bug #

Looking at your code:

for row in query_cursor:
    data.append({'name': query_result[row]['name']})

If row is each record returned by the cursor, using it as a key into query_result is probably incorrect. Chances are you meant to access the 'name' field directly from the row itself, like row['name'] (assuming your cursor returns dictionary-like rows). That alone could be causing extra slowdowns from invalid or unnecessary lookups.

Optimized Solutions #

Here are a few ways to speed up this process:

1. Use a List Comprehension (Fastest for Most Cases)

List comprehensions are implemented in C, so they're way faster than explicit for loops with append calls. They also make the code cleaner:

data = [{'name': row['name']} for row in query_cursor]

This replaces the entire loop and append process in one line, cutting down on the overhead of repeated attribute access to data.append.

2. Fetch All Rows First (If Memory Allows)

If your database cursor supports fetchall(), retrieving all rows at once into a list before processing can sometimes be faster, especially if the cursor has any per-row retrieval overhead:

rows = query_cursor.fetchall()
data = [{'name': row['name']} for row in rows]

Note: This uses more memory since all rows are stored in memory at once, but for 10,000 rows, this is totally manageable.

3. Generator Expression (Memory-Efficient Alternative)

If you don't need to store the entire list of dictionaries in memory (e.g., you're just iterating over the data once later), use a generator expression instead. It uses almost no extra memory and is just as fast to iterate over:

data_generator = ({'name': row['name']} for row in query_cursor)

You can loop over data_generator just like a list, but it doesn't store all items at once.

4. Use Tuple Indexes (If Rows Are Tuples)

If your cursor returns rows as tuples instead of dictionaries, accessing the 'name' field by its index (e.g., row[0] if name is the first column) is faster than dictionary lookups:

data = [{'name': row[0]} for row in query_cursor]

This avoids the hash table lookup overhead of dictionary keys.

Why These Work #

• List comprehensions minimize the number of Python-level operations by handling iteration in C.
• Avoiding repeated append calls cuts down on attribute lookup overhead (each data.append is a method call that Python has to resolve every time).
• Fixing the query_result[row] mistake removes unnecessary (and likely incorrect) dictionary lookups that were slowing things down.

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