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/main/en/_app/immutable/assets/0.e3b0c442.css" rel="modulepreload"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/entry/start.96b44205.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/scheduler.65852ee5.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/singletons.a64a46c3.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/paths.f88132ad.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/entry/app.e92a3d99.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/index.aa74147d.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/nodes/0.0809e592.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/each.e59479a4.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/nodes/6.e22a0d83.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/DocNotebookDropdown.479f4286.js"> | |
| <link rel="modulepreload" href="/docs/cookbook/main/en/_app/immutable/chunks/EditOnGithub.4eda6a96.js"><!-- HEAD_svelte-u9bgzb_START --><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}"><!-- HEAD_svelte-u9bgzb_END --> <p></p> <div class="flex space-x-1 absolute z-10 right-0 top-0"> <a href="https://colab.research.google.com/github/huggingface/cookbook/blob/multiagent_assist_improvements/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> </div> <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 class="" 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 data-svelte-h="svelte-1xlqnsv"><em>Authored by: <a href="https://huggingface.co/m-ric" rel="nofollow">Aymeric Roucher</a></em></p> <p data-svelte-h="svelte-wem15p">In this tutorial, we’ll see how to implement an agent that leverages SQL using <code>transformers.agents</code>.</p> <p data-svelte-h="svelte-3ojw25">What’s the advantage over a standard text-to-SQL pipeline?</p> <p data-svelte-h="svelte-1hw7g6s">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 data-svelte-h="svelte-q251yb">👉 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 data-svelte-h="svelte-1tzrwc8">Let’s build this agent! 💪</p> <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 class="" 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 class="" 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=""><!-- HTML_TAG_START --><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)<!-- HTML_TAG_END --></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 class="" 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=""><!-- HTML_TAG_START -->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)<!-- HTML_TAG_END --></pre></div> <p data-svelte-h="svelte-ec23hm">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 class="" 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=""><!-- HTML_TAG_START --><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)<!-- HTML_TAG_END --></pre></div> <pre data-svelte-h="svelte-jbb28p">(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> <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 class="" 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 data-svelte-h="svelte-fdu5cw">Now let’s make our SQL table retrievable by a tool.</p> <p data-svelte-h="svelte-jme9kq">The tool’s <code>description</code> attribute will be embedded in the LLM’s prompt by the agent system: it gives the LLM information about how to use the tool. So that is where we want to describe the SQL 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 class="" 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=""><!-- HTML_TAG_START --><span class="hljs-meta">>>> </span>inspector = inspect(engine) | |
| <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(<span class="hljs-string">"receipts"</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><span class="hljs-built_in">print</span>(table_description)<!-- HTML_TAG_END --></pre></div> <pre data-svelte-h="svelte-1pz02dc">Columns: | |
| - receipt_id: INTEGER | |
| - customer_name: VARCHAR(16) | |
| - price: FLOAT | |
| - tip: FLOAT | |
| </pre> <p data-svelte-h="svelte-84s4c8">Now the tool will need a few mandatory arguments (read <a href="https://huggingface.co/docs/transformers/en/agents" rel="nofollow">the documentation</a> for more detail):</p> <ul data-svelte-h="svelte-1l8h02j"><li>The <code>description</code> mentioned above</li> <li>A <code>name</code> by which the agent system can call it.</li> <li>A dictionary of <code>inputs</code>: describe the tool’s input, each with a <code>type</code> and a <code>description</code> key.</li> <li>An <code>output_type</code> for compatibility with gradio demos for instance.</li> <li>A <code>forward</code> method that gets called when the tool is called.</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 class="" 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=""><!-- HTML_TAG_START --><span class="hljs-keyword">from</span> transformers.agents <span class="hljs-keyword">import</span> Tool | |
| <span class="hljs-keyword">class</span> <span class="hljs-title class_">SQLExecutorTool</span>(<span class="hljs-title class_ inherited__">Tool</span>): | |
| name = <span class="hljs-string">"sql_engine"</span> | |
| description = <span class="hljs-string">f"""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: \n<span class="hljs-subst">{table_description}</span>"""</span> | |
| inputs = { | |
| <span class="hljs-string">"query"</span>: { | |
| <span class="hljs-string">"type"</span>: <span class="hljs-string">"text"</span>, | |
| <span class="hljs-string">"description"</span>: <span class="hljs-string">f"The query to perform. This should be correct SQL."</span>, | |
| } | |
| } | |
| output_type = <span class="hljs-string">"text"</span> | |
| <span class="hljs-keyword">def</span> <span class="hljs-title function_">forward</span>(<span class="hljs-params">self, query: <span class="hljs-built_in">str</span></span>) -> <span class="hljs-built_in">str</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<!-- HTML_TAG_END --></pre></div> <p data-svelte-h="svelte-1t4r7cu">Now let us create an agent that leverages this tool.</p> <p data-svelte-h="svelte-12rc58c">We use the <code>ReactCodeAgent</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 data-svelte-h="svelte-1efohjn">The <code>llm_engine</code> is the LLM that powers the agent system. <code>HfEngine</code> allows you to call LLMs using HF’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 class="" 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=""><!-- HTML_TAG_START --><span class="hljs-keyword">from</span> transformers.agents <span class="hljs-keyword">import</span> ReactCodeAgent, HfEngine | |
| agent = ReactCodeAgent( | |
| tools=[SQLExecutorTool()], | |
| llm_engine=HfEngine(<span class="hljs-string">"meta-llama/Meta-Llama-3-70B-Instruct"</span>), | |
| )<!-- HTML_TAG_END --></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 class="" 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=""><!-- HTML_TAG_START -->agent.run(<span class="hljs-string">"Can you give me the name of the client who got the most expensive receipt?"</span>)<!-- HTML_TAG_END --></pre></div> <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 class="" 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 data-svelte-h="svelte-nr3fl7">Now let’s make it more challenging! We want our agent to handle joins across multiple tables.</p> <p data-svelte-h="svelte-6lwjm6">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 class="" 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=""><!-- HTML_TAG_START -->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)<!-- HTML_TAG_END --></pre></div> <p data-svelte-h="svelte-frhx8p">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 class="" 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=""><!-- HTML_TAG_START --><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)<!-- HTML_TAG_END --></pre></div> <pre data-svelte-h="svelte-d75mf2">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> <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 class="" 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=""><!-- HTML_TAG_START --><span class="hljs-meta">>>> </span>sql_tool = SQLExecutorTool() | |
| <span class="hljs-meta">>>> </span>sql_tool.description = updated_description | |
| <span class="hljs-meta">>>> </span>agent = ReactCodeAgent( | |
| <span class="hljs-meta">... </span> tools=[sql_tool], | |
| <span class="hljs-meta">... </span> llm_engine=HfEngine(<span class="hljs-string">"meta-llama/Meta-Llama-3-70B-Instruct"</span>), | |
| <span class="hljs-meta">... </span>) | |
| <span class="hljs-meta">>>> </span>agent.run(<span class="hljs-string">"Which waiter got more total money from tips?"</span>)<!-- HTML_TAG_END --></pre></div> <pre data-svelte-h="svelte-1fqzj2e">['time', 'queue', 'random', 'unicodedata', 're', 'collections', 'itertools', 'stat', 'math', 'statistics'] | |
| </pre> <p data-svelte-h="svelte-1umn140">It directly works! The setup was surprisingly simple, wasn’t it?</p> <p data-svelte-h="svelte-bt1f1w">✅ 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"><span data-svelte-h="svelte-1kd6by1"><</span> <span data-svelte-h="svelte-x0xyl0">></span> <span data-svelte-h="svelte-1dajgef"><span class="underline ml-1.5">Update</span> on GitHub</span></a> <p></p> | |
| <script> | |
| { | |
| __sveltekit_1l2350x = { | |
| assets: "/docs/cookbook/main/en", | |
| base: "/docs/cookbook/main/en", | |
| env: {} | |
| }; | |
| const element = document.currentScript.parentElement; | |
| const data = [null,null]; | |
| Promise.all([ | |
| import("/docs/cookbook/main/en/_app/immutable/entry/start.96b44205.js"), | |
| import("/docs/cookbook/main/en/_app/immutable/entry/app.e92a3d99.js") | |
| ]).then(([kit, app]) => { | |
| kit.start(app, element, { | |
| node_ids: [0, 6], | |
| data, | |
| form: null, | |
| error: null | |
| }); | |
| }); | |
| } | |
| </script> | |
Xet Storage Details
- Size:
- 35.7 kB
- Xet hash:
- 45fb3bca304716d95d46015563e31edb1043d1eea9724714d8e2624972701571
·
Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.