sandysound
Posts: 100
Joined: Thu Sep 14, 2017 6:05 pm

A question about mariadb logging and more

Mon Apr 19, 2021 9:56 pm

I have a script, that turns a pump on and off depending on the water level of the reservoir:

Code: Select all


#!/usr/bin/env python3 
#XKC-Y25-PNP Non Contact Water Level Sensor

from time import sleep
import RPi.GPIO as GPIO

GPIO.setmode(GPIO.BCM)
GPIO.setup(23, GPIO.IN) #level sensor
GPIO.setup(17, GPIO.OUT) #Relay1

def liquidsensor():
    return GPIO.input(23)

def pumpon():
    GPIO.output(17,0)

def pumpoff():
    GPIO.output(17,1)

try:
    while True:
        if liquidsensor() == 1:
            pumpoff()
            print "Tank Full"
            sleep(1)
            break
            GPIO.cleanup()

        else:
            pumpon()
            print "Tank Empty"
            sleep(1)

except KeyboardInterrupt:
    GPIO.cleanup()

This is working fine, but now I would like to log the status of the pump control pin, so I can know how long the pump is running for. Is this possible?
I have been trying to log to a database, but I am seriously confused as to how to implement it within this script.

My attempt:

Code: Select all


#!/usr/bin/env python3
#XKC-Y25-PNP Non Contact Water Level Sensor

import time
import datetime
import mariadb
from time import sleep
import RPi.GPIO as GPIO

#GPIO SETUP
GPIO.setmode(GPIO.BCM)
GPIO.setup(23, GPIO.IN) #level sensor
GPIO.setup(17, GPIO.OUT) #Pump Relay

#db setup
db = mariadb.connect(host="localhost", user="sensor", passwd="password", db="database")
cur = db.cursur()

def timeread():
        stamp = time.time()*1000
        return stamp

def liquidsensor():
    return GPIO.input(23)

def pump():
    return GPIO.output(17)

def pumpon():
    GPIO.output(17,0)
#    return GPIO.output(17)

def pumpoff():
    GPIO.output(17,1)
#    return GPIO.output(17)

stamp = timeread()
liqsensor = liquidsensor()
pumpon = pumpon()
pumpoff = pumpoff() 
sql = ("""INSERT INTO hware (Stamp,liqlevel,Pump) VALUES(%s,%s,%s)""", (stamp,liqsensor,pump))

try:
    while True:
        if liquidsensor() == 1:
            pumpoff()
            print("Tank Full")
            sleep(1)
            pump()
            sleep(1)
            cur.execute(*sql)
            db.commit()
            sleep(1)
            break
            GPIO.cleanup()
            cur.close()
            db.close()

        else:
            pumpon()
            print("Tank Empty")
            sleep(1)
            pump()
            sleep(1)
            cur.execute(*sql)
            db.commit()
            sleep(1)


except KeyboardInterrupt:
    GPIO.cleanup()
    cur.close
    db.close
Its not working, it returns:

Code: Select all

$ python3 watersensetodb.py
watersensetodb.py:13: RuntimeWarning: This channel is already in use, continuing anyway.  Use GPIO.setwarnings(False) to disable warnings.
  GPIO.setup(17, GPIO.OUT) #Relay1
Traceback (most recent call last):
  File "watersensetodb.py", line 20, in <module>
    cur = db.cursur()
AttributeError: 'mariadb.connection' object has no attribute 'cursur'
So I have an issue there, but will this approach work? Or is it better to keep the logging seperate?

I guess it's easier for me to use a different script, but will the pin like being referenced at the same time by two different scripts?

Or should I use another script, that uses a different pin as an input, and is connected to the pump control pin, which I could use to monitor what the pump is doing...?

It's probably even better to monitor the actual power usage of the pump, for ultimate feedback, but that might be a step too far for me at this stage.

It would be efficient to only log when the pump is turned on and then off, essentially capturing its running time. So I don't have to have a script running constantly, can I "trigger" the logging script to start when the water level check script starts? How would I do that?

So many questions, thanks in advance for your insights!

Cheers,

Sandy

User avatar
DougieLawson
Posts: 41177
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

Re: A question about mariadb logging and more

Mon Apr 19, 2021 11:04 pm

How's your table defined?

It's cursor not cursur.

Code: Select all

stamp = timeread()
liqsensor = liquidsensor()
pumpon = pumpon()
pumpoff = pumpoff() 
sql = ("""INSERT INTO hware (Stamp,liqlevel,Pump) VALUES(%s,%s,%s)""", (stamp,liqsensor,pump))
How is that pump variable created?

I'd create the table as

Code: Select all

CREATE TABLE hware (Stamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, liqlevel float, Pump int);
I'd insert a row with

Code: Select all

sql = (""" INSERT INTO hware(liqlevel, Pump) VALUES(%S, %S)""", (liqsensor, pump))
the timestamp column (called Stamp) will look after itself.
Any language using left-hand whitespace for syntax is ridiculous

Any DMs sent on Twitter will be answered next month.
Fake doctors - are all on my foes list.

Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

sandysound
Posts: 100
Joined: Thu Sep 14, 2017 6:05 pm

Re: A question about mariadb logging and more

Tue Apr 20, 2021 9:34 pm

Thanks Dougie! Your a Leg End!

I have been reading up on edge detection and callbacks. Perhaps this is the best way to achieve what I am trying to do?
Am I correct in thinking that doing it this way it can run in the background (setup before the loop), then while the loop is happening can detect the status changes and log them to the database without affecting the running loop?
Cheers,
Sandy

User avatar
DougieLawson
Posts: 41177
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

Re: A question about mariadb logging and more

Wed Apr 21, 2021 12:09 am

Post all of your code.
Any language using left-hand whitespace for syntax is ridiculous

Any DMs sent on Twitter will be answered next month.
Fake doctors - are all on my foes list.

Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

Return to “Other programming languages”