Querying MariaDB from python

[Tony van der Hoff]

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?
 

-- 
Tony van der Hoff        | mailto:tony at vanderhoff.org
Buckinghamshire, England |