Python Forum
Trouble retrieving dictionary from mysql.connector cursor
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Trouble retrieving dictionary from mysql.connector cursor
#1
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
Reply
#2
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  KeyError while retrieving ESPN data john317ab 2 769 Nov-29-2023, 09:07 PM
Last Post: john317ab
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  .get() not retrieving value? Sedos101 2 534 Aug-25-2023, 11:48 AM
Last Post: deanhystad
  Cursor Variable inside Another Cursor . CX_ORacle paulo79 1 1,481 Apr-09-2022, 10:24 AM
Last Post: ibreeden
  [Solved] Retrieving a pdf from sqlite3 BigMan 4 2,245 Mar-12-2022, 01:56 PM
Last Post: deanhystad
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,684 Feb-09-2022, 09:55 AM
Last Post: ibreeden
Question Debian 11 Bullseye | Python 3.9.x | pip install mysql-connector-python-rf problems BrandonKastning 4 6,574 Feb-05-2022, 08:25 PM
Last Post: BrandonKastning
  mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python ilknurg 3 5,466 Jan-18-2022, 06:25 PM
Last Post: ilknurg
  Retrieving a column from a data set using a function Bayle 6 2,290 Oct-06-2021, 08:52 PM
Last Post: Bayle
  Program stuck at mysql.connector.connect zazas321 1 2,040 Jul-29-2021, 10:49 AM
Last Post: zazas321

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020