Пример #1
0
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()
Пример #2
0
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()
Пример #3
0
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)
Пример #4
0
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)
Пример #5
0
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)
Пример #6
0
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)
Пример #7
0
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) + "')")
Пример #8
0
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()
Пример #9
0
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)
Пример #10
0
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)
Пример #11
0
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)
Пример #12
0
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])
Пример #13
0
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()
Пример #14
0
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()
Пример #15
0
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()
Пример #16
0
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()
Пример #17
0
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()