使用LoggingCursor时自定义cursor_class不兼容dictionary属性问题

问题背景 #

使用mysql-connector-python==9.0.0时，默认通过以下方式可让游标以字典形式返回结果：

cursor = db.cursor(dictionary=True)

但当自定义继承CMySQLCursor的LoggingCursor类，并通过cursor_class指定时，dictionary=True参数会被忽略，结果仍以元组返回：

cursor = db.cursor(cursor_class=LoggingCursor, dictionary=True)

自定义LoggingCursor代码如下：

class LoggingCursor(CMySQLCursor):
    def __init__(self, *args, **kwargs):
        super(LoggingCursor, self).__init__(*args, **kwargs)
        self.executed_statements: list[str] = []

    def execute(self, operation, params=(), multi=False):
        result = super(LoggingCursor, self).execute(operation, params, multi)
        self.executed_statements.append(self.statement)
        return result

    def executemany(self, operation, seq_params):
        result = super(LoggingCursor, self).executemany(operation, seq_params)
        for _ in seq_params:
            self.executed_statements.append(self.statement)
        return result

    @property
    def all_executed_statements(self):
        return self.executed_statements

另外必须使用C扩展游标，使用默认mysql.connector.cursor.MySQLCursor会报错：

mysql.connector.errors.ProgrammingError: Cursor class needs to be a subclass of cursor_cext.CMySQLCursor 

解决方案 #

问题核心是：dictionary=True本质是让连接器使用CMySQLCursorDict（字典格式的C扩展游标），而你的自定义游标继承的是基础的CMySQLCursor（元组格式），因此指定cursor_class后，dictionary参数无法生效。

只需修改自定义游标继承的父类为CMySQLCursorDict，即可同时保留日志功能和字典返回格式：

from mysql.connector.cursor_cext import CMySQLCursorDict

class LoggingCursor(CMySQLCursorDict):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.executed_statements: list[str] = []

    def execute(self, operation, params=(), multi=False):
        result = super().execute(operation, params, multi)
        self.executed_statements.append(self.statement)
        return result

    def executemany(self, operation, seq_params):
        result = super().executemany(operation, seq_params)
        for _ in seq_params:
            self.executed_statements.append(self.statement)
        return result

    @property
    def all_executed_statements(self):
        return self.executed_statements

之后初始化游标时，直接指定cursor_class即可，无需再传dictionary=True（传参也不会冲突）：

cursor = db.cursor(cursor_class=LoggingCursor)

这样自定义游标既保留了SQL语句日志记录功能，又能以字典形式返回查询结果。

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