python查询MySQL结果输出dict/json_connection.cursor(dictionary=true)-优快云博客

本文详细介绍了使用Python的mysql.connector库进行数据库查询时，如何通过调整游标参数来改变查询结果的输出格式，包括字典、元组、缓冲、原始和预处理等不同形式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

from mysql import connector
 
cnx = connector.connect()
cur = cnx.cursor(dictionary=True)
sql = '''select 1 as one,2 as tow 
union select 11 as one,22 as tow 
union select "011" as one,"022" as tow;
'''
cur.execute(sql)
disct_result = cur.fetchall()

print(disct_result)

>>> 
[{'one': '1', 'tow': '2'}, {'one': '11', 'tow': '22'}, {'one': '011', 'tow': '022'}]

关于输出结果类型，源码里还有相关参数，可以按需取值，源码如下：

    def cursor(self, buffered=None, raw=None, prepared=None, cursor_class=None,
               dictionary=None, named_tuple=None):
        """Instantiates and returns a cursor

        By default, MySQLCursor is returned. Depending on the options
        while connecting, a buffered and/or raw cursor is instantiated
        instead. Also depending upon the cursor options, rows can be
        returned as dictionary or named tuple.

        Dictionary and namedtuple based cursors are available with buffered
        output but not raw.

        It is possible to also give a custom cursor through the
        cursor_class parameter, but it needs to be a subclass of
        mysql.connector.cursor.CursorBase.

        Raises ProgrammingError when cursor_class is not a subclass of
        CursorBase. Raises ValueError when cursor is not available.

        Returns a cursor-object
        """
        self.handle_unread_result()

        if not self.is_connected():
            raise errors.OperationalError("MySQL Connection not available.")
        if cursor_class is not None:
            if not issubclass(cursor_class, CursorBase):
                raise errors.ProgrammingError(
                    "Cursor class needs be to subclass of cursor.CursorBase")
            return (cursor_class)(self)

        buffered = buffered if buffered is not None else self._buffered
        raw = raw if raw is not None else self._raw

        cursor_type = 0
        if buffered is True:
            cursor_type |= 1
        if raw is True:
            cursor_type |= 2
        if dictionary is True:
            cursor_type |= 4
        if named_tuple is True:
            cursor_type |= 8
        if prepared is True:
            cursor_type |= 16

        types = {
            0: MySQLCursor,  # 0
            1: MySQLCursorBuffered,
            2: MySQLCursorRaw,
            3: MySQLCursorBufferedRaw,
            4: MySQLCursorDict,
            5: MySQLCursorBufferedDict,
            8: MySQLCursorNamedTuple,
            9: MySQLCursorBufferedNamedTuple,
            16: MySQLCursorPrepared
        }
        try:
            return (types[cursor_type])(self)
        except KeyError:
            args = ('buffered', 'raw', 'dictionary', 'named_tuple', 'prepared')
            raise ValueError('Cursor not available with given criteria: ' +
                             ', '.join([args[i] for i in range(5)
                                        if cursor_type & (1 << i) != 0]))

以上文sql的查询为例，不同格式的输出如下：

for conf in [dict(buffered=True),
             dict(raw=True),
             dict(dictionary=True),
             dict(named_tuple=True),
             dict(prepared=True)]:
    cur = cnx.cursor(**conf)
    cur.execute(sql)
    x = cur.fetchall()
    print(conf, ":", x)
>>>
{'buffered': True} : [('1', '2'), ('11', '22'), ('011', '022')]
{'raw': True} : [(bytearray(b'1'), bytearray(b'2')), (bytearray(b'11'), bytearray(b'22')), (bytearray(b'011'), bytearray(b'022'))]
{'dictionary': True} : [{'one': '1', 'tow': '2'}, {'one': '11', 'tow': '22'}, {'one': '011', 'tow': '022'}]
{'named_tuple': True} : [Row(one='1', tow='2'), Row(one='11', tow='22'), Row(one='011', tow='022')]
{'prepared': True} : [(bytearray(b'1'), bytearray(b'2')), (bytearray(b'11'), bytearray(b'22')), (bytearray(b'011'), bytearray(b'022'))]