Day_3_Function_calling_with_the_Gemini_API.ipynb

{
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python",
      "version": "3.10.14",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "colab": {
      "toc_visible": true,
      "name": "Day 3 - Function calling with the Gemini API",
      "provenance": [],
      "include_colab_link": true
    },
    "kaggle": {
      "accelerator": "none",
      "dataSources": [],
      "dockerImageVersionId": 30786,
      "isInternetEnabled": true,
      "language": "python",
      "sourceType": "notebook",
      "isGpuEnabled": false
    }
  },
  "nbformat_minor": 0,
  "nbformat": 4,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/nf15/handson-ml/blob/master/Day_3_Function_calling_with_the_Gemini_API.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##### Copyright 2024 Google LLC."
      ],
      "metadata": {
        "id": "b6e13eef3f5d"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "# https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ],
      "metadata": {
        "cellView": "form",
        "id": "d6597b11df14",
        "jupyter": {
          "source_hidden": true
        }
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Day 3 - Function calling with the Gemini API\n",
        "\n",
        "Welcome back to the Kaggle 5-day Generative AI course!\n",
        "\n",
        "In this notebook, you will use the Gemini API's automatic function calling to build a chat interface over a local database. This example is a toy and is missing a number of safety and security constraints you would use in a real-world example.\n",
        "\n",
        "## For help\n",
        "\n",
        "**Common issues are covered in the [FAQ and troubleshooting guide](https://www.kaggle.com/code/markishere/day-0-troubleshooting-and-faqs).**"
      ],
      "metadata": {
        "id": "4a7d3bb68371"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Setup\n",
        "\n",
        "Start by installing and importing the Python SDK."
      ],
      "metadata": {
        "id": "ea197d1d464f"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%pip install -q -U 'google-generativeai>=0.8.3'"
      ],
      "metadata": {
        "id": "a24f42e469df",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:13:58.38748Z",
          "iopub.execute_input": "2024-11-15T18:13:58.387909Z",
          "iopub.status.idle": "2024-11-15T18:14:30.359762Z",
          "shell.execute_reply.started": "2024-11-15T18:13:58.387867Z",
          "shell.execute_reply": "2024-11-15T18:14:30.358094Z"
        },
        "trusted": true,
        "outputId": "86ee32f1-b70a-499e-c7a1-d35853a880b9"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "Note: you may need to restart the kernel to use updated packages.\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import google.generativeai as genai"
      ],
      "metadata": {
        "id": "02bb0f551e25",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:14:38.596603Z",
          "iopub.execute_input": "2024-11-15T18:14:38.597523Z",
          "iopub.status.idle": "2024-11-15T18:14:38.60339Z",
          "shell.execute_reply.started": "2024-11-15T18:14:38.597474Z",
          "shell.execute_reply": "2024-11-15T18:14:38.602037Z"
        },
        "trusted": true
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Set up your API key\n",
        "\n",
        "To run the following cell, your API key must be stored it in a [Kaggle secret](https://www.kaggle.com/discussions/product-feedback/114053) named `GOOGLE_API_KEY`.\n",
        "\n",
        "If you don't already have an API key, you can grab one from [AI Studio](https://aistudio.google.com/app/apikey). You can find [detailed instructions in the docs](https://ai.google.dev/gemini-api/docs/api-key).\n",
        "\n",
        "To make the key available through Kaggle secrets, choose `Secrets` from the `Add-ons` menu and follow the instructions to add your key or enable it for this notebook."
      ],
      "metadata": {
        "id": "90e83cddff61"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from kaggle_secrets import UserSecretsClient\n",
        "\n",
        "GOOGLE_API_KEY = UserSecretsClient().get_secret(\"GOOGLE_API_KEY\")\n",
        "genai.configure(api_key=GOOGLE_API_KEY)"
      ],
      "metadata": {
        "id": "5cc8325f051d",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:16:20.496873Z",
          "iopub.execute_input": "2024-11-15T18:16:20.49734Z",
          "iopub.status.idle": "2024-11-15T18:16:20.64386Z",
          "shell.execute_reply.started": "2024-11-15T18:16:20.49728Z",
          "shell.execute_reply": "2024-11-15T18:16:20.642421Z"
        },
        "trusted": true
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "If you received an error response along the lines of `No user secrets exist for kernel id ...`, then you need to add your API key via `Add-ons`, `Secrets` **and** enable it.\n",
        "\n",
        "![Screenshot of the checkbox to enable GOOGLE_API_KEY secret](https://storage.googleapis.com/kaggle-media/Images/5gdai_sc_3.png)"
      ],
      "metadata": {
        "id": "857b6f96eb38"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create a local database\n",
        "\n",
        "For this minimal example, you'll create a local SQLite database and add some synthetic data so you have something to query.\n",
        "\n",
        "Load the `sql` IPython extension so you can interact with the database using magic commands (the `%` instructions) to create a new, empty SQLite database."
      ],
      "metadata": {
        "id": "ed8fc6062c62"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%load_ext sql\n",
        "%sql sqlite:///sample.db"
      ],
      "metadata": {
        "id": "c98a627ef07b",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:18:49.280752Z",
          "iopub.execute_input": "2024-11-15T18:18:49.281412Z",
          "iopub.status.idle": "2024-11-15T18:18:49.288571Z",
          "shell.execute_reply.started": "2024-11-15T18:18:49.281364Z",
          "shell.execute_reply": "2024-11-15T18:18:49.28751Z"
        },
        "trusted": true,
        "outputId": "89ed9c19-9f81-4a9d-ba2a-d54783dd6ee5"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "The sql extension is already loaded. To reload it, use:\n  %reload_ext sql\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Create the tables and insert some synthetic data. Feel free to tweak this structure and data."
      ],
      "metadata": {
        "id": "14e7bc18b8af"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "-- Create the 'products' table\n",
        "CREATE TABLE IF NOT EXISTS products (\n",
        "  \tproduct_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "  \tproduct_name VARCHAR(255) NOT NULL,\n",
        "  \tprice DECIMAL(10, 2) NOT NULL\n",
        "  );\n",
        "\n",
        "-- Create the 'staff' table\n",
        "CREATE TABLE IF NOT EXISTS staff (\n",
        "  \tstaff_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "  \tfirst_name VARCHAR(255) NOT NULL,\n",
        "  \tlast_name VARCHAR(255) NOT NULL\n",
        "  );\n",
        "\n",
        "-- Create the 'orders' table\n",
        "CREATE TABLE IF NOT EXISTS orders (\n",
        "  \torder_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "  \tcustomer_name VARCHAR(255) NOT NULL,\n",
        "  \tstaff_id INTEGER NOT NULL,\n",
        "  \tproduct_id INTEGER NOT NULL,\n",
        "  \tFOREIGN KEY (staff_id) REFERENCES staff (staff_id),\n",
        "  \tFOREIGN KEY (product_id) REFERENCES products (product_id)\n",
        "  );\n",
        "\n",
        "-- Insert data into the 'products' table\n",
        "INSERT INTO products (product_name, price) VALUES\n",
        "  \t('Laptop', 799.99),\n",
        "  \t('Keyboard', 129.99),\n",
        "  \t('Mouse', 29.99);\n",
        "\n",
        "-- Insert data into the 'staff' table\n",
        "INSERT INTO staff (first_name, last_name) VALUES\n",
        "  \t('Alice', 'Smith'),\n",
        "  \t('Bob', 'Johnson'),\n",
        "  \t('Charlie', 'Williams');\n",
        "\n",
        "-- Insert data into the 'orders' table\n",
        "INSERT INTO orders (customer_name, staff_id, product_id) VALUES\n",
        "  \t('David Lee', 1, 1),\n",
        "  \t('Emily Chen', 2, 2),\n",
        "  \t('Frank Brown', 1, 3);"
      ],
      "metadata": {
        "id": "4e186de46cf1",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:23:03.443893Z",
          "iopub.execute_input": "2024-11-15T18:23:03.444399Z",
          "iopub.status.idle": "2024-11-15T18:23:03.539267Z",
          "shell.execute_reply.started": "2024-11-15T18:23:03.444354Z",
          "shell.execute_reply": "2024-11-15T18:23:03.53821Z"
        },
        "trusted": true,
        "outputId": "aba366cf-37f9-4e9f-b3a2-2de511a60376"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " * sqlite:///sample.db\nDone.\nDone.\nDone.\n3 rows affected.\n3 rows affected.\n3 rows affected.\n",
          "output_type": "stream"
        },
        {
          "execution_count": 8,
          "output_type": "execute_result",
          "data": {
            "text/plain": "[]"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Define database functions\n",
        "\n",
        "Function calling with Gemini API's Python SDK can be implemented by defining [an OpenAPI schema](https://ai.google.dev/api/caching#Schema) that is passed to the model. Alternatively you can define Python functions and let the SDK inspect them to automatically define the schema. In this latter case, it's important that the functions are type annotated and have accurate docstrings that describe what the functions do - the model has no insight into the function body, so the docs function as the interface.\n",
        "\n",
        "By providing three key pieces of functionality - listing tables, describing a table, and executing a query - the LLM (or even another user) will have the basic tools needed to understand and interrogate the database.\n",
        "\n",
        "Start with a database connection that will be used across all of the functions."
      ],
      "metadata": {
        "id": "83901899a79b"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "db_file = \"sample.db\"\n",
        "db_conn = sqlite3.connect(db_file)"
      ],
      "metadata": {
        "id": "437168bc6b6e",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:38:33.500215Z",
          "iopub.execute_input": "2024-11-15T18:38:33.500696Z",
          "iopub.status.idle": "2024-11-15T18:38:33.507018Z",
          "shell.execute_reply.started": "2024-11-15T18:38:33.500649Z",
          "shell.execute_reply": "2024-11-15T18:38:33.505564Z"
        },
        "trusted": true
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The first function will list all tables available in the database. Define it, and test it out to ensure it works."
      ],
      "metadata": {
        "id": "b68b1a2c37d9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def list_tables() -> list[str]:\n",
        "    \"\"\"Retrieve the names of all tables in the database.\"\"\"\n",
        "    # Include print logging statements so you can see when functions are being called.\n",
        "    print(' - DB CALL: list_tables')\n",
        "\n",
        "    cursor = db_conn.cursor()\n",
        "\n",
        "    # Fetch the table names.\n",
        "    cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
        "\n",
        "    tables = cursor.fetchall()\n",
        "    return [t[0] for t in tables]\n",
        "\n",
        "\n",
        "list_tables()"
      ],
      "metadata": {
        "id": "bdb0e4d2bb4b",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:40:59.92456Z",
          "iopub.execute_input": "2024-11-15T18:40:59.925074Z",
          "iopub.status.idle": "2024-11-15T18:40:59.936762Z",
          "shell.execute_reply.started": "2024-11-15T18:40:59.925026Z",
          "shell.execute_reply": "2024-11-15T18:40:59.93556Z"
        },
        "trusted": true,
        "outputId": "bb26f26a-de2b-4b6f-9c95-d134b0c60e5d"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: list_tables\n",
          "output_type": "stream"
        },
        {
          "execution_count": 11,
          "output_type": "execute_result",
          "data": {
            "text/plain": "['products', 'sqlite_sequence', 'staff', 'orders']"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Once the available tables is known, the next step a database user will need is to understand what columns are available in a given table. Define that too, and test that it works as expected."
      ],
      "metadata": {
        "id": "b6c010b1b6c2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def describe_table(table_name: str) -> list[tuple[str, str]]:\n",
        "    \"\"\"Look up the table schema.\n",
        "\n",
        "    Returns:\n",
        "      List of columns, where each entry is a tuple of (column, type).\n",
        "    \"\"\"\n",
        "    print(' - DB CALL: describe_table')\n",
        "\n",
        "    cursor = db_conn.cursor()\n",
        "\n",
        "    cursor.execute(f\"PRAGMA table_info({table_name});\")\n",
        "\n",
        "    schema = cursor.fetchall()\n",
        "    # [column index, column name, column type, ...]\n",
        "    return [(col[1], col[2]) for col in schema]\n",
        "\n",
        "\n",
        "describe_table(\"products\")"
      ],
      "metadata": {
        "id": "ecdb109298c4",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:44:20.813105Z",
          "iopub.execute_input": "2024-11-15T18:44:20.813672Z",
          "iopub.status.idle": "2024-11-15T18:44:20.826084Z",
          "shell.execute_reply.started": "2024-11-15T18:44:20.813624Z",
          "shell.execute_reply": "2024-11-15T18:44:20.824798Z"
        },
        "trusted": true,
        "outputId": "8d45693c-3a06-43c4-c737-70e8e9c8f31b"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: describe_table\n",
          "output_type": "stream"
        },
        {
          "execution_count": 12,
          "output_type": "execute_result",
          "data": {
            "text/plain": "[('product_id', 'INTEGER'),\n ('product_name', 'VARCHAR(255)'),\n ('price', 'DECIMAL(10, 2)')]"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now that the system knows what tables and columns are present, it has enough information to be able to generate and run a `SELECT` query. Now provide that functionality, and test that it works."
      ],
      "metadata": {
        "id": "f6053a2ca272"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def execute_query(sql: str) -> list[list[str]]:\n",
        "    \"\"\"Execute a SELECT statement, returning the results.\"\"\"\n",
        "    print(' - DB CALL: execute_query')\n",
        "\n",
        "    cursor = db_conn.cursor()\n",
        "\n",
        "    cursor.execute(sql)\n",
        "    return cursor.fetchall()\n",
        "\n",
        "\n",
        "execute_query(\"select * from products\")"
      ],
      "metadata": {
        "id": "9e405db8b3f6",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:46:30.749273Z",
          "iopub.execute_input": "2024-11-15T18:46:30.750188Z",
          "iopub.status.idle": "2024-11-15T18:46:30.762837Z",
          "shell.execute_reply.started": "2024-11-15T18:46:30.750134Z",
          "shell.execute_reply": "2024-11-15T18:46:30.7614Z"
        },
        "trusted": true,
        "outputId": "185d9e23-655d-4322-ba6f-1d57dc4c4f47"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: execute_query\n",
          "output_type": "stream"
        },
        {
          "execution_count": 13,
          "output_type": "execute_result",
          "data": {
            "text/plain": "[(1, 'Laptop', 799.99), (2, 'Keyboard', 129.99), (3, 'Mouse', 29.99)]"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "execute_query(\"select * from Orders\")"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-11-15T18:47:47.981236Z",
          "iopub.execute_input": "2024-11-15T18:47:47.981774Z",
          "iopub.status.idle": "2024-11-15T18:47:47.990705Z",
          "shell.execute_reply.started": "2024-11-15T18:47:47.981723Z",
          "shell.execute_reply": "2024-11-15T18:47:47.98962Z"
        },
        "trusted": true,
        "id": "mu6tCiZ9O0SP",
        "outputId": "2ad3c52a-7c6e-452f-e7fd-8296ff0c565f"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: execute_query\n",
          "output_type": "stream"
        },
        {
          "execution_count": 14,
          "output_type": "execute_result",
          "data": {
            "text/plain": "[(1, 'David Lee', 1, 1), (2, 'Emily Chen', 2, 2), (3, 'Frank Brown', 1, 3)]"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "execute_query(\"select * from staff\")"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-11-15T18:48:59.780214Z",
          "iopub.execute_input": "2024-11-15T18:48:59.781177Z",
          "iopub.status.idle": "2024-11-15T18:48:59.789804Z",
          "shell.execute_reply.started": "2024-11-15T18:48:59.781126Z",
          "shell.execute_reply": "2024-11-15T18:48:59.788635Z"
        },
        "trusted": true,
        "id": "PYkw6QFDO0SP",
        "outputId": "00a1bf4e-1fff-4791-d37f-9a2c942029c7"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: execute_query\n",
          "output_type": "stream"
        },
        {
          "execution_count": 15,
          "output_type": "execute_result",
          "data": {
            "text/plain": "[(1, 'Alice', 'Smith'), (2, 'Bob', 'Johnson'), (3, 'Charlie', 'Williams')]"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Implement function calls\n",
        "\n",
        "Now you can put it all together in a call to the Gemini API.\n",
        "\n",
        "Function calling works by adding specific messages to a chat session. When function schemas are defined and made available to the model and a conversation is started, instead of returning a text response, the model may return a `function_call` instead. When this happens, the client must respond with a `function_response`, indicating the result of the call, and the conversation can continue on as normal.\n",
        "\n",
        "This function calling interaction normally happens manually, allowing you, the client, to validate and initiate the call. However the Python SDK also supports **automatic function calling**, where the supplied functions will be automatically invoked. This is a powerful feature and should only be exposed when it is safe to do so, such as when the functions have no [side-effects](https://en.wikipedia.org/wiki/Side_effect_(computer_science)).\n",
        "\n",
        "Here's the state diagram representing the conversation flow with function calling. With automatic function calling, the bottom row is executed automatically by the Python SDK. In manual function calling, you write the code to run each step individually.\n",
        "\n",
        "![function calling state diagram](https://codelabs.developers.google.com/static/codelabs/gemini-function-calling/img/gemini-function-calling-overview_1440.png)"
      ],
      "metadata": {
        "id": "ac464dfb35a0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# These are the Python functions defined above.\n",
        "db_tools = [list_tables, describe_table, execute_query]\n",
        "\n",
        "instruction = \"\"\"You are a helpful chatbot that can interact with an SQL database for a computer\n",
        "store. You will take the users questions and turn them into SQL queries using the tools\n",
        "available. Once you have the information you need, you will answer the user's question using\n",
        "the data returned. Use list_tables to see what tables are present, describe_table to understand\n",
        "the schema, and execute_query to issue an SQL SELECT query.\"\"\"\n",
        "\n",
        "model = genai.GenerativeModel(\n",
        "    \"models/gemini-1.5-flash-latest\", tools=db_tools, system_instruction=instruction\n",
        ")\n",
        "\n",
        "# Define a retry policy. The model might make multiple consecutive calls automatically\n",
        "# for a complex query, this ensures the client retries if it hits quota limits.\n",
        "from google.api_core import retry\n",
        "\n",
        "retry_policy = {\"retry\": retry.Retry(predicate=retry.if_transient_error)}\n",
        "\n",
        "# Start a chat with automatic function calling enabled.\n",
        "chat = model.start_chat(enable_automatic_function_calling=True)"
      ],
      "metadata": {
        "id": "f4839540066d",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:55:25.350123Z",
          "iopub.execute_input": "2024-11-15T18:55:25.351254Z",
          "iopub.status.idle": "2024-11-15T18:55:25.365976Z",
          "shell.execute_reply.started": "2024-11-15T18:55:25.3512Z",
          "shell.execute_reply": "2024-11-15T18:55:25.364747Z"
        },
        "trusted": true
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now you can engage in a chat conversation where you can ask about the contents of the database."
      ],
      "metadata": {
        "id": "5f120977f1ee"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resp = chat.send_message(\"What is the cheapest product?\", request_options=retry_policy)\n",
        "print(resp.text)"
      ],
      "metadata": {
        "id": "111cfb79338b",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:55:44.608248Z",
          "iopub.execute_input": "2024-11-15T18:55:44.609024Z",
          "iopub.status.idle": "2024-11-15T18:55:51.502182Z",
          "shell.execute_reply.started": "2024-11-15T18:55:44.60896Z",
          "shell.execute_reply": "2024-11-15T18:55:51.501061Z"
        },
        "trusted": true,
        "outputId": "72db2c1c-15de-407b-ef69-0a8a648a30c7"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: list_tables\n - DB CALL: describe_table\n - DB CALL: execute_query\nThe cheapest product is the Mouse, which costs $29.99.\n\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "resp = chat.send_message(\"What is the highst cost product?\", request_options=retry_policy)\n",
        "print(resp.text)"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-11-15T18:56:48.296812Z",
          "iopub.execute_input": "2024-11-15T18:56:48.297268Z",
          "iopub.status.idle": "2024-11-15T18:56:49.62133Z",
          "shell.execute_reply.started": "2024-11-15T18:56:48.297225Z",
          "shell.execute_reply": "2024-11-15T18:56:49.620102Z"
        },
        "trusted": true,
        "id": "mZdM_KBtO0SQ",
        "outputId": "a1943304-c7d5-4862-999f-ae9683fae956"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: execute_query\nThe highest cost product is the Laptop, which costs $799.99.\n\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "If you re-use the same [`ChatSession`](https://github.com/google-gemini/generative-ai-python/blob/main/docs/api/google/generativeai/ChatSession.md) object, the conversation will continue statefully. If you wish to start fresh, you can call [`start_chat`](https://github.com/google-gemini/generative-ai-python/blob/main/docs/api/google/generativeai/GenerativeModel.md#start_chat) again, or call [`rewind`](https://github.com/google-gemini/generative-ai-python/blob/main/docs/api/google/generativeai/ChatSession.md#rewind) on the chat object to go back a turn.\n",
        "\n",
        "Continue the chat here by asking a follow-up question. Note that the database information is preserved, and the context of the specific product is inferred."
      ],
      "metadata": {
        "id": "43595d4ef920"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "resp = chat.send_message(\"and how much is it?\", request_options=retry_policy)\n",
        "print(resp.text)"
      ],
      "metadata": {
        "id": "647cbcc43993",
        "execution": {
          "iopub.status.busy": "2024-11-15T18:59:29.533105Z",
          "iopub.execute_input": "2024-11-15T18:59:29.533591Z",
          "iopub.status.idle": "2024-11-15T18:59:32.534634Z",
          "shell.execute_reply.started": "2024-11-15T18:59:29.533547Z",
          "shell.execute_reply": "2024-11-15T18:59:32.533228Z"
        },
        "trusted": true,
        "outputId": "ca7ee0bb-467e-4f17-b2bb-bfa0a79d12d0"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "The highest cost product, the Laptop, is $799.99.\n\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Explore the chat session and ask your own questions. If you want to try asking more complex questions, try using the `gemini-1.5-pro` model. It has a lower rate limit, so calls might take a bit longer on the free tier, but you'll notice an improvement in expressivity."
      ],
      "metadata": {
        "id": "d67f635191c8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "model = genai.GenerativeModel(\n",
        "    \"models/gemini-1.5-pro-latest\", tools=db_tools, system_instruction=instruction\n",
        ")\n",
        "\n",
        "chat = model.start_chat(enable_automatic_function_calling=True)\n",
        "response = chat.send_message('Which salesperson sold the cheapest product?', request_options=retry_policy)\n",
        "print(response.text)"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-11-15T19:02:31.205703Z",
          "iopub.execute_input": "2024-11-15T19:02:31.206897Z",
          "iopub.status.idle": "2024-11-15T19:03:43.743338Z",
          "shell.execute_reply.started": "2024-11-15T19:02:31.206843Z",
          "shell.execute_reply": "2024-11-15T19:03:43.741878Z"
        },
        "trusted": true,
        "id": "B1YUa_38O0SR",
        "outputId": "8c633d2a-19b5-4ea6-e541-038093977f9c"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": " - DB CALL: list_tables\n - DB CALL: describe_table\n - DB CALL: describe_table\n - DB CALL: describe_table\n - DB CALL: execute_query\nAlice Smith sold the cheapest product.\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Inspecting the conversation\n",
        "\n",
        "To see the calls that the model makes, and what the client returns in response, you can inspect `chat.history`. This helper function will print out each turn along with the relevant fields passed or returned."
      ],
      "metadata": {
        "id": "1f5bb6d9bd6a"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import textwrap\n",
        "\n",
        "\n",
        "def print_chat_turns(chat):\n",
        "    \"\"\"Prints out each turn in the chat history, including function calls and responses.\"\"\"\n",
        "    for event in chat.history:\n",
        "        print(f\"{event.role.capitalize()}:\")\n",
        "\n",
        "        for part in event.parts:\n",
        "            if txt := part.text:\n",
        "                print(f'  \"{txt}\"')\n",
        "            elif fn := part.function_call:\n",
        "                args = \", \".join(f\"{key}={val}\" for key, val in fn.args.items())\n",
        "                print(f\"  Function call: {fn.name}({args})\")\n",
        "            elif resp := part.function_response:\n",
        "                print(\"  Function response:\")\n",
        "                print(textwrap.indent(str(resp), \"    \"))\n",
        "\n",
        "        print()\n",
        "\n",
        "\n",
        "print_chat_turns(chat)"
      ],
      "metadata": {
        "id": "639963cc64e2",
        "execution": {
          "iopub.status.busy": "2024-11-15T19:03:43.778599Z",
          "iopub.execute_input": "2024-11-15T19:03:43.778991Z",
          "iopub.status.idle": "2024-11-15T19:03:43.792656Z",
          "shell.execute_reply.started": "2024-11-15T19:03:43.778944Z",
          "shell.execute_reply": "2024-11-15T19:03:43.791519Z"
        },
        "trusted": true,
        "outputId": "8093a4a1-34d9-4901-cd9a-bfd4227fbca8"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "User:\n  \"Which salesperson sold the cheapest product?\"\n\nModel:\n  Function call: list_tables()\n\nUser:\n  Function response:\n    name: \"list_tables\"\n    response {\n      fields {\n        key: \"result\"\n        value {\n          list_value {\n            values {\n              string_value: \"products\"\n            }\n            values {\n              string_value: \"sqlite_sequence\"\n            }\n            values {\n              string_value: \"staff\"\n            }\n            values {\n              string_value: \"orders\"\n            }\n          }\n        }\n      }\n    }\n\n\nModel:\n  Function call: describe_table(table_name=products)\n\nUser:\n  Function response:\n    name: \"describe_table\"\n    response {\n      fields {\n        key: \"result\"\n        value {\n          list_value {\n            values {\n              list_value {\n                values {\n                  string_value: \"product_id\"\n                }\n                values {\n                  string_value: \"INTEGER\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"product_name\"\n                }\n                values {\n                  string_value: \"VARCHAR(255)\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"price\"\n                }\n                values {\n                  string_value: \"DECIMAL(10, 2)\"\n                }\n              }\n            }\n          }\n        }\n      }\n    }\n\n\nModel:\n  Function call: describe_table(table_name=orders)\n\nUser:\n  Function response:\n    name: \"describe_table\"\n    response {\n      fields {\n        key: \"result\"\n        value {\n          list_value {\n            values {\n              list_value {\n                values {\n                  string_value: \"order_id\"\n                }\n                values {\n                  string_value: \"INTEGER\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"customer_name\"\n                }\n                values {\n                  string_value: \"VARCHAR(255)\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"staff_id\"\n                }\n                values {\n                  string_value: \"INTEGER\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"product_id\"\n                }\n                values {\n                  string_value: \"INTEGER\"\n                }\n              }\n            }\n          }\n        }\n      }\n    }\n\n\nModel:\n  Function call: describe_table(table_name=staff)\n\nUser:\n  Function response:\n    name: \"describe_table\"\n    response {\n      fields {\n        key: \"result\"\n        value {\n          list_value {\n            values {\n              list_value {\n                values {\n                  string_value: \"staff_id\"\n                }\n                values {\n                  string_value: \"INTEGER\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"first_name\"\n                }\n                values {\n                  string_value: \"VARCHAR(255)\"\n                }\n              }\n            }\n            values {\n              list_value {\n                values {\n                  string_value: \"last_name\"\n                }\n                values {\n                  string_value: \"VARCHAR(255)\"\n                }\n              }\n            }\n          }\n        }\n      }\n    }\n\n\nModel:\n  Function call: execute_query(sql=SELECT staff.first_name, staff.last_name FROM staff INNER JOIN orders ON staff.staff_id = orders.staff_id INNER JOIN products ON orders.product_id = products.product_id WHERE products.price = (SELECT MIN(price) FROM products);)\n\nUser:\n  Function response:\n    name: \"execute_query\"\n    response {\n      fields {\n        key: \"result\"\n        value {\n          list_value {\n            values {\n              list_value {\n                values {\n                  string_value: \"Alice\"\n                }\n                values {\n                  string_value: \"Smith\"\n                }\n              }\n            }\n          }\n        }\n      }\n    }\n\n\nModel:\n  \"Alice Smith sold the cheapest product.\"\n\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In this output you can see each of the conversational turns that were made. Note that the model doesn't remember anything outside of a `ChatSession` object, so you can make changes to the database structure or data and the model will respond without needing any code changes - try this out!"
      ],
      "metadata": {
        "id": "8a17aeb2a3ef"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Further reading\n",
        "\n",
        "To learn more about what the Gemini API can do with function calling, check out the [Function calling cookbook](https://github.com/google-gemini/cookbook/blob/main/quickstarts/Function_calling.ipynb) (see `Manual function calling` to understand how function calling works manually) as well as [Function calling config](https://github.com/google-gemini/cookbook/blob/main/quickstarts/Function_calling_config.ipynb), which gives you fine-grained control over how function calling is triggered."
      ],
      "metadata": {
        "id": "vfyWNNvmO0SR"
      }
    }
  ]
}