Skip to content

Instantly share code, notes, and snippets.

@dboyd13
Created February 16, 2018 07:07
Show Gist options
  • Save dboyd13/4e63e770575e271129c322653f88b1fc to your computer and use it in GitHub Desktop.
Save dboyd13/4e63e770575e271129c322653f88b1fc to your computer and use it in GitHub Desktop.
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment