Send RFID Tag ID to MSSQL on a PC through Raspberry Pi?

Question

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()

score 1 · Answer 1 · edited Feb 25 '25 at 15:54

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 MS SQL 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 close 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 = &quot;{0[0]:02x}{0[1]:02x}{0[2]:02x}{0[3]:02x}&quot;.format(rawuid)

# Print UID
print "Card read UID: "+uid
    newcard = False
    if lastcarduid:
        if lastcarduid != uid or (lastcardtime and time.clock() - lastcardtime &gt;= CARDTIMEOUT):
            newcard = True
    else:
        newcard = True

    if newcard:

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


HERE IS YOUR INSERT

        cursor = conn.cursor()
        cursor.execute(
            &quot;INSERT INTO mytable VALUES ('&quot;+str(uid) +&quot;','&quot;+str(cardtypename) +&quot;')&quot;
            )
        # 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()

Send RFID Tag ID to MSSQL on a PC through Raspberry Pi?

1 Answers1

you must call commit() to persist your data if you don't set autocommit to True

the uid of the last recognized card

the time a card uid was last seen

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.

Capture SIGINT for cleanup when the script is aborted

Hook the SIGINT

Create an object of the class MFRC522

Welcome message

This loop keeps checking for chips. If one is near it will get the UID and authenticate

# Print UID

print "Card read UID: "+uid

print "New Card read UID: "+uid

HERE IS YOUR INSERT