{ "cells": [ { "cell_type": "markdown", "id": "d43ce131641908c2", "metadata": {}, "source": [ "# Generate SQL with LLM\n", "\n", "In this recipe, we will be using OpenAI GPT-4o-mini to act as a co-pilot for a Database Admin. While LLMs are powerful and do pretty well at transforming laymen queries into SQL queries, it is still dangerous to do so without supervision. This recipe will have no guardrails for mutable operations and is purely for getting started.\n", "\n", "
\n", "

Mirascope Concepts Used

\n", "\n", "
" ] }, { "cell_type": "markdown", "id": "130ae3d9", "metadata": {}, "source": [ "## Setup\n", "\n", "Let's start by installing Mirascope and its dependencies:" ] }, { "cell_type": "code", "execution_count": null, "id": "684a6b96", "metadata": {}, "outputs": [], "source": [ "!pip install \"mirascope[openai]\"" ] }, { "cell_type": "code", "execution_count": null, "id": "9ab545d1", "metadata": {}, "outputs": [], "source": [ "import os\n", "\n", "os.environ[\"OPENAI_API_KEY\"] = \"YOUR_API_KEY\"\n", "# Set the appropriate API key for the provider you're using" ] }, { "cell_type": "markdown", "id": "92fcc417", "metadata": {}, "source": [ "## Setup SQL Database\n", "\n", "We will be using SQLite, but this example will work for any common SQL dialect, such as PostgreSQL, MySQL, MSSQL, and more.\n", "\n", "Replace this part with whichever SQL dialect you are using, or skip if you have a database set up already." ] }, { "cell_type": "code", "execution_count": 2, "id": "90f8cff7fc7d109e", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T16:56:04.566028Z", "start_time": "2024-09-30T16:56:04.557690Z" } }, "outputs": [], "source": [ "import sqlite3\n", "\n", "con = sqlite3.connect(\"database.db\")\n", "cur = con.cursor()\n", "# ONE TIME SETUP\n", "cur.execute(\"\"\"\n", " CREATE TABLE ReadingList (\n", " id INTEGER PRIMARY KEY AUTOINCREMENT,\n", " title TEXT NOT NULL,\n", " status TEXT NOT NULL CHECK(status IN ('Not Started', 'In Progress', 'Complete')),\n", " rating INTEGER CHECK(rating BETWEEN 1 AND 5)\n", " )\n", "\"\"\")\n", "con.commit()" ] }, { "cell_type": "markdown", "id": "573c9b19c60d033e", "metadata": {}, "source": [ "\n", "This will be our playground example.\n", "\n", "## Write your Database Assistant\n", "\n", "We will be creating an Agent that will take non-technical queries and translate them into SQL queries that will be executed. The first step will be to create our two tools, `_run_query` and `_execute_query` , which will be read and write operations respectively.\n" ] }, { "cell_type": "code", "execution_count": 3, "id": "decfdfd13349ddb2", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T16:56:07.898863Z", "start_time": "2024-09-30T16:56:06.654843Z" } }, "outputs": [], "source": [ "import sqlite3\n", "from typing import ClassVar\n", "\n", "from mirascope.core import BaseMessageParam, openai\n", "from pydantic import BaseModel, ConfigDict\n", "\n", "\n", "class LibrarianBase(BaseModel):\n", " con: ClassVar[sqlite3.Connection] = sqlite3.connect(\"database.db\")\n", " messages: list[BaseMessageParam | openai.OpenAIMessageParam] = []\n", "\n", " model_config = ConfigDict(arbitrary_types_allowed=True)\n", "\n", " def _run_query(self, query: str) -> str:\n", " \"\"\"A SELECT query to run.\"\"\"\n", " print(query)\n", " try:\n", " cursor = self.con.cursor()\n", " cursor.execute(query)\n", " res = cursor.fetchall()\n", " return str(res)\n", " except sqlite3.Error as e:\n", " return str(e)\n", "\n", " def _execute_query(self, query: str) -> str:\n", " \"\"\"An INSERT, UPDATE, or DELETE query to execute.\"\"\"\n", " print(query)\n", " try:\n", " cursor = self.con.cursor()\n", " cursor.execute(query)\n", " rows_affected = cursor.rowcount\n", " self.con.commit()\n", " if rows_affected > 0:\n", " return f\"Query executed successfully, {rows_affected} row(s) were updated/inserted.\"\n", " else:\n", " return \"No rows were updated/inserted.\"\n", " except sqlite3.Error as e:\n", " print(e)\n", " return str(e)" ] }, { "cell_type": "markdown", "id": "7ec9101656df945c", "metadata": {}, "source": [ "\n", "Now that we have our tools setup it is time to engineer our prompt\n", "\n", "## Prompt Engineering\n", "\n", "Knowing what tools are available is crucial when prompt engineering, so that we can tell the LLM when and how the tools should be used.\n", "\n", "Now we will take our `Librarian` and add our `@openai.call`:\n" ] }, { "cell_type": "code", "execution_count": 4, "id": "f65dcc4e18b3fe9c", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T16:56:09.709937Z", "start_time": "2024-09-30T16:56:09.637583Z" } }, "outputs": [], "source": [ "from mirascope.core import prompt_template\n", "\n", "\n", "class Librarian(LibrarianBase):\n", " @openai.call(model=\"gpt-4o-mini\", stream=True)\n", " @prompt_template(\n", " \"\"\"\n", " SYSTEM:\n", " You are a friendly and knowledgeable librarian named Mira. Your role is to \n", " assist patrons with their queries, recommend books, \n", " and provide information on a wide range of topics.\n", "\n", " Personality:\n", " - Warm and approachable, always ready with a kind word\n", " - Patient and understanding, especially with those who are hesitant or confused\n", " - Enthusiastic about books and learning\n", " - Respectful of all patrons, regardless of their background or level of knowledge\n", "\n", " Services:\n", " - Keep track of patrons' reading lists using a SQLite database. Assume that the user is non technical and will ask you\n", " questions in plain English.\n", " - Recommend books based on the user's preferences\n", " Your task is to write a query based on the user's request.\n", "\n", " The database schema is as follows:\n", "\n", " TABLE ReadingList (\n", " id INTEGER PRIMARY KEY AUTOINCREMENT,\n", " title TEXT NOT NULL,\n", " status TEXT CHECK(status IN ('Not Started', 'In Progress', 'Complete')) NOT NULL,\n", " rating INTEGER CHECK(rating >= 1 AND rating <= 5),\n", " );\n", "\n", " You must interpret the user's request and write the appropriate SQL query to\n", " pass in the tools.\n", "\n", " Example interactions:\n", " 1. Select\n", " - USER: \"Show me all books.\"\n", " - ASSISTANT: \"SELECT * FROM ReadingList;\"\n", " 2. Insert\n", " - USER: \"Add Gone with the Wind to my reading list.\"\n", " - ASSISTANT: \"INSERT INTO ReadingList (title, status) VALUES ('Gone with the Wind', 'Not Started');\"\n", " 3. Update\n", " - USER: \"I just finished Gone with the Wind, can you update the status, and give it 5 stars??\"\n", " - ASSISTANT: \"UPDATE ReadingList SET status = 'Complete' and rating = 5 WHERE title = 'Gone with the Wind';\"\n", " 4. Delete\n", " - USER: \"Remove Gone with the Wind from my reading list.\"\n", " - ASSISTANT: \"DELETE FROM ReadingList WHERE title = 'Gone with the Wind';\"\n", "\n", " If field are not mentioned, omit them from the query.\n", " All queries must end with a semicolon.\n", "\n", " You have access to the following tools:\n", " - `_run_query`: When user asks for recommendations, you can use this tool to see what they have read.\n", " - `_execute_query`: Use the query generated to execute an \n", " INSERT, UPDATE, or DELETE query.\n", "\n", " You must use these tools to interact with the database.\n", "\n", " MESSAGES: {self.messages}\n", " USER: {query}\n", " \"\"\"\n", " )\n", " async def _stream(self, query: str) -> openai.OpenAIDynamicConfig:\n", " return {\"tools\": [self._run_query, self._execute_query]}\n", "\n", " async def _step(self, question: str):\n", " response = await self._stream(question)\n", " tools_and_outputs = []\n", " async for chunk, tool in response:\n", " if tool:\n", " tools_and_outputs.append((tool, tool.call()))\n", " else:\n", " print(chunk.content, end=\"\", flush=True)\n", " if response.user_message_param:\n", " self.messages.append(response.user_message_param)\n", " self.messages.append(response.message_param)\n", " if tools_and_outputs:\n", " self.messages += response.tool_message_params(tools_and_outputs)\n", " await self._step(\"\")\n", "\n", " async def run(self):\n", " while True:\n", " question = input(\"(User): \")\n", " if question == \"exit\":\n", " break\n", " print(\"(Assistant): \", end=\"\", flush=True)\n", " await self._step(question)\n", " print()" ] }, { "cell_type": "markdown", "id": "d27b8c567f66dedb", "metadata": {}, "source": [ "\n", "Let's break down the prompt:\n", "\n", "1. We give the LLM a friendly personality, which is an optional but crucial feature for user-facing applications.\n", "2. We provide the LLM with knowledge of the database schema that it will operate on.\n", "3. We give example interactions to reinforce how the LLM should operate.\n", "4. We give more fine-tuned instructions and constraints\n", "5. We tell the LLM how to use its tools\n", "\n", "After writing our prompt, we go through our agent loop and we can now use our Librarian.\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "196d0e31b4fb5e7e", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T16:57:08.925310Z", "start_time": "2024-09-30T16:56:51.960398Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(Assistant): SELECT * FROM ReadingList;\n", "It looks like your reading list is currently empty. If you're interested in fantasy books, I can recommend some excellent titles to get you started! Would you like some suggestions?\n", "(Assistant): Here are some fantastic fantasy books that you might enjoy:\n", "\n", "1. **\"The Hobbit\" by J.R.R. Tolkien** - A classic tale of adventure and friendship that follows a hobbit named Bilbo Baggins as he embarks on a quest to help a group of dwarves reclaim their homeland from a dragon.\n", "\n", "2. **\"Harry Potter and the Sorcerer's Stone\" by J.K. Rowling** - The beginning of a beloved series about a young boy discovering he is a wizard and attending Hogwarts School of Witchcraft and Wizardry.\n", "\n", "3. **\"The Name of the Wind\" by Patrick Rothfuss** - This is the first book in the \"Kingkiller Chronicle\" series, following the story of Kvothe, a gifted young man who grows up to become a legendary figure.\n", "\n", "4. **\"A Darker Shade of Magic\" by V.E. Schwab** - A captivating story set in a universe with parallel Londons, where only a few can travel between them and magic is a rare commodity.\n", "\n", "5. **\"The Priory of the Orange Tree\" by Samantha Shannon** - An epic standalone fantasy with a richly built world that features dragons, a matriarchal society, and diverse characters.\n", "\n", "If any of these titles pique your interest, just let me know and I can add them to your reading list!\n" ] } ], "source": [ "librarian = Librarian()\n", "await librarian.run()" ] }, { "cell_type": "markdown", "id": "2ab123347507f656", "metadata": {}, "source": [ "\n", "Note that the SQL statements in the dialogue are there for development purposes.\n", "\n", "Having established that we can have a quality conversation with our `Librarian`, we can now enhance our prompt. However, we must ensure that these improvements don't compromise the Librarian's core functionality. Check out [Evaluating SQL Agent](../../evals/evaluating_sql_agent) for an in-depth guide on how we evaluate the quality of our prompt.\n", "\n", "
\n", "

Additional Real-World Examples

\n", "\n", "
\n", "\n", "When adapting this recipe to your specific use-case, consider the following:\n", "\n", "- Experiment with the prompt, by adding query planning or other prompting techniques to break down a complex request.\n", "- Experiment with different model providers to balance quality and speed.\n", "- Use in a development or sandbox environment for rapid development.\n", "\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.6" } }, "nbformat": 4, "nbformat_minor": 5 }