Trouble retrieving dictionary from mysql.connector cursor

[swechsler]

I'm working on a script to report disk space with raw data stored in a MySQL table. I'm trying to retrieve the results of a SQL query into a dictionary. Here's the relevant code:

try:
    dbh = mysql.connector.connect(user = DB_USER, password = DB_PASS,
                                  host = DB_INSTANCE, database = DB_DATABASE)
    cursor = dbh.cursor(cursor_class = MySQLCursorPrepared, dictionary=True)
except mysql.connector.Error as err:
    print(f"Error connecting to  instance {DB_INSTANCE}, database {DB_DATABASE}")
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Bad username or password")
        exit(1)
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
        exit(1)
    else:
        print(err)
        exit(1)

def check_output(upper, lower):
    query = 'select host,mount_point,date_time,allocation, (avail/allocation) * 100 "percent" ' + \
            'from space_usage su0 where su0.date_time = (select max(date_time) from space_usage su1 ' + \
            'where su0.host = su1.host and su0.mount_point = su1.mount_point) and ' +\
            '((avail/allocation) * 100) < ? and ((avail/allocation) * 100) >= ? and ' +\
            'date_time >= date_sub(now(), interval 2 day) ' +\
            'order by host, mount_point'
    try:
        cursor.execute(query, (upper, lower))
    except mysql.connector.Error as err:
        print(f"Error running query '{query}':\n{err}")
        exit(1)

#    dboutput = cursor.fetchall()
    if cursor:
        error = 1
        print(f'The following filesystems are above {upper}% used:\n')
        print(header1)
        print(header2)
        for row in cursor:
            print(formatstr, (row['host'], row['filesystem'], row['mount_point'], row['allocation'], \
                              row['avail'], (row['avail']/row['allocation'])))
        print('')

But when I run it, I get this error (line numbers munged):

Output:
Traceback (most recent call last):
  File "./check_disk_usage", line xx, in <module>
    check_output(crit, 0)
  File "./check_disk_usage", line 37, in check_output
    print(formatstr, (row['host'], row['filesystem'], row['mount_point'], row['allocation'], \
TypeError: tuple indices must be integers or slices, not str

[buran]

check this: https://bugs.mysql.com/bug.php?id=92700
https://stackoverflow.com/a/52692714/4046632
It looks combination of MySQLCursorPrepared cursor to return dictionary is not supported

[swechsler]

Thanks, I suspected it might be something like that. I changed the code to access the results as a list instead.