Issue: how to get execute_sql() result to dict?
Author: mouday
State: closed
Created: 2021-03-03 09:33:16

by method execute_sql(), got result is tuple, i want to get dict result like:

```python
{'id': 1}

# or 
[{'id': 1}, {'id': 2}]
```


The `execute_sql` method is a low level method that talks directly to the db-api cursor. If you want dicts then use the query-builder APIs or the various model query APIs. They are documented

* http://docs.peewee-orm.com/en/latest/peewee/querying.html#retrieving-row-tuples-dictionaries-namedtuples
* http://docs.peewee-orm.com/en/latest/peewee/query_builder.html

i know , but, some time,  i need execute some complex sql. if I see pymysql config
```python
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)
```

it support `cursorclass` option, but i try it, got a exception
```
  File "/Users/.pyenv/versions/py370/lib/python3.7/site-packages/peewee.py", line 2161, in scalar
    return row[0] if row and not as_tuple else row
KeyError: 0
```

if use query builder, i think it belong to simple sql, it sql is long and complex, will become to complex and difficulty to fix code.

now, i use `mysql_connector_python` got a dict cursor,  so i config two mysql db connection.

so, i will support execute raw sql, and got dict result. it will be wish.
 


a good message, i support it, convert cursor result to dict

demo
```python
# -*- coding: utf-8 -*-
from peewee import MySQLDatabase
from typing import List, Dict, Union


def dict_factory(cursor, row):
    """
    convert cursor result to dict
    """
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


class DictMySQLDatabase(MySQLDatabase):

    def _execute(self, sql, params):
        return self.execute_sql(sql, params)

    def select(self, sql: str, params=()) -> List:
        """select many row"""
        cursor = self._execute(sql, params)
        lst = []
        for row in cursor.fetchall():
            dct = dict_factory(cursor, row)
            lst.append(dct)

        return lst

    def select_one(self, sql: str, params=()) -> Dict:
        """select one row"""
        cursor = self._execute(sql=sql, params=params)
        row = cursor.fetchone()
        return dict_factory(cursor, row)

    def update(self, sql: str, params=()) -> int:
        """update many row"""
        cursor = self._execute(sql=sql, params=params)
        return cursor.rowcount

    def delete(self, sql: str, params=()) -> int:
        """delete many row"""
        cursor = self._execute(sql=sql, params=params)
        return cursor.rowcount

    def insert(self, sql: str, params: Union[list, dict]) -> int:
        """insert many row"""
        cursor = self._execute(sql=sql, params=params)
        return cursor.rowcount

    def insert_one(self, sql: str, params: Union[tuple, dict] = ()) -> int:
        """insert one row"""
        cursor = self._execute(sql=sql, params=params)
        return cursor.lastrowid

```

test
```python

db = DictMySQLDatabase(**config)

rows = db.select('select * from person limit 1')
print(rows)
[{'id': 1, 'name': 'Tom'}]
```

now i can use cator impl:
```python
pip install cator
```

```python
from cator import DatabaseProxy
from peewee import MySQLDatabase

db = MySQLDatabase(**config)
db_proxy = DatabaseProxy(db)
# db_proxy can use select/select_one...
```
about cator DatabaseProxy object see: https://github.com/mouday/cator