Buckets:
| <meta charset="utf-8" /><meta name="hf:doc:metadata" content="{"title":"Agent for text-to-SQL with automatic error correction","local":"agent-for-text-to-sql-with-automatic-error-correction","sections":[{"title":"Setup SQL tables","local":"setup-sql-tables","sections":[],"depth":2},{"title":"Build our agent","local":"build-our-agent","sections":[],"depth":2},{"title":"Increasing difficulty: Table joins","local":"increasing-difficulty-table-joins","sections":[],"depth":2}],"depth":1}"/> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/entry/start.jASTe42e.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/sKjSZaOT.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/BELRLMvK.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/entry/app.BiiRsDYZ.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/Bvy3depm.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/CZ3tSluR.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/DeTtPNcu.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/DsnmJJEf.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/nodes/0.CHhuiHfp.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/BkMsHYA7.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/nodes/5.CKf9mdwE.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/1_L1e_iO.js" rel="modulepreload"> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/chunks/6gp6jeKp.js" rel="modulepreload"> | |
| <!--3rot8v--><meta name="hf:doc:metadata" content="{"title":"Agent for text-to-SQL with automatic error correction","local":"agent-for-text-to-sql-with-automatic-error-correction","sections":[{"title":"Setup SQL tables","local":"setup-sql-tables","sections":[],"depth":2},{"title":"Build our agent","local":"build-our-agent","sections":[],"depth":2},{"title":"Increasing difficulty: Table joins","local":"increasing-difficulty-table-joins","sections":[],"depth":2}],"depth":1}"/><!----> | |
| <link href="/docs/cookbook/pr_367/en/_app/immutable/assets/0.tn0RQdqM.css" rel="modulepreload"> <!--[--><!--[0--><!--[--><!--[0--><!--[--><p></p> <div class="flex space-x-1 absolute z-10 right-0 top-0"><!----> <!--[0--><a href="https://colab.research.google.com/github/huggingface/cookbook/blob/fix-trl-example-paths/notebooks/en/agent_text_to_sql.ipynb" target="_blank"><img alt="Open In Colab" class="!m-0" src="https://colab.research.google.com/assets/colab-badge.svg"/></a><!--]--> <!--[-1--><!--]--></div><!----> <div class="items-center shrink-0 min-w-[100px] max-sm:min-w-[50px] justify-end ml-auto flex" style="float: right; margin-left: 10px; display: inline-flex; position: relative; z-index: 10;"><div class="inline-flex rounded-md max-sm:rounded-sm"><button class="inline-flex items-center gap-1 h-7 max-sm:h-7 px-2 max-sm:px-1.5 text-sm font-medium text-gray-800 border border-r-0 rounded-l-md max-sm:rounded-l-sm border-gray-200 bg-white hover:shadow-inner dark:border-gray-850 dark:bg-gray-950 dark:text-gray-200 dark:hover:bg-gray-800" aria-live="polite"><span class="inline-flex items-center justify-center rounded-md p-0.5 max-sm:p-0 hover:text-gray-800 dark:hover:text-gray-200"><svg class="sm:size-3.5 size-3" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----></span> <span>Copy page</span></button> <button class="inline-flex items-center justify-center w-6 max-sm:w-5 h-7 max-sm:h-7 disabled:pointer-events-none text-sm text-gray-500 hover:text-gray-700 dark:hover:text-white rounded-r-md max-sm:rounded-r-sm border border-l transition border-gray-200 bg-white hover:shadow-inner dark:border-gray-850 dark:bg-gray-950 dark:text-gray-200 dark:hover:bg-gray-800" aria-haspopup="menu" aria-expanded="false" aria-label="Open copy menu"><svg class="transition-transform text-gray-400 overflow-visible sm:size-3.5 size-3 rotate-0" width="1em" height="1em" viewBox="0 0 12 7" fill="none" xmlns="http://www.w3.org/2000/svg"><path d="M1 1L6 6L11 1" stroke="currentColor"></path></svg><!----></button></div> <!--[-1--><!--]--></div><!----> <!--[0--><h1 class="relative group"><a id="agent-for-text-to-sql-with-automatic-error-correction" class="header-link block pr-1.5 text-lg no-hover:hidden with-hover:absolute with-hover:p-1.5 with-hover:opacity-0 with-hover:group-hover:opacity-100 with-hover:right-full" href="#agent-for-text-to-sql-with-automatic-error-correction"><span><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" aria-hidden="true" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 256 256"><path d="M167.594 88.393a8.001 8.001 0 0 1 0 11.314l-67.882 67.882a8 8 0 1 1-11.314-11.315l67.882-67.881a8.003 8.003 0 0 1 11.314 0zm-28.287 84.86l-28.284 28.284a40 40 0 0 1-56.567-56.567l28.284-28.284a8 8 0 0 0-11.315-11.315l-28.284 28.284a56 56 0 0 0 79.196 79.197l28.285-28.285a8 8 0 1 0-11.315-11.314zM212.852 43.14a56.002 56.002 0 0 0-79.196 0l-28.284 28.284a8 8 0 1 0 11.314 11.314l28.284-28.284a40 40 0 0 1 56.568 56.567l-28.285 28.285a8 8 0 0 0 11.315 11.314l28.284-28.284a56.065 56.065 0 0 0 0-79.196z" fill="currentColor"></path></svg><!----></span></a> <span>Agent for text-to-SQL with automatic error correction</span></h1><!--]--><!----> <p><em>Authored by: <a href="https://huggingface.co/m-ric" rel="nofollow">Aymeric Roucher</a></em></p> <p>In this tutorial, we’ll see how to implement an agent that leverages SQL using <code>smolagents</code>.</p> <p>What’s the advantage over a standard text-to-SQL pipeline?</p> <p>A standard text-to-sql pipeline is brittle, since the generated SQL query can be incorrect. Even worse, the query could be incorrect, but not raise an error, instead giving some incorrect/useless outputs without raising an alarm.</p> <p>👉 Instead, <strong>an agent system is able to critically inspect outputs and decide if the query needs to be changed or not</strong>, thus giving it a huge performance boost.</p> <p>Let’s build this agent! 💪</p> <!--[1--><h2 class="relative group"><a id="setup-sql-tables" class="header-link block pr-1.5 text-lg no-hover:hidden with-hover:absolute with-hover:p-1.5 with-hover:opacity-0 with-hover:group-hover:opacity-100 with-hover:right-full" href="#setup-sql-tables"><span><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" aria-hidden="true" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 256 256"><path d="M167.594 88.393a8.001 8.001 0 0 1 0 11.314l-67.882 67.882a8 8 0 1 1-11.314-11.315l67.882-67.881a8.003 8.003 0 0 1 11.314 0zm-28.287 84.86l-28.284 28.284a40 40 0 0 1-56.567-56.567l28.284-28.284a8 8 0 0 0-11.315-11.315l-28.284 28.284a56 56 0 0 0 79.196 79.197l28.285-28.285a8 8 0 1 0-11.315-11.314zM212.852 43.14a56.002 56.002 0 0 0-79.196 0l-28.284 28.284a8 8 0 1 0 11.314 11.314l28.284-28.284a40 40 0 0 1 56.568 56.567l-28.285 28.285a8 8 0 0 0 11.315 11.314l28.284-28.284a56.065 56.065 0 0 0 0-79.196z" fill="currentColor"></path></svg><!----></span></a> <span>Setup SQL tables</span></h2><!--]--><!----> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!----><span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> ( | |
| create_engine, | |
| MetaData, | |
| Table, | |
| Column, | |
| String, | |
| Integer, | |
| Float, | |
| insert, | |
| inspect, | |
| text, | |
| ) | |
| engine = create_engine(<span class="hljs-string">"sqlite:///:memory:"</span>) | |
| metadata_obj = MetaData() | |
| <span class="hljs-comment"># create city SQL table</span> | |
| table_name = <span class="hljs-string">"receipts"</span> | |
| receipts = Table( | |
| table_name, | |
| metadata_obj, | |
| Column(<span class="hljs-string">"receipt_id"</span>, Integer, primary_key=<span class="hljs-literal">True</span>), | |
| Column(<span class="hljs-string">"customer_name"</span>, String(<span class="hljs-number">16</span>), primary_key=<span class="hljs-literal">True</span>), | |
| Column(<span class="hljs-string">"price"</span>, Float), | |
| Column(<span class="hljs-string">"tip"</span>, Float), | |
| ) | |
| metadata_obj.create_all(engine)<!----></pre></div><!----> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!---->rows = [ | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">1</span>, <span class="hljs-string">"customer_name"</span>: <span class="hljs-string">"Alan Payne"</span>, <span class="hljs-string">"price"</span>: <span class="hljs-number">12.06</span>, <span class="hljs-string">"tip"</span>: <span class="hljs-number">1.20</span>}, | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">2</span>, <span class="hljs-string">"customer_name"</span>: <span class="hljs-string">"Alex Mason"</span>, <span class="hljs-string">"price"</span>: <span class="hljs-number">23.86</span>, <span class="hljs-string">"tip"</span>: <span class="hljs-number">0.24</span>}, | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">3</span>, <span class="hljs-string">"customer_name"</span>: <span class="hljs-string">"Woodrow Wilson"</span>, <span class="hljs-string">"price"</span>: <span class="hljs-number">53.43</span>, <span class="hljs-string">"tip"</span>: <span class="hljs-number">5.43</span>}, | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">4</span>, <span class="hljs-string">"customer_name"</span>: <span class="hljs-string">"Margaret James"</span>, <span class="hljs-string">"price"</span>: <span class="hljs-number">21.11</span>, <span class="hljs-string">"tip"</span>: <span class="hljs-number">1.00</span>}, | |
| ] | |
| <span class="hljs-keyword">for</span> row <span class="hljs-keyword">in</span> rows: | |
| stmt = insert(receipts).values(**row) | |
| <span class="hljs-keyword">with</span> engine.begin() <span class="hljs-keyword">as</span> connection: | |
| cursor = connection.execute(stmt)<!----></pre></div><!----> <p>Let’s check that our system works with a basic query:</p> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!----><span class="hljs-meta">>>> </span><span class="hljs-keyword">with</span> engine.connect() <span class="hljs-keyword">as</span> con: | |
| <span class="hljs-meta">... </span> rows = con.execute(text(<span class="hljs-string">"""SELECT * from receipts"""</span>)) | |
| <span class="hljs-meta">... </span> <span class="hljs-keyword">for</span> row <span class="hljs-keyword">in</span> rows: | |
| <span class="hljs-meta">... </span> <span class="hljs-built_in">print</span>(row)<!----></pre></div><!----> <pre>(1, 'Alan Payne', 12.06, 1.2) | |
| (2, 'Alex Mason', 23.86, 0.24) | |
| (3, 'Woodrow Wilson', 53.43, 5.43) | |
| (4, 'Margaret James', 21.11, 1.0) | |
| </pre> <!--[1--><h2 class="relative group"><a id="build-our-agent" class="header-link block pr-1.5 text-lg no-hover:hidden with-hover:absolute with-hover:p-1.5 with-hover:opacity-0 with-hover:group-hover:opacity-100 with-hover:right-full" href="#build-our-agent"><span><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" aria-hidden="true" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 256 256"><path d="M167.594 88.393a8.001 8.001 0 0 1 0 11.314l-67.882 67.882a8 8 0 1 1-11.314-11.315l67.882-67.881a8.003 8.003 0 0 1 11.314 0zm-28.287 84.86l-28.284 28.284a40 40 0 0 1-56.567-56.567l28.284-28.284a8 8 0 0 0-11.315-11.315l-28.284 28.284a56 56 0 0 0 79.196 79.197l28.285-28.285a8 8 0 1 0-11.315-11.314zM212.852 43.14a56.002 56.002 0 0 0-79.196 0l-28.284 28.284a8 8 0 1 0 11.314 11.314l28.284-28.284a40 40 0 0 1 56.568 56.567l-28.285 28.285a8 8 0 0 0 11.315 11.314l28.284-28.284a56.065 56.065 0 0 0 0-79.196z" fill="currentColor"></path></svg><!----></span></a> <span>Build our agent</span></h2><!--]--><!----> <p>Now let’s make our SQL table retrievable by a tool.</p> <p>Our <code>sql_engine</code> tool needs the following: (read <a href="https://huggingface.co/docs/transformers/en/agents#create-a-new-tool" rel="nofollow">the documentation</a> for more detail)</p> <ul><li>A docstring with an <code>Args:</code> part. This docstring will be parsed to become the tool’s <code>description</code> attribute, which will be used as the instruction manual for the LLM powering the agent, so it’s important to provide it!</li> <li>Type hints for inputs and output.</li></ul> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!----><span class="hljs-keyword">from</span> smolagents <span class="hljs-keyword">import</span> tool | |
| <span class="hljs-meta">@tool</span> | |
| <span class="hljs-keyword">def</span> <span class="hljs-title function_">sql_engine</span>(<span class="hljs-params">query: <span class="hljs-built_in">str</span></span>) -> <span class="hljs-built_in">str</span>: | |
| <span class="hljs-string">""" | |
| Allows you to perform SQL queries on the table. Returns a string representation of the result. | |
| The table is named 'receipts'. Its description is as follows: | |
| Columns: | |
| - receipt_id: INTEGER | |
| - customer_name: VARCHAR(16) | |
| - price: FLOAT | |
| - tip: FLOAT | |
| Args: | |
| query: The query to perform. This should be correct SQL. | |
| """</span> | |
| output = <span class="hljs-string">""</span> | |
| <span class="hljs-keyword">with</span> engine.connect() <span class="hljs-keyword">as</span> con: | |
| rows = con.execute(text(query)) | |
| <span class="hljs-keyword">for</span> row <span class="hljs-keyword">in</span> rows: | |
| output += <span class="hljs-string">"\n"</span> + <span class="hljs-built_in">str</span>(row) | |
| <span class="hljs-keyword">return</span> output<!----></pre></div><!----> <p>Now let us create an agent that leverages this tool.</p> <p>We use the <code>CodeAgent</code>, which is <code>transformers.agents</code>’ main agent class: an agent that writes actions in code and can iterate on previous output according to the ReAct framework.</p> <p>The <code>llm_engine</code> is the LLM that powers the agent system. <code>InferenceClientModel</code> allows you to call LLMs using Hugging Face’s Inference API, either via Serverless or Dedicated endpoint, but you could also use any proprietary API: check out <a href="agent_change_llm">this other cookbook</a> to learn how to adapt it.</p> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!----><span class="hljs-keyword">from</span> smolagents <span class="hljs-keyword">import</span> CodeAgent, InferenceClientModel | |
| agent = CodeAgent( | |
| tools=[sql_engine], | |
| model=InferenceClientModel(<span class="hljs-string">"meta-llama/Meta-Llama-3-8B-Instruct"</span>), | |
| )<!----></pre></div><!----> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!---->agent.run(<span class="hljs-string">"Can you give me the name of the client who got the most expensive receipt?"</span>)<!----></pre></div><!----> <!--[1--><h2 class="relative group"><a id="increasing-difficulty-table-joins" class="header-link block pr-1.5 text-lg no-hover:hidden with-hover:absolute with-hover:p-1.5 with-hover:opacity-0 with-hover:group-hover:opacity-100 with-hover:right-full" href="#increasing-difficulty-table-joins"><span><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" aria-hidden="true" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 256 256"><path d="M167.594 88.393a8.001 8.001 0 0 1 0 11.314l-67.882 67.882a8 8 0 1 1-11.314-11.315l67.882-67.881a8.003 8.003 0 0 1 11.314 0zm-28.287 84.86l-28.284 28.284a40 40 0 0 1-56.567-56.567l28.284-28.284a8 8 0 0 0-11.315-11.315l-28.284 28.284a56 56 0 0 0 79.196 79.197l28.285-28.285a8 8 0 1 0-11.315-11.314zM212.852 43.14a56.002 56.002 0 0 0-79.196 0l-28.284 28.284a8 8 0 1 0 11.314 11.314l28.284-28.284a40 40 0 0 1 56.568 56.567l-28.285 28.285a8 8 0 0 0 11.315 11.314l28.284-28.284a56.065 56.065 0 0 0 0-79.196z" fill="currentColor"></path></svg><!----></span></a> <span>Increasing difficulty: Table joins</span></h2><!--]--><!----> <p>Now let’s make it more challenging! We want our agent to handle joins across multiple tables.</p> <p>So let’s make a second table recording the names of waiters for each <code>receipt_id</code>!</p> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!---->table_name = <span class="hljs-string">"waiters"</span> | |
| receipts = Table( | |
| table_name, | |
| metadata_obj, | |
| Column(<span class="hljs-string">"receipt_id"</span>, Integer, primary_key=<span class="hljs-literal">True</span>), | |
| Column(<span class="hljs-string">"waiter_name"</span>, String(<span class="hljs-number">16</span>), primary_key=<span class="hljs-literal">True</span>), | |
| ) | |
| metadata_obj.create_all(engine) | |
| rows = [ | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">1</span>, <span class="hljs-string">"waiter_name"</span>: <span class="hljs-string">"Corey Johnson"</span>}, | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">2</span>, <span class="hljs-string">"waiter_name"</span>: <span class="hljs-string">"Michael Watts"</span>}, | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">3</span>, <span class="hljs-string">"waiter_name"</span>: <span class="hljs-string">"Michael Watts"</span>}, | |
| {<span class="hljs-string">"receipt_id"</span>: <span class="hljs-number">4</span>, <span class="hljs-string">"waiter_name"</span>: <span class="hljs-string">"Margaret James"</span>}, | |
| ] | |
| <span class="hljs-keyword">for</span> row <span class="hljs-keyword">in</span> rows: | |
| stmt = insert(receipts).values(**row) | |
| <span class="hljs-keyword">with</span> engine.begin() <span class="hljs-keyword">as</span> connection: | |
| cursor = connection.execute(stmt)<!----></pre></div><!----> <p>We need to update the <code>SQLExecutorTool</code> with this table’s description to let the LLM properly leverage information from this table.</p> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!----><span class="hljs-meta">>>> </span>updated_description = <span class="hljs-string">"""Allows you to perform SQL queries on the table. Beware that this tool's output is a string representation of the execution output. | |
| <span class="hljs-meta">... </span>It can use the following tables:"""</span> | |
| <span class="hljs-meta">>>> </span>inspector = inspect(engine) | |
| <span class="hljs-meta">>>> </span><span class="hljs-keyword">for</span> table <span class="hljs-keyword">in</span> [<span class="hljs-string">"receipts"</span>, <span class="hljs-string">"waiters"</span>]: | |
| <span class="hljs-meta">... </span> columns_info = [(col[<span class="hljs-string">"name"</span>], col[<span class="hljs-string">"type"</span>]) <span class="hljs-keyword">for</span> col <span class="hljs-keyword">in</span> inspector.get_columns(table)] | |
| <span class="hljs-meta">... </span> table_description = <span class="hljs-string">f"Table '<span class="hljs-subst">{table}</span>':\n"</span> | |
| <span class="hljs-meta">... </span> table_description += <span class="hljs-string">"Columns:\n"</span> + <span class="hljs-string">"\n"</span>.join([<span class="hljs-string">f" - <span class="hljs-subst">{name}</span>: <span class="hljs-subst">{col_type}</span>"</span> <span class="hljs-keyword">for</span> name, col_type <span class="hljs-keyword">in</span> columns_info]) | |
| <span class="hljs-meta">... </span> updated_description += <span class="hljs-string">"\n\n"</span> + table_description | |
| <span class="hljs-meta">>>> </span><span class="hljs-built_in">print</span>(updated_description)<!----></pre></div><!----> <pre>Allows you to perform SQL queries on the table. Beware that this tool's output is a string representation of the execution output. | |
| It can use the following tables: | |
| Table 'receipts': | |
| Columns: | |
| - receipt_id: INTEGER | |
| - customer_name: VARCHAR(16) | |
| - price: FLOAT | |
| - tip: FLOAT | |
| Table 'waiters': | |
| Columns: | |
| - receipt_id: INTEGER | |
| - waiter_name: VARCHAR(16) | |
| </pre> <p>Since this request is a bit harder than the previous one, we’ll switch the llm engine to use the more powerful <a href="https://huggingface.co/Qwen/Qwen2.5-72B-Instruct" rel="nofollow">Qwen/Qwen2.5-72B-Instruct</a>!</p> <div class="code-block relative "><div class="absolute top-2.5 right-4"><button class="inline-flex items-center relative text-sm focus:text-green-500 cursor-pointer focus:outline-none transition duration-200 ease-in-out opacity-0 mx-0.5 text-gray-600 " title="code excerpt" type="button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M28,10V28H10V10H28m0-2H10a2,2,0,0,0-2,2V28a2,2,0,0,0,2,2H28a2,2,0,0,0,2-2V10a2,2,0,0,0-2-2Z" transform="translate(0)"></path><path d="M4,18H2V4A2,2,0,0,1,4,2H18V4H4Z" transform="translate(0)"></path><rect fill="none" width="32" height="32"></rect></svg><!----> <div class=" absolute pointer-events-none transition-opacity bg-black text-white py-1 px-2 leading-tight rounded font-normal shadow left-1/2 top-full transform -translate-x-1/2 translate-y-2 opacity-0 "><div class="absolute bottom-full left-1/2 transform -translate-x-1/2 w-0 h-0 border-black border-4 border-t-0" style="border-left-color: transparent; border-right-color: transparent;"></div> Copied</div><!----></button><!----></div> <pre class="language-python "><!---->sql_engine.description = updated_description | |
| agent = CodeAgent( | |
| tools=[sql_engine], | |
| model=InferenceClientModel(<span class="hljs-string">"Qwen/Qwen2.5-72B-Instruct"</span>), | |
| ) | |
| agent.run(<span class="hljs-string">"Which waiter got more total money from tips?"</span>)<!----></pre></div><!----> <p>It directly works! The setup was surprisingly simple, wasn’t it?</p> <p>✅ Now you can go build this text-to-SQL system you’ve always dreamt of! ✨</p> <a class="!text-gray-400 !no-underline text-sm flex items-center not-prose mt-4" href="https://github.com/huggingface/cookbook/blob/main/notebooks/en/agent_text_to_sql.md" target="_blank"><svg class="mr-1" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" fill="currentColor" focusable="false" role="img" width="1em" height="1em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 32 32"><path d="M31,16l-7,7l-1.41-1.41L28.17,16l-5.58-5.59L24,9l7,7z"></path><path d="M1,16l7-7l1.41,1.41L3.83,16l5.58,5.59L8,23l-7-7z"></path><path d="M12.419,25.484L17.639,6.552l1.932,0.518L14.351,26.002z"></path></svg><!----> <span><span class="underline">Update</span> on GitHub</span></a><!----> <p></p><!--]--><!----><!--]--><!--]--><!--]--> <!--[-1--><!--]--><!--]--> | |
| <script> | |
| { | |
| __sveltekit_k74v8w = { | |
| base: "/docs/cookbook/pr_367/en", | |
| assets: "/docs/cookbook/pr_367/en" | |
| }; | |
| const element = document.currentScript.parentElement; | |
| Promise.all([ | |
| import("/docs/cookbook/pr_367/en/_app/immutable/entry/start.jASTe42e.js"), | |
| import("/docs/cookbook/pr_367/en/_app/immutable/entry/app.BiiRsDYZ.js") | |
| ]).then(([kit, app]) => { | |
| kit.start(app, element, { | |
| node_ids: [0, 5], | |
| data: [null,null], | |
| form: null, | |
| error: null | |
| }); | |
| }); | |
| } | |
| </script> | |
Xet Storage Details
- Size:
- 34.2 kB
- Xet hash:
- e679513fe8d38f9554f65fd1a0e932b5a726f97df069b1d01912e9c310e9e2f5
·
Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.