Thursday 26 December 2019

BirdBox entrance counter v3: postgres / docker / python

This post describes the software side of my v3.0 birdbox entrance hole implementation, refer to part 1 related post  for the physical build details.  To log activity, I used a basic Postgres database, which (to continue an ongoing theme) is run in a docker container to simplify setup.  I updated the python logging script to log to a database rather than a text file, and corrected some previous errors along the way...

The existing birdbox setup remains the same, its a Raspberry Pi ZeroW running Pikrellcam for motion detection and auto capture.  The LED illumination, IR cut and RasPi IR camera module etc remain unchanged.

Like before.. but with entrance hole activity logged to a database

v3.0 entrance hole counter updates...
The two other boxes that I have 'in the wild' with entrance hole counters log partial and full 'in' vs 'out' events activity to a text file.  This updates (v3.0) to a Postgres database instead.  Using a database rather than a text file opens up more possibilities, in particular allowing collected data to be accessed remotely.  I plan to pull data off for analysis on a separate machine, for example to populate activity plots on my grafana nestbox CCTV-sytle dashboard.  Having the activity data sitting in a database makes this much easier.  I've used a 'dockerised' version of postgres as its simpler to setup than installing it from scratch.

This post will cover
1) Docker Installation
2) Docker-Compose Installation
3) Add Postgres container to  Docker installation
4) Creation of a database to log activity
5) The activity logging script itself

I use a base image of Raspbian 'Lite' to start off.  This takes up less space on the SD card but may need more things installing along the way than than 'full-fat' version.  You can follow these setup steps on a Raspberry Pi ZeroW, but be prepared to wait a long time for some of them (especially for Docker Compose).  I use a spare Pi 3 or 4 for the build (much quicker) and transfer the sd card over to a Zero W when its done.  The installtion Pi is assumed to be networked. There's about a zillion how-tos online for that.

1) Docker installation

Think of Docker as an empty applications box.  Once Docker is running there are many many pre-configured applications than can be (fairly) effortlessly run without having to do lots of fiddly setup...

This is mostly lifted from these sites, with my comments added:

Install commands (I left my hashed-out comments in)
sudo apt-get install apt-transport-https ca-certificates software-properties-common -y
curl -fsSL get.docker.com -o get-docker.sh && sh get-docker.sh
sudo groupadd docker  #not actually required, groupadd: group 'docker' already exists
sudo gpasswd -a $USER docker
newgrp docker #avoids reboot
docker run hello-world  #test that it works

If it all works, you get the following message:
Hello from Docker!
This message shows that your installation appears to be working correctly. 
Run Postgres on Docker
Details here: https://dev.to/rohansawant/spin-up-a-postgres-docker-container-on-the-raspberry-pi-in-2-minutes-2klo and https://dev.to/rohansawant/installing-docker-and-docker-compose-on-the-raspberry-pi-in-5-simple-steps-3mgl

2) Docker-Compose Installation

Install proper dependencies:
sudo apt-get install libffi-dev libssl-dev
sudo apt-get install -y python python-pip  #?had issue with python 2.7.13 not being compatible with the version of docker-compose below
#edit 
sudo apt-get install -y python3 python3-pip

sudo apt-get remove python-configparser

Install Docker Compose

#sudo pip install docker-compose #takes a long time on a Pi ZeroW
# edit 12/2/21
sudo pip3 install docker-compose
#see details here

3) Add a Postgres database container to Docker:

See https://github.com/CT83/Raspberry-Pi-PostGres-Docker-Compose
Note, MySQL is another option, there are MySQL images available on Docker, or maybe install from scratch.  I went with Postgres as I like pgAdmin as a sql interpretor.

# clone the repo which contains the Compose file: 
git clone https://github.com/CT83/Raspberry-Pi-PostGres-Docker-Compose.git
# Up the container:  
cd Raspberry-Pi-PostGres-Docker-Compose
sudo docker-compose up --build -d
# note this returns an error for a file it cant find:
# ERROR: Couldn't find env file: /home/pi/Raspberry-Pi-PostGres-Docker-Compose/.env
# fix this by making a dummy file first:
touch .env  
# then re-run
sudo docker-compose up --build -d

All being well, postgres database should be running in a docker container on the host Pi, on the default port (5432).   As-is the default user is postgres, password is password@7979  You can change these in the docker-compose.yml.

pgAdmin is a handy sql interpretor/tool for managing local or remote postgres databases.  Install it on a networked PC and you should be able to remotely connect to the postgres instance running on the Pi created above.  See https://www.pgadmin.org/.  Google for loads of tutorials around this bit.

4) Creation of a database to log activity

Within pgAdmin (on a remote PC), this sql command should make the necessary database and table within it

CREATE DATABASE db_activity
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'C.UTF-8'
    LC_CTYPE = 'C.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

CREATE TABLE public.entrance_log
(
    sensor integer,
    state integer,
    event_time timestamp without time zone
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.entrance_log
    OWNER to postgres; 

I'm starting basic, so there's only only one table, for the entrance hole activity.

5) The activity logging script

This is written in Python.  Need to install some extra python packages first:
#install the postgres/sql connector psycopg2
pip install psycopg2  #it does not work properly, need to run the following command first:
sudo apt-get install libpq-dev #(suggestion here: https://github.com/psycopg/psycopg2/issues/699)

The following python script creates a entry in the db_activity database in a table called entrance_log.

A simple bird  'head bob in' event breaks the outer beam[1,0], then the inner beam [2,0],  then withdraws: Inner beam whole again [2,1], then outer beam whole [1,1].
Note that in this example, Outer beam = 1, Inner beam = 2, Broken = 0, Whole = 1

Database entry
This above event as recorded in postgres database

I added some error checking code to stop the script from crashing out if a beam is triggered and it cant see the postgres database for whatever reason - it will carry on merrily logging to file instead.
I have left in the 'log to file' functionality as a backup, I'll may remove that eventually, its there as a backup.

The above event as logged to a txt file
Input comes from two GPIO input pins from the entrance hole sensor: pin 22 (outer beam) and pin 23 (inner beam).  Other pins are available....

HoleSensor.py
import RPi.GPIO as GPIO

from time import sleep

#postgres error handling from here: https://kb.objectrocket.com/postgresql/python-error-handling-with-the-psycopg2-postgresql-adapter-645
#postgres add record adapted from here: https://pynative.com/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations/

# import sys to get more detailed Python exception info
import sys
# import the connect library for psycopg2
from psycopg2 import connect
# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors
# import the psycopg2 library's __version__ string
from psycopg2 import __version__ as psycopg2_version

from datetime import datetime

# Change log
# 16/09/19 updated for zerocam 7
# 14/12/19  modified to write activity to a local postgres database, database implemented in docker
#           postgres error handler added so the script does not crash out if the database isnt available for whatever reason

GPIO.setmode(GPIO.BCM)          #use BCM pin numbering system
GPIO.setwarnings(False)

whichBirdcam = 'zerocam6'

#file used to log entrance actions
EntrancelogFile='/home/pi/zerocam6/logs/'+zerocam6_birdlog.txt'
OpenEntrancelogFile= open(EntrancelogFile,  'a', 0)

#function to return the current time, formatted as
# e.g. 13 Jun 2013 :: 572
def getFormattedTime():
    now = datetime.now()
    return now.strftime("%d %b %Y %H:%M:%S.") + str(int(round(now.microsecond/1000.0)))
    #note that this does not work correctly, as rounding 040 will give 40, but this modified function is only meant as a backup for the local file log

# ********* postgres stuff

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception(err):
    # get details about the exception
    err_type, err_obj, traceback = sys.exc_info()

    # get the line number when exception occurred
    line_num = traceback.tb_lineno

    # print the connect() error
    print '\n' "psycopg2 ERROR:", err, "on line number:", line_num
    print "psycopg2 traceback:", traceback, "-- type:", err_type

    # psycopg2 extensions.Diagnostics object attribute
    print '\n' "extensions.Diagnostics:", err.diag

    # print the pgcode and pgerror exceptions
    print "pgerror:", err.pgerror
    print "pgcode:", err.pgcode, '\n'

def logEntranceEventPostgres(sensor, state):
    dt = datetime.now()  #set the census datetime for the event

    try:

        conn = connect(
            dbname = "db_activity",
            user = "postgres",
            host = "127.0.0.1",
            password = "password@7979")

    except OperationalError as err:
        # pass exception to function
        print_psycopg2_exception(err)

        # set the connection to 'None' in case of error
        conn = None

    # if the connection was successful
    if conn != None:

        # declare a cursor object from the connection
        cursor = conn.cursor()
        print "cursor object:", cursor, '\n'

        #dt = datetime.now()
        cursor.execute('INSERT INTO entrance_log (sensor,state,event_time) VALUES (%s,%s,%s)', (sensor,state,dt,))
        conn.commit()
        count = cursor.rowcount
        print count, "record(s) inserted successfully into entrance_log table"

        # close the cursor object to avoid memory leaks
        cursor.close()

        # close the connection object also
        conn.close()

    #append the same timestamp data to file for good measure
    #formattedTime_old = dt.strftime("%d %b %Y %H:%M:%S.") + str(int(round(dt.microsecond/1000.0)))  #note that this is NOT correct since it 'rounds' a 041ms to 41
    formattedTime = dt.strftime('%d %b %Y %H:%M:%S.%f')[:-3]  #this works correctly
    #write to file until we figure out if the same data is being captured
    OpenEntrancelogFile.write(str(sensor) + "," + str(state) + "," + formattedTime + "\n")

#setup GPIOs
detect_OUTER = 22 #6    #set GPIO pin for Outer photransducer (input)
detect_INNER = 23 #12    #set GPIO pin for Inner photransducer (input)

print 'detect_OUTER = ' + str(detect_OUTER)
print 'detect_INNER = ' + str(detect_INNER)

#Constants
#OUTER_BEAM = 1
#INNER_BEAM = 2

#WHOLE = 1
#BROKEN = 0

# setup GPIO pins:
GPIO.setup(detect_OUTER, GPIO.IN)   #set Outer GPIO Phototransducer as input
GPIO.setup(detect_INNER, GPIO.IN)   #set Inner GPIO Phototransducer as input


#indicate the point the program started in the log
OpenEntrancelogFile.write("### recordBird_v3 starting up at:" + getFormattedTime() + "\n")
print "============================================"
print whichBirdcam.upper() + ": Starting up entrance hole counter script..."
sleep (0.5)

#Set initial state of WasBroken for both beams:
OUTER_WasBroken = False
INNER_WasBroken = False

# LED status check
# LEDstate= GPIO.input(detect_INNER)

print ""
print "detect_OUTER status = " + str(GPIO.input(detect_OUTER))
print "detect_INNER status = " + str(GPIO.input(detect_INNER))
print ""


#When the detector          'sees' IR led, the detector pin is 0/LOW/False
#When the detector does not 'see ' IR led, the detector pin is 1/HIGH/True

def checkStatus():
    if GPIO.input(detect_OUTER):  #if OUTER detector does not see IR led, print error, GPIO.input = HIGH
        print "OUTER beam detect failure!, status = " +str(GPIO.input(detect_OUTER))
        #quit()
    else:
        print "OUTER beam detect - passed :) | Status = "+str(GPIO.input(detect_OUTER))

    if GPIO.input(detect_INNER):  #if INNER detector does not see IR led, print error, GPIO.input = HIGH
        print "INNER beam detect failure!, status = " +str(GPIO.input(detect_INNER))
        #quit()
    else:
        print "INNER beam detect - passed :) | Status = "+str(GPIO.input(detect_INNER))
        print "============================================"
        print ""

def status2():

    print "============================================"
    print "OUTER_IsWhole = "+str(OUTER_IsWhole)
    print "OUTER_WasBroken = "+str(OUTER_WasBroken)
    print ""
    print "INNER_IsWhole = "+str(INNER_IsWhole)
    print "INNER_WasBroken = "+str(INNER_WasBroken)
    print "============================================"
    print ""

checkStatus()

# (x,y)
#  x=beam   (1=Outer,2=inner)
#  y=state  (1=Whole,0=Broken)

while (True):
    OUTER_IsWhole = (GPIO.input(detect_OUTER) == 0)  #read current state of beam
    INNER_IsWhole = (GPIO.input(detect_INNER) == 0)  #read current state of beam
    #print ""
    #print "INNER BeamIsWhole = " + str(INNER_IsWhole)
    #print "OUTER BeamIsWhole = " + str(OUTER_IsWhole)

    sleep(0.05)

    if (not OUTER_IsWhole and not OUTER_WasBroken): #if OUTER beam is broken [FALSE], and OUTER_WasBroken=FALSE (ie default value)
        #GPIO.output(greenLED, 1) #greenLED output to HIGH
        OUTER_WasBroken = True
        print "(OUTER,Broken)"+ getFormattedTime()
        status2()
        #logEntranceEvent(1,0)
        logEntranceEventPostgres(1,0)

    if (OUTER_IsWhole and OUTER_WasBroken): #if Outer beam is whole [TRUE] and OUTER_WasBroken=TRUE
        #GPIO.output(greenLED, 0) #greenLED output to LOW
        OUTER_WasBroken = False
        print "(OUTER,Whole)"+ getFormattedTime()
        status2()
        #logEntranceEvent(1,1)
        logEntranceEventPostgres(1,1)

    if (not INNER_IsWhole and not INNER_WasBroken): #if INNER beam is broken [FALSE], and INNER_WasBroken=FALSE (ie default value)
        #GPIO.output(redLED, 1) #redLED output to HIGH
        INNER_WasBroken = True
        print "(INNER,Broken)"+ getFormattedTime()
        status2()
        #logEntranceEvent(2,0)
        logEntranceEventPostgres(2,0)

    if (INNER_IsWhole and INNER_WasBroken): #if INNER beam is whole [TRUE] and INNER_WasBroken=TRUE
        #GPIO.output(redLED, 0) #redLED output to LOW
        INNER_WasBroken = False
        print "(INNER,Whole)"+ getFormattedTime()
        status2()
        #logEntranceEvent(2,1)
        logEntranceEventPostgres(2,1)

GPIO.cleanup()

To make this script run in the background, add an entry to /etc/rc.local as follows:

# open a text editor in the command window
sudo nano /etc/rc.local


# after this section in the rc.local file 
_IP=$(hostname -I) || true
if [ "$_IP" ]; then
  printf "My IP address is %s\n" "$_IP"
fi

# add the following
python /home/pi/zerocam6/HoleSensor.py &


No comments:

Post a Comment