ChatGPT With SQL Database using LangChain
Published: 2023-10-05 13:10:46

<div class="col-lg-8 col-xl-8 blog-content-col">
					
<h2>Introduction to LangChain</h2>



<p><a href="https://blog.nextideatech.com/chat-with-documents-using-langchain-gpt-4-python/" class="ek-link">LangChain</a> is a revolutionary technology that serves as a bridge between natural language processing (NLP), ChatGPT and databases. It allows users to interact with their databases using natural language, making it easier to retrieve, manipulate, and manage data without the need for intricate SQL queries. LangChain leverages advanced NLP techniques to understand user queries and translate them into SQL, enabling seamless communication with databases.</p>


<h2>Why LangChain?</h2>



<p>As we mentioned in <a href="https://blog.nextideatech.com/chat-with-documents-using-langchain-gpt-4-python/" class="ek-link">this </a>article, LangChain&nbsp;fills a crucial need in the realm of Large Language Models (LLMs). While LLMs excel at a wide range of tasks, they may fall short when it comes to providing specific answers or deep domain expertise. To address this limitation, LangChain adds data-awareness and agentic capabilities to LLMs, allowing applications to connect to external data sources and engage in interactive, context-aware interactions. This integration empowers developers to build more powerful and specialized language model applications that can provide targeted and nuanced responses, bridging the gap between LLMs and domain-specific knowledge requirements.</p>



<h2>How LangChain Works with SQL Databases</h2>



<p>LangChain can be integrated with SQL databases to facilitate natural language interactions. Users can ask questions or make requests in plain English, and LangChain translates these into the corresponding SQL queries. This interaction enables users, even those without technical expertise in SQL, to access and manage their database information efficiently.</p>



<h2>Step 1: Installing Dependencies</h2>



<p>Start by creating and activating a virtual environment. Execute the following commands:</p>


<pre class="wp-block-code has-ek-typography language-bash" style="" tabindex="0"><code lang="bash" class="language-bash">python -m venv venv
<span class="token builtin class-name">source</span> venv/bin/activate <span class="token comment"># for Ubuntu</span>
venv/Scripts/activate <span class="token comment"># for Windows</span></code></pre>


<p>Once the virtual environment is activated, install the necessary packages:</p>


<pre class="wp-block-code has-ek-typography language-bash" style="" tabindex="0"><code lang="bash" class="language-bash">pip <span class="token function">install</span> langchain langchain-experimental openai</code></pre>


<h2>Step 2: OpenAI API Key</h2>



<p>LangChain is compatible with <a aria-label=" (opens in a new tab)" href="https://python.langchain.com/docs/modules/model_io/models/llms/" target="_blank" rel="noreferrer noopener" class="ek-link">several LLMs</a>, but using OpenAI models often yields better results. </p>



<p>To use LangChain with OpenAI models, you need an OpenAI key. Follow the steps below to create a new OpenAI key:</p>



<ol><li>Visit <a href="https://platform.openai.com/">platform.openai.com</a>.</li><li>Click on your name or icon, located on the top right corner, and select “View API Keys” or follow this <a href="https://platform.openai.com/account/api-keys" class="ek-link">link</a>.</li><li>Click on the “Create new secret key” button to generate a new OpenAI key.</li><li>We will use the API key in the next segment where we will write chat function.</li></ol>



<h2>Step 3: Creating a Python Script with LangChain + ChatGPT</h2>



<p>Create a new Python file named <code>langchain_sql_chat.py</code> and add the following code to it:</p>



<pre class="wp-block-code language-python" tabindex="0"><code lang="python" class="language-python"><span class="token keyword">import</span> os
<span class="token keyword">from</span> langchain<span class="token punctuation">.</span>utilities <span class="token keyword">import</span> SQLDatabase
<span class="token keyword">from</span> langchain<span class="token punctuation">.</span>llms <span class="token keyword">import</span> OpenAI
<span class="token keyword">from</span> langchain_experimental<span class="token punctuation">.</span>sql <span class="token keyword">import</span> SQLDatabaseChain

os<span class="token punctuation">.</span>environ<span class="token punctuation">[</span><span class="token string">'OPENAI_API_KEY'</span><span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token string">'OPENAI API KEY from Step 2'</span>

db <span class="token operator">=</span> SQLDatabase<span class="token punctuation">.</span>from_uri<span class="token punctuation">(</span><span class="token string">"mysql://username:password@hostname/database_name"</span><span class="token punctuation">)</span>

llm <span class="token operator">=</span> OpenAI<span class="token punctuation">(</span>temperature<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">,</span> verbose<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
db_chain <span class="token operator">=</span> SQLDatabaseChain<span class="token punctuation">.</span>from_llm<span class="token punctuation">(</span>llm<span class="token punctuation">,</span> db<span class="token punctuation">,</span> verbose<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>

<span class="token keyword">def</span> <span class="token function">chat_with_sql</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
    <span class="token keyword">print</span><span class="token punctuation">(</span><span class="token string">"Type 'exit' to quit"</span><span class="token punctuation">)</span>

    <span class="token keyword">while</span> <span class="token boolean">True</span><span class="token punctuation">:</span>
        prompt <span class="token operator">=</span> <span class="token builtin">input</span><span class="token punctuation">(</span><span class="token string">"Enter a prompt: "</span><span class="token punctuation">)</span>

        <span class="token keyword">if</span> prompt<span class="token punctuation">.</span>lower<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">==</span> <span class="token string">'exit'</span><span class="token punctuation">:</span>
            <span class="token keyword">print</span><span class="token punctuation">(</span><span class="token string">'Exiting...'</span><span class="token punctuation">)</span>
            <span class="token keyword">break</span>
        <span class="token keyword">else</span><span class="token punctuation">:</span>
            <span class="token keyword">try</span><span class="token punctuation">:</span>

                <span class="token keyword">print</span><span class="token punctuation">(</span>db_chain<span class="token punctuation">.</span>run<span class="token punctuation">(</span>prompt<span class="token punctuation">)</span><span class="token punctuation">)</span>
            <span class="token keyword">except</span> Exception <span class="token keyword">as</span> e<span class="token punctuation">:</span>
                <span class="token keyword">print</span><span class="token punctuation">(</span>e<span class="token punctuation">)</span>

chat_with_sql<span class="token punctuation">(</span><span class="token punctuation">)</span></code></pre>



<p>The above code connects to a SQL database defined by connection string <em>mysql://username:password@hostname/database_name</em></p>



<p>Ensure that you replace <code>username</code>, <code>password</code>, <code>hostname</code>, and <code>database_name</code> with your actual database credentials and details.</p>



<h2>The SQL Database</h2>



<p>For the purpose of this article, we are using an inventory database, which is a structured repository designed to manage various aspects of stock levels in a retail environment. This database contains several tables, each serving a specific purpose in inventory management. The <strong><code>products</code> </strong>table holds essential information about the items, including product ID, name, description, and price.</p>



<p><em>products.sql</em></p>



<pre class="wp-block-code language-sql" tabindex="0"><code lang="sql" class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> products <span class="token punctuation">(</span>
    product_id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    product_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    description <span class="token keyword">TEXT</span><span class="token punctuation">,</span>
    price <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">CHECK</span> <span class="token punctuation">(</span>price <span class="token operator">&gt;=</span> <span class="token number">0</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre>



<p><em>suppliers.sql</em></p>



<pre class="wp-block-code language-sql" tabindex="0"><code lang="sql" class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> suppliers <span class="token punctuation">(</span>
    supplier_id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    supplier_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    contact_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    contact_email <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    contact_phone <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre>



<p><em>inventory.sql</em></p>



<pre class="wp-block-code language-sql" tabindex="0"><code lang="sql" class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> inventory <span class="token punctuation">(</span>
    inventory_id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    product_id <span class="token keyword">INT</span><span class="token punctuation">,</span>
    supplier_id <span class="token keyword">INT</span><span class="token punctuation">,</span>
    quantity_in_stock <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">CHECK</span> <span class="token punctuation">(</span>quantity_in_stock <span class="token operator">&gt;=</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    last_stocked_date <span class="token keyword">DATE</span><span class="token punctuation">,</span>
    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>product_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> products<span class="token punctuation">(</span>product_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>supplier_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> suppliers<span class="token punctuation">(</span>supplier_id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre>



<h2>Chat with your data using LangChain + ChatGPT</h2>



<p>Now that we have our code ready, run the function using:</p>



<pre class="wp-block-code language-bash" tabindex="0"><code lang="bash" class="language-bash">
python langchain_sql_chat.py</code></pre>



<p>The following prompt should appear</p>



<pre class="wp-block-code language-bash" tabindex="0"><code lang="bash" class="language-bash">Enter a prompt:</code></pre>



<p>To query the database, ask questions such as “<em>How many units of Product X are in stock</em>“</p>



<p>You should see the following:</p>



<pre class="wp-block-code language-bash" tabindex="0"><code lang="bash" class="language-bash">Type <span class="token string">'exit'</span> to quit
<span class="token operator">&gt;</span> Entering new SQLDatabaseChain chain<span class="token punctuation">..</span>.
    How many <span class="token function">units</span> of Product X are <span class="token keyword">in</span> stock?
    SQLQuery:SELECT quantity_in_stock FROM <span class="token string">"inventory"</span> WHERE product_id <span class="token operator">=</span> <span class="token punctuation">(</span>SELECT product_id FROM <span class="token string">"products"</span> WHERE product_name <span class="token operator">=</span> <span class="token string">'Product X'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    SQLResult: <span class="token punctuation">[</span><span class="token punctuation">(</span><span class="token number">15</span>,<span class="token punctuation">)</span><span class="token punctuation">]</span>
    Answer:There are <span class="token number">15</span> <span class="token function">units</span> of Product X <span class="token keyword">in</span> stock.
    <span class="token operator">&gt;</span> Finished chain.

</code></pre>



<p><strong>More complex questions</strong></p>



<p>Let’s ask a question that requires a JOIN statement.</p>



<pre class="wp-block-code language-bash" tabindex="0"><code lang="bash" class="language-bash">Type <span class="token string">'exit'</span> to quit
<span class="token operator">&gt;</span> Entering new SQLDatabaseChain chain<span class="token punctuation">..</span>.
    List all products with <span class="token function">less</span> than <span class="token number">10</span> <span class="token function">units</span> <span class="token keyword">in</span> stock.
    SQLQuery:SELECT product_name FROM <span class="token string">"products"</span> INNER JOIN <span class="token string">"inventory"</span> ON products.product_id <span class="token operator">=</span> inventory.product_id WHERE quantity_in_stock <span class="token operator">&lt;</span> <span class="token number">10</span><span class="token punctuation">;</span>
    SQLResult: <span class="token punctuation">[</span><span class="token punctuation">(</span><span class="token string">'Product A'</span>,<span class="token punctuation">)</span>, <span class="token punctuation">(</span><span class="token string">'Product B'</span>,<span class="token punctuation">)</span>, <span class="token punctuation">(</span><span class="token string">'Product C'</span>,<span class="token punctuation">)</span><span class="token punctuation">]</span>
    Answer:Products with <span class="token function">less</span> than <span class="token number">10</span> <span class="token function">units</span> <span class="token keyword">in</span> stock are Product A, Product B, and Product C.
    <span class="token operator">&gt;</span> Finished chain.
</code></pre>



<p>LangChain was able to deduce that a “JOIN” statement is required in order to satisfy the prompt.</p>



<h2>Conclusion</h2>



<p>This guide serves as a foundational step to get started with LangChain and Database implementations, enabling you to interact conversationally with your SQL database. By following the outlined steps, you can leverage the power of Langchain to make database interactions more user-friendly and efficient. </p>



<p>Whether you are managing an inventory database or any other type of database, LangChain, combined with GPT, translates your natural language inquiries into precise SQL queries, allowing for seamless communication with your database.</p>



<p>Embark on your software development adventure with <a href="https://nextideatech.com" class="ek-link">Next Idea Tech</a>! As a distinguished software development and nearshore outsourcing firm, we deliver a plethora of business solutions tailored to your needs. We are here to connect you with the cream of the crop in talent for your projects. Our mission is to pair you with seasoned professionals who are experts in their fields. We boast a team of experienced developers, testers, project managers, and marketing specialists, all poised to contribute to your success!</p>



<p><a href="https://nextideatech.com/contact" class="ek-link">Let’s discuss</a> your business aspirations! We extend complimentary consultations to understand your project better. </p>
					<!-- <section class="cta-card">
					  <div class="cta-card__container">
					    <div class="cta-card__header">
					      <p class="cta-card__title">Tell us the skills you need and we'll find the best developer for you in days, not weeks.</p>
					      <a href="https://nextideatech.com/contact/">Hire Developers</a>
					    </div>
					  </div>
					</section> -->

					<!-- subscribe form -->
					<div id="mc_embed_signup" class="subscribe-form">
						<form action="https://nextideatech.us7.list-manage.com/subscribe/post?u=ae45185df605e4c3b1371d09f&amp;id=22c96b1894&amp;f_id=0004c5e4f0" method="post" id="mc-embedded-subscribe-form" name="mc-embedded-subscribe-form" class="validate" target="_blank" novalidate="novalidate">
							<div id="mc_embed_signup_scroll">
								<h4>Subscribe to our newsletter and get<br> exclusive content</h4>
								<div class="mc-field-group">
									<input type="email" value="" name="EMAIL" class="required email" placeholder="Email Address" id="mce-EMAIL" aria-required="true">
									<span id="mce-EMAIL-HELPERTEXT" class="helper_text"></span>
									<input type="submit" value="Subscribe" name="subscribe" id="mc-embedded-subscribe" class="button">
								</div>
								<div id="mce-responses" class="clear foot">
									<div class="response" id="mce-error-response" style="display:none"></div>
									<div class="response" id="mce-success-response" style="display:none"></div>
								</div>
						    <div style="position: absolute; left: -5000px;" aria-hidden="true"><input type="text" name="b_ae45185df605e4c3b1371d09f_22c96b1894" tabindex="-1" value=""></div>
						   </div>
						</form>
					</div>
					<script type="text/javascript" src="//s3.amazonaws.com/downloads.mailchimp.com/js/mc-validate.js"></script>
					<script type="text/javascript">(function($) {window.fnames = new Array(); window.ftypes = new Array();fnames[0]='EMAIL';ftypes[0]='email';fnames[1]='FNAME';ftypes[1]='text';fnames[2]='LNAME';ftypes[2]='text';fnames[3]='ADDRESS';ftypes[3]='address';fnames[4]='PHONE';ftypes[4]='phone';}(jQuery));var $mcj = jQuery.noConflict(true);</script>

				</div>