SGBD/sql.py

class SQL:
    def __init__(self, tables: dict, query: str) -> None:
        self.tables = tables
        self.query = query.replace(";", "")

    def _try_parse_int(self, value: str) -> int:
        try:
            return int(value)
        except ValueError:
            return value

    # ISCOLHE * DE tabela
    def execute(self):
        query_parts = self.query.split(" ")

        if query_parts[0] == "ISCOLHE":
            self.select(query_parts)
        elif query_parts[0] == "BOTAR":
            self.insert(query_parts)
        elif query_parts[0] == "SUMIR":
            self.delete(query_parts)
        elif query_parts[0] == "UPDEITA":
            self.update(query_parts)
        else:
            pass

    # ISCOLHE * DE tabela AJUNTAR tabela2 EM coluna;
    # ISCOLHE * DE tabela DONDE coluna = valor;
    def select(self, query_parts: list) -> None:
        where_filter = None
        join_stmt = None

        if "AJUNTAR" in query_parts:
            index = query_parts.index("AJUNTAR")
            join_table = query_parts[index + 1]
            join_operator = query_parts[index + 2] # EM -> estetico
            join_column = query_parts[index + 3]
            join_stmt = [self.tables[join_table], join_column]

        if "DONDE" in query_parts:
            index = query_parts.index("DONDE")
            where_filter_column = query_parts[index + 1]
            where_filter_operator = query_parts[index + 2]
            where_filter_value = self._try_parse_int(query_parts[index + 3])
            where_filter = [where_filter_column, where_filter_operator, where_filter_value]

        # parse TABLE
        table = self.tables[query_parts[3]]

        # join columns
        tables_columns = table.columns
        tables_rows = table.rows

        if join_stmt is not None:
            join_table = join_stmt[0]
            join_column = join_stmt[1]

            for i in range(len(tables_rows)):
                for j in range(len(join_table.rows)):
                    row_column_index = table.columns.index(join_column)
                    join_row_column_index = join_table.columns.index(join_column)

                    if tables_rows[i][row_column_index] == join_table.rows[j][join_row_column_index]:
                        tables_rows[i] += join_table.rows[j]

            tables_columns = table.columns + join_table.columns

        # parse SELECT COLUMNS
        column_list = query_parts[1].split(",")
        for i in range(len(column_list)):
            column_list[i] = column_list[i].strip()

        if column_list[0] == '*':
            column_list = tables_columns

        # print the columns
        print(', '.join(column_list))
        print('-' * 20)

        # print the rows
        for row in tables_rows:
            if where_filter is not None:
                column_index = tables_columns.index(where_filter[0])
                row_value = row[column_index]

                if where_filter[1] == '=':
                    if row_value != where_filter[2]:
                        continue
                elif where_filter[1] == '>':
                    if row_value <= where_filter[2]:
                        continue
                elif where_filter[1] == '<':
                    if row_value >= where_filter[2]:
                        continue

            row_values = []
            for column in column_list:
                column_index = tables_columns.index(column)
                row_values.append(str(row[column_index]))

            print(', '.join(row_values))

    # BOTAR tabela Coluna1,Coluna2,Coluna3 VALORES Valor1,Valor2,Valor3
    def insert(self, query_parts: list) -> None:
        insert_tabela = None
        insert_colunas = []
        insert_valores = []

        index = query_parts.index("BOTAR")
        insert_tabela = query_parts[index + 1]
        insert_colunas = query_parts[index + 2].split(",")
        insert_valores = query_parts[index + 4].split(",")

        table = self.tables[insert_tabela]

        row_value = []
        for column_index in range(len(table.columns)):
            column_name = table.columns[column_index]

            try:
                coluna_index = insert_colunas.index(column_name)
                valor = self._try_parse_int(insert_valores[coluna_index])
            except ValueError:
                coluna_index = -1
                valor = ""

            row_value.append(valor)

        table.rows.append(row_value)
        table.save_to_csv()

    # BOTAR tabela Coluna1,Coluna2,Coluna3 VALORES Valor1,Valor2,Valor3
    def insert(self, query_parts: list) -> None:
        insert_tabela = None
        insert_colunas = []
        insert_valores = []

        index = query_parts.index("BOTAR")
        insert_tabela = query_parts[index + 1]
        insert_colunas = query_parts[index + 2].split(",")
        insert_valores = query_parts[index + 4].split(",")

        table = self.tables[insert_tabela]

        row_value = []
        for column_index in range(len(table.columns)):
            column_name = table.columns[column_index]

            try:
                coluna_index = insert_colunas.index(column_name)
                valor = self._try_parse_int(insert_valores[coluna_index])
            except ValueError:
                coluna_index = -1
                valor = ""

            row_value.append(valor)

        table.rows.append(row_value)
        table.save_to_csv()

    # SUMIR tabela DONDE coluna = valor
    def delete(self, query_parts: list) -> None:
        delete_tabela = None
        delete_coluna = None
        delete_condicao = None
        delete_valor = None

        index = query_parts.index("SUMIR")
        delete_tabela = query_parts[index + 1]
        delete_coluna = query_parts[index + 3]
        delete_condicao = query_parts[index + 4]
        delete_valor = self._try_parse_int(query_parts[index + 5])

        table = self.tables[delete_tabela]

        for row in table.rows:
            column_index = table.columns.index(delete_coluna)

            if delete_condicao == '=' and row[column_index] == delete_valor:
                table.rows.remove(row)
            elif delete_condicao == '<' and row[column_index] < delete_valor:
                table.rows.remove(row)
            elif delete_condicao == '>' and row[column_index] > delete_valor:
                table.rows.remove(row)
            elif delete_condicao == '!=' and row[column_index] != delete_valor:
                table.rows.remove(row)

        table.save_to_csv()

    # SUMIR tabela DONDE coluna = valor
    def delete(self, query_parts: list) -> None:
        delete_tabela = None
        delete_coluna = None
        delete_condicao = None
        delete_valor = None

        index = query_parts.index("SUMIR")
        delete_tabela = query_parts[index + 1]
        delete_coluna = query_parts[index + 3]
        delete_condicao = query_parts[index + 4]
        delete_valor = self._try_parse_int(query_parts[index + 5])

        table = self.tables[delete_tabela]

        column_index = table.columns.index(delete_coluna)

        for row in table.rows:
            if delete_condicao == '=' and row[column_index] == delete_valor:
                table.rows.remove(row)
            elif delete_condicao == '<' and row[column_index] < delete_valor:
                table.rows.remove(row)
            elif delete_condicao == '>' and row[column_index] > delete_valor:
                table.rows.remove(row)
            elif delete_condicao == '!=' and row[column_index] != delete_valor:
                table.rows.remove(row)

        table.save_to_csv()

    # UPDEITA tabela coluna PARA valor DONDE coluna = valor
    def update(self, query_parts: list) -> None:
        update_tabela = None
        update_coluna = None
        update_valor = None

        index = query_parts.index("UPDEITA")
        update_tabela = query_parts[index + 1]
        update_coluna = query_parts[index + 2]
        update_valor = self._try_parse_int(query_parts[index + 4])
        
        index = query_parts.index("DONDE")
        update_where_coluna = query_parts[index + 1]
        update_where_condicao = query_parts[index + 2]
        update_where_valor = query_parts[index + 3]

        table = self.tables[update_tabela]

        update_col_index = table.columns.index(update_coluna)
        where_col_index = table.columns.index(update_where_coluna)

        for i in range(len(table.rows)):
            if update_where_condicao == '=' and table.rows[i][where_col_index] == update_where_valor:
                table.rows[i][update_col_index] = update_valor
            elif update_where_condicao == '<' and table.rows[i][where_col_index] < update_where_valor:
                table.rows[i][update_col_index] = update_valor
            elif update_where_condicao == '>' and table.rows[i][where_col_index] > update_where_valor:
                table.rows[i][update_col_index] = update_valor
            elif update_where_condicao == '!=' and table.rows[i][where_col_index] != update_where_valor:
                table.rows[i][update_col_index] = update_valor

        table.save_to_csv()