sf_lactor/DB.py

#!/opt/local/bin/python3.0

import sqlite3 as sqlite
from sys import exit
from datetime import datetime
import os, shutil

def logtest (level, msg):
  print (msg)

def errlogtest (msg):
  print (msg)

def connectdb (dbfile):
  """
  Create connection to database
  return DB Object
  user gets information if failure
  """
  print ("Verbindung zu %s herstellen" %dbfile)
  try:
    db = sqlite.connect (dbfile, check_same_thread=False)
    return db
  except:
    print ("Konnte DB Verbindung nicht herstellen")
    exit (1)

class DB:
  # Database Stuff
  # --------------
  def __init__ (self, dbfile, talogfile, log, errlog):
    self.db = connectdb (dbfile)
    self.talogfile = talogfile
    self.log = log
    self.errlog = errlog

  def executesql (self, dbstring, arguments = ()):
    """
    Fuehre den uebergebenen SQL-Befehl aus und liefere ein Cursor-Objekt
    zurueck.
    """
    try:
      cursor = self.db.cursor ()
      cursor.execute (dbstring, arguments)
    except sqlite.Error as e:
      print ("An error occurred:", e.args[0])
      raise Warning
    except OperationalError: #, msg:
      print ("Datenbankausfuehrung fehlgeschlagen. Ausgeloest durch: " + dbstring)
      raise Warning
    except IntegrityError: #, msg:
      print ("Doppelter Eintrag. Ausgeloest durch: " + dbstring)
      raise Warning
    except ProgrammingError: #, msg:
      print ("Fehlerhaftes SQL. Ausgeloest durch: " + dbstring)
      raise Warning
    except Warning: #, msg:
      pass

    #Build Transaction Log
    if (dbstring.find ('DELETE') >= 0) or\
       (dbstring.find ('INSERT') >= 0) or\
       (dbstring.find ('UPDATE') >= 0):
      file = open (self.talogfile, "a")
      dt = datetime.now ()
      file.write ("%4d-%02d-%02d %02d:%02d:%02d.%06d: " %(dt.year, dt.month, dt.day, dt.hour, dt.minute, dt.second, dt.microsecond))
      file.write (dbstring + ";\n")
      file.close ()

    self.db.commit ()
    return cursor



  # Tierddaten allgemein
  #------------------

  def getanimals (self):
    """
    Return all Data of animals
    """
    try:
      cursor = self.executesql ("SELECT animalnr, earmark, rfid, tsforbidstart, tsforbidend FROM animals;")
    except:
      return None
   
    res = cursor.fetchall ()
    return res


  def addanimal (self, data):
    """
    Add animal
    """
    try:
      cursor = self.executesql ("INSERT OR REPLACE INTO animals (animalnr, earmark, rfid) VALUES ('%(animalnr)d', '%(earmark)s', '%(rfid)s');" % data) 
    except:
      print ("Couldnt insert into animals")
      raise Warning


  def updatemilkingforbid (self, data):
    """
    Update Milking Forbid
    """
    try:
      cursor = self.executesql ("UPDATE animals set tsforbidstart = '%(tsforbidstart)s', tsforbidend = '%(tsforbidend)s' WHERE animalnr = '%(animalnr)d';" % data) 
    except:
      print ("Couldnt update milkforbid information")
      raise Warning


  def getanimal (self, animalnr):
    """
    Get Animaldata
    """
    try:
      cursor = self.executesql ("SELECT animalnr, earmark, rfid, tsforbidstart, tsforbidend FROM animals WHERE animalnr = '%s';" % animalnr) 
    except:
      print ("Couldnt read from animals")
    
    return cursor.fetchall ()


  def getanimalnrbyrfid (self, rfid):
    """
    Get Animalnr by RFID
    """
    try:
      cursor = self.executesql ("SELECT animalnr FROM animals WHERE rfid = '%s';" % rfid) 
    except:
      print ("Couldnt read from animals")
    
    return cursor.fetchone ()


  def addmilkdata (self, data):
    """
    Add Milkingdata
    """
    try:
      cursor = self.executesql ("INSERT INTO milkdata (ts, animalnr, ammount, time) VALUES ('%(ts)s', '%(animalnr)d', '%(ammount)s', '%(time)s');" % data) 
    except:
      print ("Couldnt insert into milkdata")
      raise Warning


  def updatemilkdata (self, data):
    """
    Add Milkingdata
    """
    try:
      cursor = self.executesql ("UPDATE milkdata SET ammount = '%(ammount)s', time = '%(time)s' WHERE ts='%(ts)s' AND  animalnr = '%(animalnr)d';" % data) 
    except:
      print ("Couldnt update milkdata")
      raise Warning


  def getallmilkdata (self):
    """
    Return all Milkingdata
    """
    try:
      cursor = self.executesql ("SELECT * FROM milkdata;")
    except:
      return None
   
    res = cursor.fetchall ()
    return res


  def getlastmilkdatabyanimal (self, animalnr, last = 1):
    """
    Return Milkingdata of Animal
    """
    try:
      cursor = self.executesql ("SELECT ts, animalnr, ammount, time, milkplace FROM milkdata WHERE animalnr = '%d' ORDER BY ts DESC LIMIT %d;" % (int (animalnr), last))
    except:
      return None
   
    res = cursor.fetchall ()
    return res


  def getestimatedmilkamount (self, animalnr, milking):
    """
    Return Avg of last 7 Milkings for
    milking : morning / noon / evening
    in 10g
    """
    if milking not in ['morning', 'noon', 'evening']:
      return 0

    try:
      cursor = self.executesql ("SELECT SUM (ammount) / COUNT (ammount) AS dayavg, strftime('%%Y-%%m-%%d',ts) AS mdate FROM milkdata\
                                 WHERE animalnr = '%d' AND ammount > 51 AND mdate < strftime('%%Y-%%m-%%d',current_timestamp)\
                                 AND time(ts) >= (SELECT time(starttime) FROM milkingtimes WHERE milking = '%s')\
                                 AND time(ts) < (SELECT time(endtime) FROM milkingtimes WHERE milking = '%s')\
                                 GROUP BY mdate\
                                 ORDER BY mdate DESC\
                                 LIMIT 7;" % (int (animalnr), milking, milking))
    except:
      return None

    res = cursor.fetchall ()
    avgsum = 0
    count = 0
    for dayavg, mdate in res:
      avgsum += int (dayavg)
      count += 1

    if count:
      return int (avgsum / count / 100)
    else:
      return 0


  def getactualmilking (self):
    """
    Return Actual Milking
    """
    try:
      cursor = self.executesql ("SELECT milking FROM milkingtimes WHERE time(datetime(current_timestamp, 'localtime')) BETWEEN time(starttime) AND time(endtime);")
    except:
      return None

    res = cursor.fetchone ()
    if res:
     return res[0]
    else:
      return 'Invalid'



if __name__ == "__main__":
  conn = DB ('/opt/data/animaldb.sqlite', '/tmp/talog.sql', logtest, errlogtest)
  print (conn.getestimatedmilkamount (606, conn.getactualmilking ()))
  print (conn.getestimatedmilkamount (606, 'morning'))
  print (conn.getestimatedmilkamount (606, 'noon'))
  print (conn.getestimatedmilkamount (606, 'evening'))

  anr = conn.getanimalnrbyrfid ('7678l43'.lstrip('0'))
  if anr:
   print (anr [0])
  data = {'animalnr' :    711,
          'earmark'  :    'DE09 4711',
          'rfid'     :    '123543546' 
          }
  #conn.addanimal (data)
  #print (conn.getanimals ())
  
  data = {'animalnr' :    711,
          'ts'       :    '2017.03.01 14:24:57',
          'ammount'  :    '5.00', 
          'time'     :    '234'}
  #conn.addmilkdata (data)
  print (conn.getlastmilkdatabyanimal ('657', 3))
  #print ("All Milkingdata")
  #for ds in conn.getiallmilkdata ():
  #  print (ds)