Using GPIO to update a MySQL table

[notbobRSA]

Hi there,

Any help would be much appreciated!

I'm working on a project where I'm listening to 8 pins (7,11,12,13,15,16,18,22) for pulses (falling edges on the pins which have been pulled up).

Currently I'm using threaded callbacks (see code) to run the following SQL statement which just inserts a row into a table each time there is a pulse.

Code: Select all

"""INSERT INTO t_pulses
		(pulse_id, pin_number, pulse_timestamp)
		VALUES
		({}, {}, NOW());""".format(count, channel)

To test the code, I'm currently running it remotely (ssh) and shorting each pin to simulate a falling edge.

My Problem:

My code seems to escape with the following error 70% of the time, whereas it works the other 30% of the time.

It runs the callback several times (more times the longer I hold the pin shorted) and then escapes with an error.

The error message that is printed is as follows:

<class '_mysql_exceptions.OperationalError'>

My table:
pulse_id int(11) Not null default null
pin_number varchar(250) Not null default null
pulse_timestamp datetime Not null default null

My Code:

Code: Select all

#testing threaded callbacks

import RPi.GPIO as GPIO
import MySQLdb
import sys
from time import sleep
global count
global db
global cursor

def test(channel):
	global count
	global db
	global cursor
	count = count + 1

	SQL = """ INSERT INTO t_pulses
		(pulse_id, pin_number, pulse_timestamp)
		VALUES
		({}, {}, NOW());""".format(count, channel)

	print "executing {}\n".format(SQL)
	try:	
		cursor.execute(SQL)
		db.commit()
	except:
		err = sys.exc_info()[0]
		print err
		db.rollback()
		
def setupPin(pin):
	GPIO.setup(pin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
	GPIO.add_event_detect(pin, GPIO.FALLING, callback=test, bouncetime=300)

#--------------------=============== MAIN ==============---------------
count = 0

#db stuff
try:
	db = MySQLdb.connect("localhost", "guest", "guest", "TestDB")
	cursor = db.cursor()

except:
	err = sys.exc_info()[0]
	print err
#
GPIO.setmode(GPIO.BOARD)

setupPin(7)
setupPin(11)
setupPin(12)
setupPin(13)
setupPin(15)
setupPin(16)
setupPin(18)
setupPin(22)

raw_input("press any button when u want to terminate")
GPIO.cleanup()

db.close()

[joan]

Surely it should only insert one record each time you short the pin? It shouldn't detect another pulse until you remove the short.

[FLYFISH TECHNOLOGIES]

Hi,

(Not directly related to the question)
You should optimize the db table and code. Set:
- pulse_id to autoincrement & unsigned & primary key and
- pulse_timestamp to default timestamp.
Consequently, you need each time to insert just pin_number (which is not varchar(250) type, but should be short(2)).


Best wishes, Ivan Zilic.

[DougieLawson]

Your strange output
<class '_mysql_exceptions.OperationalError'>

Comes from

Code: Select all

 except:
    err = sys.exc_info()[0]
    print err

You're not catching the exception correctly.

Code: Select all

from _mysql_exceptions import (OperationalError, NotSupportedError,
        ProgrammingError)
...
 except OperationalError m:
   print m[0]

Gives me the numeric failure code. There's probably more to handling OperationalError (but I've not found any coherent documentation for it).

[notbobRSA]

@DougieLawson

Thanks for the tips on exception catching, your code has a syntax error (missing a comma on the except line: )

Code: Select all

...
 except OperationalError, m:
   print m[0]

I instead used the MySQLdb.Error import as follows to give comprehensive error messages.

Code: Select all

try:
	db = MySQLdb.connect("localhost", "guest", "guest", "TestDB")
	cursor = db.cursor()
except MySQLdb.Error, e:
		try:
			print "MySQL Error [%d]: %s" %(e.args[0], e.args[1])
		except IndexError:
			print "MySQL Error: %s" % str(e)

And found that I was forgetting to format my INSERT SQL statement with inverted commas around the text values.

So silly.

@joan

I was also expecting only one callback to be run per short.
This is now my primary concern.
Here is my updated code, can anyone tell me why my callbackFunction is being called multiple times when I am shorting my pins?

Code: Select all

#--------------=== Imports ===-------------------------
import RPi.GPIO as GPIO
import MySQLdb
import sys
from threading import Thread
import time
from time import sleep 
from collections import deque

#----------------===Functions===------------------------
def callbackFunction(channel):
	
	global number_of_statements_in_SQLqueue
	number_of_statements_in_SQLqueue = number_of_statements_in_SQLqueue + 1
	try:
		meter_id = pinMeterDictionary[channel]
	except:
		print """We have failed """
	pulseID = generatePulseID()
	
	SQL = """INSERT INTO t_pulses VALUES ({}, "{}", NOW());""".format(pulseID, meter_id)
	try:
		SQLqueue.append(SQL)
	except:
		print "Failed to add the following SQL statement to the queue:\n{}".format(SQL)
	
	print "\nappended {} for pulse {} on {}".format(SQL, pulseID, meter_id)

def executeSQL(SQL):
	try:	
		cursor.execute(SQL)
		db.commit()
	except MySQLdb.Error, e:
		try:
			print "MySQL Error [%d]: %s  {from your executeSQL method}" %(e.args[0], e.args[1])
		except IndexError:
			print "MySQL Error: %s" % str(e)
		db.rollback()
		
def setupPin(pin):
	GPIO.setup(pin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
	GPIO.add_event_detect(pin, GPIO.FALLING, callback=callbackFunction, bouncetime=300)
	
def generatePulseID():
	t = time.struct_time(time.localtime())
	pulseID = (t.tm_min/30) + (t.tm_hour*2) + ((t.tm_yday-1)*48) + (t.tm_year-2010)*17520
	return pulseID

def getPinMeterDictionary():
	global pin_meter_dictionary
	f = open('RPi_Water_Meter_Setup.txt', 'r')
	pin_meter_dictionary = {}
	currentPin = 0
	for line in f:
		if line[:4] == "Pin_":
			currentPin = int(line[4:6])
		elif line[:6] == "Meter_":
			meter_no = line[11:].lstrip().replace('\n', '')
			pin_meter_dictionary[currentPin] = meter_no
	return pin_meter_dictionary
	
class BackgroundSQLThread(Thread):
	def run(self):
		global SQLqueue
		global number_of_statements_in_SQLqueue
		while 1:
			sleep(60)
			for statement in range(number_of_statements_in_SQLqueue):
				if number_of_statements_in_SQLqueue > 0:
					executeSQL(SQLqueue.popleft())
					sleep(0.5)
					number_of_statements_in_SQLqueue = number_of_statements_in_SQLqueue - 1
					
#--------------------=============== MAIN ==============---------------

#---------------===Global Variables===------------------
global number_of_statements_in_SQLqueue
global db
global cursor
global SQLqueue
global pin_meter_dictionary

#-------------===Variables ===-------------------
count = 0
number_of_statements_in_SQLqueue = 0

#-------------===Database stuff===----------------
try:
	db = MySQLdb.connect("localhost", "guest", "guest", "TestDB")
	cursor = db.cursor()
except MySQLdb.Error, e:
		try:
			print "MySQL Error [%d]: %s  {from your connect method}" %(e.args[0], e.args[1])
		except IndexError:
			print "MySQL Error: %s" % str(e)
	
#-------------===GPIO Setup===-----------------
GPIO.setmode(GPIO.BOARD)
setupPin(7)
setupPin(11)
setupPin(12)
setupPin(13)
setupPin(15)
setupPin(16)
setupPin(18)
setupPin(22)

#-------------===Data Structures===-------------
SQLqueue = deque()
pinMeterDictionary = getPinMeterDictionary()

#-----------===SQL Thread===-------------------
sqlthread = BackgroundSQLThread()
sqlthread.start()

#-----------===Program Interupt===---------------
inp = raw_input("press q you want to quit")
while 1:
	inp = raw_input()
	if inp=='q':
		GPIO.cleanup()
		db.close()
		sys.exit()
	
#------------===Clean up===---------------------
GPIO.cleanup()
db.close()

I've updated the code for readability etc.

[joan]

No idea why you'd be getting multiple callbacks. Perhaps the contact is not firm enough and it's bouncing.

Out of interest I tried your code (but amended to use my own library). I inserted a 100,000 records or so. I didn't get any MySQL errors.

Code: Select all

#!/usr/bin/python

import sys
import MySQLdb

import pigpio

global count
global db
global cursor

def test(gpio, level, tick):
   global count
   global db
   global cursor

   count += 1

   SQL = """ INSERT INTO t_pulses (pulse_id, pin_number, pulse_timestamp)
      VALUES ({}, {}, {});""".format(count, gpio, tick)

   print SQL

   try:   
      cursor.execute(SQL)
      db.commit()
   except:
      print "Unexpected error:", sys.exc_info()[0]
      db.rollback()
      
def setup_gpio(gpio):
   pigpio.set_mode(gpio, pigpio.INPUT)
   pigpio.set_pull_up_down(gpio, pigpio.PUD_UP)
   pigpio.callback(gpio, pigpio.FALLING_EDGE, test)


#--------------------=============== MAIN ==============---------------
count = 0

try:
   db = MySQLdb.connect("localhost", "guest", "guest", "pi")
   cursor = db.cursor()

except:
   print "Unexpected error:", sys.exc_info()[0]
   raise

pigpio.start()

setup_gpio(4)
setup_gpio(7)
setup_gpio(8)
setup_gpio(9)
setup_gpio(10)
setup_gpio(11)
setup_gpio(22)
setup_gpio(23)

raw_input("press RETURN to finish")

pigpio.stop()

db.close()

[notbobRSA]

Awesome, thanks for testing my code

It is probably the bounce from a loose connection, I'll be setting up a test bed in the next week with firm connections and I'll report back.

Thank you to everyone for their help and comments, happy holidays!

[AndrejB]

Hello,

Pretty old theme. But maybe someone can help. How to modify code above to only write GPIO pin state (logic 0 or 1) to MySQL database.

Many Thanks

Andrej

[B.Goode]

Welcome to the Raspberry Pi forums.

Hello,

Pretty old theme. But maybe someone can help. How to modify code above to only write GPIO pin state (logic 0 or 1) to MySQL database.

Many Thanks

Andrej

Maybe a fresh topic outlining your own difficulty would be more productive than resurrecting a 5-years dead topic?

However, the heart of the matter appears to be:

Code: Select all

   SQL = """ INSERT INTO t_pulses (pulse_id, pin_number, pulse_timestamp)
      VALUES ({}, {}, {});""".format(count, gpio, tick)

Your requirement could be met by defining a database table with a single column (field) and modifying the INSERT statement correspondingly.