Raspberry Pi MYSQL Database Exception

Question

I am looking to post to my database on my Raspberry Pi, the code below works fine provided I enter in the temperature and humidity using 20.0 or similar but if I use either of the methods such as getTemperature() the code returns the rolling back error and does not explain to me where it's going wrong. I am not sure why it is doing this as NOW() is also a method and works fine.

Anyone any ideas on how to solve this and what might be the best way at going about it? I'm very new to Python so I am still learning but i've tried all the examples I can find and can't seem to get this to work. As I said it works if I enter the data myself into the statement instead of it trying to get it from either method.

I also created the DB using the following:

CREATE TABLE ca1data (date DATE, time TIME, humidity NUMERIC, temperature NUMERIC, buttonpress TEXT);

import fcntl,socket,struct,dweepy,time,platform,random,grovepi,math
import MySQLdb

db=MySQLdb.connect("localhost", "monitor", "password", "temps")
curs=db.cursor()

sensor = 4 

blue = 0    
white = 1 

def getTemp():
    try:
        # This example uses the blue colored sensor. 
        # The first parameter is the port, the second parameter is the type of sensor.
        [temp,humidity] = grovepi.dht(sensor, blue)  
        if math.isnan(temp) == False and math.isnan(humidity) == False:
            return temp

    except IOError:
        return "Error"

def getHumidity(): 
    try:
        # This example uses the blue colored sensor. 
        # The first parameter is the port, the second parameter is the type of sensor.
        [temp,humidity] = grovepi.dht(sensor, blue)  
        if math.isnan(temp) == False and math.isnan(humidity) == False:
            return humidity

    except IOError:
        return "Error"
def getOS():
    return platform.platform()

def post(dic):
    thing = "CurtisBoylanTempCA1" 
    print dweepy.dweet_for(thing, dic)

def getReadings () :
    dict = {}
    dict ["temperature"] = getTemp() ; 
    dict ["humidity"] = getHumidity ()
    return dict

while True:
    dict = getReadings(); 
    post(dict)
    try:
        curs.execute ("""INSERT INTO ca1data 
            values(CURRENT_DATE(), NOW(), getHumidity(), getTemp(), 'ON')""")

        db.commit()
        print "Data committed"

    except:
        print "Error: the database is being rolled back"
        db.rollback()

    time.sleep(5)

Stephen · Accepted Answer · 2018-11-14 17:30:37Z

1

getHumidity and getTemp are functions defined in your Python script whereas CURRENT_DATE and NOW are functions that SQL knows how to interpret. To accomplish what you want, you would execute the functions in your script and insert them into the query as values like so:

curs.execute ("""INSERT INTO ca1data 
            values(CURRENT_DATE(), NOW(), %f, %f, 'ON')""" % (getHumidity(), getTemp()))

answered Nov 14, 2018 at 17:30

Stephen

1,53817 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Raspberry Pi MYSQL Database Exception

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related