# SQL Agent with Cohere and LangChain (i-5O Case Study)

> This page contains a tutorial on how to build a SQL agent with Cohere and LangChain in the manufacturing industry.

<CookbookHeader href="https://github.com/cohere-ai/cohere-developer-experience/blob/main/notebooks/agents/i-5O-sql-agent/sql_agent_demo.ipynb" />

This tutorial demonstrates how to create a SQL agent using Cohere and LangChain. The agent can translate natural language queries coming from users into SQL, and execute them against a database. This powerful combination allows for intuitive interaction with databases without requiring direct SQL knowledge.

Key topics covered:

1. Setting up the necessary libraries and environment
2. Connecting to a SQLite database
3. Configuring the LangChain SQL Toolkit
4. Creating a custom prompt template with few-shot examples
5. Building and running the SQL agent
6. Adding memory to the agent to keep track of historical messages

By the end of this tutorial, you'll have a functional SQL agent that can answer questions about your data using natural language.

This tutorial uses a mocked up data of a manufacturing environment where a product item's production is tracked across multiple stations, allowing for analysis of production efficiency, station performance, and individual item progress through the manufacturing process. This is modelled after a real customer use case.

The database contains two tables:

* The `product_tracking` table records the movement of items through different zones in manufacturing stations, including start and end times, station names, and product IDs.
* The `status` table logs the operational status of stations, including timestamps, station names, and whether they are productive or in downtime.

## Import the required libraries

First, let's import the necessary libraries for creating a SQL agent using Cohere and LangChain. These libraries enable natural language interaction with databases and provide tools for building AI-powered agents.

```python PYTHON
import os

os.environ["COHERE_API_KEY"] = "<cohere-api-key>"
```

```python PYTHON
! pip install langchain-core langchain-cohere langchain-community faiss-cpu -qq
```

```python PYTHON
from langchain_cohere import create_sql_agent
from langchain_cohere.chat_models import ChatCohere
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_community.vectorstores import FAISS
from langchain_core.example_selectors import SemanticSimilarityExampleSelector
from langchain_cohere import CohereEmbeddings
from datetime import datetime
```

## Load the database

Next, we load the database for our manufacturing data.

### Download the sql files from the link below to create the database.

We create an in-memory SQLite database using SQL scripts for the `product_tracking` and `status` tables. You can get the [SQL tables here](https://github.com/cohere-ai/notebooks/tree/main/notebooks/agents/i-5O-sql-agent).

We then create a SQLDatabase instance, which will be used by our LangChain tools and agents to interact with the data.

```python PYTHON
import sqlite3

from langchain_community.utilities.sql_database import SQLDatabase
from sqlalchemy import create_engine
from sqlalchemy.pool import StaticPool

def get_engine_for_manufacturing_db():
    """Create an in-memory database with the manufacturing data tables."""
    connection = sqlite3.connect(":memory:", check_same_thread=False)

    # Read and execute the SQL scripts
    for sql_file in ['product_tracking.sql', 'status.sql']:
        with open(sql_file, 'r') as file:
            sql_script = file.read()
            connection.executescript(sql_script)

    return create_engine(
        "sqlite://",
        creator=lambda: connection,
        poolclass=StaticPool,
        connect_args={"check_same_thread": False},
    )