craisondigital craisondigital - 11 months ago 50
MySQL Question

How to store mySQL query as python dictionary and call later in script?

I have a table called "uid" which has two fields, rfid and empid:

  • EXAMPLE RECORD 1 rfid = 88 999 33 555 empid = 1

  • EXAMPLE RECORD 2 rfid = 64 344 77 222 empid = 2

I would like to run a query which stores all of these values in a python dictionary, and allows me to set the rfid number as a variable (MyRFID) to be called later in the script:

db = MySQLdb.connect("MyIp", "MyUser", "MyPass", "MyDB", cursorclass=MySQLdb.cursors.DictCursor)
curs.execute("""SELECT number, empid FROM rfid """)

So that later in the code I can check to see if I get a match from an existing variable:

if MyVariable == MyRFID:
#Create new record for that empid

Answer Source

I think you're looking for the fetchall method. After you call

cur.execute("""SELECT number, empid FROM rfid """)

you would do:

rows = cur.fetchall()

and then rows would contain a list of tuples. If you specifically need it to be a dictionary, you could just do dict(rows) in this example but that only works because your query happens to return two columns. If it was more than two columns you would need something like:

rows_dict = dict([ k[0], k[1:]) for k in rows])

(see: Converting List of 3 Element Tuple to Dictionary)

If the dictionary you were imagining had column names as keys you need to look at DictCursor (Python: use mysqldb to import a MySQL table as a dictionary?).