pymysql fetchall() results as dictionary?

Question

Is there any way to get the results from a fetchall() as a dictionary using pymysql?

Answer 1

PyMySQL includes a DictCursor. It does what I think you want. Here's how to use it:

import pymysql
import pymysql.cursors
connection = pymysql.connect(db="test")
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT ...")

https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/tests/test_DictCursor.py

Answer 2

Use pymysql.cursors.DictCursor, which will return rows represented as dictionaries mapping column names to values.

A few ways to use it...

Create a connection object and have all cursors spawned from it be DictCursors:

>>> import pymysql
>>> connection = pymysql.connect(db='foo', cursorclass=pymysql.cursors.DictCursor)
>>> with connection.cursor() as cursor:
...     print cursor
... 
<pymysql.cursors.DictCursor object at 0x7f87682fefd0>
>>> with connection.cursor() as cursor:
...     cursor.execute("SELECT * FROM bar")
...     print cursor.fetchall()
... 
2
[{u'col2': 'rty', u'col1': 'qwe'}, {u'col2': 'fgh', u'col1': 'asd'}]

Create a DictCursor from an ordinary connection object:

>>> connection = pymysql.connect(db='foo')
>>> with connection.cursor(pymysql.cursors.DictCursor) as cursor:
...     print cursor
... 
<pymysql.cursors.DictCursor object at 0x7f876830c050>

Connect and create a DictCursor in one line with with:

>>> from pymysql.cursors import DictCursor
>>> with pymysql.connect(db='foo', cursorclass=DictCursor) as cursor:
...     print cursor
... 
<pymysql.cursors.DictCursor object at 0x7f8767769490>

Answer 3

Use a DictCursor in the cursor() method.

Answer 4

If you mean that you want to fetch two columns, and return them as a dictionary, you can use this method.

def fetch_as_dict(cursor select_query):
    '''Execute a select query and return the outcome as a dict.'''

    cursor.execute(select_query)
    data = cursor.fetchall()
    try:
        result = dict(data)
    except:
        msg = 'SELECT query must have exactly two columns'
        raise AssertionError(msg)

    return result