AutoNote Jupyter Notebook Code Snippet Manager

autonote.py

# AutoNote version 3.8 (MySQL Database)
import json
import uuid
import mysql.connector
from IPython.display import display, clear_output, Javascript, HTML
from ipywidgets import Button, Output, HBox, Textarea, Checkbox, widgets, ButtonStyle
import os
from datetime import date
import autonote_config as ac
import colorsys
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns


class AutoNote:
    """
    AutoNote version 3.8 (MySQL Database)

    This module provides a class named AutoNote for managing code snippets in a MySQL database.
    It can be used to create, load, delete, and edit code snippet groups and their associated code blocks.
    It also provides functionality to insert code and markdown cells in Jupyter Notebooks.

    Class AutoNote:
        This class provides methods to manage code snippets in a MySQL database.
        It allows the user to create, load, delete, and edit code snippet groups and their associated code blocks.

    Methods:
        - __init__: Initializes an instance of AutoNote, sets button colors, and connects to the MySQL database.
        - connect_to_mysql_with_status: Connects to the MySQL database and displays the connection status.
        - create_code_blocks_table: Creates the 'code_blocks3' table in the database if it doesn't exist.
        - create_code_groups_table: Creates the 'code_groups3' table in the database if it doesn't exist.
        - new_code: Displays widgets to input a new code snippet group and its associated code blocks.
        - get_code_group_names: Retrieves the names of all code snippet groups from the database.
        - add_new_code: Adds a new code snippet group and its associated code blocks to the database.
        - load_code_snippets: Retrieves code snippets for a given group_id and inserts them in a Jupyter Notebook.
        - create_code_cell: Inserts a code cell with the given code in a Jupyter Notebook.
        - create_markdown_cell: Inserts a markdown cell with the given text in a Jupyter Notebook.
        - load_code: Displays widgets to select and insert a code snippet group in a Jupyter Notebook.
        - get_group_id_by_name: Retrieves the group_id for a given group_name.
        - get_code_snippets: Retrieves the code snippets for a given group_id from the database.
        - delete_code: Displays widgets to delete a code snippet group and its associated code blocks.
        - delete_group_and_snippets: Deletes a code snippet group and its associated code blocks from the database.
        - edit_groupname: Displays widgets to edit the name of a code snippet group.
        - update_group_name: Updates the name of a code snippet group in the database.
    """

    def __init__(self):
        self.but_color3 = '#d8dcde'
        self.but_color2 = '#e5e7e8'
        self.but_color1 = '#ccdbdc'
        # Connect to MySQL database
        self.connect_to_mysql_with_status()
        self.create_code_cell("# an.edit_groupname() an.print_groupnames() an.search_code() an.new_code()\nan.search_code()")

    def connect_to_mysql_with_status(self):
        # Define a button widget for connecting to the database
        connect_button = Button(description='⚡️Connect')
        connect_button.style.button_color = self.but_color3

        # Define an output widget for displaying connection status
        status_output = Output()

        # Define a function to close the database connection
        def close_connection(_):
            self.mydb.close()
            with status_output:
                status_output.clear_output()
                display(HTML("<span style='color:#b94d4e;'>🪫 Connection closed successfully.</span>"))


        # Define a function to connect to the database and update the status output widget
        def connect_to_mysql(_):
            try:
                # Establish a connection to MySQL
                self.mydb = mysql.connector.connect(
                  host=ac.host,
                  user=ac.db_user,
                  password=ac.db_password,
                  database=ac.database
                )
                self.c = self.mydb.cursor()
                with status_output:
                    status_output.clear_output()
                    display(HTML("<span style='color:#1e5945;'>🔌 Connected to <B>✩AutoNote</B> MySQL database.</span>"))


            except mysql.connector.Error as error:
                with status_output:
                    status_output.clear_output()
                    display(HTML("<span style='color:#b94d4e;'>⌽ Error while connecting to MySQL: {}</span>".format(error)))

        # Bind the connect_to_mysql() function to the connect button
        connect_button.on_click(connect_to_mysql)

        # Define a button widget to close the database connection
        close_button = Button(description='Close Connection')
        close_button.style.button_color = self.but_color2
        close_button.on_click(close_connection)

        # Display the connect button, close button, and status output widget horizontally
        display(HBox([connect_button, close_button, status_output]))


    def create_code_blocks_table(self):
        self.c.execute("""
            CREATE TABLE IF NOT EXISTS code_blocks3 (
                id INTEGER PRIMARY KEY AUTO_INCREMENT,
                group_id INTEGER NOT NULL,
                execution_order INTEGER NOT NULL,
                markdown INTEGER,
                code TEXT NOT NULL,
                FOREIGN KEY (group_id)
                    REFERENCES code_groups(id)
                    ON DELETE CASCADE
            )
        """)

        self.mydb.commit()

    def create_code_groups_table(self):
        self.c.execute("""
            CREATE TABLE IF NOT EXISTS code_groups3 (
                id INTEGER PRIMARY KEY AUTO_INCREMENT,
                group_name TEXT NOT NULL
            )
        """)

        self.mydb.commit()

    def new_code(self, no_blocks=10):
        group_name_input = widgets.Text(description='Group Name:')

        code_rows = []
        for i in range(no_blocks):
            code_text_area = Textarea(description=f'Code {i + 1}:', layout=widgets.Layout(width='100%', height='50px'))
            markdown_checkbox = Checkbox(description='Markdown', value=False)
            code_row = widgets.HBox([code_text_area, markdown_checkbox])
            code_rows.append(code_row)

        submit_button = Button(description='Submit')
        submit_button.style.button_color = self.but_color1

        def submit_new_code(_):
            group_name = group_name_input.value
            code_snippets = [(row.children[0].value, row.children[1].value) for row in code_rows if row.children[0].value.strip() != '']
            self.add_new_code(group_name, code_snippets)
            clear_output()

        submit_button.on_click(submit_new_code)
        display(group_name_input, *code_rows, submit_button)

    def get_code_group_names(self):
        self.c.execute("SELECT group_name FROM code_groups3")
        group_names = [row[0] for row in self.c.fetchall()]
        return group_names

    def add_new_code(self, group_name, code_snippets):
        self.c = self.mydb.cursor()

        # Insert new code group
        self.c.execute("INSERT INTO code_groups3 (group_name) VALUES (%s)", (group_name,))
        group_id = self.c.lastrowid

        # Insert code blocks for the group
        for idx, (code, markdown) in enumerate(code_snippets):
            self.c.execute("""
                INSERT INTO code_blocks3 (group_id, execution_order, markdown, code)
                VALUES (%s, %s, %s, %s)
            """, (group_id, idx + 1, int(markdown), code))

        self.mydb.commit()

    def load_code_snippets(self, group_id):
        code_snippets = self.get_code_snippets(group_id)
        for code, markdown in code_snippets:
            if markdown == 0:
                self.create_code_cell(code)
            elif markdown == 1:
                self.create_markdown_cell(code)


    def create_code_cell(self, code):
        cell_id = str(uuid.uuid4())
        escaped_code = json.dumps(code)
        display(Javascript(f"""
            var code = {escaped_code};
            var cell = Jupyter.notebook.insert_cell_above('code');
            cell.set_text(code);
            cell.metadata.id = '{cell_id}';
        """))

    def create_code_cell_below(self, code):
        cell_id = str(uuid.uuid4())
        escaped_code = json.dumps(code)
        display(Javascript(f"""
            var code = {escaped_code};
            var cell = Jupyter.notebook.insert_cell_below('code');
            cell.set_text(code);
            cell.metadata.id = '{cell_id}';
        """))

    def create_markdown_cell(self, text):
        cell_id = str(uuid.uuid4())
        escaped_text = json.dumps(text)
        display(Javascript(f"""
            var text = {escaped_text};
            var cell = Jupyter.notebook.insert_cell_above('markdown');
            cell.set_text(text);
            cell.metadata.id = '{cell_id}';
        """))


    def load_code(self):
        """
        Displays a dropdown list of code group names and a button to load the selected code group's snippets.
        """
        group_names = sorted(self.get_code_group_names())
        code_group_dropdown = widgets.Dropdown(options=group_names, description='Group Name:')
        insert_button = widgets.Button(description='Insert Code')
        insert_button.style.button_color = self.but_color1

        # Add custom CSS for the button text color
        custom_button_style = """
        <style>
            .custom-button .widget-label {
                color: #e5e7e8;
            }
        </style>
        """

        # Add a custom class to the Button widget
        insert_button.add_class("custom-button")

        # Define the on_click event handler
        def on_insert_click(_):
            group_name = code_group_dropdown.value
            group_id = self.get_group_id_by_name(group_name)
            self.load_code_snippets(group_id)

        # Attach the event handler to the button
        insert_button.on_click(on_insert_click)

        # Display the button with custom CSS
        display(HTML(custom_button_style))
        display(HBox([code_group_dropdown, insert_button]))


    def get_group_id_by_name(self, group_name):
        """
        Retrieves the group ID for a given group name.

        Args:
            group_name (str): The name of the code group.

        Returns:
            int: The ID of the code group.
        """
        self.c.execute("SELECT id FROM code_groups3 WHERE group_name = %s", (group_name,))
        group_id = self.c.fetchone()[0]
        return group_id

    def get_code_snippets(self, group_id):
        """
        Retrieves code snippets for a given group ID.

        Args:
            group_id (int): The ID of the code group.

        Returns:
            list: A list of tuples containing the code and markdown for each code snippet.
        """
        self.c.execute("""
            SELECT code, markdown
            FROM code_blocks3
            WHERE group_id = %s
            ORDER BY execution_order
        """, (group_id,))
        return self.c.fetchall()


    def delete_code(self):
        """
        Displays a dropdown list of code group names and a button to delete the selected code group and its snippets.
        """
        group_names = sorted(self.get_code_group_names())
        code_group_dropdown = widgets.Dropdown(options=group_names, description='Group Name:')
        delete_button = widgets.Button(description='Delete')
        delete_button.style.button_color = self.but_color1

        def on_delete_click(_):
            group_name = code_group_dropdown.value
            self.delete_group_and_snippets(group_name)
            clear_output()
            print(f"Group '{group_name}' and its code snippets have been deleted.")
            self.delete_code()  # Refresh the dropdown list

        delete_button.on_click(on_delete_click)
        display(HBox([code_group_dropdown, delete_button]))


    def delete_group_and_snippets(self, group_name):
        """
        Deletes a code group and its associated code snippets.

        Args:
            group_name (str): The name of the code group to delete.
        """
        group_id = self.get_group_id_by_name(group_name)

        # Delete code snippets
        self.c.execute("DELETE FROM code_blocks3 WHERE group_id = %s", (group_id,))
        self.mydb.commit()

        # Delete code group
        self.c.execute("DELETE FROM code_groups3 WHERE id = %s", (group_id,))
        self.mydb.commit()

    def edit_groupname(self):
        """
        Displays a dropdown list of code group names, a text input to enter a new name, and a button to update the selected code group's name.
        """
        group_names = sorted(self.get_code_group_names())
        current_group_dropdown = widgets.Dropdown(options=group_names, description='Current:')
        new_group_input = widgets.Text(description='New Name:')
        update_button = widgets.Button(description='Update')
        update_button.style.button_color = self.but_color1

        def on_update_click(_):
            current_group_name = current_group_dropdown.value
            new_group_name = new_group_input.value.strip()
            if new_group_name:
                self.update_group_name(current_group_name, new_group_name)
                clear_output()
                print(f"Group name updated from '{current_group_name}' to '{new_group_name}'.")
                self.edit_groupname()  # Refresh the dropdown lists
            else:
                print("Please enter a new group name.")

        update_button.on_click(on_update_click)
        display(HBox([current_group_dropdown, new_group_input, update_button]))


    def update_group_name(self, current_group_name, new_group_name):
        """
        Updates a code group's name.

        Args:
            current_group_name (str): The current name of the code group.
            new_group_name (str): The new name for the code group.
        """
        group_id = self.get_group_id_by_name(current_group_name)

        # Update the group name in the database
        self.c.execute("UPDATE code_groups3 SET group_name = %s WHERE id = %s", (new_group_name, group_id))
        self.mydb.commit()

    def print_groupnames(self):
        """
        Prints the names and code snippet counts of all code groups.
        """
        # Get all group names and their IDs
        self.c.execute("SELECT id, group_name FROM code_groups3 ORDER BY group_name")
        groups = self.c.fetchall()

        # Count the number of code snippets for each group
        group_counts = []
        for group_id, group_name in groups:
            self.c.execute("SELECT COUNT(*) FROM code_blocks3 WHERE group_id = %s", (group_id,))
            count = self.c.fetchone()[0]
            group_counts.append((group_name, count))

        # Print group names and their code snippet counts
        for group_name, count in group_counts:
            print(f"{group_name} ({count})")

    # New search fuynction
    def search_code(self):
        """
        Displays a text input for searching code group names, a dropdown list of filtered code group names, and a button to load the selected code group's snippets.
        """
        group_names = sorted(self.get_code_group_names())
        search_input = widgets.Text(description='Search:')
        code_group_dropdown = widgets.Dropdown(options=group_names, description='Group Name:')
        load_button = widgets.Button(description='Load Code')
        load_button.style.button_color = self.but_color1

        def on_search_change(change):
            search_text = change['new'].strip().lower()
            filtered_group_names = [group_name for group_name in group_names if search_text in group_name.lower()]
            code_group_dropdown.options = filtered_group_names

        def on_load_click(_):
            group_name = code_group_dropdown.value
            group_id = self.get_group_id_by_name(group_name)
            self.load_code_snippets(group_id)

        search_input.observe(on_search_change, names='value')
        load_button.on_click(on_load_click)
        #display(search_input, code_group_dropdown, load_button)
        display(HBox([search_input, code_group_dropdown, load_button]))

    def backup_mysql(self):
        """
        Creates a backup of the MySQL database and saves it as an SQL file.
        """
        import mysql.connector
        import subprocess

        # Replace the values in the following variables with your own database credentials
        username = ac.db_user
        password = ac.db_password
        hostname = ac.host
        database = ac.database

        # Define the filename and location for the backup file
        filename = 'autonote_mysql_backup.sql'

        # Define the mysqldump command to create the backup
        backup_command = f"mysqldump --host={hostname} --user={username} --password={password} --compact --skip-comments --skip-lock-tables {database} > {filename}"

        # Execute the mysqldump command using subprocess
        subprocess.run(backup_command, shell=True)

        print("Backup created successfully!")

    def color_shader(self, color1):
        """
        Lightens the given color by a fixed amount.

        Args:
            color1 (str): A hex color string (e.g. "#1e758a")

        Returns:
            str: A new hex color string representing the lightened color.
        """
        input_hex = color1

        # Convert the hex string to RGB values
        r, g, b = tuple(int(input_hex[i:i+2], 16) for i in (1, 3, 5))

        # Convert RGB values to HSL (hue, saturation, lightness) values
        h, l, s = colorsys.rgb_to_hls(r/255, g/255, b/255)

        # Increase the lightness value by 2/100 to make the color 2 shades lighter
        l += 0.13

        # Convert the new HSL values back to RGB values
        r, g, b = [int(c*255) for c in colorsys.hls_to_rgb(h, l, s)]

        # Convert the RGB values to a hex color string
        color2 = f"#{hex(r)[2:]:0>2}{hex(g)[2:]:0>2}{hex(b)[2:]:0>2}"

        return color2

    def plot_weeks(self, df, date_column='DATE', color1='#1e758a'):
        """
        Plots the weekly counts of occurrences in a given DataFrame for two years (2022 and 2023).

        Args:
            df (pandas.DataFrame): The input DataFrame containing the data to be plotted.
            date_column (str, optional): The name of the column containing date information. Defaults to 'DATE'.
            color1 (str, optional): The primary hex color string for the plot. Defaults to '#1e758a'.
        """

        # Calls the color_shader function to produce secondary matching color
        color2 = self.color_shader(color1)

        # Convert the date column to datetime format
        df[date_column] = pd.to_datetime(df[date_column])

        # Extract the week number and year from the date
        df['WEEK'] = df[date_column].dt.isocalendar().week
        df['YEAR'] = df[date_column].dt.year

        # Count the number of occurrences for each week and year
        weekly_counts = df.groupby(['YEAR', 'WEEK']).size().reset_index(name='COUNT')

        # Create a dataframe with all weeks of the year
        all_weeks = pd.DataFrame({'WEEK': range(1, 53)})

        # Define colors for each year
        colors = {2022: color2, 2023: color1}

        # Create subplots for the two bar charts
        fig, axes = plt.subplots(2, 1, figsize=(12, 7), sharex=True)
        fig.subplots_adjust(hspace=0.4)

        for idx, year in enumerate([2022, 2023]):
            # Merge the weekly_counts dataframe with the all_weeks dataframe for the given year
            merged_weeks = all_weeks.merge(weekly_counts[weekly_counts['YEAR'] == year], on='WEEK', how='left')

            # Fill missing counts with 0
            merged_weeks['COUNT'] = merged_weeks['COUNT'].fillna(0)

            # Create the bar plot using Matplotlib
            axes[idx].grid(True, linestyle='--', linewidth=0.5, color='lightgray', alpha=0.5)
            axes[idx].bar(merged_weeks['WEEK'], merged_weeks['COUNT'], color=colors[year])

            # Set plot labels and title
            axes[idx].set_xlabel('Week')
            axes[idx].set_ylabel('Count')
            axes[idx].set_title(f'Weekly Counts {date_column} for {year}')

        # Show the plot
        plt.show()

    def plot_months(self, df, date_column='DATE', color1='#386641'):
        """
        Plots the monthly counts of occurrences in a given DataFrame for two years (2022 and 2023).

        Args:
            df (pandas.DataFrame): The input DataFrame containing the data to be plotted.
            date_column (str, optional): The name of the column containing date information. Defaults to 'DATE'.
            color1 (str, optional): The primary hex color string for the plot. Defaults to '#386641'.
        """
        # Calls the color_shader function to produce secondary matching color
        color2 = self.color_shader(color1)

        # Convert the date column to datetime format
        df[date_column] = pd.to_datetime(df[date_column])

        # Extract the month and year from the date
        df['MONTH'] = df[date_column].dt.month
        df['YEAR'] = df[date_column].dt.year

        # Count the number of occurrences for each month and year
        monthly_counts = df.groupby(['YEAR', 'MONTH']).size().reset_index(name='COUNT')

        # Create a dataframe with all months of the year
        all_months = pd.DataFrame({'MONTH': range(1, 13)})

        # Define colors for each year
        colors = {2022: color2, 2023: color1}

        # Create subplots for the two bar charts
        fig, axes = plt.subplots(2, 1, figsize=(12, 7), sharex=True)
        fig.subplots_adjust(hspace=0.4)

        for idx, year in enumerate([2022, 2023]):
            # Merge the monthly_counts dataframe with the all_months dataframe for the given year
            merged_months = all_months.merge(monthly_counts[monthly_counts['YEAR'] == year], on='MONTH', how='left')

            # Fill missing counts with 0
            merged_months['COUNT'] = merged_months['COUNT'].fillna(0)

            # Create the bar plot using Matplotlib
            axes[idx].grid(True, linestyle='--', linewidth=0.5, color='lightgray', alpha=0.5)
            axes[idx].bar(merged_months['MONTH'], merged_months['COUNT'], color=colors[year])

            # Set plot labels and title
            axes[idx].set_xlabel('Month')
            axes[idx].set_ylabel('Count')
            axes[idx].set_title(f'Monthly Counts {date_column} for {year}')

        # Show the plot
        plt.show()

    def staff_plot(self, df, staff_column='DONEBY'):
        """
        Creates a horizontal bar plot of the count of registrations completed by each staff member.

        Args:
            df (pd.DataFrame): A pandas DataFrame containing the registration data.
            staff_column (str, optional): The name of the column in the DataFrame that represents staff members. Defaults to 'DONEBY'.

        Returns:
            bool: True if the plot is successfully displayed, otherwise False.
        """
        # Group by 'DONEBY' and count the occurrences
        grouped_data = df.groupby(staff_column).size().reset_index(name='Count')

        # Create a Seaborn bar plot
        sns.set(style='whitegrid')
        bar_plot = sns.barplot(x='Count', y=staff_column, data=grouped_data)

        # Customize plot (optional)
        bar_plot.set_title(f'Count of {staff_column}')
        bar_plot.set_xlabel('Count')
        bar_plot.set_ylabel(staff_column)
        plt.yticks(fontsize=6)

        # Display count numbers on top of the bars
        for index, row in grouped_data.iterrows():
            bar_plot.text(row['Count'], index, row['Count'], color='gray', ha='left', va='center')

        # Show the plot
        plt.show()


    def large_print(self, desc='Description', value=''):
        """
        Displays a given description and value in large font size using HTML.

        Args:
            desc (str, optional): The description text to display. Defaults to 'Description'.
            value (str, optional): The value text to display. Defaults to an empty string.
        """
        # Create an HTML string with a large font size
        html = f'<p style="font-size:18pt">{desc} <B>{value}</b></p>'

        # Display the HTML string
        display(HTML(html))

    def medium_print(self, desc='Description', value=''):
        """
        Displays a given description and value in medium font size using HTML.

        Args:
            desc (str, optional): The description text to display. Defaults to 'Description'.
            value (str, optional): The value text to display. Defaults to an empty string.
        """
        # Create an HTML string with a large font size
        html = f'<p style="font-size:16pt">{desc} <B>{value}</b></p>'

        # Display the HTML string
        display(HTML(html))