Vanna 函数

注意

通常情况下，除非进行大量自定义工作，否则您不需要使用这些函数。

术语

前缀	定义	示例
vn.get_	获取一些数据	vn.get_related_ddl(...)
vn.add_	向检索层添加内容	vn.add_question_sql(...) vn.add_ddl(...)
vn.generate_	使用 AI 基于模型中的信息生成内容	vn.generate_sql(...) [vn.generate_explanation()][vanna.base.base.VannaBase.generate_explanation]
vn.run_	运行代码 (SQL)	vn.run_sql
vn.remove_	从检索层移除内容	vn.remove_training_data
vn.connect_	连接到数据库	[vn.connect_to_snowflake(...)][vanna.base.base.VannaBase.connect_to_snowflake]
vn.update_	更新内容	不适用 -- 未使用
vn.set_	设置内容	不适用 -- 未使用

开源与扩展

Vanna.AI 是开源且可扩展的。如果您想在没有服务器的情况下使用 Vanna，请参阅此处的示例。

以下是使用默认“本地”版本 Vanna 时，代码库中实现各种函数的示例。vanna.base.VannaBase 是提供 vanna.base.VannaBase.ask 和 vanna.base.VannaBase.train 函数的基类。这些函数依赖于在子类 vanna.openai_chat.OpenAI_Chat 和 vanna.chromadb_vector.ChromaDB_VectorStore 中实现的抽象方法。vanna.openai_chat.OpenAI_Chat 使用 OpenAI API 生成 SQL 和 Plotly 代码。vanna.chromadb_vector.ChromaDB_VectorStore 使用 ChromaDB 存储训练数据并生成嵌入（embeddings）。

如果您想将 Vanna 与其他 LLMs 或数据库一起使用，您可以创建 vanna.base.VannaBase 的自己的子类并实现抽象方法。

flowchart
    subgraph VannaBase
        ask
        train
    end

    subgraph OpenAI_Chat
        get_sql_prompt
        submit_prompt
        generate_question
        generate_plotly_code
    end

    subgraph ChromaDB_VectorStore
        generate_embedding
        add_question_sql
        add_ddl
        add_documentation
        get_similar_question_sql
        get_related_ddl
        get_related_documentation
    end

VannaBase

基类: ABC

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

class VannaBase(ABC):
    def __init__(self, config=None):
        if config is None:
            config = {}

        self.config = config
        self.run_sql_is_set = False
        self.static_documentation = ""
        self.dialect = self.config.get("dialect", "SQL")
        self.language = self.config.get("language", None)
        self.max_tokens = self.config.get("max_tokens", 14000)

    def log(self, message: str, title: str = "Info"):
        print(f"{title}: {message}")

    def _response_language(self) -> str:
        if self.language is None:
            return ""

        return f"Respond in the {self.language} language."

    def generate_sql(self, question: str, allow_llm_to_see_data=False, **kwargs) -> str:
        """
        Example:
        ```python
        vn.generate_sql("What are the top 10 customers by sales?")
        ```

        Uses the LLM to generate a SQL query that answers a question. It runs the following methods:

        - [`get_similar_question_sql`][vanna.base.base.VannaBase.get_similar_question_sql]

        - [`get_related_ddl`][vanna.base.base.VannaBase.get_related_ddl]

        - [`get_related_documentation`][vanna.base.base.VannaBase.get_related_documentation]

        - [`get_sql_prompt`][vanna.base.base.VannaBase.get_sql_prompt]

        - [`submit_prompt`][vanna.base.base.VannaBase.submit_prompt]


        Args:
            question (str): The question to generate a SQL query for.
            allow_llm_to_see_data (bool): Whether to allow the LLM to see the data (for the purposes of introspecting the data to generate the final SQL).

        Returns:
            str: The SQL query that answers the question.
        """
        if self.config is not None:
            initial_prompt = self.config.get("initial_prompt", None)
        else:
            initial_prompt = None
        question_sql_list = self.get_similar_question_sql(question, **kwargs)
        ddl_list = self.get_related_ddl(question, **kwargs)
        doc_list = self.get_related_documentation(question, **kwargs)
        prompt = self.get_sql_prompt(
            initial_prompt=initial_prompt,
            question=question,
            question_sql_list=question_sql_list,
            ddl_list=ddl_list,
            doc_list=doc_list,
            **kwargs,
        )
        self.log(title="SQL Prompt", message=prompt)
        llm_response = self.submit_prompt(prompt, **kwargs)
        self.log(title="LLM Response", message=llm_response)

        if 'intermediate_sql' in llm_response:
            if not allow_llm_to_see_data:
                return "The LLM is not allowed to see the data in your database. Your question requires database introspection to generate the necessary SQL. Please set allow_llm_to_see_data=True to enable this."

            if allow_llm_to_see_data:
                intermediate_sql = self.extract_sql(llm_response)

                try:
                    self.log(title="Running Intermediate SQL", message=intermediate_sql)
                    df = self.run_sql(intermediate_sql)

                    prompt = self.get_sql_prompt(
                        initial_prompt=initial_prompt,
                        question=question,
                        question_sql_list=question_sql_list,
                        ddl_list=ddl_list,
                        doc_list=doc_list+[f"The following is a pandas DataFrame with the results of the intermediate SQL query {intermediate_sql}: \n" + df.to_markdown()],
                        **kwargs,
                    )
                    self.log(title="Final SQL Prompt", message=prompt)
                    llm_response = self.submit_prompt(prompt, **kwargs)
                    self.log(title="LLM Response", message=llm_response)
                except Exception as e:
                    return f"Error running intermediate SQL: {e}"


        return self.extract_sql(llm_response)

    def extract_sql(self, llm_response: str) -> str:
        """
        Example:
        ```python
        vn.extract_sql("Here's the SQL query in a code block: ```sql\nSELECT * FROM customers\n```")
        ```

        Extracts the SQL query from the LLM response. This is useful in case the LLM response contains other information besides the SQL query.
        Override this function if your LLM responses need custom extraction logic.

        Args:
            llm_response (str): The LLM response.

        Returns:
            str: The extracted SQL query.
        """

        # If the llm_response contains a CTE (with clause), extract the last sql between WITH and ;
        sqls = re.findall(r"\bWITH\b .*?;", llm_response, re.DOTALL)
        if sqls:
            sql = sqls[-1]
            self.log(title="Extracted SQL", message=f"{sql}")
            return sql

        # If the llm_response is not markdown formatted, extract last sql by finding select and ; in the response
        sqls = re.findall(r"SELECT.*?;", llm_response, re.DOTALL)
        if sqls:
            sql = sqls[-1]
            self.log(title="Extracted SQL", message=f"{sql}")
            return sql

        # If the llm_response contains a markdown code block, with or without the sql tag, extract the last sql from it
        sqls = re.findall(r"```sql\n(.*)```", llm_response, re.DOTALL)
        if sqls:
            sql = sqls[-1]
            self.log(title="Extracted SQL", message=f"{sql}")
            return sql

        sqls = re.findall(r"```(.*)```", llm_response, re.DOTALL)
        if sqls:
            sql = sqls[-1]
            self.log(title="Extracted SQL", message=f"{sql}")
            return sql

        return llm_response

    def is_sql_valid(self, sql: str) -> bool:
        """
        Example:
        ```python
        vn.is_sql_valid("SELECT * FROM customers")
        ```
        Checks if the SQL query is valid. This is usually used to check if we should run the SQL query or not.
        By default it checks if the SQL query is a SELECT statement. You can override this method to enable running other types of SQL queries.

        Args:
            sql (str): The SQL query to check.

        Returns:
            bool: True if the SQL query is valid, False otherwise.
        """

        parsed = sqlparse.parse(sql)

        for statement in parsed:
            if statement.get_type() == 'SELECT':
                return True

        return False

    def should_generate_chart(self, df: pd.DataFrame) -> bool:
        """
        Example:
        ```python
        vn.should_generate_chart(df)
        ```

        Checks if a chart should be generated for the given DataFrame. By default, it checks if the DataFrame has more than one row and has numerical columns.
        You can override this method to customize the logic for generating charts.

        Args:
            df (pd.DataFrame): The DataFrame to check.

        Returns:
            bool: True if a chart should be generated, False otherwise.
        """

        if len(df) > 1 and df.select_dtypes(include=['number']).shape[1] > 0:
            return True

        return False

    def generate_followup_questions(
        self, question: str, sql: str, df: pd.DataFrame, n_questions: int = 5, **kwargs
    ) -> list:
        """
        **Example:**
        ```python
        vn.generate_followup_questions("What are the top 10 customers by sales?", sql, df)
        ```

        Generate a list of followup questions that you can ask Vanna.AI.

        Args:
            question (str): The question that was asked.
            sql (str): The LLM-generated SQL query.
            df (pd.DataFrame): The results of the SQL query.
            n_questions (int): Number of follow-up questions to generate.

        Returns:
            list: A list of followup questions that you can ask Vanna.AI.
        """

        message_log = [
            self.system_message(
                f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe SQL query for this question was: {sql}\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
            ),
            self.user_message(
                f"Generate a list of {n_questions} followup questions that the user might ask about this data. Respond with a list of questions, one per line. Do not answer with any explanations -- just the questions. Remember that there should be an unambiguous SQL query that can be generated from the question. Prefer questions that are answerable outside of the context of this conversation. Prefer questions that are slight modifications of the SQL query that was generated that allow digging deeper into the data. Each question will be turned into a button that the user can click to generate a new SQL query so don't use 'example' type questions. Each question must have a one-to-one correspondence with an instantiated SQL query." +
                self._response_language()
            ),
        ]

        llm_response = self.submit_prompt(message_log, **kwargs)

        numbers_removed = re.sub(r"^\d+\.\s*", "", llm_response, flags=re.MULTILINE)
        return numbers_removed.split("\n")

    def generate_questions(self, **kwargs) -> List[str]:
        """
        **Example:**
        ```python
        vn.generate_questions()
        ```

        Generate a list of questions that you can ask Vanna.AI.
        """
        question_sql = self.get_similar_question_sql(question="", **kwargs)

        return [q["question"] for q in question_sql]

    def generate_summary(self, question: str, df: pd.DataFrame, **kwargs) -> str:
        """
        **Example:**
        ```python
        vn.generate_summary("What are the top 10 customers by sales?", df)
        ```

        Generate a summary of the results of a SQL query.

        Args:
            question (str): The question that was asked.
            df (pd.DataFrame): The results of the SQL query.

        Returns:
            str: The summary of the results of the SQL query.
        """

        message_log = [
            self.system_message(
                f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
            ),
            self.user_message(
                "Briefly summarize the data based on the question that was asked. Do not respond with any additional explanation beyond the summary." +
                self._response_language()
            ),
        ]

        summary = self.submit_prompt(message_log, **kwargs)

        return summary

    # ----------------- Use Any Embeddings API ----------------- #
    @abstractmethod
    def generate_embedding(self, data: str, **kwargs) -> List[float]:
        pass

    # ----------------- Use Any Database to Store and Retrieve Context ----------------- #
    @abstractmethod
    def get_similar_question_sql(self, question: str, **kwargs) -> list:
        """
        This method is used to get similar questions and their corresponding SQL statements.

        Args:
            question (str): The question to get similar questions and their corresponding SQL statements for.

        Returns:
            list: A list of similar questions and their corresponding SQL statements.
        """
        pass

    @abstractmethod
    def get_related_ddl(self, question: str, **kwargs) -> list:
        """
        This method is used to get related DDL statements to a question.

        Args:
            question (str): The question to get related DDL statements for.

        Returns:
            list: A list of related DDL statements.
        """
        pass

    @abstractmethod
    def get_related_documentation(self, question: str, **kwargs) -> list:
        """
        This method is used to get related documentation to a question.

        Args:
            question (str): The question to get related documentation for.

        Returns:
            list: A list of related documentation.
        """
        pass

    @abstractmethod
    def add_question_sql(self, question: str, sql: str, **kwargs) -> str:
        """
        This method is used to add a question and its corresponding SQL query to the training data.

        Args:
            question (str): The question to add.
            sql (str): The SQL query to add.

        Returns:
            str: The ID of the training data that was added.
        """
        pass

    @abstractmethod
    def add_ddl(self, ddl: str, **kwargs) -> str:
        """
        This method is used to add a DDL statement to the training data.

        Args:
            ddl (str): The DDL statement to add.

        Returns:
            str: The ID of the training data that was added.
        """
        pass

    @abstractmethod
    def add_documentation(self, documentation: str, **kwargs) -> str:
        """
        This method is used to add documentation to the training data.

        Args:
            documentation (str): The documentation to add.

        Returns:
            str: The ID of the training data that was added.
        """
        pass

    @abstractmethod
    def get_training_data(self, **kwargs) -> pd.DataFrame:
        """
        Example:
        ```python
        vn.get_training_data()
        ```

        This method is used to get all the training data from the retrieval layer.

        Returns:
            pd.DataFrame: The training data.
        """
        pass

    @abstractmethod
    def remove_training_data(id: str, **kwargs) -> bool:
        """
        Example:
        ```python
        vn.remove_training_data(id="123-ddl")
        ```

        This method is used to remove training data from the retrieval layer.

        Args:
            id (str): The ID of the training data to remove.

        Returns:
            bool: True if the training data was removed, False otherwise.
        """
        pass

    # ----------------- Use Any Language Model API ----------------- #

    @abstractmethod
    def system_message(self, message: str) -> any:
        pass

    @abstractmethod
    def user_message(self, message: str) -> any:
        pass

    @abstractmethod
    def assistant_message(self, message: str) -> any:
        pass

    def str_to_approx_token_count(self, string: str) -> int:
        return len(string) / 4

    def add_ddl_to_prompt(
        self, initial_prompt: str, ddl_list: list[str], max_tokens: int = 14000
    ) -> str:
        if len(ddl_list) > 0:
            initial_prompt += "\n===Tables \n"

            for ddl in ddl_list:
                if (
                    self.str_to_approx_token_count(initial_prompt)
                    + self.str_to_approx_token_count(ddl)
                    < max_tokens
                ):
                    initial_prompt += f"{ddl}\n\n"

        return initial_prompt

    def add_documentation_to_prompt(
        self,
        initial_prompt: str,
        documentation_list: list[str],
        max_tokens: int = 14000,
    ) -> str:
        if len(documentation_list) > 0:
            initial_prompt += "\n===Additional Context \n\n"

            for documentation in documentation_list:
                if (
                    self.str_to_approx_token_count(initial_prompt)
                    + self.str_to_approx_token_count(documentation)
                    < max_tokens
                ):
                    initial_prompt += f"{documentation}\n\n"

        return initial_prompt

    def add_sql_to_prompt(
        self, initial_prompt: str, sql_list: list[str], max_tokens: int = 14000
    ) -> str:
        if len(sql_list) > 0:
            initial_prompt += "\n===Question-SQL Pairs\n\n"

            for question in sql_list:
                if (
                    self.str_to_approx_token_count(initial_prompt)
                    + self.str_to_approx_token_count(question["sql"])
                    < max_tokens
                ):
                    initial_prompt += f"{question['question']}\n{question['sql']}\n\n"

        return initial_prompt

    def get_sql_prompt(
        self,
        initial_prompt : str,
        question: str,
        question_sql_list: list,
        ddl_list: list,
        doc_list: list,
        **kwargs,
    ):
        """
        Example:
        ```python
        vn.get_sql_prompt(
            question="What are the top 10 customers by sales?",
            question_sql_list=[{"question": "What are the top 10 customers by sales?", "sql": "SELECT * FROM customers ORDER BY sales DESC LIMIT 10"}],
            ddl_list=["CREATE TABLE customers (id INT, name TEXT, sales DECIMAL)"],
            doc_list=["The customers table contains information about customers and their sales."],
        )

        ```

        This method is used to generate a prompt for the LLM to generate SQL.

        Args:
            question (str): The question to generate SQL for.
            question_sql_list (list): A list of questions and their corresponding SQL statements.
            ddl_list (list): A list of DDL statements.
            doc_list (list): A list of documentation.

        Returns:
            any: The prompt for the LLM to generate SQL.
        """

        if initial_prompt is None:
            initial_prompt = f"You are a {self.dialect} expert. " + \
            "Please help to generate a SQL query to answer the question. Your response should ONLY be based on the given context and follow the response guidelines and format instructions. "

        initial_prompt = self.add_ddl_to_prompt(
            initial_prompt, ddl_list, max_tokens=self.max_tokens
        )

        if self.static_documentation != "":
            doc_list.append(self.static_documentation)

        initial_prompt = self.add_documentation_to_prompt(
            initial_prompt, doc_list, max_tokens=self.max_tokens
        )

        initial_prompt += (
            "===Response Guidelines \n"
            "1. If the provided context is sufficient, please generate a valid SQL query without any explanations for the question. \n"
            "2. If the provided context is almost sufficient but requires knowledge of a specific string in a particular column, please generate an intermediate SQL query to find the distinct strings in that column. Prepend the query with a comment saying intermediate_sql \n"
            "3. If the provided context is insufficient, please explain why it can't be generated. \n"
            "4. Please use the most relevant table(s). \n"
            "5. If the question has been asked and answered before, please repeat the answer exactly as it was given before. \n"
        )

        message_log = [self.system_message(initial_prompt)]

        for example in question_sql_list:
            if example is None:
                print("example is None")
            else:
                if example is not None and "question" in example and "sql" in example:
                    message_log.append(self.user_message(example["question"]))
                    message_log.append(self.assistant_message(example["sql"]))

        message_log.append(self.user_message(question))

        return message_log

    def get_followup_questions_prompt(
        self,
        question: str,
        question_sql_list: list,
        ddl_list: list,
        doc_list: list,
        **kwargs,
    ) -> list:
        initial_prompt = f"The user initially asked the question: '{question}': \n\n"

        initial_prompt = self.add_ddl_to_prompt(
            initial_prompt, ddl_list, max_tokens=self.max_tokens
        )

        initial_prompt = self.add_documentation_to_prompt(
            initial_prompt, doc_list, max_tokens=self.max_tokens
        )

        initial_prompt = self.add_sql_to_prompt(
            initial_prompt, question_sql_list, max_tokens=self.max_tokens
        )

        message_log = [self.system_message(initial_prompt)]
        message_log.append(
            self.user_message(
                "Generate a list of followup questions that the user might ask about this data. Respond with a list of questions, one per line. Do not answer with any explanations -- just the questions."
            )
        )

        return message_log

    @abstractmethod
    def submit_prompt(self, prompt, **kwargs) -> str:
        """
        Example:
        ```python
        vn.submit_prompt(
            [
                vn.system_message("The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."),
                vn.user_message("What are the top 10 customers by sales?"),
            ]
        )
        ```

        This method is used to submit a prompt to the LLM.

        Args:
            prompt (any): The prompt to submit to the LLM.

        Returns:
            str: The response from the LLM.
        """
        pass

    def generate_question(self, sql: str, **kwargs) -> str:
        response = self.submit_prompt(
            [
                self.system_message(
                    "The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."
                ),
                self.user_message(sql),
            ],
            **kwargs,
        )

        return response

    def _extract_python_code(self, markdown_string: str) -> str:
        # Regex pattern to match Python code blocks
        pattern = r"```[\w\s]*python\n([\s\S]*?)```|```([\s\S]*?)```"

        # Find all matches in the markdown string
        matches = re.findall(pattern, markdown_string, re.IGNORECASE)

        # Extract the Python code from the matches
        python_code = []
        for match in matches:
            python = match[0] if match[0] else match[1]
            python_code.append(python.strip())

        if len(python_code) == 0:
            return markdown_string

        return python_code[0]

    def _sanitize_plotly_code(self, raw_plotly_code: str) -> str:
        # Remove the fig.show() statement from the plotly code
        plotly_code = raw_plotly_code.replace("fig.show()", "")

        return plotly_code

    def generate_plotly_code(
        self, question: str = None, sql: str = None, df_metadata: str = None, **kwargs
    ) -> str:
        if question is not None:
            system_msg = f"The following is a pandas DataFrame that contains the results of the query that answers the question the user asked: '{question}'"
        else:
            system_msg = "The following is a pandas DataFrame "

        if sql is not None:
            system_msg += f"\n\nThe DataFrame was produced using this query: {sql}\n\n"

        system_msg += f"The following is information about the resulting pandas DataFrame 'df': \n{df_metadata}"

        message_log = [
            self.system_message(system_msg),
            self.user_message(
                "Can you generate the Python plotly code to chart the results of the dataframe? Assume the data is in a pandas dataframe called 'df'. If there is only one value in the dataframe, use an Indicator. Respond with only Python code. Do not answer with any explanations -- just the code."
            ),
        ]

        plotly_code = self.submit_prompt(message_log, kwargs=kwargs)

        return self._sanitize_plotly_code(self._extract_python_code(plotly_code))

    # ----------------- Connect to Any Database to run the Generated SQL ----------------- #

    def connect_to_snowflake(
        self,
        account: str,
        username: str,
        password: str,
        database: str,
        role: Union[str, None] = None,
        warehouse: Union[str, None] = None,
    ):
        try:
            snowflake = __import__("snowflake.connector")
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method, run command:"
                " \npip install vanna[snowflake]"
            )

        if username == "my-username":
            username_env = os.getenv("SNOWFLAKE_USERNAME")

            if username_env is not None:
                username = username_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake username.")

        if password == "my-password":
            password_env = os.getenv("SNOWFLAKE_PASSWORD")

            if password_env is not None:
                password = password_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake password.")

        if account == "my-account":
            account_env = os.getenv("SNOWFLAKE_ACCOUNT")

            if account_env is not None:
                account = account_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake account.")

        if database == "my-database":
            database_env = os.getenv("SNOWFLAKE_DATABASE")

            if database_env is not None:
                database = database_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake database.")

        conn = snowflake.connector.connect(
            user=username,
            password=password,
            account=account,
            database=database,
            client_session_keep_alive=True
        )

        def run_sql_snowflake(sql: str) -> pd.DataFrame:
            cs = conn.cursor()

            if role is not None:
                cs.execute(f"USE ROLE {role}")

            if warehouse is not None:
                cs.execute(f"USE WAREHOUSE {warehouse}")
            cs.execute(f"USE DATABASE {database}")

            cur = cs.execute(sql)

            results = cur.fetchall()

            # Create a pandas dataframe from the results
            df = pd.DataFrame(results, columns=[desc[0] for desc in cur.description])

            return df

        self.dialect = "Snowflake SQL"
        self.run_sql = run_sql_snowflake
        self.run_sql_is_set = True

    def connect_to_sqlite(self, url: str):
        """
        Connect to a SQLite database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            url (str): The URL of the database to connect to.

        Returns:
            None
        """

        # URL of the database to download

        # Path to save the downloaded database
        path = os.path.basename(urlparse(url).path)

        # Download the database if it doesn't exist
        if not os.path.exists(url):
            response = requests.get(url)
            response.raise_for_status()  # Check that the request was successful
            with open(path, "wb") as f:
                f.write(response.content)
            url = path

        # Connect to the database
        conn = sqlite3.connect(url, check_same_thread=False)

        def run_sql_sqlite(sql: str):
            return pd.read_sql_query(sql, conn)

        self.dialect = "SQLite"
        self.run_sql = run_sql_sqlite
        self.run_sql_is_set = True

    def connect_to_postgres(
        self,
        host: str = None,
        dbname: str = None,
        user: str = None,
        password: str = None,
        port: int = None,
    ):
        """
        Connect to postgres using the psycopg2 connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
        **Example:**
        ```python
        vn.connect_to_postgres(
            host="myhost",
            dbname="mydatabase",
            user="myuser",
            password="mypassword",
            port=5432
        )
        ```
        Args:
            host (str): The postgres host.
            dbname (str): The postgres database name.
            user (str): The postgres user.
            password (str): The postgres password.
            port (int): The postgres Port.
        """

        try:
            import psycopg2
            import psycopg2.extras
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install vanna[postgres]"
            )

        if not host:
            host = os.getenv("HOST")

        if not host:
            raise ImproperlyConfigured("Please set your postgres host")

        if not dbname:
            dbname = os.getenv("DATABASE")

        if not dbname:
            raise ImproperlyConfigured("Please set your postgres database")

        if not user:
            user = os.getenv("PG_USER")

        if not user:
            raise ImproperlyConfigured("Please set your postgres user")

        if not password:
            password = os.getenv("PASSWORD")

        if not password:
            raise ImproperlyConfigured("Please set your postgres password")

        if not port:
            port = os.getenv("PORT")

        if not port:
            raise ImproperlyConfigured("Please set your postgres port")

        conn = None

        try:
            conn = psycopg2.connect(
                host=host,
                dbname=dbname,
                user=user,
                password=password,
                port=port,
            )
        except psycopg2.Error as e:
            raise ValidationError(e)

        def run_sql_postgres(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    cs = conn.cursor()
                    cs.execute(sql)
                    results = cs.fetchall()

                    # Create a pandas dataframe from the results
                    df = pd.DataFrame(
                        results, columns=[desc[0] for desc in cs.description]
                    )
                    return df

                except psycopg2.Error as e:
                    conn.rollback()
                    raise ValidationError(e)

                except Exception as e:
                    conn.rollback()
                    raise e

        self.dialect = "PostgreSQL"
        self.run_sql_is_set = True
        self.run_sql = run_sql_postgres


    def connect_to_mysql(
            self,
            host: str = None,
            dbname: str = None,
            user: str = None,
            password: str = None,
            port: int = None,
    ):

        try:
            import pymysql.cursors
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install PyMySQL"
            )

        if not host:
            host = os.getenv("HOST")

        if not host:
            raise ImproperlyConfigured("Please set your MySQL host")

        if not dbname:
            dbname = os.getenv("DATABASE")

        if not dbname:
            raise ImproperlyConfigured("Please set your MySQL database")

        if not user:
            user = os.getenv("USER")

        if not user:
            raise ImproperlyConfigured("Please set your MySQL user")

        if not password:
            password = os.getenv("PASSWORD")

        if not password:
            raise ImproperlyConfigured("Please set your MySQL password")

        if not port:
            port = os.getenv("PORT")

        if not port:
            raise ImproperlyConfigured("Please set your MySQL port")

        conn = None

        try:
            conn = pymysql.connect(host=host,
                                   user=user,
                                   password=password,
                                   database=dbname,
                                   port=port,
                                   cursorclass=pymysql.cursors.DictCursor)
        except pymysql.Error as e:
            raise ValidationError(e)

        def run_sql_mysql(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    conn.ping(reconnect=True)
                    cs = conn.cursor()
                    cs.execute(sql)
                    results = cs.fetchall()

                    # Create a pandas dataframe from the results
                    df = pd.DataFrame(
                        results, columns=[desc[0] for desc in cs.description]
                    )
                    return df

                except pymysql.Error as e:
                    conn.rollback()
                    raise ValidationError(e)

                except Exception as e:
                    conn.rollback()
                    raise e

        self.run_sql_is_set = True
        self.run_sql = run_sql_mysql

    def connect_to_clickhouse(
            self,
            host: str = None,
            dbname: str = None,
            user: str = None,
            password: str = None,
            port: int = None,
    ):

        try:
            import clickhouse_connect
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install clickhouse_connect"
            )

        if not host:
            host = os.getenv("HOST")

        if not host:
            raise ImproperlyConfigured("Please set your ClickHouse host")

        if not dbname:
            dbname = os.getenv("DATABASE")

        if not dbname:
            raise ImproperlyConfigured("Please set your ClickHouse database")

        if not user:
            user = os.getenv("USER")

        if not user:
            raise ImproperlyConfigured("Please set your ClickHouse user")

        if not password:
            password = os.getenv("PASSWORD")

        if not password:
            raise ImproperlyConfigured("Please set your ClickHouse password")

        if not port:
            port = os.getenv("PORT")

        if not port:
            raise ImproperlyConfigured("Please set your ClickHouse port")

        conn = None

        try:
            conn = clickhouse_connect.get_client(
                host=host,
                port=port,
                username=user,
                password=password,
                database=dbname,
            )
            print(conn)
        except Exception as e:
            raise ValidationError(e)

        def run_sql_clickhouse(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    result = conn.query(sql)
                    results = result.result_rows

                    # Create a pandas dataframe from the results
                    df = pd.DataFrame(results, columns=result.column_names)
                    return df

                except Exception as e:
                    raise e

        self.run_sql_is_set = True
        self.run_sql = run_sql_clickhouse

    def connect_to_oracle(
    self,
    user: str = None,
    password: str = None,
    dsn: str = None,
    ):

        """
        Connect to an Oracle db using oracledb package. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
        **Example:**
        ```python
        vn.connect_to_oracle(
        user="username",
        password="password",
        dns="host:port/sid",
        )
        ```
        Args:
            USER (str): Oracle db user name.
            PASSWORD (str): Oracle db user password.
            DSN (str): Oracle db host ip - host:port/sid.
        """

        try:
            import oracledb
        except ImportError:

            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install oracledb"
            )

        if not dsn:
            dsn = os.getenv("DSN")

        if not dsn:
            raise ImproperlyConfigured("Please set your Oracle dsn which should include host:port/sid")

        if not user:
            user = os.getenv("USER")

        if not user:
            raise ImproperlyConfigured("Please set your Oracle db user")

        if not password:
            password = os.getenv("PASSWORD")

        if not password:
            raise ImproperlyConfigured("Please set your Oracle db password")

        conn = None

        try:
            conn = oracledb.connect(
                user=user,
                password=password,
                dsn=dsn,
                )
        except oracledb.Error as e:
            raise ValidationError(e)

        def run_sql_oracle(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    sql = sql.rstrip()
                    if sql.endswith(';'): #fix for a known problem with Oracle db where an extra ; will cause an error.
                        sql = sql[:-1]

                    cs = conn.cursor()
                    cs.execute(sql)
                    results = cs.fetchall()

                    # Create a pandas dataframe from the results
                    df = pd.DataFrame(
                        results, columns=[desc[0] for desc in cs.description]
                    )
                    return df

                except oracledb.Error as e:
                    conn.rollback()
                    raise ValidationError(e)

                except Exception as e:
                    conn.rollback()
                    raise e

        self.run_sql_is_set = True
        self.run_sql = run_sql_oracle

    def connect_to_bigquery(self, cred_file_path: str = None, project_id: str = None):
        """
        Connect to gcs using the bigquery connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
        **Example:**
        ```python
        vn.connect_to_bigquery(
            project_id="myprojectid",
            cred_file_path="path/to/credentials.json",
        )
        ```
        Args:
            project_id (str): The gcs project id.
            cred_file_path (str): The gcs credential file path
        """

        try:
            from google.api_core.exceptions import GoogleAPIError
            from google.cloud import bigquery
            from google.oauth2 import service_account
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method, run command:"
                " \npip install vanna[bigquery]"
            )

        if not project_id:
            project_id = os.getenv("PROJECT_ID")

        if not project_id:
            raise ImproperlyConfigured("Please set your Google Cloud Project ID.")

        import sys

        if "google.colab" in sys.modules:
            try:
                from google.colab import auth

                auth.authenticate_user()
            except Exception as e:
                raise ImproperlyConfigured(e)
        else:
            print("Not using Google Colab.")

        conn = None

        if not cred_file_path:
            try:
                conn = bigquery.Client(project=project_id)
            except:
                print("Could not found any google cloud implicit credentials")
        else:
            # Validate file path and pemissions
            validate_config_path(cred_file_path)

        if not conn:
            with open(cred_file_path, "r") as f:
                credentials = service_account.Credentials.from_service_account_info(
                    json.loads(f.read()),
                    scopes=["https://www.googleapis.com/auth/cloud-platform"],
                )

            try:
                conn = bigquery.Client(project=project_id, credentials=credentials)
            except:
                raise ImproperlyConfigured(
                    "Could not connect to bigquery please correct credentials"
                )

        def run_sql_bigquery(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    job = conn.query(sql)
                    df = job.result().to_dataframe()
                    return df
                except GoogleAPIError as error:
                    errors = []
                    for error in error.errors:
                        errors.append(error["message"])
                    raise errors
            return None

        self.dialect = "BigQuery SQL"
        self.run_sql_is_set = True
        self.run_sql = run_sql_bigquery

    def connect_to_duckdb(self, url: str, init_sql: str = None):
        """
        Connect to a DuckDB database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            url (str): The URL of the database to connect to. Use :memory: to create an in-memory database. Use md: or motherduck: to use the MotherDuck database.
            init_sql (str, optional): SQL to run when connecting to the database. Defaults to None.

        Returns:
            None
        """
        try:
            import duckdb
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install vanna[duckdb]"
            )
        # URL of the database to download
        if url == ":memory:" or url == "":
            path = ":memory:"
        else:
            # Path to save the downloaded database
            print(os.path.exists(url))
            if os.path.exists(url):
                path = url
            elif url.startswith("md") or url.startswith("motherduck"):
                path = url
            else:
                path = os.path.basename(urlparse(url).path)
                # Download the database if it doesn't exist
                if not os.path.exists(path):
                    response = requests.get(url)
                    response.raise_for_status()  # Check that the request was successful
                    with open(path, "wb") as f:
                        f.write(response.content)

        # Connect to the database
        conn = duckdb.connect(path)
        if init_sql:
            conn.query(init_sql)

        def run_sql_duckdb(sql: str):
            return conn.query(sql).to_df()

        self.dialect = "DuckDB SQL"
        self.run_sql = run_sql_duckdb
        self.run_sql_is_set = True

    def connect_to_mssql(self, odbc_conn_str: str):
        """
        Connect to a Microsoft SQL Server database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            odbc_conn_str (str): The ODBC connection string.

        Returns:
            None
        """
        try:
            import pyodbc
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: pip install pyodbc"
            )

        try:
            import sqlalchemy as sa
            from sqlalchemy.engine import URL
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: pip install sqlalchemy"
            )

        connection_url = URL.create(
            "mssql+pyodbc", query={"odbc_connect": odbc_conn_str}
        )

        from sqlalchemy import create_engine

        engine = create_engine(connection_url)

        def run_sql_mssql(sql: str):
            # Execute the SQL statement and return the result as a pandas DataFrame
            with engine.begin() as conn:
                df = pd.read_sql_query(sa.text(sql), conn)
                conn.close()
                return df

            raise Exception("Couldn't run sql")

        self.dialect = "T-SQL / Microsoft SQL Server"
        self.run_sql = run_sql_mssql
        self.run_sql_is_set = True
    def connect_to_presto(
      self,
      host: str,
      catalog: str = 'hive',
      schema: str = 'default',
      user: str = None,
      password: str = None,
      port: int = None,
      combined_pem_path: str = None,
      protocol: str = 'https',
      requests_kwargs: dict = None
    ):
      """
        Connect to a Presto database using the specified parameters.

        Args:
            host (str): The host address of the Presto database.
            catalog (str): The catalog to use in the Presto environment.
            schema (str): The schema to use in the Presto environment.
            user (str): The username for authentication.
            password (str): The password for authentication.
            port (int): The port number for the Presto connection.
            combined_pem_path (str): The path to the combined pem file for SSL connection.
            protocol (str): The protocol to use for the connection (default is 'https').
            requests_kwargs (dict): Additional keyword arguments for requests.

        Raises:
            DependencyError: If required dependencies are not installed.
            ImproperlyConfigured: If essential configuration settings are missing.

        Returns:
            None
      """
      try:
        from pyhive import presto
      except ImportError:
        raise DependencyError(
          "You need to install required dependencies to execute this method,"
          " run command: \npip install pyhive"
        )

      if not host:
        host = os.getenv("PRESTO_HOST")

      if not host:
        raise ImproperlyConfigured("Please set your presto host")

      if not catalog:
        catalog = os.getenv("PRESTO_CATALOG")

      if not catalog:
        raise ImproperlyConfigured("Please set your presto catalog")

      if not user:
        user = os.getenv("PRESTO_USER")

      if not user:
        raise ImproperlyConfigured("Please set your presto user")

      if not password:
        password = os.getenv("PRESTO_PASSWORD")

      if not port:
        port = os.getenv("PRESTO_PORT")

      if not port:
        raise ImproperlyConfigured("Please set your presto port")

      conn = None

      try:
        if requests_kwargs is None and combined_pem_path is not None:
          # use the combined pem file to verify the SSL connection
          requests_kwargs = {
            'verify': combined_pem_path,  # 使用转换后得到的 PEM 文件进行 SSL 验证
          }
        conn = presto.Connection(host=host,
                                 username=user,
                                 password=password,
                                 catalog=catalog,
                                 schema=schema,
                                 port=port,
                                 protocol=protocol,
                                 requests_kwargs=requests_kwargs)
      except presto.Error as e:
        raise ValidationError(e)

      def run_sql_presto(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
          try:
            sql = sql.rstrip()
            # fix for a known problem with presto db where an extra ; will cause an error.
            if sql.endswith(';'):
                sql = sql[:-1]
            cs = conn.cursor()
            cs.execute(sql)
            results = cs.fetchall()

            # Create a pandas dataframe from the results
            df = pd.DataFrame(
              results, columns=[desc[0] for desc in cs.description]
            )
            return df

          except presto.Error as e:
            print(e)
            raise ValidationError(e)

          except Exception as e:
            print(e)
            raise e

      self.run_sql_is_set = True
      self.run_sql = run_sql_presto

    def connect_to_hive(
      self,
      host: str = None,
      dbname: str = 'default',
      user: str = None,
      password: str = None,
      port: int = None,
      auth: str = 'CUSTOM'
    ):
      """
        Connect to a Hive database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
        Connect to a Hive database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            host (str): The host of the Hive database.
            dbname (str): The name of the database to connect to.
            user (str): The username to use for authentication.
            password (str): The password to use for authentication.
            port (int): The port to use for the connection.
            auth (str): The authentication method to use.

        Returns:
            None
      """

      try:
        from pyhive import hive
      except ImportError:
        raise DependencyError(
          "You need to install required dependencies to execute this method,"
          " run command: \npip install pyhive"
        )

      if not host:
        host = os.getenv("HIVE_HOST")

      if not host:
        raise ImproperlyConfigured("Please set your hive host")

      if not dbname:
        dbname = os.getenv("HIVE_DATABASE")

      if not dbname:
        raise ImproperlyConfigured("Please set your hive database")

      if not user:
        user = os.getenv("HIVE_USER")

      if not user:
        raise ImproperlyConfigured("Please set your hive user")

      if not password:
        password = os.getenv("HIVE_PASSWORD")

      if not port:
        port = os.getenv("HIVE_PORT")

      if not port:
        raise ImproperlyConfigured("Please set your hive port")

      conn = None

      try:
        conn = hive.Connection(host=host,
                               username=user,
                               password=password,
                               database=dbname,
                               port=port,
                               auth=auth)
      except hive.Error as e:
        raise ValidationError(e)

      def run_sql_hive(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
          try:
            cs = conn.cursor()
            cs.execute(sql)
            results = cs.fetchall()

            # Create a pandas dataframe from the results
            df = pd.DataFrame(
              results, columns=[desc[0] for desc in cs.description]
            )
            return df

          except hive.Error as e:
            print(e)
            raise ValidationError(e)

          except Exception as e:
            print(e)
            raise e

      self.run_sql_is_set = True
      self.run_sql = run_sql_hive

    def run_sql(self, sql: str, **kwargs) -> pd.DataFrame:
        """
        Example:
        ```python
        vn.run_sql("SELECT * FROM my_table")
        ```

        Run a SQL query on the connected database.

        Args:
            sql (str): The SQL query to run.

        Returns:
            pd.DataFrame: The results of the SQL query.
        """
        raise Exception(
            "You need to connect to a database first by running vn.connect_to_snowflake(), vn.connect_to_postgres(), similar function, or manually set vn.run_sql"
        )

    def ask(
        self,
        question: Union[str, None] = None,
        print_results: bool = True,
        auto_train: bool = True,
        visualize: bool = True,  # if False, will not generate plotly code
        allow_llm_to_see_data: bool = False,
    ) -> Union[
        Tuple[
            Union[str, None],
            Union[pd.DataFrame, None],
            Union[plotly.graph_objs.Figure, None],
        ],
        None,
    ]:
        """
        **Example:**
        ```python
        vn.ask("What are the top 10 customers by sales?")
        ```

        Ask Vanna.AI a question and get the SQL query that answers it.

        Args:
            question (str): The question to ask.
            print_results (bool): Whether to print the results of the SQL query.
            auto_train (bool): Whether to automatically train Vanna.AI on the question and SQL query.
            visualize (bool): Whether to generate plotly code and display the plotly figure.

        Returns:
            Tuple[str, pd.DataFrame, plotly.graph_objs.Figure]: The SQL query, the results of the SQL query, and the plotly figure.
        """

        if question is None:
            question = input("Enter a question: ")

        try:
            sql = self.generate_sql(question=question, allow_llm_to_see_data=allow_llm_to_see_data)
        except Exception as e:
            print(e)
            return None, None, None

        if print_results:
            try:
                Code = __import__("IPython.display", fromList=["Code"]).Code
                display(Code(sql))
            except Exception as e:
                print(sql)

        if self.run_sql_is_set is False:
            print(
                "If you want to run the SQL query, connect to a database first. See here: https://vanna.org.cn/docs/databases.html"
            )

            if print_results:
                return None
            else:
                return sql, None, None

        try:
            df = self.run_sql(sql)

            if print_results:
                try:
                    display = __import__(
                        "IPython.display", fromList=["display"]
                    ).display
                    display(df)
                except Exception as e:
                    print(df)

            if len(df) > 0 and auto_train:
                self.add_question_sql(question=question, sql=sql)
            # Only generate plotly code if visualize is True
            if visualize:
                try:
                    plotly_code = self.generate_plotly_code(
                        question=question,
                        sql=sql,
                        df_metadata=f"Running df.dtypes gives:\n {df.dtypes}",
                    )
                    fig = self.get_plotly_figure(plotly_code=plotly_code, df=df)
                    if print_results:
                        try:
                            display = __import__(
                                "IPython.display", fromlist=["display"]
                            ).display
                            Image = __import__(
                                "IPython.display", fromlist=["Image"]
                            ).Image
                            img_bytes = fig.to_image(format="png", scale=2)
                            display(Image(img_bytes))
                        except Exception as e:
                            fig.show()
                except Exception as e:
                    # Print stack trace
                    traceback.print_exc()
                    print("Couldn't run plotly code: ", e)
                    if print_results:
                        return None
                    else:
                        return sql, df, None
            else:
                return sql, df, None

        except Exception as e:
            print("Couldn't run sql: ", e)
            if print_results:
                return None
            else:
                return sql, None, None
        return sql, df, fig

    def train(
        self,
        question: str = None,
        sql: str = None,
        ddl: str = None,
        documentation: str = None,
        plan: TrainingPlan = None,
    ) -> str:
        """
        **Example:**
        ```python
        vn.train()
        ```

        Train Vanna.AI on a question and its corresponding SQL query.
        If you call it with no arguments, it will check if you connected to a database and it will attempt to train on the metadata of that database.
        If you call it with the sql argument, it's equivalent to [`vn.add_question_sql()`][vanna.base.base.VannaBase.add_question_sql].
        If you call it with the ddl argument, it's equivalent to [`vn.add_ddl()`][vanna.base.base.VannaBase.add_ddl].
        If you call it with the documentation argument, it's equivalent to [`vn.add_documentation()`][vanna.base.base.VannaBase.add_documentation].
        Additionally, you can pass a [`TrainingPlan`][vanna.types.TrainingPlan] object. Get a training plan with [`vn.get_training_plan_generic()`][vanna.base.base.VannaBase.get_training_plan_generic].

        Args:
            question (str): The question to train on.
            sql (str): The SQL query to train on.
            ddl (str):  The DDL statement.
            documentation (str): The documentation to train on.
            plan (TrainingPlan): The training plan to train on.
        """

        if question and not sql:
            raise ValidationError("Please also provide a SQL query")

        if documentation:
            print("Adding documentation....")
            return self.add_documentation(documentation)

        if sql:
            if question is None:
                question = self.generate_question(sql)
                print("Question generated with sql:", question, "\nAdding SQL...")
            return self.add_question_sql(question=question, sql=sql)

        if ddl:
            print("Adding ddl:", ddl)
            return self.add_ddl(ddl)

        if plan:
            for item in plan._plan:
                if item.item_type == TrainingPlanItem.ITEM_TYPE_DDL:
                    self.add_ddl(item.item_value)
                elif item.item_type == TrainingPlanItem.ITEM_TYPE_IS:
                    self.add_documentation(item.item_value)
                elif item.item_type == TrainingPlanItem.ITEM_TYPE_SQL:
                    self.add_question_sql(question=item.item_name, sql=item.item_value)

    def _get_databases(self) -> List[str]:
        try:
            print("Trying INFORMATION_SCHEMA.DATABASES")
            df_databases = self.run_sql("SELECT * FROM INFORMATION_SCHEMA.DATABASES")
        except Exception as e:
            print(e)
            try:
                print("Trying SHOW DATABASES")
                df_databases = self.run_sql("SHOW DATABASES")
            except Exception as e:
                print(e)
                return []

        return df_databases["DATABASE_NAME"].unique().tolist()

    def _get_information_schema_tables(self, database: str) -> pd.DataFrame:
        df_tables = self.run_sql(f"SELECT * FROM {database}.INFORMATION_SCHEMA.TABLES")

        return df_tables

    def get_training_plan_generic(self, df) -> TrainingPlan:
        """
        This method is used to generate a training plan from an information schema dataframe.

        Basically what it does is breaks up INFORMATION_SCHEMA.COLUMNS into groups of table/column descriptions that can be used to pass to the LLM.

        Args:
            df (pd.DataFrame): The dataframe to generate the training plan from.

        Returns:
            TrainingPlan: The training plan.
        """
        # For each of the following, we look at the df columns to see if there's a match:
        database_column = df.columns[
            df.columns.str.lower().str.contains("database")
            | df.columns.str.lower().str.contains("table_catalog")
        ].to_list()[0]
        schema_column = df.columns[
            df.columns.str.lower().str.contains("table_schema")
        ].to_list()[0]
        table_column = df.columns[
            df.columns.str.lower().str.contains("table_name")
        ].to_list()[0]
        columns = [database_column,
                    schema_column,
                    table_column]
        candidates = ["column_name",
                      "data_type",
                      "comment"]
        matches = df.columns.str.lower().str.contains("|".join(candidates), regex=True)
        columns += df.columns[matches].to_list()

        plan = TrainingPlan([])

        for database in df[database_column].unique().tolist():
            for schema in (
                df.query(f'{database_column} == "{database}"')[schema_column]
                .unique()
                .tolist()
            ):
                for table in (
                    df.query(
                        f'{database_column} == "{database}" and {schema_column} == "{schema}"'
                    )[table_column]
                    .unique()
                    .tolist()
                ):
                    df_columns_filtered_to_table = df.query(
                        f'{database_column} == "{database}" and {schema_column} == "{schema}" and {table_column} == "{table}"'
                    )
                    doc = f"The following columns are in the {table} table in the {database} database:\n\n"
                    doc += df_columns_filtered_to_table[columns].to_markdown()

                    plan._plan.append(
                        TrainingPlanItem(
                            item_type=TrainingPlanItem.ITEM_TYPE_IS,
                            item_group=f"{database}.{schema}",
                            item_name=table,
                            item_value=doc,
                        )
                    )

        return plan

    def get_training_plan_snowflake(
        self,
        filter_databases: Union[List[str], None] = None,
        filter_schemas: Union[List[str], None] = None,
        include_information_schema: bool = False,
        use_historical_queries: bool = True,
    ) -> TrainingPlan:
        plan = TrainingPlan([])

        if self.run_sql_is_set is False:
            raise ImproperlyConfigured("Please connect to a database first.")

        if use_historical_queries:
            try:
                print("Trying query history")
                df_history = self.run_sql(
                    """ select * from table(information_schema.query_history(result_limit => 5000)) order by start_time"""
                )

                df_history_filtered = df_history.query("ROWS_PRODUCED > 1")
                if filter_databases is not None:
                    mask = (
                        df_history_filtered["QUERY_TEXT"]
                        .str.lower()
                        .apply(
                            lambda x: any(
                                s in x for s in [s.lower() for s in filter_databases]
                            )
                        )
                    )
                    df_history_filtered = df_history_filtered[mask]

                if filter_schemas is not None:
                    mask = (
                        df_history_filtered["QUERY_TEXT"]
                        .str.lower()
                        .apply(
                            lambda x: any(
                                s in x for s in [s.lower() for s in filter_schemas]
                            )
                        )
                    )
                    df_history_filtered = df_history_filtered[mask]

                if len(df_history_filtered) > 10:
                    df_history_filtered = df_history_filtered.sample(10)

                for query in df_history_filtered["QUERY_TEXT"].unique().tolist():
                    plan._plan.append(
                        TrainingPlanItem(
                            item_type=TrainingPlanItem.ITEM_TYPE_SQL,
                            item_group="",
                            item_name=self.generate_question(query),
                            item_value=query,
                        )
                    )

            except Exception as e:
                print(e)

        databases = self._get_databases()

        for database in databases:
            if filter_databases is not None and database not in filter_databases:
                continue

            try:
                df_tables = self._get_information_schema_tables(database=database)

                print(f"Trying INFORMATION_SCHEMA.COLUMNS for {database}")
                df_columns = self.run_sql(
                    f"SELECT * FROM {database}.INFORMATION_SCHEMA.COLUMNS"
                )

                for schema in df_tables["TABLE_SCHEMA"].unique().tolist():
                    if filter_schemas is not None and schema not in filter_schemas:
                        continue

                    if (
                        not include_information_schema
                        and schema == "INFORMATION_SCHEMA"
                    ):
                        continue

                    df_columns_filtered_to_schema = df_columns.query(
                        f"TABLE_SCHEMA == '{schema}'"
                    )

                    try:
                        tables = (
                            df_columns_filtered_to_schema["TABLE_NAME"]
                            .unique()
                            .tolist()
                        )

                        for table in tables:
                            df_columns_filtered_to_table = (
                                df_columns_filtered_to_schema.query(
                                    f"TABLE_NAME == '{table}'"
                                )
                            )
                            doc = f"The following columns are in the {table} table in the {database} database:\n\n"
                            doc += df_columns_filtered_to_table[
                                [
                                    "TABLE_CATALOG",
                                    "TABLE_SCHEMA",
                                    "TABLE_NAME",
                                    "COLUMN_NAME",
                                    "DATA_TYPE",
                                    "COMMENT",
                                ]
                            ].to_markdown()

                            plan._plan.append(
                                TrainingPlanItem(
                                    item_type=TrainingPlanItem.ITEM_TYPE_IS,
                                    item_group=f"{database}.{schema}",
                                    item_name=table,
                                    item_value=doc,
                                )
                            )

                    except Exception as e:
                        print(e)
                        pass
            except Exception as e:
                print(e)

        return plan

    def get_plotly_figure(
        self, plotly_code: str, df: pd.DataFrame, dark_mode: bool = True
    ) -> plotly.graph_objs.Figure:
        """
        **Example:**
        ```python
        fig = vn.get_plotly_figure(
            plotly_code="fig = px.bar(df, x='name', y='salary')",
            df=df
        )
        fig.show()
        ```
        Get a Plotly figure from a dataframe and Plotly code.

        Args:
            df (pd.DataFrame): The dataframe to use.
            plotly_code (str): The Plotly code to use.

        Returns:
            plotly.graph_objs.Figure: The Plotly figure.
        """
        ldict = {"df": df, "px": px, "go": go}
        try:
            exec(plotly_code, globals(), ldict)

            fig = ldict.get("fig", None)
        except Exception as e:
            # Inspect data types
            numeric_cols = df.select_dtypes(include=["number"]).columns.tolist()
            categorical_cols = df.select_dtypes(
                include=["object", "category"]
            ).columns.tolist()

            # Decision-making for plot type
            if len(numeric_cols) >= 2:
                # Use the first two numeric columns for a scatter plot
                fig = px.scatter(df, x=numeric_cols[0], y=numeric_cols[1])
            elif len(numeric_cols) == 1 and len(categorical_cols) >= 1:
                # Use a bar plot if there's one numeric and one categorical column
                fig = px.bar(df, x=categorical_cols[0], y=numeric_cols[0])
            elif len(categorical_cols) >= 1 and df[categorical_cols[0]].nunique() < 10:
                # Use a pie chart for categorical data with fewer unique values
                fig = px.pie(df, names=categorical_cols[0])
            else:
                # Default to a simple line plot if above conditions are not met
                fig = px.line(df)

        if fig is None:
            return None

        if dark_mode:
            fig.update_layout(template="plotly_dark")

        return fig

add_ddl(ddl, **kwargs) 抽象方法

此方法用于向训练数据添加 DDL 语句。

参数

名称	类型	描述	默认值
ddl	str	要添加的 DDL 语句。	必需

返回值

名称	类型	描述
str	str	添加的训练数据的 ID。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def add_ddl(self, ddl: str, **kwargs) -> str:
    """
    This method is used to add a DDL statement to the training data.

    Args:
        ddl (str): The DDL statement to add.

    Returns:
        str: The ID of the training data that was added.
    """
    pass

add_documentation(documentation, **kwargs) 抽象方法

此方法用于向训练数据添加文档。

参数

名称	类型	描述	默认值
documentation	str	要添加的文档。	必需

返回值

名称	类型	描述
str	str	添加的训练数据的 ID。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def add_documentation(self, documentation: str, **kwargs) -> str:
    """
    This method is used to add documentation to the training data.

    Args:
        documentation (str): The documentation to add.

    Returns:
        str: The ID of the training data that was added.
    """
    pass

add_question_sql(question, sql, **kwargs) 抽象方法

此方法用于向训练数据添加问题及其对应的 SQL 查询。

参数

名称	类型	描述	默认值
question	str	要添加的问题。	必需
sql	str	要添加的 SQL 查询。	必需

返回值

名称	类型	描述
str	str	添加的训练数据的 ID。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def add_question_sql(self, question: str, sql: str, **kwargs) -> str:
    """
    This method is used to add a question and its corresponding SQL query to the training data.

    Args:
        question (str): The question to add.
        sql (str): The SQL query to add.

    Returns:
        str: The ID of the training data that was added.
    """
    pass

ask(question=None, print_results=True, auto_train=True, visualize=True, allow_llm_to_see_data=False)

示例

vn.ask("What are the top 10 customers by sales?")

询问 Vanna.AI 一个问题并获取回答该问题的 SQL 查询。

参数

名称	类型	描述	默认值
question	str	要询问的问题。	None
print_results	bool	是否打印 SQL 查询的结果。	True
auto_train	bool	是否自动根据问题和 SQL 查询训练 Vanna.AI。	True
visualize	bool	是否生成 plotly 代码并显示 plotly 图形。	True

返回值

类型	描述
Union[Tuple[Union[str, None], Union[DataFrame, None], Union[Figure, None]], None]	Tuple[str, pd.DataFrame, plotly.graph_objs.Figure]: SQL 查询，SQL 查询的结果，以及 plotly 图形。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def ask(
    self,
    question: Union[str, None] = None,
    print_results: bool = True,
    auto_train: bool = True,
    visualize: bool = True,  # if False, will not generate plotly code
    allow_llm_to_see_data: bool = False,
) -> Union[
    Tuple[
        Union[str, None],
        Union[pd.DataFrame, None],
        Union[plotly.graph_objs.Figure, None],
    ],
    None,
]:
    """
    **Example:**
    ```python
    vn.ask("What are the top 10 customers by sales?")
    ```

    Ask Vanna.AI a question and get the SQL query that answers it.

    Args:
        question (str): The question to ask.
        print_results (bool): Whether to print the results of the SQL query.
        auto_train (bool): Whether to automatically train Vanna.AI on the question and SQL query.
        visualize (bool): Whether to generate plotly code and display the plotly figure.

    Returns:
        Tuple[str, pd.DataFrame, plotly.graph_objs.Figure]: The SQL query, the results of the SQL query, and the plotly figure.
    """

    if question is None:
        question = input("Enter a question: ")

    try:
        sql = self.generate_sql(question=question, allow_llm_to_see_data=allow_llm_to_see_data)
    except Exception as e:
        print(e)
        return None, None, None

    if print_results:
        try:
            Code = __import__("IPython.display", fromList=["Code"]).Code
            display(Code(sql))
        except Exception as e:
            print(sql)

    if self.run_sql_is_set is False:
        print(
            "If you want to run the SQL query, connect to a database first. See here: https://vanna.org.cn/docs/databases.html"
        )

        if print_results:
            return None
        else:
            return sql, None, None

    try:
        df = self.run_sql(sql)

        if print_results:
            try:
                display = __import__(
                    "IPython.display", fromList=["display"]
                ).display
                display(df)
            except Exception as e:
                print(df)

        if len(df) > 0 and auto_train:
            self.add_question_sql(question=question, sql=sql)
        # Only generate plotly code if visualize is True
        if visualize:
            try:
                plotly_code = self.generate_plotly_code(
                    question=question,
                    sql=sql,
                    df_metadata=f"Running df.dtypes gives:\n {df.dtypes}",
                )
                fig = self.get_plotly_figure(plotly_code=plotly_code, df=df)
                if print_results:
                    try:
                        display = __import__(
                            "IPython.display", fromlist=["display"]
                        ).display
                        Image = __import__(
                            "IPython.display", fromlist=["Image"]
                        ).Image
                        img_bytes = fig.to_image(format="png", scale=2)
                        display(Image(img_bytes))
                    except Exception as e:
                        fig.show()
            except Exception as e:
                # Print stack trace
                traceback.print_exc()
                print("Couldn't run plotly code: ", e)
                if print_results:
                    return None
                else:
                    return sql, df, None
        else:
            return sql, df, None

    except Exception as e:
        print("Couldn't run sql: ", e)
        if print_results:
            return None
        else:
            return sql, None, None
    return sql, df, fig

connect_to_bigquery(cred_file_path=None, project_id=None)

使用 bigquery 连接器连接到 gcs。这只是一个设置 vn.run_sql 的辅助函数。示例：

vn.connect_to_bigquery(
    project_id="myprojectid",
    cred_file_path="path/to/credentials.json",
)

参数：project_id (str): gcs 项目 ID。cred_file_path (str): gcs 凭据文件路径

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_bigquery(self, cred_file_path: str = None, project_id: str = None):
    """
    Connect to gcs using the bigquery connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
    **Example:**
    ```python
    vn.connect_to_bigquery(
        project_id="myprojectid",
        cred_file_path="path/to/credentials.json",
    )
    ```
    Args:
        project_id (str): The gcs project id.
        cred_file_path (str): The gcs credential file path
    """

    try:
        from google.api_core.exceptions import GoogleAPIError
        from google.cloud import bigquery
        from google.oauth2 import service_account
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method, run command:"
            " \npip install vanna[bigquery]"
        )

    if not project_id:
        project_id = os.getenv("PROJECT_ID")

    if not project_id:
        raise ImproperlyConfigured("Please set your Google Cloud Project ID.")

    import sys

    if "google.colab" in sys.modules:
        try:
            from google.colab import auth

            auth.authenticate_user()
        except Exception as e:
            raise ImproperlyConfigured(e)
    else:
        print("Not using Google Colab.")

    conn = None

    if not cred_file_path:
        try:
            conn = bigquery.Client(project=project_id)
        except:
            print("Could not found any google cloud implicit credentials")
    else:
        # Validate file path and pemissions
        validate_config_path(cred_file_path)

    if not conn:
        with open(cred_file_path, "r") as f:
            credentials = service_account.Credentials.from_service_account_info(
                json.loads(f.read()),
                scopes=["https://www.googleapis.com/auth/cloud-platform"],
            )

        try:
            conn = bigquery.Client(project=project_id, credentials=credentials)
        except:
            raise ImproperlyConfigured(
                "Could not connect to bigquery please correct credentials"
            )

    def run_sql_bigquery(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
            try:
                job = conn.query(sql)
                df = job.result().to_dataframe()
                return df
            except GoogleAPIError as error:
                errors = []
                for error in error.errors:
                    errors.append(error["message"])
                raise errors
        return None

    self.dialect = "BigQuery SQL"
    self.run_sql_is_set = True
    self.run_sql = run_sql_bigquery

connect_to_duckdb(url, init_sql=None)

连接到 DuckDB 数据库。这只是一个设置 vn.run_sql 的辅助函数。

参数

名称	类型	描述	默认值
url	str	要连接的数据库的 URL。使用 :memory: 创建内存数据库。使用 md: 或 motherduck: 使用 MotherDuck 数据库。	必需
init_sql	str	连接到数据库时要运行的 SQL。默认为 None。	None

返回值

类型	描述
	None

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_duckdb(self, url: str, init_sql: str = None):
    """
    Connect to a DuckDB database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        url (str): The URL of the database to connect to. Use :memory: to create an in-memory database. Use md: or motherduck: to use the MotherDuck database.
        init_sql (str, optional): SQL to run when connecting to the database. Defaults to None.

    Returns:
        None
    """
    try:
        import duckdb
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: \npip install vanna[duckdb]"
        )
    # URL of the database to download
    if url == ":memory:" or url == "":
        path = ":memory:"
    else:
        # Path to save the downloaded database
        print(os.path.exists(url))
        if os.path.exists(url):
            path = url
        elif url.startswith("md") or url.startswith("motherduck"):
            path = url
        else:
            path = os.path.basename(urlparse(url).path)
            # Download the database if it doesn't exist
            if not os.path.exists(path):
                response = requests.get(url)
                response.raise_for_status()  # Check that the request was successful
                with open(path, "wb") as f:
                    f.write(response.content)

    # Connect to the database
    conn = duckdb.connect(path)
    if init_sql:
        conn.query(init_sql)

    def run_sql_duckdb(sql: str):
        return conn.query(sql).to_df()

    self.dialect = "DuckDB SQL"
    self.run_sql = run_sql_duckdb
    self.run_sql_is_set = True

connect_to_hive(host=None, dbname='default', user=None, password=None, port=None, auth='CUSTOM')

连接到 Hive 数据库。这只是一个设置 vn.run_sql 的辅助函数。连接到 Hive 数据库。这只是一个设置 vn.run_sql 的辅助函数。

参数

名称	类型	描述	默认值
host	str	Hive 数据库的主机。	None
dbname	str	要连接的数据库的名称。	'default'
user	str	用于认证的用户名。	None
password	str	用于认证的密码。	None
port	int	用于连接的端口。	None
auth	str	要使用的认证方法。	'CUSTOM'

返回值

类型	描述
	None

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_hive(
  self,
  host: str = None,
  dbname: str = 'default',
  user: str = None,
  password: str = None,
  port: int = None,
  auth: str = 'CUSTOM'
):
  """
    Connect to a Hive database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
    Connect to a Hive database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        host (str): The host of the Hive database.
        dbname (str): The name of the database to connect to.
        user (str): The username to use for authentication.
        password (str): The password to use for authentication.
        port (int): The port to use for the connection.
        auth (str): The authentication method to use.

    Returns:
        None
  """

  try:
    from pyhive import hive
  except ImportError:
    raise DependencyError(
      "You need to install required dependencies to execute this method,"
      " run command: \npip install pyhive"
    )

  if not host:
    host = os.getenv("HIVE_HOST")

  if not host:
    raise ImproperlyConfigured("Please set your hive host")

  if not dbname:
    dbname = os.getenv("HIVE_DATABASE")

  if not dbname:
    raise ImproperlyConfigured("Please set your hive database")

  if not user:
    user = os.getenv("HIVE_USER")

  if not user:
    raise ImproperlyConfigured("Please set your hive user")

  if not password:
    password = os.getenv("HIVE_PASSWORD")

  if not port:
    port = os.getenv("HIVE_PORT")

  if not port:
    raise ImproperlyConfigured("Please set your hive port")

  conn = None

  try:
    conn = hive.Connection(host=host,
                           username=user,
                           password=password,
                           database=dbname,
                           port=port,
                           auth=auth)
  except hive.Error as e:
    raise ValidationError(e)

  def run_sql_hive(sql: str) -> Union[pd.DataFrame, None]:
    if conn:
      try:
        cs = conn.cursor()
        cs.execute(sql)
        results = cs.fetchall()

        # Create a pandas dataframe from the results
        df = pd.DataFrame(
          results, columns=[desc[0] for desc in cs.description]
        )
        return df

      except hive.Error as e:
        print(e)
        raise ValidationError(e)

      except Exception as e:
        print(e)
        raise e

  self.run_sql_is_set = True
  self.run_sql = run_sql_hive

connect_to_mssql(odbc_conn_str)

连接到 Microsoft SQL Server 数据库。这只是一个设置 vn.run_sql 的辅助函数。

参数

名称	类型	描述	默认值
odbc_conn_str	str	ODBC 连接字符串。	必需

返回值

类型	描述
	None

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_mssql(self, odbc_conn_str: str):
    """
    Connect to a Microsoft SQL Server database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        odbc_conn_str (str): The ODBC connection string.

    Returns:
        None
    """
    try:
        import pyodbc
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: pip install pyodbc"
        )

    try:
        import sqlalchemy as sa
        from sqlalchemy.engine import URL
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: pip install sqlalchemy"
        )

    connection_url = URL.create(
        "mssql+pyodbc", query={"odbc_connect": odbc_conn_str}
    )

    from sqlalchemy import create_engine

    engine = create_engine(connection_url)

    def run_sql_mssql(sql: str):
        # Execute the SQL statement and return the result as a pandas DataFrame
        with engine.begin() as conn:
            df = pd.read_sql_query(sa.text(sql), conn)
            conn.close()
            return df

        raise Exception("Couldn't run sql")

    self.dialect = "T-SQL / Microsoft SQL Server"
    self.run_sql = run_sql_mssql
    self.run_sql_is_set = True

connect_to_oracle(user=None, password=None, dsn=None)

使用 oracledb 包连接到 Oracle 数据库。这只是一个设置 vn.run_sql 的辅助函数。示例：

vn.connect_to_oracle(
user="username",
password="password",
dns="host:port/sid",
)

参数：USER (str): Oracle 数据库用户名。PASSWORD (str): Oracle 数据库用户密码。DSN (str): Oracle 数据库主机 IP - host:port/sid。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_oracle(
self,
user: str = None,
password: str = None,
dsn: str = None,
):

    """
    Connect to an Oracle db using oracledb package. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
    **Example:**
    ```python
    vn.connect_to_oracle(
    user="username",
    password="password",
    dns="host:port/sid",
    )
    ```
    Args:
        USER (str): Oracle db user name.
        PASSWORD (str): Oracle db user password.
        DSN (str): Oracle db host ip - host:port/sid.
    """

    try:
        import oracledb
    except ImportError:

        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: \npip install oracledb"
        )

    if not dsn:
        dsn = os.getenv("DSN")

    if not dsn:
        raise ImproperlyConfigured("Please set your Oracle dsn which should include host:port/sid")

    if not user:
        user = os.getenv("USER")

    if not user:
        raise ImproperlyConfigured("Please set your Oracle db user")

    if not password:
        password = os.getenv("PASSWORD")

    if not password:
        raise ImproperlyConfigured("Please set your Oracle db password")

    conn = None

    try:
        conn = oracledb.connect(
            user=user,
            password=password,
            dsn=dsn,
            )
    except oracledb.Error as e:
        raise ValidationError(e)

    def run_sql_oracle(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
            try:
                sql = sql.rstrip()
                if sql.endswith(';'): #fix for a known problem with Oracle db where an extra ; will cause an error.
                    sql = sql[:-1]

                cs = conn.cursor()
                cs.execute(sql)
                results = cs.fetchall()

                # Create a pandas dataframe from the results
                df = pd.DataFrame(
                    results, columns=[desc[0] for desc in cs.description]
                )
                return df

            except oracledb.Error as e:
                conn.rollback()
                raise ValidationError(e)

            except Exception as e:
                conn.rollback()
                raise e

    self.run_sql_is_set = True
    self.run_sql = run_sql_oracle

connect_to_postgres(host=None, dbname=None, user=None, password=None, port=None)

使用 psycopg2 连接器连接到 postgres。这只是一个设置 vn.run_sql 的辅助函数。示例：

vn.connect_to_postgres(
    host="myhost",
    dbname="mydatabase",
    user="myuser",
    password="mypassword",
    port=5432
)

参数：host (str): postgres 主机。dbname (str): postgres 数据库名。user (str): postgres 用户。password (str): postgres 密码。port (int): postgres 端口。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_postgres(
    self,
    host: str = None,
    dbname: str = None,
    user: str = None,
    password: str = None,
    port: int = None,
):
    """
    Connect to postgres using the psycopg2 connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
    **Example:**
    ```python
    vn.connect_to_postgres(
        host="myhost",
        dbname="mydatabase",
        user="myuser",
        password="mypassword",
        port=5432
    )
    ```
    Args:
        host (str): The postgres host.
        dbname (str): The postgres database name.
        user (str): The postgres user.
        password (str): The postgres password.
        port (int): The postgres Port.
    """

    try:
        import psycopg2
        import psycopg2.extras
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: \npip install vanna[postgres]"
        )

    if not host:
        host = os.getenv("HOST")

    if not host:
        raise ImproperlyConfigured("Please set your postgres host")

    if not dbname:
        dbname = os.getenv("DATABASE")

    if not dbname:
        raise ImproperlyConfigured("Please set your postgres database")

    if not user:
        user = os.getenv("PG_USER")

    if not user:
        raise ImproperlyConfigured("Please set your postgres user")

    if not password:
        password = os.getenv("PASSWORD")

    if not password:
        raise ImproperlyConfigured("Please set your postgres password")

    if not port:
        port = os.getenv("PORT")

    if not port:
        raise ImproperlyConfigured("Please set your postgres port")

    conn = None

    try:
        conn = psycopg2.connect(
            host=host,
            dbname=dbname,
            user=user,
            password=password,
            port=port,
        )
    except psycopg2.Error as e:
        raise ValidationError(e)

    def run_sql_postgres(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
            try:
                cs = conn.cursor()
                cs.execute(sql)
                results = cs.fetchall()

                # Create a pandas dataframe from the results
                df = pd.DataFrame(
                    results, columns=[desc[0] for desc in cs.description]
                )
                return df

            except psycopg2.Error as e:
                conn.rollback()
                raise ValidationError(e)

            except Exception as e:
                conn.rollback()
                raise e

    self.dialect = "PostgreSQL"
    self.run_sql_is_set = True
    self.run_sql = run_sql_postgres

connect_to_presto(host, catalog='hive', schema='default', user=None, password=None, port=None, combined_pem_path=None, protocol='https', requests_kwargs=None)

使用指定的参数连接到 Presto 数据库。

参数

名称	类型	描述	默认值
host	str	Presto 数据库的主机地址。	必需
catalog	str	在 Presto 环境中要使用的 catalog。	'hive'
schema	str	在 Presto 环境中要使用的 schema。	'default'
user	str	用于认证的用户名。	None
password	str	用于认证的密码。	None
port	int	Presto 连接的端口号。	None
combined_pem_path	str	用于 SSL 连接的组合 pem 文件路径。	None
protocol	str	用于连接的协议（默认为 'https'）。	'https'
requests_kwargs	dict	requests 的额外关键字参数。	None

引发

类型	描述
DependencyError	如果未安装必需的依赖项。
ImproperlyConfigured	如果缺少必要的配置设置。

返回值

类型	描述
	None

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_presto(
  self,
  host: str,
  catalog: str = 'hive',
  schema: str = 'default',
  user: str = None,
  password: str = None,
  port: int = None,
  combined_pem_path: str = None,
  protocol: str = 'https',
  requests_kwargs: dict = None
):
  """
    Connect to a Presto database using the specified parameters.

    Args:
        host (str): The host address of the Presto database.
        catalog (str): The catalog to use in the Presto environment.
        schema (str): The schema to use in the Presto environment.
        user (str): The username for authentication.
        password (str): The password for authentication.
        port (int): The port number for the Presto connection.
        combined_pem_path (str): The path to the combined pem file for SSL connection.
        protocol (str): The protocol to use for the connection (default is 'https').
        requests_kwargs (dict): Additional keyword arguments for requests.

    Raises:
        DependencyError: If required dependencies are not installed.
        ImproperlyConfigured: If essential configuration settings are missing.

    Returns:
        None
  """
  try:
    from pyhive import presto
  except ImportError:
    raise DependencyError(
      "You need to install required dependencies to execute this method,"
      " run command: \npip install pyhive"
    )

  if not host:
    host = os.getenv("PRESTO_HOST")

  if not host:
    raise ImproperlyConfigured("Please set your presto host")

  if not catalog:
    catalog = os.getenv("PRESTO_CATALOG")

  if not catalog:
    raise ImproperlyConfigured("Please set your presto catalog")

  if not user:
    user = os.getenv("PRESTO_USER")

  if not user:
    raise ImproperlyConfigured("Please set your presto user")

  if not password:
    password = os.getenv("PRESTO_PASSWORD")

  if not port:
    port = os.getenv("PRESTO_PORT")

  if not port:
    raise ImproperlyConfigured("Please set your presto port")

  conn = None

  try:
    if requests_kwargs is None and combined_pem_path is not None:
      # use the combined pem file to verify the SSL connection
      requests_kwargs = {
        'verify': combined_pem_path,  # 使用转换后得到的 PEM 文件进行 SSL 验证
      }
    conn = presto.Connection(host=host,
                             username=user,
                             password=password,
                             catalog=catalog,
                             schema=schema,
                             port=port,
                             protocol=protocol,
                             requests_kwargs=requests_kwargs)
  except presto.Error as e:
    raise ValidationError(e)

  def run_sql_presto(sql: str) -> Union[pd.DataFrame, None]:
    if conn:
      try:
        sql = sql.rstrip()
        # fix for a known problem with presto db where an extra ; will cause an error.
        if sql.endswith(';'):
            sql = sql[:-1]
        cs = conn.cursor()
        cs.execute(sql)
        results = cs.fetchall()

        # Create a pandas dataframe from the results
        df = pd.DataFrame(
          results, columns=[desc[0] for desc in cs.description]
        )
        return df

      except presto.Error as e:
        print(e)
        raise ValidationError(e)

      except Exception as e:
        print(e)
        raise e

  self.run_sql_is_set = True
  self.run_sql = run_sql_presto

connect_to_sqlite(url)

连接到 SQLite 数据库。这只是一个设置 vn.run_sql 的辅助函数。

参数

名称	类型	描述	默认值
url	str	要连接的数据库的 URL。	必需

返回值

类型	描述
	None

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def connect_to_sqlite(self, url: str):
    """
    Connect to a SQLite database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        url (str): The URL of the database to connect to.

    Returns:
        None
    """

    # URL of the database to download

    # Path to save the downloaded database
    path = os.path.basename(urlparse(url).path)

    # Download the database if it doesn't exist
    if not os.path.exists(url):
        response = requests.get(url)
        response.raise_for_status()  # Check that the request was successful
        with open(path, "wb") as f:
            f.write(response.content)
        url = path

    # Connect to the database
    conn = sqlite3.connect(url, check_same_thread=False)

    def run_sql_sqlite(sql: str):
        return pd.read_sql_query(sql, conn)

    self.dialect = "SQLite"
    self.run_sql = run_sql_sqlite
    self.run_sql_is_set = True

extract_sql(llm_response)

    Example:
    ```python
    vn.extract_sql("Here's the SQL query in a code block: ```sql

SELECT * FROM customers ")

    Extracts the SQL query from the LLM response. This is useful in case the LLM response contains other information besides the SQL query.
    Override this function if your LLM responses need custom extraction logic.

    Args:
        llm_response (str): The LLM response.

    Returns:
        str: The extracted SQL query.

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def extract_sql(self, llm_response: str) -> str:
    """
    Example:
    ```python
    vn.extract_sql("Here's the SQL query in a code block: ```sql\nSELECT * FROM customers\n```")
    ```

    Extracts the SQL query from the LLM response. This is useful in case the LLM response contains other information besides the SQL query.
    Override this function if your LLM responses need custom extraction logic.

    Args:
        llm_response (str): The LLM response.

    Returns:
        str: The extracted SQL query.
    """

    # If the llm_response contains a CTE (with clause), extract the last sql between WITH and ;
    sqls = re.findall(r"\bWITH\b .*?;", llm_response, re.DOTALL)
    if sqls:
        sql = sqls[-1]
        self.log(title="Extracted SQL", message=f"{sql}")
        return sql

    # If the llm_response is not markdown formatted, extract last sql by finding select and ; in the response
    sqls = re.findall(r"SELECT.*?;", llm_response, re.DOTALL)
    if sqls:
        sql = sqls[-1]
        self.log(title="Extracted SQL", message=f"{sql}")
        return sql

    # If the llm_response contains a markdown code block, with or without the sql tag, extract the last sql from it
    sqls = re.findall(r"```sql\n(.*)```", llm_response, re.DOTALL)
    if sqls:
        sql = sqls[-1]
        self.log(title="Extracted SQL", message=f"{sql}")
        return sql

    sqls = re.findall(r"```(.*)```", llm_response, re.DOTALL)
    if sqls:
        sql = sqls[-1]
        self.log(title="Extracted SQL", message=f"{sql}")
        return sql

    return llm_response

generate_followup_questions(question, sql, df, n_questions=5, **kwargs)

示例

vn.generate_followup_questions("What are the top 10 customers by sales?", sql, df)

生成您可以询问 Vanna.AI 的后续问题列表。

参数

名称	类型	描述	默认值
question	str	已询问的问题。	必需
sql	str	LLM 生成的 SQL 查询。	必需
df	DataFrame	SQL 查询的结果。	必需
n_questions	int	要生成的后续问题数量。	5

返回值

名称	类型	描述
list	list	您可以询问 Vanna.AI 的后续问题列表。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def generate_followup_questions(
    self, question: str, sql: str, df: pd.DataFrame, n_questions: int = 5, **kwargs
) -> list:
    """
    **Example:**
    ```python
    vn.generate_followup_questions("What are the top 10 customers by sales?", sql, df)
    ```

    Generate a list of followup questions that you can ask Vanna.AI.

    Args:
        question (str): The question that was asked.
        sql (str): The LLM-generated SQL query.
        df (pd.DataFrame): The results of the SQL query.
        n_questions (int): Number of follow-up questions to generate.

    Returns:
        list: A list of followup questions that you can ask Vanna.AI.
    """

    message_log = [
        self.system_message(
            f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe SQL query for this question was: {sql}\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
        ),
        self.user_message(
            f"Generate a list of {n_questions} followup questions that the user might ask about this data. Respond with a list of questions, one per line. Do not answer with any explanations -- just the questions. Remember that there should be an unambiguous SQL query that can be generated from the question. Prefer questions that are answerable outside of the context of this conversation. Prefer questions that are slight modifications of the SQL query that was generated that allow digging deeper into the data. Each question will be turned into a button that the user can click to generate a new SQL query so don't use 'example' type questions. Each question must have a one-to-one correspondence with an instantiated SQL query." +
            self._response_language()
        ),
    ]

    llm_response = self.submit_prompt(message_log, **kwargs)

    numbers_removed = re.sub(r"^\d+\.\s*", "", llm_response, flags=re.MULTILINE)
    return numbers_removed.split("\n")

generate_questions(**kwargs)

示例

vn.generate_questions()

生成您可以询问 Vanna.AI 的问题列表。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def generate_questions(self, **kwargs) -> List[str]:
    """
    **Example:**
    ```python
    vn.generate_questions()
    ```

    Generate a list of questions that you can ask Vanna.AI.
    """
    question_sql = self.get_similar_question_sql(question="", **kwargs)

    return [q["question"] for q in question_sql]

generate_sql(question, allow_llm_to_see_data=False, **kwargs)

示例

vn.generate_sql("What are the top 10 customers by sales?")

使用 LLM 生成回答问题的 SQL 查询。它运行以下方法

• get_similar_question_sql

• get_related_ddl

• get_related_documentation

• get_sql_prompt

• submit_prompt

参数

名称	类型	描述	默认值
question	str	要为其生成 SQL 查询的问题。	必需
allow_llm_to_see_data	bool	是否允许 LLM 查看数据（为了内省数据以生成最终 SQL）。	False

返回值

名称	类型	描述
str	str	回答该问题的 SQL 查询。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def generate_sql(self, question: str, allow_llm_to_see_data=False, **kwargs) -> str:
    """
    Example:
    ```python
    vn.generate_sql("What are the top 10 customers by sales?")
    ```

    Uses the LLM to generate a SQL query that answers a question. It runs the following methods:

    - [`get_similar_question_sql`][vanna.base.base.VannaBase.get_similar_question_sql]

    - [`get_related_ddl`][vanna.base.base.VannaBase.get_related_ddl]

    - [`get_related_documentation`][vanna.base.base.VannaBase.get_related_documentation]

    - [`get_sql_prompt`][vanna.base.base.VannaBase.get_sql_prompt]

    - [`submit_prompt`][vanna.base.base.VannaBase.submit_prompt]


    Args:
        question (str): The question to generate a SQL query for.
        allow_llm_to_see_data (bool): Whether to allow the LLM to see the data (for the purposes of introspecting the data to generate the final SQL).

    Returns:
        str: The SQL query that answers the question.
    """
    if self.config is not None:
        initial_prompt = self.config.get("initial_prompt", None)
    else:
        initial_prompt = None
    question_sql_list = self.get_similar_question_sql(question, **kwargs)
    ddl_list = self.get_related_ddl(question, **kwargs)
    doc_list = self.get_related_documentation(question, **kwargs)
    prompt = self.get_sql_prompt(
        initial_prompt=initial_prompt,
        question=question,
        question_sql_list=question_sql_list,
        ddl_list=ddl_list,
        doc_list=doc_list,
        **kwargs,
    )
    self.log(title="SQL Prompt", message=prompt)
    llm_response = self.submit_prompt(prompt, **kwargs)
    self.log(title="LLM Response", message=llm_response)

    if 'intermediate_sql' in llm_response:
        if not allow_llm_to_see_data:
            return "The LLM is not allowed to see the data in your database. Your question requires database introspection to generate the necessary SQL. Please set allow_llm_to_see_data=True to enable this."

        if allow_llm_to_see_data:
            intermediate_sql = self.extract_sql(llm_response)

            try:
                self.log(title="Running Intermediate SQL", message=intermediate_sql)
                df = self.run_sql(intermediate_sql)

                prompt = self.get_sql_prompt(
                    initial_prompt=initial_prompt,
                    question=question,
                    question_sql_list=question_sql_list,
                    ddl_list=ddl_list,
                    doc_list=doc_list+[f"The following is a pandas DataFrame with the results of the intermediate SQL query {intermediate_sql}: \n" + df.to_markdown()],
                    **kwargs,
                )
                self.log(title="Final SQL Prompt", message=prompt)
                llm_response = self.submit_prompt(prompt, **kwargs)
                self.log(title="LLM Response", message=llm_response)
            except Exception as e:
                return f"Error running intermediate SQL: {e}"


    return self.extract_sql(llm_response)

generate_summary(question, df, **kwargs)

示例

vn.generate_summary("What are the top 10 customers by sales?", df)

生成 SQL 查询结果的摘要。

参数

名称	类型	描述	默认值
question	str	已询问的问题。	必需
df	DataFrame	SQL 查询的结果。	必需

返回值

名称	类型	描述
str	str	SQL 查询结果的摘要。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def generate_summary(self, question: str, df: pd.DataFrame, **kwargs) -> str:
    """
    **Example:**
    ```python
    vn.generate_summary("What are the top 10 customers by sales?", df)
    ```

    Generate a summary of the results of a SQL query.

    Args:
        question (str): The question that was asked.
        df (pd.DataFrame): The results of the SQL query.

    Returns:
        str: The summary of the results of the SQL query.
    """

    message_log = [
        self.system_message(
            f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
        ),
        self.user_message(
            "Briefly summarize the data based on the question that was asked. Do not respond with any additional explanation beyond the summary." +
            self._response_language()
        ),
    ]

    summary = self.submit_prompt(message_log, **kwargs)

    return summary

get_plotly_figure(plotly_code, df, dark_mode=True)

示例

fig = vn.get_plotly_figure(
    plotly_code="fig = px.bar(df, x='name', y='salary')",
    df=df
)
fig.show()

从 dataframe 和 Plotly 代码获取 Plotly 图形。

参数

名称	类型	描述	默认值
df	DataFrame	要使用的 dataframe。	必需
plotly_code	str	要使用的 Plotly 代码。	必需

返回值

类型	描述
Figure	plotly.graph_objs.Figure: Plotly 图形。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def get_plotly_figure(
    self, plotly_code: str, df: pd.DataFrame, dark_mode: bool = True
) -> plotly.graph_objs.Figure:
    """
    **Example:**
    ```python
    fig = vn.get_plotly_figure(
        plotly_code="fig = px.bar(df, x='name', y='salary')",
        df=df
    )
    fig.show()
    ```
    Get a Plotly figure from a dataframe and Plotly code.

    Args:
        df (pd.DataFrame): The dataframe to use.
        plotly_code (str): The Plotly code to use.

    Returns:
        plotly.graph_objs.Figure: The Plotly figure.
    """
    ldict = {"df": df, "px": px, "go": go}
    try:
        exec(plotly_code, globals(), ldict)

        fig = ldict.get("fig", None)
    except Exception as e:
        # Inspect data types
        numeric_cols = df.select_dtypes(include=["number"]).columns.tolist()
        categorical_cols = df.select_dtypes(
            include=["object", "category"]
        ).columns.tolist()

        # Decision-making for plot type
        if len(numeric_cols) >= 2:
            # Use the first two numeric columns for a scatter plot
            fig = px.scatter(df, x=numeric_cols[0], y=numeric_cols[1])
        elif len(numeric_cols) == 1 and len(categorical_cols) >= 1:
            # Use a bar plot if there's one numeric and one categorical column
            fig = px.bar(df, x=categorical_cols[0], y=numeric_cols[0])
        elif len(categorical_cols) >= 1 and df[categorical_cols[0]].nunique() < 10:
            # Use a pie chart for categorical data with fewer unique values
            fig = px.pie(df, names=categorical_cols[0])
        else:
            # Default to a simple line plot if above conditions are not met
            fig = px.line(df)

    if fig is None:
        return None

    if dark_mode:
        fig.update_layout(template="plotly_dark")

    return fig

get_related_ddl(question, **kwargs) 抽象方法

此方法用于获取与问题相关的 DDL 语句。

参数

名称	类型	描述	默认值
question	str	要获取相关 DDL 语句的问题。	必需

返回值

名称	类型	描述
list	list	相关 DDL 语句列表。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def get_related_ddl(self, question: str, **kwargs) -> list:
    """
    This method is used to get related DDL statements to a question.

    Args:
        question (str): The question to get related DDL statements for.

    Returns:
        list: A list of related DDL statements.
    """
    pass

get_related_documentation(question, **kwargs) 抽象方法

此方法用于获取与问题相关的文档。

参数

名称	类型	描述	默认值
question	str	要获取相关文档的问题。	必需

返回值

名称	类型	描述
list	list	相关文档列表。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def get_related_documentation(self, question: str, **kwargs) -> list:
    """
    This method is used to get related documentation to a question.

    Args:
        question (str): The question to get related documentation for.

    Returns:
        list: A list of related documentation.
    """
    pass

get_similar_question_sql(question, **kwargs) 抽象方法

此方法用于获取相似问题及其对应的 SQL 语句。

参数

名称	类型	描述	默认值
question	str	要获取相似问题及其对应的 SQL 语句的问题。	必需

返回值

名称	类型	描述
list	list	相似问题及其对应的 SQL 语句列表。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def get_similar_question_sql(self, question: str, **kwargs) -> list:
    """
    This method is used to get similar questions and their corresponding SQL statements.

    Args:
        question (str): The question to get similar questions and their corresponding SQL statements for.

    Returns:
        list: A list of similar questions and their corresponding SQL statements.
    """
    pass

get_sql_prompt(initial_prompt, question, question_sql_list, ddl_list, doc_list, **kwargs)

示例

vn.get_sql_prompt(
    question="What are the top 10 customers by sales?",
    question_sql_list=[{"question": "What are the top 10 customers by sales?", "sql": "SELECT * FROM customers ORDER BY sales DESC LIMIT 10"}],
    ddl_list=["CREATE TABLE customers (id INT, name TEXT, sales DECIMAL)"],
    doc_list=["The customers table contains information about customers and their sales."],
)

此方法用于为 LLM 生成 SQL 生成提示。

参数

名称	类型	描述	默认值
question	str	要为其生成 SQL 的问题。	必需
question_sql_list	list	问题及其对应的 SQL 语句列表。	必需
ddl_list	list	DDL 语句列表。	必需
doc_list	list	文档列表。	必需

返回值

名称	类型	描述
any		用于 LLM 生成 SQL 的提示。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def get_sql_prompt(
    self,
    initial_prompt : str,
    question: str,
    question_sql_list: list,
    ddl_list: list,
    doc_list: list,
    **kwargs,
):
    """
    Example:
    ```python
    vn.get_sql_prompt(
        question="What are the top 10 customers by sales?",
        question_sql_list=[{"question": "What are the top 10 customers by sales?", "sql": "SELECT * FROM customers ORDER BY sales DESC LIMIT 10"}],
        ddl_list=["CREATE TABLE customers (id INT, name TEXT, sales DECIMAL)"],
        doc_list=["The customers table contains information about customers and their sales."],
    )

    ```

    This method is used to generate a prompt for the LLM to generate SQL.

    Args:
        question (str): The question to generate SQL for.
        question_sql_list (list): A list of questions and their corresponding SQL statements.
        ddl_list (list): A list of DDL statements.
        doc_list (list): A list of documentation.

    Returns:
        any: The prompt for the LLM to generate SQL.
    """

    if initial_prompt is None:
        initial_prompt = f"You are a {self.dialect} expert. " + \
        "Please help to generate a SQL query to answer the question. Your response should ONLY be based on the given context and follow the response guidelines and format instructions. "

    initial_prompt = self.add_ddl_to_prompt(
        initial_prompt, ddl_list, max_tokens=self.max_tokens
    )

    if self.static_documentation != "":
        doc_list.append(self.static_documentation)

    initial_prompt = self.add_documentation_to_prompt(
        initial_prompt, doc_list, max_tokens=self.max_tokens
    )

    initial_prompt += (
        "===Response Guidelines \n"
        "1. If the provided context is sufficient, please generate a valid SQL query without any explanations for the question. \n"
        "2. If the provided context is almost sufficient but requires knowledge of a specific string in a particular column, please generate an intermediate SQL query to find the distinct strings in that column. Prepend the query with a comment saying intermediate_sql \n"
        "3. If the provided context is insufficient, please explain why it can't be generated. \n"
        "4. Please use the most relevant table(s). \n"
        "5. If the question has been asked and answered before, please repeat the answer exactly as it was given before. \n"
    )

    message_log = [self.system_message(initial_prompt)]

    for example in question_sql_list:
        if example is None:
            print("example is None")
        else:
            if example is not None and "question" in example and "sql" in example:
                message_log.append(self.user_message(example["question"]))
                message_log.append(self.assistant_message(example["sql"]))

    message_log.append(self.user_message(question))

    return message_log

get_training_data(**kwargs) 抽象方法

示例

vn.get_training_data()

此方法用于从检索层获取所有训练数据。

返回值

类型	描述
DataFrame	pd.DataFrame: 训练数据。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def get_training_data(self, **kwargs) -> pd.DataFrame:
    """
    Example:
    ```python
    vn.get_training_data()
    ```

    This method is used to get all the training data from the retrieval layer.

    Returns:
        pd.DataFrame: The training data.
    """
    pass

get_training_plan_generic(df)

此方法用于从信息 schema dataframe 生成训练计划。

它的基本作用是将 INFORMATION_SCHEMA.COLUMNS 分解为可以传递给 LLM 的表/列描述组。

参数

名称	类型	描述	默认值
df	DataFrame	要从中生成训练计划的 dataframe。	必需

返回值

名称	类型	描述
TrainingPlan	TrainingPlan	训练计划。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def get_training_plan_generic(self, df) -> TrainingPlan:
    """
    This method is used to generate a training plan from an information schema dataframe.

    Basically what it does is breaks up INFORMATION_SCHEMA.COLUMNS into groups of table/column descriptions that can be used to pass to the LLM.

    Args:
        df (pd.DataFrame): The dataframe to generate the training plan from.

    Returns:
        TrainingPlan: The training plan.
    """
    # For each of the following, we look at the df columns to see if there's a match:
    database_column = df.columns[
        df.columns.str.lower().str.contains("database")
        | df.columns.str.lower().str.contains("table_catalog")
    ].to_list()[0]
    schema_column = df.columns[
        df.columns.str.lower().str.contains("table_schema")
    ].to_list()[0]
    table_column = df.columns[
        df.columns.str.lower().str.contains("table_name")
    ].to_list()[0]
    columns = [database_column,
                schema_column,
                table_column]
    candidates = ["column_name",
                  "data_type",
                  "comment"]
    matches = df.columns.str.lower().str.contains("|".join(candidates), regex=True)
    columns += df.columns[matches].to_list()

    plan = TrainingPlan([])

    for database in df[database_column].unique().tolist():
        for schema in (
            df.query(f'{database_column} == "{database}"')[schema_column]
            .unique()
            .tolist()
        ):
            for table in (
                df.query(
                    f'{database_column} == "{database}" and {schema_column} == "{schema}"'
                )[table_column]
                .unique()
                .tolist()
            ):
                df_columns_filtered_to_table = df.query(
                    f'{database_column} == "{database}" and {schema_column} == "{schema}" and {table_column} == "{table}"'
                )
                doc = f"The following columns are in the {table} table in the {database} database:\n\n"
                doc += df_columns_filtered_to_table[columns].to_markdown()

                plan._plan.append(
                    TrainingPlanItem(
                        item_type=TrainingPlanItem.ITEM_TYPE_IS,
                        item_group=f"{database}.{schema}",
                        item_name=table,
                        item_value=doc,
                    )
                )

    return plan

is_sql_valid(sql)

示例

vn.is_sql_valid("SELECT * FROM customers")

检查 SQL 查询是否有效。这通常用于检查我们是否应该运行 SQL 查询。默认情况下，它检查 SQL 查询是否是 SELECT 语句。您可以覆盖此方法以启用运行其他类型的 SQL 查询。

参数

名称	类型	描述	默认值
sql	str	要检查的 SQL 查询。	必需

返回值

名称	类型	描述
bool	bool	如果 SQL 查询有效则为 True，否则为 False。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def is_sql_valid(self, sql: str) -> bool:
    """
    Example:
    ```python
    vn.is_sql_valid("SELECT * FROM customers")
    ```
    Checks if the SQL query is valid. This is usually used to check if we should run the SQL query or not.
    By default it checks if the SQL query is a SELECT statement. You can override this method to enable running other types of SQL queries.

    Args:
        sql (str): The SQL query to check.

    Returns:
        bool: True if the SQL query is valid, False otherwise.
    """

    parsed = sqlparse.parse(sql)

    for statement in parsed:
        if statement.get_type() == 'SELECT':
            return True

    return False

remove_training_data(id, **kwargs) 抽象方法

示例

vn.remove_training_data(id="123-ddl")

此方法用于从检索层移除训练数据。

参数

名称	类型	描述	默认值
id	str	要移除的训练数据的 ID。	必需

返回值

名称	类型	描述
bool	bool	如果训练数据已移除则为 True，否则为 False。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def remove_training_data(id: str, **kwargs) -> bool:
    """
    Example:
    ```python
    vn.remove_training_data(id="123-ddl")
    ```

    This method is used to remove training data from the retrieval layer.

    Args:
        id (str): The ID of the training data to remove.

    Returns:
        bool: True if the training data was removed, False otherwise.
    """
    pass

run_sql(sql, **kwargs)

示例

vn.run_sql("SELECT * FROM my_table")

在已连接的数据库上运行 SQL 查询。

参数

名称	类型	描述	默认值
sql	str	要运行的 SQL 查询。	必需

返回值

类型	描述
DataFrame	pd.DataFrame: SQL 查询的结果。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def run_sql(self, sql: str, **kwargs) -> pd.DataFrame:
    """
    Example:
    ```python
    vn.run_sql("SELECT * FROM my_table")
    ```

    Run a SQL query on the connected database.

    Args:
        sql (str): The SQL query to run.

    Returns:
        pd.DataFrame: The results of the SQL query.
    """
    raise Exception(
        "You need to connect to a database first by running vn.connect_to_snowflake(), vn.connect_to_postgres(), similar function, or manually set vn.run_sql"
    )

should_generate_chart(df)

示例

vn.should_generate_chart(df)

检查是否应为给定的 DataFrame 生成图表。默认情况下，它检查 DataFrame 是否包含多行以及是否包含数值列。您可以覆盖此方法以自定义生成图表的逻辑。

参数

名称	类型	描述	默认值
df	DataFrame	要检查的 DataFrame。	必需

返回值

名称	类型	描述
bool	bool	如果应该生成图表则为 True，否则为 False。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def should_generate_chart(self, df: pd.DataFrame) -> bool:
    """
    Example:
    ```python
    vn.should_generate_chart(df)
    ```

    Checks if a chart should be generated for the given DataFrame. By default, it checks if the DataFrame has more than one row and has numerical columns.
    You can override this method to customize the logic for generating charts.

    Args:
        df (pd.DataFrame): The DataFrame to check.

    Returns:
        bool: True if a chart should be generated, False otherwise.
    """

    if len(df) > 1 and df.select_dtypes(include=['number']).shape[1] > 0:
        return True

    return False

submit_prompt(prompt, **kwargs) 抽象方法

示例

vn.submit_prompt(
    [
        vn.system_message("The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."),
        vn.user_message("What are the top 10 customers by sales?"),
    ]
)

此方法用于向 LLM 提交提示。

参数

名称	类型	描述	默认值
prompt	any	要提交给 LLM 的提示。	必需

返回值

名称	类型	描述
str	str	来自 LLM 的响应。

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

@abstractmethod
def submit_prompt(self, prompt, **kwargs) -> str:
    """
    Example:
    ```python
    vn.submit_prompt(
        [
            vn.system_message("The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."),
            vn.user_message("What are the top 10 customers by sales?"),
        ]
    )
    ```

    This method is used to submit a prompt to the LLM.

    Args:
        prompt (any): The prompt to submit to the LLM.

    Returns:
        str: The response from the LLM.
    """
    pass

train(question=None, sql=None, ddl=None, documentation=None, plan=None)

示例

vn.train()

根据问题及其对应的 SQL 查询训练 Vanna.AI。如果您在不带参数的情况下调用它，它会检查是否已连接到数据库，并尝试根据该数据库的元数据进行训练。如果使用 sql 参数调用它，则等同于 vn.add_question_sql()。如果使用 ddl 参数调用它，则等同于 vn.add_ddl()。如果使用 documentation 参数调用它，则等同于 vn.add_documentation()。此外，您还可以传递一个 [TrainingPlan][vanna.types.TrainingPlan] 对象。使用 vn.get_training_plan_generic() 获取训练计划。

参数

名称	类型	描述	默认值
question	str	要训练的问题。	None
sql	str	要训练的 SQL 查询。	None
ddl	str	DDL 语句。	None
documentation	str	要训练的文档。	None
plan	TrainingPlan	要训练的训练计划。	None

源代码位于 venv/lib/python3.12/site-packages/vanna/base/base.py

def train(
    self,
    question: str = None,
    sql: str = None,
    ddl: str = None,
    documentation: str = None,
    plan: TrainingPlan = None,
) -> str:
    """
    **Example:**
    ```python
    vn.train()
    ```

    Train Vanna.AI on a question and its corresponding SQL query.
    If you call it with no arguments, it will check if you connected to a database and it will attempt to train on the metadata of that database.
    If you call it with the sql argument, it's equivalent to [`vn.add_question_sql()`][vanna.base.base.VannaBase.add_question_sql].
    If you call it with the ddl argument, it's equivalent to [`vn.add_ddl()`][vanna.base.base.VannaBase.add_ddl].
    If you call it with the documentation argument, it's equivalent to [`vn.add_documentation()`][vanna.base.base.VannaBase.add_documentation].
    Additionally, you can pass a [`TrainingPlan`][vanna.types.TrainingPlan] object. Get a training plan with [`vn.get_training_plan_generic()`][vanna.base.base.VannaBase.get_training_plan_generic].

    Args:
        question (str): The question to train on.
        sql (str): The SQL query to train on.
        ddl (str):  The DDL statement.
        documentation (str): The documentation to train on.
        plan (TrainingPlan): The training plan to train on.
    """

    if question and not sql:
        raise ValidationError("Please also provide a SQL query")

    if documentation:
        print("Adding documentation....")
        return self.add_documentation(documentation)

    if sql:
        if question is None:
            question = self.generate_question(sql)
            print("Question generated with sql:", question, "\nAdding SQL...")
        return self.add_question_sql(question=question, sql=sql)

    if ddl:
        print("Adding ddl:", ddl)
        return self.add_ddl(ddl)

    if plan:
        for item in plan._plan:
            if item.item_type == TrainingPlanItem.ITEM_TYPE_DDL:
                self.add_ddl(item.item_value)
            elif item.item_type == TrainingPlanItem.ITEM_TYPE_IS:
                self.add_documentation(item.item_value)
            elif item.item_type == TrainingPlanItem.ITEM_TYPE_SQL:
                self.add_question_sql(question=item.item_name, sql=item.item_value)