kegbot_db_cleaner.py

import sqlite3 #Import the SQLite3 module
import sys

commit = False

try: 
    sqlite_file = sys.argv[1]
except:
    print "You must specify a sqlite db file as an argument."
    quit()

try:
    if sys.argv[2] == "--commit":
        commit = True
        print "[!] You specified the --commit argument - changes WILL be written to the database."
except:
   print "[!] You did not specify --commit argument - so WILL NOT commit DB changes. This is a dry run."

db = sqlite3.connect(sqlite_file)
kegbot_db = db.cursor()

keg_to_update = {}
drinks_to_delete = []
# All drinks, on any day between Midnight and 10am, where the user is guest.

startTime = "00:00:00.000000"
endTime = "10:00:00.000000"

kegbot_db.execute('''SELECT id,volume_ml,keg_id,datetime(time,"localtime") FROM core_drink WHERE time(time,"localtime") BETWEEN time(?) and time (?) and user_id=1''',((startTime),(endTime),))

drinksCounter = 0

for kegbot_db_row in kegbot_db:
    drinksCounter += 1
    print "[-] #" + str(drinksCounter) + " - On " + str(kegbot_db_row[3]) + " GUEST poured: " + str(kegbot_db_row[1]) + "ml from keg_id: " + str(kegbot_db_row[2])    
    if kegbot_db_row[2] in keg_to_update:
        keg_to_update[kegbot_db_row[2]] += int(kegbot_db_row[1])
    else:
        keg_to_update[kegbot_db_row[2]] = int(kegbot_db_row[1])

    drinks_to_delete.append(int(kegbot_db_row[0]))

# Delete the drinks/pours from core_drink table
for drink_id in drinks_to_delete:
    kegbot_db.execute('''DELETE FROM core_drink WHERE id=?''',(str(drink_id),))

print "[-] Deleted " + str(drinksCounter) + " drinks."

# Get the current served_volume_ml for in-scope kegs, and update

kegCounter = 0
for keg_id in keg_to_update:
    kegCounter += 1
    kegbot_db.execute('''SELECT served_volume_ml FROM core_keg WHERE id=?''',(str(keg_id),))

    for served_volume_ml in kegbot_db:
       print "[-] #" + str(kegCounter) + " - Keg id: " + str(keg_id) + " original served_volume_ml: " + str(served_volume_ml[0]) + "ml"
       current_served_volume_ml = int(served_volume_ml[0])

    new_served_volume_ml = current_served_volume_ml - int(keg_to_update[keg_id])

    print "[-] #" + str(kegCounter) + " - Keg id: " + str(keg_id) + " revised served_volume_ml: " + str(new_served_volume_ml) + "ml"
    kegbot_db_update = db.cursor()
    kegbot_db_update.execute('''UPDATE core_keg SET served_volume_ml=? WHERE id=?''',(str(new_served_volume_ml),str(keg_id),))

print "[-] Updated " + str(kegCounter) + " kegs."
print "[-] Done"

if commit == True:
    db.commit()
db.close()