fix this issue-
code:
def convert_keywords(query):
    # Define a mapping of keywords to their replacements (case-insensitive)
    keyword_mapping = {
        re.compile(r'\bfetch\b', re.IGNORECASE): 'SELECT',
        re.compile(r'\bput\b', re.IGNORECASE): 'INSERT',
        re.compile(r'\bremove\b', re.IGNORECASE): 'DELETE',
        re.compile(r'\bmerge\b', re.IGNORECASE): 'JOIN',
        re.compile(r'\bfilter\b', re.IGNORECASE): 'WHERE',
    }

    # Replace keywords in the query
    for keyword, replacement in keyword_mapping.items():
        query = keyword.sub(replacement, query)

    return query




def parse_query(query):
    # Use regular expression to split by space or comma, excluding commas within parentheses
    tokens = re.split(r'[,\s]+(?![^()]*\))', query.upper())
    # Remove empty strings from the list
    tokens = [token for token in tokens if token]
    # Handle 'AND' as a separate token
    tokens = [token if token != 'AND' else 'AND' for token in tokens]
    return tokens



def custom_sort(data, column, ascending=True):
    """
    Custom sorting function to sort a DataFrame by a specified column.
    
    Parameters:
    - data: DataFrame to be sorted
    - column: Column by which to sort the DataFrame
    - ascending: If True, sort in ascending order; if False, sort in descending order
    
    Returns:
    - Sorted DataFrame
    """
    # Extract the values of the specified column
    column_values = data[column].tolist()

    # Create a list of tuples containing index and corresponding values
    indexed_values = list(enumerate(column_values))

    # Sort the list of tuples based on values and order
    sorted_values = sorted(indexed_values, key=lambda x: x[1], reverse=not ascending)

    # Extract the sorted indices
    sorted_indices = [index for index, _ in sorted_values]

    # Reorder the DataFrame based on the sorted indices
    sorted_data = data.iloc[sorted_indices]

    return sorted_data


class Database:

    def __init__(self, chunk_size=100):
        self.chunk_size = chunk_size
        self.property_info_chunks = []
        self.sales_info_chunks = []


    def load_sales_info(self, file_path):
        sales_columns = ['zip_code', 'city', 'status', 'prev_sold_date', 'price', 'state']

        with open(file_path, 'r') as csvfile:
            reader = csv.DictReader(csvfile)
            chunk = []
            for row in reader:
                chunk.append(row)
                if len(chunk) == self.chunk_size:
                    self.sales_info_chunks.append(pd.DataFrame(chunk, columns=sales_columns))
                    chunk = []
            if chunk:
                self.sales_info_chunks.append(pd.DataFrame(chunk, columns=sales_columns))
    
    def load_property_info(self, file_path):
        property_columns = ['bed', 'bath', 'acre_lot', 'house_size', 'zip_code', 'city']

        with open(file_path, 'r') as csvfile:
            reader = csv.DictReader(csvfile)
            chunk = []
            for row in reader:
                chunk.append(row)
                if len(chunk) == self.chunk_size:
                    self.property_info_chunks.append(pd.DataFrame(chunk, columns=property_columns))
                    chunk = []
            if chunk:
                self.property_info_chunks.append(pd.DataFrame(chunk, columns=property_columns))

        # Save property_info_chunks back to the CSV file
        self.save_chunks_to_csv('property_info.csv', self.property_info_chunks)

    def save_chunks_to_csv(self, file_path, chunks):
    # Concatenate all chunks and save to CSV
        if chunks:
            df = pd.concat(chunks, ignore_index=True)
            df.to_csv(file_path, index=False)
            
  
    def retrieve_data(self, columns, table_name, conditions=None, order_by=None, aggregation=None, join=None):
        if table_name.upper() == 'PROPERTYINFO':
            table_chunks = self.property_info_chunks
        elif table_name.upper() == 'SALESINFO':
            table_chunks = self.sales_info_chunks
        else:
            return "Invalid table name."

        # Convert column names to uppercase and trim whitespace
        columns = [col.strip().lower() for col in columns]

        # Ensure all columns exist in the dataset (case-insensitive)
        matching_columns = [col for col in table_chunks[0].columns if col.lower() in columns]

        if not matching_columns:
            # If no specific columns are specified, include all columns
            matching_columns = table_chunks[0].columns

        # Concatenate relevant chunks
        table = pd.concat([chunk[matching_columns] for chunk in table_chunks], ignore_index=True)

        # Apply WHERE condition if present
        if conditions:
            table = self.filter_data(table, conditions)

        # Ensure consistent 'city' values
        if 'city' in matching_columns:
            table['city'] = table['city'].str.strip().str.upper()

        # Handle JOIN operation if present
        if join:
            join_table_name, join_column = join
            join_table_chunks = self.property_info_chunks if join_table_name.upper() == 'PROPERTYINFO' else self.sales_info_chunks

            # Concatenate the relevant chunks for the join table
            join_table = pd.concat([chunk for chunk in join_table_chunks], ignore_index=True)

            # Perform the JOIN operation
            table = pd.merge(table, join_table, left_on=matching_columns, right_on=[join_column], how='inner')

        if aggregation:
            table = self.aggregate_data(table, aggregation)

        if order_by:
            order_by_column, order_by_direction = order_by[0], 'ASC'
            if len(order_by) > 1 and order_by[1].upper() == 'DESC':
                order_by_direction = 'DESC'

            # Use the custom sorting function
            table = custom_sort(table, column=order_by_column, ascending=(order_by_direction == 'ASC'))

        return table

        


    def filter_data(self, data, conditions):
            # Process conditions
            while "AND" in conditions:
                and_index = conditions.index("AND")
                data = self.apply_condition(data, conditions[:and_index])
                conditions = conditions[and_index + 1:]

            # Process the last or single condition
            data = self.apply_condition(data, conditions)
            return data



    def apply_condition(self, data, condition):
        # Assuming condition is ['column', 'operator', 'value'] without 'AND'
        column, operator, value = condition[0], condition[1], "".join(condition[2:])
        
        # Handle string values by removing surrounding single quotes
        if value.startswith("'") and value.endswith("'"):
            value = value[1:-1]  # Remove the surrounding single quotes
        
        # Convert column name to lowercase
        column_lower = column.lower()

        # Handle case-insensitive string comparisons
        if isinstance(data[column_lower].iloc[0], str):
            condition_result = data[column_lower].str.lower() == value.lower()
        else:
            # Apply the correct operator for non-string values
            if operator == '=':
                condition_result = data[column_lower] == value
            elif operator == '<>':
                condition_result = data[column_lower] != value
            # Additional operators (>, <, >=, <=) can be added here

        # Apply the condition to the data
        return data[condition_result]
    
    
    def insert_data(self, table_name, values):
        # Check if the table name is valid
        if table_name.upper() == 'PROPERTYINFO':
            # Column names for property_info.csv
            property_columns = ['bed', 'bath', 'acre_lot', 'house_size', 'zip_code', 'city']

            # Create a dictionary to store values for each column
            property_info = {column: [] for column in property_columns}

            # Extract values from the tuple and append to the dictionary
            for column, value in zip(property_columns, eval(values[0])):
                # Convert the value to the appropriate type
                if column == 'acre_lot':
                    value = float(value)
                property_info[column].append(value)

            array_lengths = [len(value) for value in property_info.values()]

            # Check that all arrays have the same length
            if len(set(array_lengths)) != 1:
                raise ValueError("All arrays must be of the same length.")

            # Create a DataFrame and append it to the property_info_chunks list
            df = pd.DataFrame(property_info)
            self.property_info_chunks.append(df)
            self.save_chunks_to_csv('property_info.csv', self.property_info_chunks)

            # Print the values being inserted
            print(f"Values being inserted: {property_info}")

            # Print the lengths of the arrays
            print(f"Array lengths: {array_lengths}")

            return f"Inserted values into {table_name}: {', '.join(values)}"

        elif table_name.upper() == 'SALESINFO':
            # Column names for sales_info.csv
            sales_columns = ['zip_code', 'city', 'status', 'prev_sold_date', 'price', 'state']

            # Create a dictionary to store values for each column
            sales_info = {column: [] for column in sales_columns}

            # Extract values from the tuple and append to the dictionary
            for column, value in zip(sales_columns, eval(values[0])):
                # Convert the value to the appropriate type
                if column == 'price':
                    value = float(value)
                sales_info[column].append(value)

            # Print the values being inserted
            print(f"Values being inserted: {sales_info}")

            # Print the lengths of the arrays
            array_lengths = [len(value) for value in sales_info.values()]
            print(f"Array lengths: {array_lengths}")

            # Check that all arrays have the same length
            if len(set(array_lengths)) != 1:
                raise ValueError("All arrays must be of the same length.")

            # Create a DataFrame and append it to the sales_info_chunks list
            df = pd.DataFrame(sales_info)
            self.sales_info_chunks.append(df)
            self.save_chunks_to_csv('sales_info.csv', self.sales_info_chunks)

            return f"Inserted values into {table_name}: {', '.join(values)}"
        else:
            return f"Invalid table name: {table_name}"

    
    
    def retrieve_data_groupby(self, columns, table_name, groupby_columns, order_by=None, aggregation=None):
        print("Debug: Starting retrieve_data_groupby")
        groupby_columns = [col.lower() for col in groupby_columns]
        data = self.retrieve_data(columns, table_name)

        if data is None:
            print("Error: No data found.")
            return None

        # Clean the city names for consistent grouping
        for col in groupby_columns:
            if col in data.columns:
                data[col] = data[col].str.strip().str.upper()

        print("Debug: Data retrieved successfully")

        if aggregation:
            # Handle the case when an aggregation function is specified
            aggregated_data = self.perform_aggregation(data, groupby_columns, aggregation)

            print("Debug: Returning aggregated data")
            return aggregated_data
        else:
            # When no aggregation function is specified, return the grouped data
            grouped_data = data.groupby(groupby_columns).size().reset_index(name='count')

            # Apply ORDER BY clause if present
            if order_by:
                order_by_column, order_by_direction = order_by[0], 'ASC'
                if len(order_by) > 1 and order_by[1].upper() == 'DESC':
                    order_by_direction = 'DESC'

                # Ensure the ordered column is part of the result
                if order_by_column.lower() in grouped_data.columns:
                    # Sort the result DataFrame based on the ORDER BY clause
                    grouped_data = grouped_data.sort_values(by=order_by_column.lower(), ascending=(order_by_direction == 'ASC')).reset_index(drop=True)
                else:
                    print(f"Error: Column '{order_by_column}' not found in the result set.")
                    return None  # Exit early if the ordered column is not found

                print("Debug: Returning grouped data")
                return grouped_data








    def perform_aggregation(self, data, groupby_columns, aggregation_function):
        # Create a dictionary to store aggregation results for each column
        aggregated_data_dict = {'group': []}

        for col in groupby_columns:
            if col in data.columns:
                if aggregation_function == 'COUNT':
                    aggregated_data_dict[col] = data.groupby(groupby_columns)[col].count().reset_index()[col]
                elif aggregation_function == 'SUM':
                    aggregated_data_dict[col] = data.groupby(groupby_columns)[col].sum().reset_index()[col]
                elif aggregation_function == 'AVG':
                    aggregated_data_dict[col] = data.groupby(groupby_columns)[col].mean().reset_index()[col]
                elif aggregation_function == 'MIN':
                    aggregated_data_dict[col] = data.groupby(groupby_columns)[col].min().reset_index()[col]
                elif aggregation_function == 'MAX':
                    aggregated_data_dict[col] = data.groupby(groupby_columns)[col].max().reset_index()[col]

        # Create a DataFrame from the aggregated data dictionary
        aggregated_data = pd.DataFrame(aggregated_data_dict)

        return aggregated_data



    
    def aggregate_data(self, data, aggregation):
        """
        Perform aggregation on the data based on the specified aggregation function.

        Parameters:
        - data: DataFrame to be aggregated
        - aggregation: Tuple ('column_name', 'function')

        Returns:
        - Aggregated DataFrame
        """
        column, func = aggregation

        if func.upper() == 'COUNT':
            return self.count_data(data, column)
        elif func.upper() == 'SUM':
            return self.sum_data(data, column)
        elif func.upper() == 'AVG':
            return self.avg_data(data, column)
        elif func.upper() == 'MIN':
            return self.min_data(data, column)
        elif func.upper() == 'MAX':
            return self.max_data(data, column)
        else:
            print(f"Error: Unsupported aggregation function '{func}'.")
            return data

    def count_data(self, data, count_column):
        """
        Count the occurrences of each group in the specified column.

        Parameters:
        - data: DataFrame to be counted
        - count_column: Column by which to count the occurrences

        Returns:
        - DataFrame with counted occurrences
        """
        grouped_counts = {}
        for value in data[count_column]:
            grouped_counts[value] = grouped_counts.get(value, 0) + 1

        result_data = {'group': [], 'count': []}
        for group, count in grouped_counts.items():
            result_data['group'].append(group)
            result_data['count'].append(count)

        return pd.DataFrame(result_data)

    def sum_data(self, data, sum_column):
        """
        Sum the values in the specified column.

        Parameters:
        - data: DataFrame to be summed
        - sum_column: Column by which to sum the values

        Returns:
        - DataFrame with summed values
        """
        sum_value = sum(data[sum_column])
        result_data = {'sum': [sum_value]}
        return pd.DataFrame(result_data)

    def avg_data(self, data, avg_column):
        """
        Calculate the average of the values in the specified column.

        Parameters:
        - data: DataFrame to be averaged
        - avg_column: Column by which to average the values

        Returns:
        - DataFrame with the average value
        """
        avg_value = sum(data[avg_column]) / len(data[avg_column])
        result_data = {'average': [avg_value]}
        return pd.DataFrame(result_data)

    def min_data(self, data, min_column):
        """
        Find the minimum value in the specified column.

        Parameters:
        - data: DataFrame to find the minimum value
        - min_column: Column by which to find the minimum value

        Returns:
        - DataFrame with the minimum value
        """
        min_value = min(data[min_column])
        result_data = {'min': [min_value]}
        return pd.DataFrame(result_data)

    def max_data(self, data, max_column):
        """
        Find the maximum value in the specified column.

        Parameters:
        - data: DataFrame to find the maximum value
        - max_column: Column by which to find the maximum value

        Returns:
        - DataFrame with the maximum value
        """
        max_value = max(data[max_column])
        result_data = {'max': [max_value]}
        return pd.DataFrame(result_data)











class QueryExecutor:
    def __init__(self, database):
        self.database = database

    def execute_query(self, query):
        converted_query = convert_keywords(query)

        # After converting, parse the query into tokens using parse_query
        tokens = parse_query(converted_query)
        command = tokens[0]

        if command == 'SELECT':
            return self.handle_select(tokens)
        elif command == 'INSERT':
            return self.handle_insert(tokens[1:])
        elif command == 'DELETE':
            return self.handle_delete(tokens[1:])
        elif command == 'UPDATE':
            return self.handle_update(tokens[1:])
        elif command == 'EXIT':
            return self.handle_exit()
        else:
            return "Unrecognized command."

    
    def handle_select(self, tokens):
        groupby_columns = []
        order_by = None
        aggregation = None

        if 'FROM' in tokens:
            index_from = tokens.index('FROM')
            projection_list = tokens[1:index_from]
            table_name = tokens[index_from + 1]

            # Check if GROUP BY clause is present
            if 'GROUP' in tokens:
                index_group = tokens.index('GROUP')
                groupby_columns = tokens[index_group + 2:]
                order_by = None
                if 'ORDER' in tokens:
                    # Extract ORDER BY clause if present
                    order_by_index = tokens.index('ORDER') if 'ORDER' in tokens else len(tokens)
                    order_by_clause = tokens[order_by_index:]

                    # Extract the order_by information
                    order_by = None
                    if len(order_by_clause) > 2 and order_by_clause[1] == 'BY':
                        order_by = order_by_clause[2:]

                # Extract aggregation function and column if present
                aggregation = None
                if 'SUM' in tokens or 'COUNT' in tokens or 'AVG' in tokens or 'MIN' in tokens or 'MAX' in tokens:
                    index_aggregation = min(tokens.index('SUM') if 'SUM' in tokens else len(tokens),
                                            tokens.index('COUNT') if 'COUNT' in tokens else len(tokens),
                                            tokens.index('AVG') if 'AVG' in tokens else len(tokens),
                                            tokens.index('MIN') if 'MIN' in tokens else len(tokens),
                                            tokens.index('MAX') if 'MAX' in tokens else len(tokens))
                    aggregation = tokens[index_aggregation:index_aggregation + 3]

                # Check if groupby_columns is empty before calling retrieve_data_groupby
                if groupby_columns:
                    result = self.database.retrieve_data_groupby(projection_list, table_name, groupby_columns, order_by, aggregation)

                    # Check if the result is a DataFrame
                    if isinstance(result, pd.DataFrame):
                        print(result.columns)
                    else:
                        print(result)

                    return result
                else:
                    # No GROUP BY clause, proceed with regular retrieval
                    conditions = None
                    if 'WHERE' in tokens:
                        index_where = tokens.index('WHERE')
                        conditions = tokens[index_where + 1:]

                    result = self.database.retrieve_data(projection_list, table_name, conditions)

                    # Check if the result is a DataFrame
                    if isinstance(result, pd.DataFrame):
                        print(result.columns)
                    else:
                        print(result)

                    return result
            else:
                return "Invalid SELECT query. Missing GROUP BY clause."
        else:
            return "Invalid SELECT query. Missing FROM clause."



    def handle_insert(self, tokens):
        if 'INTO' in tokens:
            index_into = tokens.index('INTO')
            table_name = tokens[index_into + 1]

            # Check if VALUES clause is present
            if 'VALUES' in tokens:
                index_values = tokens.index('VALUES')
                values = tokens[index_values + 1:]

                # Handle the insertion of values into the table
                result = self.database.insert_data(table_name, values)
                return result
            else:
                return "Invalid INSERT query. Missing VALUES clause."
        else:
            return "Invalid INSERT query. Missing INTO clause."

    def handle_delete(self, tokens):
        return "Handling DELETE: " + " ".join(tokens)

    def handle_update(self, tokens):
        return "Handling UPDATE: " + " ".join(tokens)

    def handle_exit(self):
        return "Exiting the system."

query:
# Test the GROUP BY query with COUNT(*)
query = "SELECT all FROM PropertyInfo GROUP BY city ORDER BY city DESC"
result = query_executor.execute_query(query)
print(result)


output:
Debug: Starting retrieve_data_groupby
Debug: Data retrieved successfully
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
/Users/midas/Library/CloudStorage/Dropbox/DSCI 551/Project/project copy.ipynb Cell 29 line 3
      1 # Test the GROUP BY query with COUNT(*)
      2 query = "SELECT all FROM PropertyInfo GROUP BY city ORDER BY city DESC"
----> 3 result = query_executor.execute_query(query)
      4 print(result)

/Users/midas/Library/CloudStorage/Dropbox/DSCI 551/Project/project copy.ipynb Cell 29 line 4
    476 command = tokens[0]
    478 if command == 'SELECT':
--> 479     return self.handle_select(tokens)
    480 elif command == 'INSERT':
    481     return self.handle_insert(tokens[1:])

/Users/midas/Library/CloudStorage/Dropbox/DSCI 551/Project/project copy.ipynb Cell 29 line 5
    527 # Check if groupby_columns is empty before calling retrieve_data_groupby
    528 if groupby_columns:
--> 529     result = self.database.retrieve_data_groupby(projection_list, table_name, groupby_columns, order_by, aggregation)
    531     # Check if the result is a DataFrame
    532     if isinstance(result, pd.DataFrame):

/Users/midas/Library/CloudStorage/Dropbox/DSCI 551/Project/project copy.ipynb Cell 29 line 2
    294     return aggregated_data
    295 else:
...
    986 elif isinstance(gpr, Grouper) and gpr.key is not None:
    987     # Add key to exclusions
    988     exclusions.add(gpr.key)

KeyError: 'order'


don't use opening and closing quotes separately but use ', "