You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
787 lines
30 KiB
Plaintext
787 lines
30 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "3e67f200",
|
|
"metadata": {},
|
|
"source": [
|
|
"# How to call functions with chat models\n",
|
|
"\n",
|
|
"This notebook covers how to use the Chat Completions API in combination with external functions to extend the capabilities of GPT models.\n",
|
|
"\n",
|
|
"`tools` is an optional parameter in the Chat Completion API which can be used to provide function specifications. The purpose of this is to enable models to generate function arguments which adhere to the provided specifications. Note that the API will not actually execute any function calls. It is up to developers to execute function calls using model outputs.\n",
|
|
"\n",
|
|
"Within the `tools` parameter, if the `functions` parameter is provided then by default the model will decide when it is appropriate to use one of the functions. The API can be forced to use a specific function by setting the `tool_choice` parameter to `{\"type\": \"function\", \"function\": {\"name\": \"my_function\"}}`. The API can also be forced to not use any function by setting the `tool_choice` parameter to `\"none\"`. If a function is used, the output will contain `\"finish_reason\": \"tool_calls\"` in the response, as well as a `tool_calls` object that has the name of the function and the generated function arguments.\n",
|
|
"\n",
|
|
"### Overview\n",
|
|
"\n",
|
|
"This notebook contains the following 2 sections:\n",
|
|
"\n",
|
|
"- **How to generate function arguments:** Specify a set of functions and use the API to generate function arguments.\n",
|
|
"- **How to call functions with model generated arguments:** Close the loop by actually executing functions with model generated arguments."
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "64c85e26",
|
|
"metadata": {},
|
|
"source": [
|
|
"## How to generate function arguments"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 1,
|
|
"id": "80e71f33",
|
|
"metadata": {
|
|
"pycharm": {
|
|
"is_executing": true
|
|
}
|
|
},
|
|
"outputs": [],
|
|
"source": [
|
|
"!pip install scipy --quiet\n",
|
|
"!pip install tenacity --quiet\n",
|
|
"!pip install tiktoken --quiet\n",
|
|
"!pip install termcolor --quiet\n",
|
|
"!pip install openai --quiet"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 2,
|
|
"id": "dab872c5",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"import json\n",
|
|
"from openai import OpenAI\n",
|
|
"from tenacity import retry, wait_random_exponential, stop_after_attempt\n",
|
|
"from termcolor import colored \n",
|
|
"\n",
|
|
"GPT_MODEL = \"gpt-3.5-turbo-0613\"\n",
|
|
"client = OpenAI()"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "69ee6a93",
|
|
"metadata": {},
|
|
"source": [
|
|
"### Utilities\n",
|
|
"\n",
|
|
"First let's define a few utilities for making calls to the Chat Completions API and for maintaining and keeping track of the conversation state."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 3,
|
|
"id": "745ceec5",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"@retry(wait=wait_random_exponential(multiplier=1, max=40), stop=stop_after_attempt(3))\n",
|
|
"def chat_completion_request(messages, tools=None, tool_choice=None, model=GPT_MODEL):\n",
|
|
" try:\n",
|
|
" response = client.chat.completions.create(\n",
|
|
" model=model,\n",
|
|
" messages=messages,\n",
|
|
" tools=tools,\n",
|
|
" tool_choice=tool_choice,\n",
|
|
" )\n",
|
|
" return response\n",
|
|
" except Exception as e:\n",
|
|
" print(\"Unable to generate ChatCompletion response\")\n",
|
|
" print(f\"Exception: {e}\")\n",
|
|
" return e\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 4,
|
|
"id": "c4d1c99f",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"def pretty_print_conversation(messages):\n",
|
|
" role_to_color = {\n",
|
|
" \"system\": \"red\",\n",
|
|
" \"user\": \"green\",\n",
|
|
" \"assistant\": \"blue\",\n",
|
|
" \"function\": \"magenta\",\n",
|
|
" }\n",
|
|
" \n",
|
|
" for message in messages:\n",
|
|
" if message[\"role\"] == \"system\":\n",
|
|
" print(colored(f\"system: {message['content']}\\n\", role_to_color[message[\"role\"]]))\n",
|
|
" elif message[\"role\"] == \"user\":\n",
|
|
" print(colored(f\"user: {message['content']}\\n\", role_to_color[message[\"role\"]]))\n",
|
|
" elif message[\"role\"] == \"assistant\" and message.get(\"function_call\"):\n",
|
|
" print(colored(f\"assistant: {message['function_call']}\\n\", role_to_color[message[\"role\"]]))\n",
|
|
" elif message[\"role\"] == \"assistant\" and not message.get(\"function_call\"):\n",
|
|
" print(colored(f\"assistant: {message['content']}\\n\", role_to_color[message[\"role\"]]))\n",
|
|
" elif message[\"role\"] == \"function\":\n",
|
|
" print(colored(f\"function ({message['name']}): {message['content']}\\n\", role_to_color[message[\"role\"]]))\n"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "29d4e02b",
|
|
"metadata": {},
|
|
"source": [
|
|
"### Basic concepts\n",
|
|
"\n",
|
|
"Let's create some function specifications to interface with a hypothetical weather API. We'll pass these function specification to the Chat Completions API in order to generate function arguments that adhere to the specification."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 5,
|
|
"id": "d2e25069",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"tools = [\n",
|
|
" {\n",
|
|
" \"type\": \"function\",\n",
|
|
" \"function\": {\n",
|
|
" \"name\": \"get_current_weather\",\n",
|
|
" \"description\": \"Get the current weather\",\n",
|
|
" \"parameters\": {\n",
|
|
" \"type\": \"object\",\n",
|
|
" \"properties\": {\n",
|
|
" \"location\": {\n",
|
|
" \"type\": \"string\",\n",
|
|
" \"description\": \"The city and state, e.g. San Francisco, CA\",\n",
|
|
" },\n",
|
|
" \"format\": {\n",
|
|
" \"type\": \"string\",\n",
|
|
" \"enum\": [\"celsius\", \"fahrenheit\"],\n",
|
|
" \"description\": \"The temperature unit to use. Infer this from the users location.\",\n",
|
|
" },\n",
|
|
" },\n",
|
|
" \"required\": [\"location\", \"format\"],\n",
|
|
" },\n",
|
|
" }\n",
|
|
" },\n",
|
|
" {\n",
|
|
" \"type\": \"function\",\n",
|
|
" \"function\": {\n",
|
|
" \"name\": \"get_n_day_weather_forecast\",\n",
|
|
" \"description\": \"Get an N-day weather forecast\",\n",
|
|
" \"parameters\": {\n",
|
|
" \"type\": \"object\",\n",
|
|
" \"properties\": {\n",
|
|
" \"location\": {\n",
|
|
" \"type\": \"string\",\n",
|
|
" \"description\": \"The city and state, e.g. San Francisco, CA\",\n",
|
|
" },\n",
|
|
" \"format\": {\n",
|
|
" \"type\": \"string\",\n",
|
|
" \"enum\": [\"celsius\", \"fahrenheit\"],\n",
|
|
" \"description\": \"The temperature unit to use. Infer this from the users location.\",\n",
|
|
" },\n",
|
|
" \"num_days\": {\n",
|
|
" \"type\": \"integer\",\n",
|
|
" \"description\": \"The number of days to forecast\",\n",
|
|
" }\n",
|
|
" },\n",
|
|
" \"required\": [\"location\", \"format\", \"num_days\"]\n",
|
|
" },\n",
|
|
" }\n",
|
|
" },\n",
|
|
"]"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "bfc39899",
|
|
"metadata": {},
|
|
"source": [
|
|
"If we prompt the model about the current weather, it will respond with some clarifying questions."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 6,
|
|
"id": "518d6827",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"ChatCompletionMessage(content='Sure, could you please tell me the location for which you would like to know the weather?', role='assistant', function_call=None, tool_calls=None)"
|
|
]
|
|
},
|
|
"execution_count": 6,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"What's the weather like today\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools\n",
|
|
")\n",
|
|
"assistant_message = chat_response.choices[0].message\n",
|
|
"messages.append(assistant_message)\n",
|
|
"assistant_message\n"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "4c999375",
|
|
"metadata": {},
|
|
"source": [
|
|
"Once we provide the missing information, it will generate the appropriate function arguments for us."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 7,
|
|
"id": "23c42a6e",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"ChatCompletionMessage(content=None, role='assistant', function_call=None, tool_calls=[ChatCompletionMessageToolCall(id='call_2PArU89L2uf4uIzRqnph4SrN', function=Function(arguments='{\\n \"location\": \"Glasgow, Scotland\",\\n \"format\": \"celsius\"\\n}', name='get_current_weather'), type='function')])"
|
|
]
|
|
},
|
|
"execution_count": 7,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"messages.append({\"role\": \"user\", \"content\": \"I'm in Glasgow, Scotland.\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools\n",
|
|
")\n",
|
|
"assistant_message = chat_response.choices[0].message\n",
|
|
"messages.append(assistant_message)\n",
|
|
"assistant_message\n"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "c14d4762",
|
|
"metadata": {},
|
|
"source": [
|
|
"By prompting it differently, we can get it to target the other function we've told it about."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 8,
|
|
"id": "fa232e54",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"ChatCompletionMessage(content='Sure, I can help you with that. How many days would you like to get the weather forecast for?', role='assistant', function_call=None, tool_calls=None)"
|
|
]
|
|
},
|
|
"execution_count": 8,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"what is the weather going to be like in Glasgow, Scotland over the next x days\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools\n",
|
|
")\n",
|
|
"assistant_message = chat_response.choices[0].message\n",
|
|
"messages.append(assistant_message)\n",
|
|
"assistant_message\n"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "6172ddac",
|
|
"metadata": {},
|
|
"source": [
|
|
"Once again, the model is asking us for clarification because it doesn't have enough information yet. In this case it already knows the location for the forecast, but it needs to know how many days are required in the forecast."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 9,
|
|
"id": "c7d8a543",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"Choice(finish_reason='tool_calls', index=0, logprobs=None, message=ChatCompletionMessage(content=None, role='assistant', function_call=None, tool_calls=[ChatCompletionMessageToolCall(id='call_ujD1NwPxzeOSCbgw2NOabOin', function=Function(arguments='{\\n \"location\": \"Glasgow, Scotland\",\\n \"format\": \"celsius\",\\n \"num_days\": 5\\n}', name='get_n_day_weather_forecast'), type='function')]), internal_metrics=[{'cached_prompt_tokens': 128, 'total_accepted_tokens': 0, 'total_batched_tokens': 273, 'total_predicted_tokens': 0, 'total_rejected_tokens': 0, 'total_tokens_in_completion': 274, 'cached_embeddings_bytes': 0, 'cached_embeddings_n': 0, 'uncached_embeddings_bytes': 0, 'uncached_embeddings_n': 0, 'fetched_embeddings_bytes': 0, 'fetched_embeddings_n': 0, 'n_evictions': 0, 'sampling_steps': 40, 'sampling_steps_with_predictions': 0, 'batcher_ttft': 0.035738229751586914, 'batcher_initial_queue_time': 0.0007979869842529297}])"
|
|
]
|
|
},
|
|
"execution_count": 9,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"messages.append({\"role\": \"user\", \"content\": \"5 days\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools\n",
|
|
")\n",
|
|
"chat_response.choices[0]\n"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "4b758a0a",
|
|
"metadata": {},
|
|
"source": [
|
|
"#### Forcing the use of specific functions or no function"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "412f79ba",
|
|
"metadata": {},
|
|
"source": [
|
|
"We can force the model to use a specific function, for example get_n_day_weather_forecast by using the function_call argument. By doing so, we force the model to make assumptions about how to use it."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 10,
|
|
"id": "559371b7",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"ChatCompletionMessage(content=None, role='assistant', function_call=None, tool_calls=[ChatCompletionMessageToolCall(id='call_MapM0kaNZBR046H4tAB2UGVu', function=Function(arguments='{\\n \"location\": \"Toronto, Canada\",\\n \"format\": \"celsius\",\\n \"num_days\": 1\\n}', name='get_n_day_weather_forecast'), type='function')])"
|
|
]
|
|
},
|
|
"execution_count": 10,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"# in this cell we force the model to use get_n_day_weather_forecast\n",
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"Give me a weather report for Toronto, Canada.\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools, tool_choice={\"type\": \"function\", \"function\": {\"name\": \"get_n_day_weather_forecast\"}}\n",
|
|
")\n",
|
|
"chat_response.choices[0].message"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 11,
|
|
"id": "a7ab0f58",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"ChatCompletionMessage(content=None, role='assistant', function_call=None, tool_calls=[ChatCompletionMessageToolCall(id='call_z8ijGSoMLS7xcaU7MjLmpRL8', function=Function(arguments='{\\n \"location\": \"Toronto, Canada\",\\n \"format\": \"celsius\"\\n}', name='get_current_weather'), type='function')])"
|
|
]
|
|
},
|
|
"execution_count": 11,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"# if we don't force the model to use get_n_day_weather_forecast it may not\n",
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"Give me a weather report for Toronto, Canada.\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools\n",
|
|
")\n",
|
|
"chat_response.choices[0].message"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "3bd70e48",
|
|
"metadata": {},
|
|
"source": [
|
|
"We can also force the model to not use a function at all. By doing so we prevent it from producing a proper function call."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 12,
|
|
"id": "acfe54e6",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"ChatCompletionMessage(content='{\\n \"location\": \"Toronto, Canada\",\\n \"format\": \"celsius\"\\n}', role='assistant', function_call=None, tool_calls=None)"
|
|
]
|
|
},
|
|
"execution_count": 12,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"Give me the current weather (use Celcius) for Toronto, Canada.\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools, tool_choice=\"none\"\n",
|
|
")\n",
|
|
"chat_response.choices[0].message\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "b616353b",
|
|
"metadata": {},
|
|
"source": [
|
|
"### Parallel Function Calling\n",
|
|
"\n",
|
|
"Newer models like gpt-4-1106-preview or gpt-3.5-turbo-1106 can call multiple functions in one turn."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 13,
|
|
"id": "380eeb68",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"[ChatCompletionMessageToolCall(id='call_8BlkS2yvbkkpL3V1Yxc6zR6u', function=Function(arguments='{\"location\": \"San Francisco, CA\", \"format\": \"celsius\", \"num_days\": 4}', name='get_n_day_weather_forecast'), type='function'),\n",
|
|
" ChatCompletionMessageToolCall(id='call_vSZMy3f24wb3vtNXucpFfAbG', function=Function(arguments='{\"location\": \"Glasgow\", \"format\": \"celsius\", \"num_days\": 4}', name='get_n_day_weather_forecast'), type='function')]"
|
|
]
|
|
},
|
|
"execution_count": 13,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Don't make assumptions about what values to plug into functions. Ask for clarification if a user request is ambiguous.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"what is the weather going to be like in San Francisco and Glasgow over the next 4 days\"})\n",
|
|
"chat_response = chat_completion_request(\n",
|
|
" messages, tools=tools, model='gpt-3.5-turbo-1106'\n",
|
|
")\n",
|
|
"\n",
|
|
"assistant_message = chat_response.choices[0].message.tool_calls\n",
|
|
"assistant_message"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "b4482aee",
|
|
"metadata": {},
|
|
"source": [
|
|
"## How to call functions with model generated arguments\n",
|
|
"\n",
|
|
"In our next example, we'll demonstrate how to execute functions whose inputs are model-generated, and use this to implement an agent that can answer questions for us about a database. For simplicity we'll use the [Chinook sample database](https://www.sqlitetutorial.net/sqlite-sample-database/).\n",
|
|
"\n",
|
|
"*Note:* SQL generation can be high-risk in a production environment since models are not perfectly reliable at generating correct SQL."
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "f7654fef",
|
|
"metadata": {},
|
|
"source": [
|
|
"### Specifying a function to execute SQL queries\n",
|
|
"\n",
|
|
"First let's define some helpful utility functions to extract data from a SQLite database."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 14,
|
|
"id": "30f6b60e",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Opened database successfully\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"import sqlite3\n",
|
|
"\n",
|
|
"conn = sqlite3.connect(\"data/Chinook.db\")\n",
|
|
"print(\"Opened database successfully\")"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 15,
|
|
"id": "abec0214",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"def get_table_names(conn):\n",
|
|
" \"\"\"Return a list of table names.\"\"\"\n",
|
|
" table_names = []\n",
|
|
" tables = conn.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
|
|
" for table in tables.fetchall():\n",
|
|
" table_names.append(table[0])\n",
|
|
" return table_names\n",
|
|
"\n",
|
|
"\n",
|
|
"def get_column_names(conn, table_name):\n",
|
|
" \"\"\"Return a list of column names.\"\"\"\n",
|
|
" column_names = []\n",
|
|
" columns = conn.execute(f\"PRAGMA table_info('{table_name}');\").fetchall()\n",
|
|
" for col in columns:\n",
|
|
" column_names.append(col[1])\n",
|
|
" return column_names\n",
|
|
"\n",
|
|
"\n",
|
|
"def get_database_info(conn):\n",
|
|
" \"\"\"Return a list of dicts containing the table name and columns for each table in the database.\"\"\"\n",
|
|
" table_dicts = []\n",
|
|
" for table_name in get_table_names(conn):\n",
|
|
" columns_names = get_column_names(conn, table_name)\n",
|
|
" table_dicts.append({\"table_name\": table_name, \"column_names\": columns_names})\n",
|
|
" return table_dicts\n"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "77e6e5ea",
|
|
"metadata": {},
|
|
"source": [
|
|
"Now can use these utility functions to extract a representation of the database schema."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 16,
|
|
"id": "0c0104cd",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"database_schema_dict = get_database_info(conn)\n",
|
|
"database_schema_string = \"\\n\".join(\n",
|
|
" [\n",
|
|
" f\"Table: {table['table_name']}\\nColumns: {', '.join(table['column_names'])}\"\n",
|
|
" for table in database_schema_dict\n",
|
|
" ]\n",
|
|
")"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "ae73c9ee",
|
|
"metadata": {},
|
|
"source": [
|
|
"As before, we'll define a function specification for the function we'd like the API to generate arguments for. Notice that we are inserting the database schema into the function specification. This will be important for the model to know about."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 17,
|
|
"id": "0258813a",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"tools = [\n",
|
|
" {\n",
|
|
" \"type\": \"function\",\n",
|
|
" \"function\": {\n",
|
|
" \"name\": \"ask_database\",\n",
|
|
" \"description\": \"Use this function to answer user questions about music. Input should be a fully formed SQL query.\",\n",
|
|
" \"parameters\": {\n",
|
|
" \"type\": \"object\",\n",
|
|
" \"properties\": {\n",
|
|
" \"query\": {\n",
|
|
" \"type\": \"string\",\n",
|
|
" \"description\": f\"\"\"\n",
|
|
" SQL query extracting info to answer the user's question.\n",
|
|
" SQL should be written using this database schema:\n",
|
|
" {database_schema_string}\n",
|
|
" The query should be returned in plain text, not in JSON.\n",
|
|
" \"\"\",\n",
|
|
" }\n",
|
|
" },\n",
|
|
" \"required\": [\"query\"],\n",
|
|
" },\n",
|
|
" }\n",
|
|
" }\n",
|
|
"]"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "da08c121",
|
|
"metadata": {},
|
|
"source": [
|
|
"### Executing SQL queries\n",
|
|
"\n",
|
|
"Now let's implement the function that will actually excute queries against the database."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 18,
|
|
"id": "65585e74",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"def ask_database(conn, query):\n",
|
|
" \"\"\"Function to query SQLite database with a provided SQL query.\"\"\"\n",
|
|
" try:\n",
|
|
" results = str(conn.execute(query).fetchall())\n",
|
|
" except Exception as e:\n",
|
|
" results = f\"query failed with error: {e}\"\n",
|
|
" return results\n",
|
|
"\n",
|
|
"def execute_function_call(message):\n",
|
|
" if message.tool_calls[0].function.name == \"ask_database\":\n",
|
|
" query = json.loads(message.tool_calls[0].function.arguments)[\"query\"]\n",
|
|
" results = ask_database(conn, query)\n",
|
|
" else:\n",
|
|
" results = f\"Error: function {message.tool_calls[0].function.name} does not exist\"\n",
|
|
" return results"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 19,
|
|
"id": "38c55083",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"\u001b[31msystem: Answer user questions by generating SQL queries against the Chinook Music Database.\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[32muser: Hi, who are the top 5 artists by number of tracks?\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[34massistant: Function(arguments='{\\n \"query\": \"SELECT Artist.Name, COUNT(Track.TrackId) AS TrackCount FROM Artist JOIN Album ON Artist.ArtistId = Album.ArtistId JOIN Track ON Album.AlbumId = Track.AlbumId GROUP BY Artist.ArtistId ORDER BY TrackCount DESC LIMIT 5;\"\\n}', name='ask_database')\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[35mfunction (ask_database): [('Iron Maiden', 213), ('U2', 135), ('Led Zeppelin', 114), ('Metallica', 112), ('Lost', 92)]\n",
|
|
"\u001b[0m\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"messages = []\n",
|
|
"messages.append({\"role\": \"system\", \"content\": \"Answer user questions by generating SQL queries against the Chinook Music Database.\"})\n",
|
|
"messages.append({\"role\": \"user\", \"content\": \"Hi, who are the top 5 artists by number of tracks?\"})\n",
|
|
"chat_response = chat_completion_request(messages, tools)\n",
|
|
"assistant_message = chat_response.choices[0].message\n",
|
|
"assistant_message.content = str(assistant_message.tool_calls[0].function)\n",
|
|
"messages.append({\"role\": assistant_message.role, \"content\": assistant_message.content})\n",
|
|
"if assistant_message.tool_calls:\n",
|
|
" results = execute_function_call(assistant_message)\n",
|
|
" messages.append({\"role\": \"function\", \"tool_call_id\": assistant_message.tool_calls[0].id, \"name\": assistant_message.tool_calls[0].function.name, \"content\": results})\n",
|
|
"pretty_print_conversation(messages)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 20,
|
|
"id": "710481dc",
|
|
"metadata": {
|
|
"scrolled": true
|
|
},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"\u001b[31msystem: Answer user questions by generating SQL queries against the Chinook Music Database.\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[32muser: Hi, who are the top 5 artists by number of tracks?\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[34massistant: Function(arguments='{\\n \"query\": \"SELECT Artist.Name, COUNT(Track.TrackId) AS TrackCount FROM Artist JOIN Album ON Artist.ArtistId = Album.ArtistId JOIN Track ON Album.AlbumId = Track.AlbumId GROUP BY Artist.ArtistId ORDER BY TrackCount DESC LIMIT 5;\"\\n}', name='ask_database')\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[35mfunction (ask_database): [('Iron Maiden', 213), ('U2', 135), ('Led Zeppelin', 114), ('Metallica', 112), ('Lost', 92)]\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[32muser: What is the name of the album with the most tracks?\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[34massistant: Function(arguments='{\\n \"query\": \"SELECT Album.Title, COUNT(Track.TrackId) AS TrackCount FROM Album JOIN Track ON Album.AlbumId = Track.AlbumId GROUP BY Album.AlbumId ORDER BY TrackCount DESC LIMIT 1;\"\\n}', name='ask_database')\n",
|
|
"\u001b[0m\n",
|
|
"\u001b[35mfunction (ask_database): [('Greatest Hits', 57)]\n",
|
|
"\u001b[0m\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"messages.append({\"role\": \"user\", \"content\": \"What is the name of the album with the most tracks?\"})\n",
|
|
"chat_response = chat_completion_request(messages, tools)\n",
|
|
"assistant_message = chat_response.choices[0].message\n",
|
|
"assistant_message.content = str(assistant_message.tool_calls[0].function)\n",
|
|
"messages.append({\"role\": assistant_message.role, \"content\": assistant_message.content})\n",
|
|
"if assistant_message.tool_calls:\n",
|
|
" results = execute_function_call(assistant_message)\n",
|
|
" messages.append({\"role\": \"function\", \"tool_call_id\": assistant_message.tool_calls[0].id, \"name\": assistant_message.tool_calls[0].function.name, \"content\": results})\n",
|
|
"pretty_print_conversation(messages)"
|
|
]
|
|
},
|
|
{
|
|
"attachments": {},
|
|
"cell_type": "markdown",
|
|
"id": "2d89073c",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Next Steps\n",
|
|
"\n",
|
|
"See our other [notebook](How_to_call_functions_for_knowledge_retrieval.ipynb) that demonstrates how to use the Chat Completions API and functions for knowledge retrieval to interact conversationally with a knowledge base."
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3 (ipykernel)",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"codemirror_mode": {
|
|
"name": "ipython",
|
|
"version": 3
|
|
},
|
|
"file_extension": ".py",
|
|
"mimetype": "text/x-python",
|
|
"name": "python",
|
|
"nbconvert_exporter": "python",
|
|
"pygments_lexer": "ipython3",
|
|
"version": "3.12.1"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|