mySQLdb python help

[cscuilla]

I'm working with an rfid reader (MFRC522). Each card has a unique ID written to the card (UID). I would like to store the UIDs for cards I give out in a mysql database. So that when a user scans his card, it searches for that UID w/in the database, and if found, returns his username.

Below, is how I can assign the uids w/in the python script
(user 1 = [xxx])

Code: Select all

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

continue_reading = True
MIFAREReader = MFRC522.MFRC522()

#UIDs
user1 = [99,12,123,89,96]

#Initiate LCD
lcd = Adafruit_CharLCD(rs=26, en=19, d4=13, d5=6, d6=5, d7=21, cols=16, lines=2)
lcd.clear()

def end_read(signal, frame):
  global continue_reading
  continue_reading = False
  print "Ctrl+C captured, ending read."
  MIFAREReader.GPIO_CLEEN()
signal.signal(signal.SIGINT, end_read)

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

  if backData == user1:
    print "IT WORKED"

I can connect to the DB successfully by adding this..

Code: Select all

# DB INFO
db = MySQLdb.connect("SQLserver", "username", "password", "database")
curs=db.cursor()

I need some help to figure out how to store and format all of the UIDs from the database into the python script

Code: Select all

if backData == UID from database
    #Do Something

[DougieLawson]

You need to start by modelling the data you're planning to store. Apart from the UID what other data are you collecting? Once you've done that piece look at whether those data are character, timestamps, integers, floating point, variable length character (varchar) strings, bytes, booleans, lobs, blobs, clobs, etc. Does it really need to be SQL or would stuffing the data in a flat file work?

When you have a design you can create a table.

Look at how you're going to retrieve the data. How many rows do you have? How many requests per second are you getting to retrieve it? Is one of the fields perfect for indexing (unique UID)? What predicates are you going to use to read the table?