{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f18f2a0a-92eb-4e91-939a-099daac1921e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from dotenv import load_dotenv, find_dotenv\n",
    "_ = load_dotenv(find_dotenv())\n",
    "openai_api_key = os.environ[\"OPENAI_API_KEY\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdbb8999-b8b9-4d88-8a75-4d862c3f412b",
   "metadata": {},
   "source": [
    "## Basic app for querying data from a database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1a412d51-f561-433d-ba3e-969751e2a744",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_openai import OpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "19eebe57-6844-409d-9287-1855f20c33b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = OpenAI()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03480af2-e3af-4d12-9a79-e065b0932828",
   "metadata": {},
   "source": [
    "**Load SQLite database**\n",
    "<br>\n",
    "We have created a short vesion (with only 1000 rows) of de database [San Francisco Trees.](https://data.sfgov.org/City-Infrastructure/Street-Tree-List/tkzw-k3nq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6b85a593-2e94-446d-8afa-02c5bb497074",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install langchain_experimental"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c1dfa691-85be-4afb-b9d9-6327cc566cf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain import SQLDatabase\n",
    "from langchain_experimental.sql import SQLDatabaseChain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2f1756ed-fc56-4be9-b54a-6ae7e4429122",
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlite_db_path = \"data/street_tree_db.sqlite\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1093f4f4-6fae-441d-a271-e744187b79bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = SQLDatabase.from_uri(f\"sqlite:///{sqlite_db_path}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "601d943c-6d9d-49ae-95ce-9d6d6e295cf4",
   "metadata": {},
   "source": [
    "**Create a chain with de LLM and the database**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "37252e97-8d08-4908-a4fc-f33da804a15f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/juliocolomer/.pyenv/versions/3.11.4/envs/venv020124/lib/python3.11/site-packages/langchain_experimental/sql/base.py:76: UserWarning: Directly instantiating an SQLDatabaseChain with an llm is deprecated. Please instantiate with llm_chain argument or using the from_llm class method.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "db_chain = SQLDatabaseChain(\n",
    "    llm=llm,\n",
    "    database=db,\n",
    "    verbose=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b117b515-1b0f-452f-ae00-e93da12662f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/juliocolomer/.pyenv/versions/3.11.4/envs/venv020124/lib/python3.11/site-packages/langchain_core/_api/deprecation.py:117: LangChainDeprecationWarning: The function `run` was deprecated in LangChain 0.1.0 and will be removed in 0.2.0. Use invoke instead.\n",
      "  warn_deprecated(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many species of trees are in San Francisco?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(DISTINCT qSpecies) FROM street_trees\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(148,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3m148\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'148'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_chain.run(\"How many species of trees are in San Francisco?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8e6bb70d-f755-4537-a9a1-7ed0cf20d86f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many trees of the species Ficus nitida are there in San Francisco?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM street_trees WHERE qSpecies = 'Ficus nitida'\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(0,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3m0\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'0'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_chain.run(\"How many trees of the species Ficus nitida are there in San Francisco?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7d43afd-65c7-4d70-a096-e94fb3282d3e",
   "metadata": {},
   "source": [
    "**Both answers are correct for the 1,000 rows database we created.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6439c2df-2895-4f13-ab3d-43d02896643e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}