Trouble retrieving dictionary from mysql.connector cursor - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Trouble retrieving dictionary from mysql.connector cursor (/thread-21160.html) |
Trouble retrieving dictionary from mysql.connector cursor - swechsler - Sep-17-2019 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):
RE: Trouble retrieving dictionary from mysql.connector cursor - buran - Sep-17-2019 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 RE: Trouble retrieving dictionary from mysql.connector cursor - swechsler - Sep-17-2019 Thanks, I suspected it might be something like that. I changed the code to access the results as a list instead. |