def update(inpt, info):
"""Runs an update on the current DB host table."""
criteria = "UPDATE " + info[1] + " SET " + find_change(inpt) + " WHERE "
where = query.parse_flags(inpt)
query.execute_sql(info[0], criteria + where)
info[0].commit()
def insert(inpt, info):
"""Inserts a record into the current DB host table."""
inpt = repackage(inpt, info[1])
sql_query = "INSERT INTO " + info[1] + \
"(" + ", ".join(theme.HOST_SET[info[1]]) + ") VALUES ('" + "', '".join(inpt) + "')"
query.execute_sql(info[0], sql_query)
info[0].commit()
def report(inpt, info):
"""Performs a search with the input, then passes the results to dataprint's export to create a simply report."""
view = -1
if "-v" in inpt:
view = inpt.index("-v")+1
elif "--view" in inpt:
view = inpt.index("--view")+1
if view != -1:
filename = " ".join(inpt[view:])
print()
if filename.lower() in theme.LIST_FILES:
fileview("./reports/")
elif filename.lower() in theme.LIST_TEMPS:
fileview("./templates/")
else:
filename += ".txt" if filename[-4:] != ".txt" else ""
with open("reports/"+filename, "r") as rep:
for line in rep:
print(line, end="")
input(theme.PAUSE)
else:
rep, templ = get_template(info, inpt)
args = [el for el in inpt]
sql_query = query.parse_sql(inpt, info[1], "search")
columns, results = query.execute_sql(info[0], sql_query)
dataprint.export(columns, results, tb=info[1], args="report " + " ".join(args), source=info[1], template=templ, rep_name=rep)
def aggregate(agg, inpt, info):
"""Creates and runs a DB update."""
sql_query = "SELECT " + agg + "(" + inpt[0] + ")" + " FROM " + info[1] + " WHERE " + query.parse_flags(inpt[1:])
columns, results = query.execute_sql(info[0], sql_query)
dataprint.table(columns, results)
input(theme.PAUSE)
def cmd_stats(inpt, info):
"""Intermediary function for dataprint's stats function."""
sql_query = query.parse_sql(inpt, info[1], "search")
columns, results = query.execute_sql(info[0], sql_query)
dataprint.stats(columns, results)
input(theme.PAUSE)
def search(inpt, info):
"""Runs a DB search then acts as an intermediary function for dataprint's table function."""
sql_query = query.parse_sql(inpt, info[1], "search")
columns, results = query.execute_sql(info[0], sql_query)
dataprint.table(columns, results)
input(theme.PAUSE)
def change_username(info, inpt):
"""Changes username in credentials table by deleting old hash and generating/inserting new hash"""
password = ""
valid = False
while not valid:
print()
password = getpass("Enter " + theme.GET_PW)
valid = verify(info[0], info[2], password)
if not valid:
print(theme.PASS_ERROR[1:] + "\n")
query.execute_sql(
info[0], "DELETE FROM credentials WHERE Hash='" +
hash_credentials(info[2], password) + "'")
query.execute_sql(
info[0], "INSERT INTO credentials VALUES('" +
hash_credentials(inpt, password) + "')")
def upload(inpt, info):
"""Uploads a CSV or TSV into the current DB host table."""
# loops through the file counting rows
file_len = 0
for line in open(inpt[0], "r"):
file_len += 1
# sets the row processing function depending on file format
funct = (lambda r: "', '".join(prepare_row(r))) if inpt[0][-3:] == "csv" else (lambda r: r.replace("\t", "', '"))
with open(inpt[0], "r") as infile:
row = infile.readline().strip("\n")
curr = 1
while row:
# adjusts statics.PROGRESS if there is a change in terminal size while running
session.terminal_size()
# processes progress ratios
ratio = curr / file_len
progress = ceil(ratio * theme.PROGRESS)
ratio = ceil(ratio * 100)
row = row.replace("'", "''")
row = funct(row)
# prints the progress bar
session.clear_screen()
print(info[2] + "@" + info[1] + ": upload " + " ".join(inpt))
print("\n\n |" + ("#" * progress) + (" " * (theme.PROGRESS - progress)) + "| " + str(ratio) + "%")
print("\n '" + row + "'")
sql_query = "INSERT INTO " + info[1] + "(" + ", ".join(theme.HOST_SET[info[1]]) + ")" \
" VALUES('" + row + "')"
query.execute_sql(info[0], sql_query)
curr += 1
row = infile.readline().strip("\n")
info[0].commit()
def plot(inpt, info):
"""Preprocessing for dataprint's plot.
Creates the augmented SQL query specifically for plot."""
args = {"whr": [], "-X": [], "-Y": []}
agg = ["count", "sum", "avg"]
temp = []
# WHERE exists, scale, operation type, aggregated axis
spec_vars = [False, 1, "count", "-Y"]
# creates lists of each flags arguments
for el in reversed(inpt):
if "-" in el:
if el in args.keys():
args[el] += reversed(temp)
else:
spec_vars[0] = True
args["whr"] += [el]
args["whr"] += reversed(temp)
temp = []
else:
temp += [el]
# finds the aggregate axis, sets variables for use in printing
for el in (args["-X"] + ["-X"]) if len(args["-X"]) > 1 else (args["-Y"] + ["-Y"]):
try:
int(el)
spec_vars[1] = int(el)
except ValueError:
if el in agg:
spec_vars[2] = el
elif el in args.keys():
spec_vars[3] = el
args[spec_vars[3]].remove(spec_vars[2])
args[spec_vars[3]].remove(str(spec_vars[1]))
op_flg = "-X" if spec_vars[3] == "-Y" else "-Y"
sql_query = "SELECT " + ((args["-Y"][0] + ", " + args["-X"][0]) if spec_vars[3] == "-X" else (args["-X"][0] + ", " + args["-Y"][0])) + \
" FROM " + info[1] + ((" WHERE " + query.parse_flags(args["whr"])) if spec_vars[0] else "") + " ORDER BY " + args[op_flg][0]
columns, results = query.execute_sql(info[0], sql_query)
dataprint.plot(columns, results, spec_vars[2], spec_vars[1], spec_vars[3][1].lower(), buffer_val=(0 if spec_vars[3][1] == "X" else 1))
input(theme.PAUSE)
def sql(inpt, info):
"""Runs a raw SQL query."""
# checks if query is accessing a blocked table
if set(inpt).intersection(set(theme.BLOCKED)) != set():
return
command = inpt.pop(0)
sql_query = " ".join(inpt)
columns, results = query.execute_sql(info[0], sql_query)
if command == "search":
dataprint.table(columns, results)
elif command == "stats":
dataprint.stats(columns, results)
elif command == "tsv":
fileout(columns, results, "\t", info[1])
return
input(theme.PAUSE)
def distinct(inpt, info):
"""Executes a standard search query, then cuts out non-distinct records.
After, executes dataprint functions: stats, report, or search on the results set."""
rep = -1
templ = None
op = find_op(inpt, "-C", "--command")
if op == "report":
rep, templ = get_template(info, inpt)
sql_query = query.parse_sql(inpt[1:], info[1], "search")
columns, results = query.execute_sql(info[0], sql_query)
pivot = columns.index(inpt[0].capitalize())
dist = set()
dupl = []
for i in range(len(results)):
if results[i][pivot] in dist:
dupl += [i]
else:
dist = dist.union({results[i][pivot]})
dupl.reverse()
for i in dupl:
results.pop(i)
if op == "stats":
dataprint.stats(columns, results)
elif op == "report":
dataprint.export(columns, results, info[1], "distinct " + " ".join(inpt), source=info[1], template=templ, rep_name=rep)
return
elif op == "tsv":
fileout(columns, results, "\t", info[1])
return
elif op == "search":
dataprint.table(columns, results)
input(theme.PAUSE)
def tsv(inpt, info):
"""Exports a search query to TSV."""
sql_query = query.parse_sql(inpt, info[1], "search")
columns, results = query.execute_sql(info[0], sql_query)
fileout(columns, results, "\t", info[1])
def create_user(inpt, connection):
"""Creates the new user in the DB."""
sql_statement = "INSERT INTO credentials VALUES('" + session.hash_credentials(
inpt[0], inpt[1]) + "')"
query.execute_sql(connection, sql_statement)
connection.commit()
def create_record(inpt, info):
"""Creates and submits a record of a user inputted command."""
sql_query = "INSERT INTO records VALUES('" + strftime(theme.DATE_TIME + " %H:%M:%S") + "', '" + info[2] + "', '" + \
inpt[0] + "', '" + info[1] + "', '" + " ".join(inpt[1:]) + "')"
query.execute_sql(info[0], sql_query)
info[0].commit()
def alter_table(table, columns, connection):
"""Creates SQL statement, and runs ALTER TABLE statement."""
sql_statement = "ALTER TABLE " + table + " ADD COLUMN " + " ".join(
columns[0]) + "\n"
query.execute_sql(connection, sql_statement)
connection.commit()
def remove(inpt, info):
"""Intermediary function to remove some set of records from the current DB host table."""
sql_query = query.parse_sql(inpt, info[1], "remove")
query.execute_sql(info[0], sql_query)
info[0].commit()
def create_table(table, columns, connection):
"""Creates SQL statement, and runs CREATE TABLE statement."""
sql_statement = "CREATE TABLE IF NOT EXISTS " + table + " (\n" + ",\n".join(
[" ".join(col) for col in columns]) + "\n)"
query.execute_sql(connection, sql_statement)
connection.commit()