How can I insert rows into Oracle SQL databse from a raspberry pi using python?

Question

I'm currently working on a personal project which involves:

• a Raspberry Pi Model b with an RFID reader/writer module
• an Oracle SQL database (11g)
• Python Scripting

Here's what I'm trying to-do: I need the Raspberry Pi to send the UID (unique identification number) which I've read from the RFID tag and insert a row into my SQL database with the UID. The information read from the RFID tag is a bunch of numbers which can be stored as a String.

I am currently able to read tags and print the UID onto the screen. The reading is being processed by a Python script which I've modified from a source code I found online.

I am struggling to send the UID to my SQL database. I have looked into cx_Oracle but it seems that it doesn't exist for the ARM Architecture which the Raspberry Pi uses. I have also looked into pyodbc but I can't seem to get that working either. Here is my Python script which I am using to read the RFID tags.

Extra Info : I am a noob when it comes to Python, I have a background in C, Java, JDBC and Oracle SQL. I understand how JDBC connections work but I can't seem to implement the same theory in Python. To anyone that is a pro at Python, please feel free to modify my code below to access an Oracle SQL database. The credentials to my database is as follows:

• Address: localhost
• Port: 1521
• User: student
• Password: test
• Scheme: STUDENT

• Table Name: EMP

  #!/usr/bin/env python
  # -*- coding: utf8 -*-
  
  import RPi.GPIO as GPIO
  import MFRC522
  import signal
  continue_reading = True
  
  # 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."
  
  # 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)
  
  # If a card is found
  if status == MIFAREReader.MI_OK:
  print "Card detected"
  
  # Get the UID of the card
  (status,uid) = MIFAREReader.MFRC522_Anticoll()
  
  # If we have the UID, continue
  if status == MIFAREReader.MI_OK:
  
  # Print UID
  print "Card read UID: "+str(uid[0])+","+str(uid[1])+","+str(uid[2])+","+str(uid[3])
  

Answer 1

To access an oracle database then you need client libraries on the Rpi.

As you seem to have discovered already, these don't exist for the ARM Linux architecture, and certainly not for the Raspian O/S.

You could look into using an ODBC driver for RPi to see if you can get this to work.

This Post shows what somebody has tried this already.

And I also found this article, where somebody claims to have gotten this working for Rpi via PHP. The company who's driver he used (which he works for) also have a python driver. However, looking on their website the software is commercial (free trial available) and does not claim to support ARM Linux. Although you could try with their free trial.

Here is a list of other ODBC drivers which have python drivers available.

Although I have never tried it or have experience in this area, there is a way to expose SOAP web services from the oracle database. So you could try his approach instead, so the DB exposes the services and then you access the web service from python. But as I have not done it I don't know about any limitation or if this would actually work for you.

Otherwise, you would need to take a different approach and use some kind of middle ware between the Rpi and the DB to receive the data and insert it. Or use another database!