CursorDebugWrapper should allow using iterators/generators for executemany().

[Vadim Fint]

executemany on cursors can have a little speedup on huge lists if developer sends iterators or generators as args. Thus, they will have no len. CursorDebugWrapper tries to determine length for only one reason: put a record about number of executed queries into connection.queries.

It's good idea probably to determine if args have no len -- dont put info about amount of executed queries into connection.queries.

Right now I can do connection.cursor().cursor.executemany(sql, iterator) for that purpose. I mean -- python db wrappers usually allow iterators in executemany's args. Another ugly way right now - catch TypeError and silently ignore. This is because django do len() after calling executemany on underlying cursor =). Non-pythonic, yeah?

[Vadim Fint]

Easiest fixup:

django/db/backends/util.py

@@ -32 +32 @@
         finally:
             stop = time()
             self.db.queries.append({
-                'sql': '%s times: %s' % (len(param_list), sql),
+                'sql': '%s times: %s' % (len(param_list) if hasattr(param_list, '__len__') else '??', sql),
                 'time': "%.3f" % (stop - start),
             })

Because param_list may be iterator and already iterated iterator (hmm =)) -- there is no way to determine it's length after underlying executemany() cursor call. Thus, -- '??' is okay, imho :).

[(none)]

Milestone post-1.0 deleted

[Aymeric Augustin]

Strictly speaking, PEP 249 defines executemany(operation, seq_of_parameters), where seq_of_parameters is a ​sequence, and len is always available on sequences.

However, three out of four database backends support calling Cursor.executemany with an iterator as second argument:

• The built-in sqlite3 module ​also allows using an iterator yielding parameters instead of a sequence.
• It's clear from the source of pyscopg2, in cursor_type.c, that psycopg.Cursor.executemany accepts iterators, and if it receives a non-iterable, it actually converts it to an iterator -- if (!PyIter_Check(vars)) { vars = iter = PyObject_GetIter(vars); ... }.
• MySQLdb.Cursor.executemany is implemented in Python, and the two possible code paths use for row in args, which clearly works with iterators.
• Only cx_Oracle.Cursor.executemany fails to accept an iterator as argument: in Cursor.c, Cursor_ExecuteMany does numRows = PyList_GET_SIZE(listOfArguments);

So, it would be a good idea to support iterators in CursorDebugWrapper. Note that the performance argument is irrelevant for CursorDebugWrapper itself, since it's only used when DEBUG=True. The real reason for the fix is that CursorDebugWrapper shouldn't be more restrictive than regular cursors.

NB: django.db.backends.oracle.base.FormatStylePlaceholderCursor also relies on params not being a iterator, since it tries to access params[0]. But that could be fixed by adding something along the lines of if params is not None: params = list(params).

[Aymeric Augustin]

In [17387]:

Fixed #10320 -- Made it possible to use executemany with iterators. Thanks MockSoul for the report.

[anonymous]

Good to see you eventually close old tickets, thanks!!