2

I have a RFID-RC522 connected to Raspberry Pi and a python script which reads my RFID tag and prints out the result of the reading.

I want to transfer the ID of the RFID tag to a Microsoft SQL Server database on a remote PC. On the Microsoft SQL server database this ID should be joined to related data of this ID.

Please any advice!

here's my RFID script :

here's the script :

#!/usr/bin/env python
# -*- coding: utf8 -*-

import RPi.GPIO as GPIO
import MFRC522
import signal
import time
from os import getenv
import pymssql

server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")    
conn = pymssql.connect("192.168.1.20:1433", "sa", "Tne-project2017", "project")


continue_reading = True
# the uid of the last recognized card
lastcarduid = None
# the time a card uid was last seen
lastcardtime = 0.0

# this long after a card has been noticed, it can be noticed again
# This works because the reader generates repeated notifications of the card
# as it is held agains the reader - faster than this interval.
# The timer is restarted every time the reader generates a uid.
# If you sometimes get repeated new card IDs when holding a card against the
# reader, increase this a little bit.
CARDTIMEOUT = 1.0

# Capture SIGINT for cleanup when the script is aborted
def end_read(signal,frame):
    global continue_reading
    print "Ctrl+C captured, ending read."
    continue_reading = False
    GPIO.cleanup()

# Hook the SIGINT
signal.signal(signal.SIGINT, end_read)

# Create an object of the class MFRC522
MIFAREReader = MFRC522.MFRC522()

## Welcome message
#print "Welcome to the MFRC522 data read example"
#print "Press Ctrl-C to stop."

def gettypename( typecode ):
    typecode &= 0x7F;
    if typecode == 0x00:
        return "MIFARE Ultralight or Ultralight C"
    elif typecode == 0x01:
        return "MIFARE TNP3XXX"
    elif typecode == 0x04:
        return "SAK indicates UID is not complete"
    elif typecode == 0x08:
        return "MIFARE 1KB"
    elif typecode == 0x09:    
        return "MIFARE Mini, 320 bytes"
    elif typecode == 0x10 or typecode == 0x11:
        return "MIFARE Plus"
    elif typecode == 0x18:
        return "MIFARE 4KB"
    elif typecode == 0x20:
        return "PICC compliant with ISO/IEC 14443-4"
    elif typecode == 0x40:
        return "PICC compliant with ISO/IEC 18092 (NFC)"
    return "Unknown type";

# This loop keeps checking for chips. If one is near it will get the UID and authenticate
while continue_reading:
    # Scan for cards    
    (status,TagType) = MIFAREReader.MFRC522_Request(MIFAREReader.PICC_REQIDL)

    # Get the UID of the card
    (status,rawuid) = MIFAREReader.MFRC522_Anticoll()

    # If we have a good read of the UID, process it
    if status == MIFAREReader.MI_OK:
        uid = "{0[0]:02x}{0[1]:02x}{0[2]:02x}{0[3]:02x}".format(rawuid)
#        # Print UID
#        print "Card read UID: "+uid
        newcard = False
        if lastcarduid:
            if lastcarduid != uid or (lastcardtime and time.clock() - lastcardtime >= CARDTIMEOUT):
                newcard = True
        else:
            newcard = True

        if newcard:
#            print "New Card read UID: "+uid
            # String payload = "{\"d\":{\"cardUID\":\"";
            #  payload += UID_HEX;
            #  payload += "\",\"cardtype\":\"";
            #  payload += mfrc522.PICC_GetTypeName(piccType);
            #  payload += "\"}}"; 
            rawcardtype = MIFAREReader.MFRC522_SelectTag(rawuid)
            cardtypename = gettypename( rawcardtype )
            print '{ "cardUID": "'+uid+'","cardtype":"'+cardtypename+'" }'
cursor = conn.cursor()
cursor.execute(
"INSERT INTO Attendence VALUES ('"+str (uid) +"','"+str (cardtypename) +"')"
)
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()
conn.close()
# remember the last card uid recognized
lastcarduid = uid
# remember when it was seen
lastcardtime = time.clock()
Improve this question
2
  • Connect to the database and input said data? And if you want to do it the other way, make a php page with a get page and load that one.
    – Dr_Bunsen
    Mar 17, 2017 at 18:32
  • Hello Zaid, thank you very much for providing additional information to your question. This can be the key for helping you. Please, be so kind to accept an advice. Please make the tour, there you will get some substantial information for asking questions.
    – Joe Platano
    Apr 6, 2017 at 21:14

1 Answer 1

Reset to default
1

Use the pymssql module.

Install with following command:

pip install pymssql

In your script you can use the following code:

from os import getenv
import pymssql

server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")

conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor()
cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

If using MSSQL you can also create a CSV file with python and import it using bulk insert or SSIS. I would recommend the python module - but if you don't have a SQL user with the permission to connect and insert into your table MS SQL tools are a great alternative.

for inserting a variable you can try something like this:

cursor.execute("
    INSERT INTO class
    VALUES(
        1,
        'got rfid',
        '" + str(rfidvalue) + "'
    )
")

Considering your script, the code could be like this. It is also possible to open and clode the DB Connection once, outside of the while loop... both have advantages and disadvantages. #!/usr/bin/env python # -- coding: utf8 -- from os import getenv import pymssql
import RPi.GPIO as GPIO import MFRC522 import signal import time

#set SQL Server vars
server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")    
conn = pymssql.connect(server, user, password, "tempdb")

continue_reading = True
# the uid of the last recognized card
lastcarduid = None
# the time a card uid was last seen
lastcardtime = 0.0

# this long after a card has been noticed, it can be noticed again
# This works because the reader generates repeated notifications of the card
# as it is held agains the reader - faster than this interval.
# The timer is restarted every time the reader generates a uid.
# If you sometimes get repeated new card IDs when holding a card against the
# reader, increase this a little bit.
CARDTIMEOUT = 1.0

# Capture SIGINT for cleanup when the script is aborted
def end_read(signal,frame):
    global continue_reading
    print "Ctrl+C captured, ending read."
    continue_reading = False
    GPIO.cleanup()

# Hook the SIGINT
signal.signal(signal.SIGINT, end_read)

# Create an object of the class MFRC522
MIFAREReader = MFRC522.MFRC522()

## Welcome message
#print "Welcome to the MFRC522 data read example"
#print "Press Ctrl-C to stop."

def gettypename( typecode ):
    typecode &= 0x7F;
    if typecode == 0x00:
        return "MIFARE Ultralight or Ultralight C"
    elif typecode == 0x01:
        return "MIFARE TNP3XXX"
    elif typecode == 0x04:
        return "SAK indicates UID is not complete"
    elif typecode == 0x08:
        return "MIFARE 1KB"
    elif typecode == 0x09:    
        return "MIFARE Mini, 320 bytes"
    elif typecode == 0x10 or typecode == 0x11:
        return "MIFARE Plus"
    elif typecode == 0x18:
        return "MIFARE 4KB"
    elif typecode == 0x20:
        return "PICC compliant with ISO/IEC 14443-4"
    elif typecode == 0x40:
        return "PICC compliant with ISO/IEC 18092 (NFC)"
    return "Unknown type";

# This loop keeps checking for chips. If one is near it will get the UID and authenticate
while continue_reading:
    # Scan for cards    
    (status,TagType) = MIFAREReader.MFRC522_Request(MIFAREReader.PICC_REQIDL)

    # Get the UID of the card
    (status,rawuid) = MIFAREReader.MFRC522_Anticoll()

    # If we have a good read of the UID, process it
    if status == MIFAREReader.MI_OK:
        uid = "{0[0]:02x}{0[1]:02x}{0[2]:02x}{0[3]:02x}".format(rawuid)
#        # Print UID
#        print "Card read UID: "+uid
        newcard = False
        if lastcarduid:
            if lastcarduid != uid or (lastcardtime and time.clock() - lastcardtime >= CARDTIMEOUT):
                newcard = True
        else:
            newcard = True

        if newcard:
#            print "New Card read UID: "+uid
            # String payload = "{\"d\":{\"cardUID\":\"";
            #  payload += UID_HEX;
            #  payload += "\",\"cardtype\":\"";
            #  payload += mfrc522.PICC_GetTypeName(piccType);
            #  payload += "\"}}"; 
            rawcardtype = MIFAREReader.MFRC522_SelectTag(rawuid)
            cardtypename = gettypename( rawcardtype )
            print '{ "cardUID": "'+uid+'","cardtype":"'+cardtypename+'" }'
#
#   HERE IS YOUR INSERT
#

            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO mytable VALUES ('"+str(uid) +"','"+str(cardtypename) +"')"
                )
            # you must call commit() to persist your data if you don't set autocommit to True
            conn.commit()
            conn.close()
        # remember the last card uid recognized
        lastcarduid = uid
        # remember when it was seen
        lastcardtime = time.clock()
Improve this answer
6
  • Thank you Joe, I don't know how to edit the code so that it takes the ID and insert it to table for example : Class table
    – Zaid Al Aqqad
    Apr 2, 2017 at 12:10
  • you said you have a python script that reads the RFID tags. So in this scripts the values will be stored in a data structure. this you can use, convert an insert into the DB.
    – Joe Platano
    Apr 4, 2017 at 16:12
  • Dear Joe, you're a nice man
    – Zaid Al Aqqad
    Apr 6, 2017 at 20:26
  • Tell me where and how to insert the code,, I've insert the RFID script in my question please check it and tell me steps to make it works
    – Zaid Al Aqqad
    Apr 6, 2017 at 20:29
  • Dear Joe, thank you a lot for help ... when i run the module it says : Traceback (most recent call last): File "rfidreader.py", line 102, in <module> "INSERT INTO Attendence VALUES ('"+str (uid) +"','"+str (cardtypename) +"')" NameError: name 'uid' is not defined
    – Zaid Al Aqqad
    Apr 7, 2017 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.