Getting SQL database parameters as dict for comparison

Question

So, I'm able to get the information from MySQL database. However, I'm trying to import the database as a dictionary so that I can compare "username" to the "level" of that user. However, it appears to be pulling it as a list. Here is my code:

user_name = self.uInputUsername.text()
# user_pass = self.UinputPass.text()
"""
User inputs username
"""
con = mdb.connect(host="<hostIP>", user="<db_username>", passwd="<db_user_pass>", db="<db_name>")
list_cursor = con.cursor()
dict_cursor = con.cursor(MySQLdb.cursors.DictCursor)

with con:
    cur = con.cursor()
    cur.execute("SELECT * FROM `users`")
    rows = cur.fetchall()
    for row in rows:
        print(row)

Then the current output is:

(4L, 'Jack', 'Polluck', '[email protected]', 'very-secret', '', 0, 0L)

I'm trying to get:

{'firstname':'Jack','password':'verysecret','something':'','level':'0','active':'0L'}

I believe the above would be proper dictionary output. So how do I create this as a dictionary output? Like if column six (which is level) == 0 then do action? or if column six == 1 then do another action?

Essentially, comparing the 'name' to the 'level' of that name.

Thanks guys! I know asking questions here can be pretty rough, so hopefully this will not only help me but other people.

Best! N

Answer 1

Dictionary is created using curly bracket, so you can directly convert your list into a dictionary. Let say row is the result of your fetch. Thus,

rows = ['4L', 'Jack', 'Polluck', '[email protected]', 'very-secret', '', 0, '0L']
rows_dic = {'firstname': rows[1],'password': rows[4],'something': rows[5],'level': rows[6],'active': rows[7]}
if  rows[6] == 0:
    print('do something')
elif rows[6] == 1:
    print('do something else')

Answer 2

Since you're already creating a dictionary cursor, dict_cursor, you should use that instead of creating another list cursor.

with con:
    dict_cursor.execute("SELECT * FROM `users`")
    rows = dict_cursor.fetchall()
    for row in rows:
        print(row)