database.py

import sys
def create(database, tableName, columns): #Creates us a table with the given table name and column names and record them into database
    if tableName in database:
        print(f"Table '{tableName}' already exists.")
        return
    database[tableName] = {"columns": columns,"rows": []} #Records the datas in a dictionary that will help us in further functions
    print(f"###################### CREATE #########################")
    print(f"Table '{tableName}' created with columns: {columns}")
    print("#"*55)
def insert(database, tableName, datas): #Inserts the given datas to the table
    datas = [data.strip() for data in datas]
    row = (list(datas))
    if tableName not in database:
        print("\n"f"###################### INSERT #########################")
        print(f"Table {tableName} not found")
        print(f"Inserted into '{tableName}': {row}")
        return          #Gives us informative message about the situation where table name is not registered in database which means there is no table named like that
    table = database[tableName]
    columns = table["columns"]
    table['rows'].append(row)
    if len(datas) != len(columns):      #Checks the amount of datas which are given and compares the amount of it with columns
        print(f"Column count does not match value count for table '{tableName}'.")
        print(f"Expected columns: {len(columns)} | Provided values: {len(datas)}")
        return
    print("\n"f"###################### INSERT #########################")
    print(f"Inserted into '{tableName}': {row}")
    print("\n"f"Table: {tableName}")
    printTable(table['columns'], table['rows'])
    print("#" * 55)
def condDetect(conditions): #Helps us to the detect the conditions that are given with "WHERE" phrase
    conDict = {}
    conditions = conditions.strip('{}').strip()
    if not conditions:
        return conDict
    try:
        pairs = conditions.split(',')
        for pair in pairs:
            key, value = pair.split(':')
            key = key.strip().strip('"').strip("'")
            value = value.strip().strip('"').strip("'")
            conDict[key] = value    #Separates the WHERE condition and appends them as key-value pair to our cond. dict.
    except:
        print(f"Defective WHERE condition '{conditions}'")
        return None
    return conDict
def select(database, tableName, selectedColumns, conditions): #Shows us the datas that matches with the given conditions from table
    conDict = condDetect(conditions)
    if tableName not in database:
        print("\n"f"###################### SELECT #########################") #The situation about table name
        print(f"Table {tableName} not found")
        print(f"Condition: {conDict}")
        print(f"Select result from '{tableName}': None")
        print("#" * 55)
        return
    table = database[tableName]
    allColumns = table['columns']
    rows = table['rows']
    for column in selectedColumns: #The situation about columns in table
        if column not in allColumns:
            print("\n"f"###################### SELECT #########################")
            print(f"Column {column} does not exist")
            print(f"Condition: {conDict}")
            print(f"Select result from '{tableName}': None")
            print("#" * 55)
            return
    conDict = condDetect(conditions)
    if conDict is None: #No given condition
        return
    for column in conDict.keys(): #The situation about columns in WHERE clause
        if column not in allColumns:
            print("\n"f"###################### SELECT #########################")
            print(f"Column {column} does not exist")
            print(f"Condition: {conDict}")
            print(f"Select result from '{tableName}': None")
            print("#" * 55)
            return
    matchRows = []
    for row in rows: #Checks the rows,finds the matching ones with the WHERE clause, add them into a tuple
        rowDict = {}
        for index in range(len(allColumns)):
            colName = allColumns[index]
            selData = row[index]
            rowDict[colName] = selData
        match = True
        for key, value in conDict.items():
            if str(rowDict.get(key)) != str(value):
                match = False
                break
        if match:
            matchRows.append(row)
    colIndexes = []
    for col in selectedColumns:
        colIndexes.append(allColumns.index(col))
    selectRows = []
    for row in matchRows:
        selectedData = []
        for index in colIndexes:
            selData = row[index]
            selectedData.append(selData)
        selectedRow = tuple(selectedData)
        selectRows.append(selectedRow)
    print("\n"f"###################### SELECT #########################")
    print(f"Condition: {conditions}")
    print(f"Select result from '{tableName}': {selectRows}")
    print("#" * 55)
def update(database, tableName, updates, conditions): #Function that updates the table according to WHERE phrase
    conDict = condDetect(conditions)
    updateDict = condDetect(updates)
    if tableName not in database:
        print("\n"f"###################### UPDATE #########################")
        print(f"Updated '{tableName}' with {updateDict} where {conDict}")
        print(f"Table {tableName} not found")
        print("0 rows updated.")
        print("#" * 55)
        return
    table = database[tableName]
    allColumns = table['columns']
    rows = table['rows']
    for column in updateDict.keys(): #The situation about columns in table
        if column not in allColumns:
            print("\n"f"###################### UPDATE #########################")
            print(f"Updated '{tableName}' with {updateDict} where {conDict}")
            print(f"Column {column} does not exist")
            print("0 rows updated.")
            print("\n"f"Table: {tableName}")
            printTable(allColumns, rows)
            print("#" * 55)
            return
    for column in conDict.keys(): #The situation about columns in WHERE clause
        if column not in allColumns:
            print("\n"f"###################### UPDATE #########################")
            print(f"Updated '{tableName}' with {updateDict} where {conDict}")
            print(f"Column {column} does not exist")
            print("0 rows updated.")
            print("\n"f"Table: {tableName}")
            printTable(allColumns, rows)
            print("#" * 55)
            return
    updatedCount = 0
    for index, row in enumerate(rows): #Checks the rows,finds the matching ones with the WHERE clause, updates them and counts the number of how many updated
        rowDict = {}
        for index in range(len(allColumns)):
            colName = allColumns[index]
            selData = row[index]
            rowDict[colName] = selData
        match = True
        for key, value in conDict.items():
            if str(rowDict.get(key)) != str(value):
                match = False
                break
        if match:
            updatedRow = []
            for data in row:
                updatedRow.append(data)
            for key, value in updateDict.items():
                colIndex = allColumns.index(key)
                updatedRow[colIndex] = value
            rows[index] = tuple(updatedRow)
            updatedCount += 1
    print("\n"f"###################### UPDATE #########################")
    print(f"Updated '{tableName}' with {updateDict} where {conDict}")
    print(f"{updatedCount} rows updated.")
    print("\n"f"Table: {tableName}")
    printTable(table['columns'], table['rows'])
    print("#" * 55)
def delete(database, tableName, conditions):#Function that deletes data from table according to WHERE phrase
    conDict = condDetect(conditions)
    if tableName not in database:
        print("\n"f"###################### DELETE #########################")#The situation about table name
        print(f"Deleted from '{tableName}' where {conDict}.")
        print(f"Table {tableName} not found")
        print("0 rows deleted.")
        print("#" * 55)
        return
    table = database[tableName]
    allColumns = table['columns']
    rows = table['rows']
    for column in conDict.keys():
        if column not in allColumns:
            print("\n"f"###################### DELETE #########################")#The situation about columns in table
            print(f"Deleted from '{tableName}' where {conDict}")
            print(f"Column {column} does not exist")
            print("0 rows deleted.")
            print("\n"f"Table: {tableName}")
            printTable(allColumns, rows)
            print("#" * 55)
            return
    rowCount = len(rows)
    newRows = []
    for row in rows:   #Checks the rows and finds the one's that doesn't match with WHERE clause
        rowDict = {}
        for index in range(len(allColumns)):
            rowDict[allColumns[index]] = row[index]
        match = True
        for key, value in conDict.items():
            if key in rowDict:
                if str(rowDict.get(key)).strip() != str(value).strip():
                    match = False
                    break
        if not match:
            newRows.append(row)
    deletedCount = rowCount - len(newRows)
    table['rows'] = newRows
    if conDict is None:
        deletedCount = len(rows)
        rows.clear()
        print("\n"f"###################### DELETE #########################") #No given conditions
        print(f"Deleted all rows from '{tableName}'")
        print(f"{deletedCount} rows deleted.")
        print("#" * 55)
        return
    else:
        print("\n"f"###################### DELETE #########################")
        print(f"Deleted from '{tableName}' where {conDict}.")
        print(f"{deletedCount} rows deleted.")
        print("\n"f"Table: {tableName}")
        printTable(allColumns, newRows)
        print("#" * 55)
        return
def count(database, tableName, conditions): #Function that counts datas which matches with the WHERE phrase
    conDict = condDetect(conditions)
    if tableName not in database:   #The situation about table name
        print("\n"f"###################### COUNT #########################")
        print(f"Table {tableName} not found")
        print(f"Total number of entries in '{tableName}' is 0")
        print("#" * 55)
        return
    table = database[tableName]
    allColumns = table['columns']
    rows = table['rows']
    if conditions is None:               #No given conditions
        totalCount = len(rows)
        print("\n"f"###################### COUNT #########################")
        print(f"Total number of entries in '{tableName}' is {totalCount}")
        print("#" * 55)
        return totalCount
    for column in conDict.keys():
        if column not in allColumns:                    #The situation about columns in table
            print("\n"f"###################### COUNT #########################")
            print(f"Column '{column}' does not exist")
            print(f"Total number of entries in '{tableName}' is 0")
            print("#" * 55)
            return
    count = 0
    for row in rows: #Checking rows and counts how many rows matches with the WHERE clause
        rowDict = {}
        for index in range(len(allColumns)):
            rowDict[allColumns[index]] = row[index]
        match = True
        for key, value in conDict.items():
            if key in rowDict:
                if str(rowDict.get(key)).strip() != str(value).strip():
                    match = False
                    break
        if match:
            count += 1
    print("\n"f"###################### COUNT #########################")
    print(f"Count: {count}")
    print(f"Total number of entries in '{tableName}' is {count}")
    print("#"*55)
    return count
def join(database, table1Name, table2Name, colName): #Function that joins two tables into one
    if table1Name not in database:                          #Situation about the first table's name
        print("\n"f"###################### JOIN #########################")
        print(f"Join tables {table1Name} and {table2Name}")
        print(f"Table {table1Name} does not exist")
        print("#"*55)
        return
    if table2Name not in database:                          #Situation about the second table's name
        print("\n"f"###################### JOIN #########################")
        print(f"Join tables {table1Name} and {table2Name}")
        print(f"Table {table2Name} does not exist")
        print("#"*55)
        return
    table1 = database[table1Name]
    table2 = database[table2Name]
    table1Col = table1['columns']     #Pulling data from database
    table2Col = table2['columns']
    table1Rows = table1['rows']
    table2Rows = table2['rows']
    if colName not in table1Col or colName not in table2Col:    #The situation about columns in tables
        print("\n"f"###################### JOIN #########################")
        print(f"Join tables {table1Name} and {table2Name}")
        print(f"Column {colName} does not exist")
        print("#"*55)
        return
    index1 = table1Col.index(colName)
    index2 = table2Col.index(colName)
    joinedCol = table1Col + table2Col
    joinedRows = []
    lenJoinedRows = len(joinedRows)
    for row1 in table1Rows:
        for row2 in table2Rows:
            if str(row1[index1]) == str(row2[index2]): #Checks matching rows
                joinedRows.append(row1 + row2)
                lenJoinedRows = len(joinedRows)
    print("\n"f"###################### JOIN #########################")
    print(f"Join tables {table1Name} and {table2Name}")
    print(f"Join result ({lenJoinedRows} rows):")
    print("\n""Table: Joined Table")
    printTable(joinedCol, joinedRows)
    print("#" * 55)
    return
def printTable(columns, rows): #Prints us table
    if len(rows) == 0: #No rows situation
        print("There are no rows in the table.")
        return
    colWidth = []
    for index in range(len(columns)):   #Finds max width for each column and sotre them in a list
        max_width = len(columns[index])
        for row in rows:
            max_width = max(max_width, len(str(row[index])))
        colWidth.append(max_width)
    print('+' + '+'.join(['-' * (width + 2) for width in colWidth]) + '+') #Top border of the table
    header = '| ' + ' | '.join([columns[index].ljust(colWidth[index]) for index in range(len(columns))]) + ' |' #Column names row(Header)
    print(header)
    print('+' + '+'.join(['-' * (width + 2) for width in colWidth]) + '+') #Border under the header
    for row in rows:
        line = '| ' + ' | '.join([str(row[index]).ljust(colWidth[index]) for index in range(len(row))]) + ' |' #ljust(left-justified) func helps us the match the width of the column with the max length
        print(line) #Prints each row of table
    print('+' + '+'.join(['-' * (width + 2) for width in colWidth]) + '+') #Bottom border
def main():
    database = {}
    inpFile = sys.argv[1]
    with open(inpFile, "r") as commandFile:
        data = commandFile.readlines()
    for index, commands in enumerate(data):
        commands = commands.strip()
        if not commands:
            continue
        parts = commands.split(" ", 2)
        command = parts[0]
        if command == "CREATE_TABLE":
            try:
                if index != 0:
                    print("\n")
                tableName = parts[1]
                columns = [column.strip() for column in parts[2].split(",")]
                create(database, tableName, columns)
            except:
                print(f"Invalid CREATE_TABLE command syntax in '{commands}'")
        elif command == "INSERT":
            try:
                tableName = parts[1]
                datas = [data.strip() for data in parts[2].split(",")]
                insert(database, tableName, datas)
            except:
                print(f"Invalid INSERT command syntax in '{commands}'")
        elif command == "SELECT":
            try:
                tableName, colandcond = parts[1], parts[2]
                basePart, condPart = colandcond.split("WHERE")
                selectedCol = [data.strip() for data in basePart.split(",")]
                cond = condPart.strip()
                select(database, tableName, selectedCol, cond)
            except:
                print(f"Invalid SELECT command syntax in '{commands}'")
        elif command == "UPDATE":
            tableName = parts[1]
            updates, cond = parts[2].split("WHERE")
            update(database, tableName, updates.strip(), cond.strip())
        elif command == "DELETE":
            try:
                if "WHERE" in parts[2]:
                    tableName, condPart = parts[1], parts[2].split("WHERE", 1)[1].strip()
                else:
                    tableName, condPart = parts[1], ""
                delete(database, tableName, condPart)
            except:
                print(f"Invalid DELETE command syntax in '{commands}'")
        elif command == "COUNT":
            try:
                if "WHERE" in parts[2]:
                    tableName, condPart = parts[1], parts[2].split("WHERE", 1)[1].strip()
                else:
                    tableName, condPart = parts[1], ""
                count(database, tableName, condPart)
            except:
                print(f"Invalid COUNT command syntax in '{commands}'")
        elif command == "JOIN":
            try:
                table1Name, table2Name = parts[1].split(",")
                colName = parts[2].replace("ON", "").strip()
                join(database, table1Name.strip(), table2Name.strip(), colName.strip())
            except:
                print(f"Invalid JOIN command syntax in '{command}'")
if __name__ == "__main__":
    main()