python查询MySQL结果输出dict/json

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'))]
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页