Querying MariaDB from python

[Larry Martell]

On Tue, Oct 2, 2018 at 11:34 AM Tony van der Hoff <lists at vanderhoff.org> wrote:
>
> I'm writing a database application, in python 3,5 under Debian9.
>
> My code:
>
>     def get_albums(self, parent_id = 0 ):
>         cursor = self.cnx.cursor()
>         sql =(  "select"
>                 "    id"
>                 ",   parent_id"
>                 ",   title"
>                 ",   ifnull( description, '' )"
>                 ",   path"
>                 ",   date( from_unixtime( date_created ) ) as date"
>                 " from album"
>                 " where parent_id = %(parent_id)s"
>                 " order by date_created"
>              )
>         cursor.execute( sql, {'parent_id': parent_id } )
>         rows = cursor.fetchall()
>
>         # return result as a list of dicts
>         result = []
>
>         for row in rows:
>             result.append({ 'id':row[0],
>                             'parent_id':row[1],
>                             'title':row[2],
>                             'description':row[3],
>                             'path':row[4],
>                             'date':row[5],
>                             }
>                         )
>         return result
>
> This works OK, but looks inelegant. Having to iterate through the
> returned data to get it into a dictionary is error-prone if the query
> changes. I would have expected the connector to be able to return a
> dictionary.
>
> Can anyone suggest a better way of doing this?

https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.DictCursor