Read MySQL table to python dictionary

[cscuilla]

I'm building a timeclock using rpi and an rfid reader. With some help from other users, I was able to store users ID and username into a CSV file python dictionary, so that when an rfid tag is scanned, it looks for a match within the python dictionary.

I have then made a Mysql database where I am storing the punches.

So now, I am thinking it would be better to pull the initial scan from the mysql database as well and get rid of the csv file. I do not understand how the dictionary code is written, so am unable to accomplish this. I am able to connect to the DB w/o issue. Here is my current code using the dictionary. Where it currently says #Do Something, is where I am currently connecting to my DB, and recording the punches. So I will need to connect to the DB at first launch and read the values from my table into the dictionary instead of the csv file values..

Code: Select all

import MFRC522
import signal
import datetime
import time
import os
import sys
import RPi.GPIO as GPIO
import csv
import MySQLdb
from time import sleep
from Adafruit_CharLCD import Adafruit_CharLCD

# VARIABLES
uid_dict = dict()
uid_file = "/mnt/lock_logs/timeclock.csv"
fieldnames = ['UUID', 'USERS']

# UID DICTIONARY
with open(uid_file, "r") as f:
    reader = csv.DictReader(f, fieldnames=fieldnames, delimiter=",")
    for row in reader:
        uid_dict[row['USERS'].strip()] = [int(i) for i in row['UUID'].split()]

while continue_reading:
  (status,TagType) = MIFAREReader.MFRC522_Request(MIFAREReader.PICC_REQIDL)
  (status,backData) = MIFAREReader.MFRC522_Anticoll()

  for key, value in uid_dict.iteritems():
       if backData == value:
            #Do Something

[DougieLawson]

The way to get a simple SQL statement to run is

Code: Select all

import MySQLdb as db

user="mysql_unprivileged_user_with_access_to_my_database"
passwd="MySQL_users_Passwd"
database="my_database"
dbcon = db.connect('localhost', user, passwd, database)

my_sql = "INSERT INTO my_table (column_1, column_2) VALUES("col1 val", "col2 val");"
dbcur = dbcon.cursor()
dbcur.execute(my_sql)
dbcon.commit()

sorry that SQL is an ugly example. There's better examples at http://mysql-python.sourceforge.net/MySQLdb.html

[cscuilla]

Thanks. I can already connect and run queries without issue w/in python. The trouble is with the python dictionary. I'm trying to store all the values from the query w/in a python dictionary.

[DougieLawson]

This reads a database and gives you a dict for each row that's returned in the resultset

Code: Select all

#!/usr/bin/python

import locale
import MySQLdb
import MySQLdb.cursors

locale.setlocale(locale.LC_ALL, 'en_GB.UTF-8')

db = MySQLdb.connect(host="localhost",
                     user="tester",
                      passwd="tester_password",
                      db="my_database", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

cur.execute("SELECT Secret, URL from my_table where Channel = 'maker';")

for row in cur.fetchall() :
    print row['Secret']
    print row['URL']

the cursorclass is the thing that does the magic.

[cscuilla]

Ok. Thank you. This looks Great!!! Won't have time to test until tomorrow. Thanks so much!!

[cscuilla]

Ok, so I had some time to get back to this. I am still having trouble. I will try to explain better..
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:

Code: Select all

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:

Code: Select all

if MyVariable == MyRFID:
    #Do Something

Thanks so much for your time!

[DougieLawson]

If you create a table with

Code: Select all

create table uid (rfid char(13), empid integer);
insert into uid (rfid, empid) values('88 999 33 555', 1);
insert into uid (rfid, empid) values('64 344 77 222', 2);

then your python program has to match the DDL used to create your table

Code: Select all

#!/usr/bin/python

import locale
import MySQLdb
import MySQLdb.cursors

locale.setlocale(locale.LC_ALL, 'en_GB.UTF-8')

db = MySQLdb.connect(host="localhost",
                     user="tester",
                      passwd="myverysecretMySQLpassword",
                      db="tester", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()
cur.execute("SELECT rfid, empid FROM uid;")

for row in cur.fetchall() :
    print row['rfid']
    print row['empid']

You can then use

Code: Select all

if row['empid'] == 1:
   # do stuff when it's employee #1

and

Code: Select all

if row['rfid'] == "64 344 77 222":
   # do stuff when you've got rfid "64 344 77 222"

[sonorobby]

Hello cscuilla, in your first post you wrote, that you realized a cose to import your rfid tag into an excel. can u provide the code? I am new in programming with python and want to test something a littel bit.
I am also interessted in the code to import into mysql...

would be great