1

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=db.cursor()
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
craisondigital
  • 69
  • 1
  • 9

1 Answers1

0

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?).

Community
  • 1
  • 1
mussorsky
  • 125
  • 1
  • 9
  • Ok, so if I do rows = cur.fetchall(), which would contain a list of tuples, then how would I find the empid if the rfid field is w/in those tuple. IE: if MyVariable == MyRFID: – craisondigital Sep 12 '16 at 18:17
  • The rfid would be rows[i][0] and the empid would be rows[i][1], if I'm understanding the query above correctly. – mussorsky Sep 12 '16 at 18:22
  • Ok. thank you. I will be able to test this shortly. So you are saying it should be.. `code` rows=cur.fetchall() `code` and then later on I could put `code` if MyVariable == rows[i][0]: `code` `code` print rows[i][1] – craisondigital Sep 12 '16 at 18:54
  • Ok. I've had a chance to test this.. What do I define 'i' as? – craisondigital Sep 12 '16 at 19:47
  • sorry I shouldn't have used 'i' there without describing what it was. I was using it to mean the iterator through a loop. In python you can do this without 'i' though, for example: `for row in rows: if MyVariable == row[0]: #do something with row[1], the empid` – mussorsky Sep 12 '16 at 19:54
  • Sorry, I'm not really sure how to make code look more readable in the comment there, it's also possible I'm not following you exactly on what you want to do once you have the list. – mussorsky Sep 12 '16 at 19:57
  • Ok, i am still doing something wrong then. If I do `for row in rows: uid = rows[0] emp = rows[1] if MyVariable == uid: print emp ` nothing is printed. If I add the print emp statement before the if statement, it prints the following.. ('98 555 201 88', 1L) which is the rfid number AND empid. – craisondigital Sep 12 '16 at 20:08
  • 1
    Nevermind, I had to take the 's' off of rows[0]. Now it is printing all of the uids! One step closer! – craisondigital Sep 12 '16 at 20:18