Does MySQLdb Cursors not support empty iterator?

Question

cur.executemany(sql, rows)

I have rows as empty iterator, it triggers an error.

If I do cur.executemany(sql, list(rows)) then it works fine.

 File "/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQLdb/cursors.py", line 252, in executemany
    r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
  File "/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQLdb/cursors.py", line 344, in _query
    rowcount = self._do_query(q)
  File "/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQLdb/cursors.py", line 308, in _do_query
    db.query(q)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")

The following is the code for MySQLdb Cursors.py

    def executemany(self, query, args):

        """Execute a multi-row query.

        query -- string, query to execute on server

        args

            Sequence of sequences or mappings, parameters to use with
            query.

        Returns long integer rows affected, if any.

        This method improves performance on multiple-row INSERT and
        REPLACE. Otherwise it is equivalent to looping over args with
        execute().

        """
        del self.messages[:]
        db = self._get_db()
        if not args: return
        if isinstance(query, unicode):
            query = query.encode(db.unicode_literal.charset)
        m = insert_values.search(query)
        if not m:
            r = 0
            for a in args:
                r = r + self.execute(query, a)
            return r
        p = m.start(1)
        e = m.end(1)
        qv = m.group(1)
        try:
            q = [ qv % db.literal(a) for a in args ]
        except TypeError, msg:
            if msg.args[0] in ("not enough arguments for format string",
                               "not all arguments converted"):
                self.errorhandler(self, ProgrammingError, msg.args[0])
            else:
                self.errorhandler(self, TypeError, msg)
        except (SystemExit, KeyboardInterrupt):
            raise
        except:
            exc, value, tb = sys.exc_info()
            del tb
            self.errorhandler(self, exc, value)
        r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
        if not self._defer_warnings: self._warning_check()
        return r

score 1 · Accepted Answer · answered May 19 '14 at 20:52

The short answer is: No, MySQLdb does not support passing an empty iterator argument to executemany.

Why not? Because of the line if not args: return. This handles the case where you provide no parameters by cutting the server out entirely and returning None. The truth value of an empty list, dict, set, or tuple is False, but the truth value of an iterator is always True.

If you comment out that line in cursors.py, then any empty sequence or mapping will throw the same ER_PARSE_ERROR as the empty iterator.

In order for executemany to support empty arguments, it has to test whether args is empty somehow. If args is an iterator, the only way to do this is to call .next() and observe whether the result is a StopIteration exception; there's no other way to determine whether an arbitrary iterator is empty. This would be impractical because it consumes an item from the iterator and doesn't work for any non-iterator type, and pointless because executemany is not intended to be used without parameters in the first place.

Does MySQLdb Cursors not support empty iterator?

1 Answers1