MontyBot.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "n2EJ6aRxuklX",
    "outputId": "958e1f32-0cda-4e81-b8ab-b68f3072aa4f"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: langchain in ./env/lib/python3.9/site-packages (0.0.234)\n",
      "Requirement already satisfied: PyYAML>=5.4.1 in ./env/lib/python3.9/site-packages (from langchain) (6.0)\n",
      "Requirement already satisfied: SQLAlchemy<3,>=1.4 in ./env/lib/python3.9/site-packages (from langchain) (2.0.19)\n",
      "Requirement already satisfied: aiohttp<4.0.0,>=3.8.3 in ./env/lib/python3.9/site-packages (from langchain) (3.8.4)\n",
      "Requirement already satisfied: async-timeout<5.0.0,>=4.0.0 in ./env/lib/python3.9/site-packages (from langchain) (4.0.2)\n",
      "Requirement already satisfied: dataclasses-json<0.6.0,>=0.5.7 in ./env/lib/python3.9/site-packages (from langchain) (0.5.9)\n",
      "Requirement already satisfied: langsmith<0.0.6,>=0.0.5 in ./env/lib/python3.9/site-packages (from langchain) (0.0.5)\n",
      "Requirement already satisfied: numexpr<3.0.0,>=2.8.4 in ./env/lib/python3.9/site-packages (from langchain) (2.8.4)\n",
      "Requirement already satisfied: numpy<2,>=1 in ./env/lib/python3.9/site-packages (from langchain) (1.25.1)\n",
      "Requirement already satisfied: openapi-schema-pydantic<2.0,>=1.2 in ./env/lib/python3.9/site-packages (from langchain) (1.2.4)\n",
      "Requirement already satisfied: pydantic<2,>=1 in ./env/lib/python3.9/site-packages (from langchain) (1.10.11)\n",
      "Requirement already satisfied: requests<3,>=2 in ./env/lib/python3.9/site-packages (from langchain) (2.31.0)\n",
      "Requirement already satisfied: tenacity<9.0.0,>=8.1.0 in ./env/lib/python3.9/site-packages (from langchain) (8.2.2)\n",
      "Requirement already satisfied: attrs>=17.3.0 in ./env/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (23.1.0)\n",
      "Requirement already satisfied: charset-normalizer<4.0,>=2.0 in ./env/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (3.2.0)\n",
      "Requirement already satisfied: multidict<7.0,>=4.5 in ./env/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (6.0.4)\n",
      "Requirement already satisfied: yarl<2.0,>=1.0 in ./env/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (1.9.2)\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in ./env/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (1.4.0)\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in ./env/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (1.3.1)\n",
      "Requirement already satisfied: marshmallow<4.0.0,>=3.3.0 in ./env/lib/python3.9/site-packages (from dataclasses-json<0.6.0,>=0.5.7->langchain) (3.19.0)\n",
      "Requirement already satisfied: marshmallow-enum<2.0.0,>=1.5.1 in ./env/lib/python3.9/site-packages (from dataclasses-json<0.6.0,>=0.5.7->langchain) (1.5.1)\n",
      "Requirement already satisfied: typing-inspect>=0.4.0 in ./env/lib/python3.9/site-packages (from dataclasses-json<0.6.0,>=0.5.7->langchain) (0.9.0)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in ./env/lib/python3.9/site-packages (from pydantic<2,>=1->langchain) (4.7.1)\n",
      "Requirement already satisfied: idna<4,>=2.5 in ./env/lib/python3.9/site-packages (from requests<3,>=2->langchain) (3.4)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in ./env/lib/python3.9/site-packages (from requests<3,>=2->langchain) (2.0.3)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in ./env/lib/python3.9/site-packages (from requests<3,>=2->langchain) (2023.5.7)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in ./env/lib/python3.9/site-packages (from SQLAlchemy<3,>=1.4->langchain) (2.0.2)\n",
      "Requirement already satisfied: packaging>=17.0 in ./env/lib/python3.9/site-packages (from marshmallow<4.0.0,>=3.3.0->dataclasses-json<0.6.0,>=0.5.7->langchain) (23.1)\n",
      "Requirement already satisfied: mypy-extensions>=0.3.0 in ./env/lib/python3.9/site-packages (from typing-inspect>=0.4.0->dataclasses-json<0.6.0,>=0.5.7->langchain) (1.0.0)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.1.2\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.2\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n",
      "Requirement already satisfied: openai in ./env/lib/python3.9/site-packages (0.27.8)\n",
      "Requirement already satisfied: requests>=2.20 in ./env/lib/python3.9/site-packages (from openai) (2.31.0)\n",
      "Requirement already satisfied: tqdm in ./env/lib/python3.9/site-packages (from openai) (4.65.0)\n",
      "Requirement already satisfied: aiohttp in ./env/lib/python3.9/site-packages (from openai) (3.8.4)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in ./env/lib/python3.9/site-packages (from requests>=2.20->openai) (3.2.0)\n",
      "Requirement already satisfied: idna<4,>=2.5 in ./env/lib/python3.9/site-packages (from requests>=2.20->openai) (3.4)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in ./env/lib/python3.9/site-packages (from requests>=2.20->openai) (2.0.3)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in ./env/lib/python3.9/site-packages (from requests>=2.20->openai) (2023.5.7)\n",
      "Requirement already satisfied: attrs>=17.3.0 in ./env/lib/python3.9/site-packages (from aiohttp->openai) (23.1.0)\n",
      "Requirement already satisfied: multidict<7.0,>=4.5 in ./env/lib/python3.9/site-packages (from aiohttp->openai) (6.0.4)\n",
      "Requirement already satisfied: async-timeout<5.0,>=4.0.0a3 in ./env/lib/python3.9/site-packages (from aiohttp->openai) (4.0.2)\n",
      "Requirement already satisfied: yarl<2.0,>=1.0 in ./env/lib/python3.9/site-packages (from aiohttp->openai) (1.9.2)\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in ./env/lib/python3.9/site-packages (from aiohttp->openai) (1.4.0)\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in ./env/lib/python3.9/site-packages (from aiohttp->openai) (1.3.1)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.1.2\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.2\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n",
      "Requirement already satisfied: python-dotenv in ./env/lib/python3.9/site-packages (1.0.0)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.1.2\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.2\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install langchain\n",
    "!pip install openai\n",
    "!pip install python-dotenv\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "5XQ8mxDKLeVO"
   },
   "outputs": [],
   "source": [
    "from langchain import OpenAI, SQLDatabase, SQLDatabaseChain\n",
    "import openai\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "cellView": "form",
    "id": "9tkc4n4GdT_s"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from dotenv import load_dotenv\n",
    "import os\n",
    "load_dotenv()\n",
    "# print(os.environ.get('OPENAI_API_KEY'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "id": "GkocMFz-uuFt"
   },
   "outputs": [],
   "source": [
    "llm = OpenAI(temperature=0, verbose=True, openai_api_key=os.environ.get('OPENAI_API_KEY'), model='text-davinci-003')\n",
    "db = SQLDatabase.from_uri(\"sqlite:///daytonprops.db\")\n",
    "\n",
    "db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, use_query_checker=True, return_intermediate_steps=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "9NuPJfGHbbrU",
    "outputId": "8a79e5fd-cea7-4678-95b9-4dbb6eace4dd",
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many parcels are in the file?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM daytonprops;\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(71157,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThere are 71157 parcels in the file.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "result = db_chain(\"How many parcels are in the file?\")\n",
    "# print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "-3QlbBM5kAEu",
    "outputId": "388e6728-7353-43eb-f6a5-01e3e12e8482"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "what are the addresses of the 5 largest properties owned by the city?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT PADDR1, PADDR2, PADDR3, ACRES \n",
      "FROM daytonprops \n",
      "WHERE OWNERNAME1 = 'DAYTON CITY OF' \n",
      "AND ACRES IS NOT NULL \n",
      "AND ACRES BETWEEN 0 AND 99999 \n",
      "ORDER BY ACRES DESC \n",
      "LIMIT 5;\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('3600 TERMINAL DR STE 300', None, 'VANDALIA OH 45377', 879.2031), ('MUNICIPAL BG', None, 'DAYTON OH 45402', 694.565), ('101 W 3RD ST', None, 'DAYTON OH 45402 1814', 369.013), ('MUNICIPAL BG', None, 'DAYTON OH 45402', 326.38), ('101 W 3RD ST', None, 'DAYTON OH 45402 1814', 100.926)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThe 5 largest properties owned by the city are located at 3600 Terminal Dr Ste 300, Vandalia OH 45377 (879.2031 acres), Municipal Bg, Dayton OH 45402 (694.565 acres), 101 W 3rd St, Dayton OH 45402 1814 (369.013 acres), Municipal Bg, Dayton OH 45402 (326.38 acres), and 101 W 3rd St, Dayton OH 45402 1814 (100.926 acres).\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "result = db_chain(\"what are the addresses of the 5 largest properties owned by the city?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "obTEK12AmaRR",
    "outputId": "f0fa9041-3983-4820-e982-6927f61aa359"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'query': 'what are the addresses of the 5 largest properties owned by the city?',\n",
       " 'result': 'The 5 largest properties owned by the city are located at 3600 Terminal Dr Ste 300, Vandalia OH 45377 (879.2031 acres), Municipal BG, Dayton OH 45402 (694.565 acres), 101 W 3rd St, Dayton OH 45402 1814 (369.013 acres), Municipal BG, Dayton OH 45402 (326.38 acres), and 101 W 3rd St, Dayton OH 45402 1814 (100.926 acres).',\n",
       " 'intermediate_steps': [{'input': 'what are the addresses of the 5 largest properties owned by the city?\\nSQLQuery:SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;\\nSQLResult: [(\\'3600 TERMINAL DR STE 300\\', None, \\'VANDALIA OH 45377\\', 879.2031), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 694.565), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 369.013), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 326.38), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 100.926)]\\nAnswer:',\n",
       "   'top_k': '5',\n",
       "   'dialect': 'sqlite',\n",
       "   'table_info': '\\nCREATE TABLE daytonprops (\\n\\t\"PARCELID\" TEXT, \\n\\t\"FULLYRAMTDUE\" REAL, \\n\\t\"NETDELQ\" REAL, \\n\\t\"PARCELLOCATION\" TEXT, \\n\\t\"LEGAL1\" TEXT, \\n\\t\"LEGAL2\" TEXT, \\n\\t\"LEGAL3\" TEXT, \\n\\t\"ACRES\" REAL, \\n\\t\"TXDST\" TEXT, \\n\\t\"CLS\" TEXT, \\n\\t\"LUC\" INTEGER, \\n\\t\"AGLAND\" INTEGER, \\n\\t\"ASMTLAND\" INTEGER, \\n\\t\"ASMTBLDG\" INTEGER, \\n\\t\"ASMTTOTAL\" INTEGER, \\n\\t\"TAXABLELAND\" INTEGER, \\n\\t\"TAXABLEBLDG\" INTEGER, \\n\\t\"PUBLICUTILITY\" INTEGER, \\n\\t\"ROLLTYPE\" TEXT, \\n\\t\"TAXABLETOTAL\" INTEGER, \\n\\t\"OWNERNAME1\" TEXT, \\n\\t\"OWNERNAME2\" TEXT, \\n\\t\"MAILINGNAME1\" TEXT, \\n\\t\"MAILINGNAME2\" TEXT, \\n\\t\"PADDR1\" TEXT, \\n\\t\"PADDR2\" TEXT, \\n\\t\"PADDR3\" TEXT, \\n\\t\"MORTCO\" INTEGER, \\n\\t\"NBHD\" TEXT, \\n\\t\"ASMTWEN\" TEXT, \\n\\t\"AC\" TEXT, \\n\\t\"HMSDLAND\" INTEGER, \\n\\t\"HMSDBLDG\" INTEGER, \\n\\t\"FRCLSR\" TEXT, \\n\\t\"SALESDTE\" TEXT, \\n\\t\"PRICE\" INTEGER, \\n\\t\"YRBL\" INTEGER, \\n\\t\"GROSSRATE\" REAL, \\n\\t\"EFFRATE\" REAL, \\n\\t\"REDRATE\" REAL, \\n\\t\"DUEDATE\" TEXT, \\n\\t\"B\" INTEGER, \\n\\t\"HLF1DELQ\" REAL, \\n\\t\"RENTALREG\" TEXT, \\n\\t\"AETASMTLAND\" INTEGER, \\n\\t\"AETASMTBLDG\" INTEGER, \\n\\t\"AETASMTTOTAL\" INTEGER, \\n\\t\"AETTAXABLELAND\" INTEGER, \\n\\t\"AETTAXABLEBLDG\" INTEGER, \\n\\t\"AETTAXABLETOTAL\" INTEGER, \\n\\t\"CASMTLAND\" INTEGER, \\n\\t\"CTAXABLELAND\" INTEGER, \\n\\t\"HMSDFLAG\" TEXT, \\n\\t\"SQ_FT\" INTEGER, \\n\\t\"DYTNCRDT\" TEXT, \\n\\t\"OWNOCC\" TEXT, \\n\\t\"CITY_TOWNSHIP\" INTEGER, \\n\\t\"SCHOOL_DISTRICT\" INTEGER, \\n\\t\"PARCEL_LOCATION_ZIP\" INTEGER, \\n\\t\"CENSUS_TRACT\" TEXT, \\n\\tlat REAL, \\n\\tlon REAL\\n)\\n\\n/*\\n3 rows from daytonprops table:\\nPARCELID\\tFULLYRAMTDUE\\tNETDELQ\\tPARCELLOCATION\\tLEGAL1\\tLEGAL2\\tLEGAL3\\tACRES\\tTXDST\\tCLS\\tLUC\\tAGLAND\\tASMTLAND\\tASMTBLDG\\tASMTTOTAL\\tTAXABLELAND\\tTAXABLEBLDG\\tPUBLICUTILITY\\tROLLTYPE\\tTAXABLETOTAL\\tOWNERNAME1\\tOWNERNAME2\\tMAILINGNAME1\\tMAILINGNAME2\\tPADDR1\\tPADDR2\\tPADDR3\\tMORTCO\\tNBHD\\tASMTWEN\\tAC\\tHMSDLAND\\tHMSDBLDG\\tFRCLSR\\tSALESDTE\\tPRICE\\tYRBL\\tGROSSRATE\\tEFFRATE\\tREDRATE\\tDUEDATE\\tB\\tHLF1DELQ\\tRENTALREG\\tAETASMTLAND\\tAETASMTBLDG\\tAETASMTTOTAL\\tAETTAXABLELAND\\tAETTAXABLEBLDG\\tAETTAXABLETOTAL\\tCASMTLAND\\tCTAXABLELAND\\tHMSDFLAG\\tSQ_FT\\tDYTNCRDT\\tOWNOCC\\tCITY_TOWNSHIP\\tSCHOOL_DISTRICT\\tPARCEL_LOCATION_ZIP\\tCENSUS_TRACT\\tlat\\tlon\\nR72 00101 0001\\t0.0\\t0.0\\tROBERT DR\\t126 PT RIVERBANK\\t1E(.008) 1E1(.007)\\tEXEMPT\\t1.146\\tR72\\tE\\t640\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tDAYTON CITY OF\\tNone\\tCITY OF DAYTON\\tNone\\t101 W 3RD ST\\tNone\\tDAYTON OH 45401\\t3001\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\t02-JAN-03\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.757987\\t-84.203876\\nR72 00101 0002\\t0.0\\t0.0\\tROBERT DR\\t126 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t670\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.758832\\t-84.204194\\nR72 00101 0003\\t0.0\\t0.0\\tROBERT DR\\t125 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t665\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\tNone\\t39113-001501-1\\t39.759657\\t-84.204154\\n*/',\n",
       "   'stop': ['\\nSQLResult:']},\n",
       "  'SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;',\n",
       "  {'sql_cmd': 'SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;'},\n",
       "  \"[('3600 TERMINAL DR STE 300', None, 'VANDALIA OH 45377', 879.2031), ('MUNICIPAL BG', None, 'DAYTON OH 45402', 694.565), ('101 W 3RD ST', None, 'DAYTON OH 45402 1814', 369.013), ('MUNICIPAL BG', None, 'DAYTON OH 45402', 326.38), ('101 W 3RD ST', None, 'DAYTON OH 45402 1814', 100.926)]\",\n",
       "  {'input': 'what are the addresses of the 5 largest properties owned by the city?\\nSQLQuery:SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;\\nSQLResult: [(\\'3600 TERMINAL DR STE 300\\', None, \\'VANDALIA OH 45377\\', 879.2031), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 694.565), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 369.013), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 326.38), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 100.926)]\\nAnswer:',\n",
       "   'top_k': '5',\n",
       "   'dialect': 'sqlite',\n",
       "   'table_info': '\\nCREATE TABLE daytonprops (\\n\\t\"PARCELID\" TEXT, \\n\\t\"FULLYRAMTDUE\" REAL, \\n\\t\"NETDELQ\" REAL, \\n\\t\"PARCELLOCATION\" TEXT, \\n\\t\"LEGAL1\" TEXT, \\n\\t\"LEGAL2\" TEXT, \\n\\t\"LEGAL3\" TEXT, \\n\\t\"ACRES\" REAL, \\n\\t\"TXDST\" TEXT, \\n\\t\"CLS\" TEXT, \\n\\t\"LUC\" INTEGER, \\n\\t\"AGLAND\" INTEGER, \\n\\t\"ASMTLAND\" INTEGER, \\n\\t\"ASMTBLDG\" INTEGER, \\n\\t\"ASMTTOTAL\" INTEGER, \\n\\t\"TAXABLELAND\" INTEGER, \\n\\t\"TAXABLEBLDG\" INTEGER, \\n\\t\"PUBLICUTILITY\" INTEGER, \\n\\t\"ROLLTYPE\" TEXT, \\n\\t\"TAXABLETOTAL\" INTEGER, \\n\\t\"OWNERNAME1\" TEXT, \\n\\t\"OWNERNAME2\" TEXT, \\n\\t\"MAILINGNAME1\" TEXT, \\n\\t\"MAILINGNAME2\" TEXT, \\n\\t\"PADDR1\" TEXT, \\n\\t\"PADDR2\" TEXT, \\n\\t\"PADDR3\" TEXT, \\n\\t\"MORTCO\" INTEGER, \\n\\t\"NBHD\" TEXT, \\n\\t\"ASMTWEN\" TEXT, \\n\\t\"AC\" TEXT, \\n\\t\"HMSDLAND\" INTEGER, \\n\\t\"HMSDBLDG\" INTEGER, \\n\\t\"FRCLSR\" TEXT, \\n\\t\"SALESDTE\" TEXT, \\n\\t\"PRICE\" INTEGER, \\n\\t\"YRBL\" INTEGER, \\n\\t\"GROSSRATE\" REAL, \\n\\t\"EFFRATE\" REAL, \\n\\t\"REDRATE\" REAL, \\n\\t\"DUEDATE\" TEXT, \\n\\t\"B\" INTEGER, \\n\\t\"HLF1DELQ\" REAL, \\n\\t\"RENTALREG\" TEXT, \\n\\t\"AETASMTLAND\" INTEGER, \\n\\t\"AETASMTBLDG\" INTEGER, \\n\\t\"AETASMTTOTAL\" INTEGER, \\n\\t\"AETTAXABLELAND\" INTEGER, \\n\\t\"AETTAXABLEBLDG\" INTEGER, \\n\\t\"AETTAXABLETOTAL\" INTEGER, \\n\\t\"CASMTLAND\" INTEGER, \\n\\t\"CTAXABLELAND\" INTEGER, \\n\\t\"HMSDFLAG\" TEXT, \\n\\t\"SQ_FT\" INTEGER, \\n\\t\"DYTNCRDT\" TEXT, \\n\\t\"OWNOCC\" TEXT, \\n\\t\"CITY_TOWNSHIP\" INTEGER, \\n\\t\"SCHOOL_DISTRICT\" INTEGER, \\n\\t\"PARCEL_LOCATION_ZIP\" INTEGER, \\n\\t\"CENSUS_TRACT\" TEXT, \\n\\tlat REAL, \\n\\tlon REAL\\n)\\n\\n/*\\n3 rows from daytonprops table:\\nPARCELID\\tFULLYRAMTDUE\\tNETDELQ\\tPARCELLOCATION\\tLEGAL1\\tLEGAL2\\tLEGAL3\\tACRES\\tTXDST\\tCLS\\tLUC\\tAGLAND\\tASMTLAND\\tASMTBLDG\\tASMTTOTAL\\tTAXABLELAND\\tTAXABLEBLDG\\tPUBLICUTILITY\\tROLLTYPE\\tTAXABLETOTAL\\tOWNERNAME1\\tOWNERNAME2\\tMAILINGNAME1\\tMAILINGNAME2\\tPADDR1\\tPADDR2\\tPADDR3\\tMORTCO\\tNBHD\\tASMTWEN\\tAC\\tHMSDLAND\\tHMSDBLDG\\tFRCLSR\\tSALESDTE\\tPRICE\\tYRBL\\tGROSSRATE\\tEFFRATE\\tREDRATE\\tDUEDATE\\tB\\tHLF1DELQ\\tRENTALREG\\tAETASMTLAND\\tAETASMTBLDG\\tAETASMTTOTAL\\tAETTAXABLELAND\\tAETTAXABLEBLDG\\tAETTAXABLETOTAL\\tCASMTLAND\\tCTAXABLELAND\\tHMSDFLAG\\tSQ_FT\\tDYTNCRDT\\tOWNOCC\\tCITY_TOWNSHIP\\tSCHOOL_DISTRICT\\tPARCEL_LOCATION_ZIP\\tCENSUS_TRACT\\tlat\\tlon\\nR72 00101 0001\\t0.0\\t0.0\\tROBERT DR\\t126 PT RIVERBANK\\t1E(.008) 1E1(.007)\\tEXEMPT\\t1.146\\tR72\\tE\\t640\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tDAYTON CITY OF\\tNone\\tCITY OF DAYTON\\tNone\\t101 W 3RD ST\\tNone\\tDAYTON OH 45401\\t3001\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\t02-JAN-03\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.757987\\t-84.203876\\nR72 00101 0002\\t0.0\\t0.0\\tROBERT DR\\t126 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t670\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.758832\\t-84.204194\\nR72 00101 0003\\t0.0\\t0.0\\tROBERT DR\\t125 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t665\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\tNone\\t39113-001501-1\\t39.759657\\t-84.204154\\n*/',\n",
       "   'stop': ['\\nSQLResult:']},\n",
       "  'The 5 largest properties owned by the city are located at 3600 Terminal Dr Ste 300, Vandalia OH 45377 (879.2031 acres), Municipal BG, Dayton OH 45402 (694.565 acres), 101 W 3rd St, Dayton OH 45402 1814 (369.013 acres), Municipal BG, Dayton OH 45402 (326.38 acres), and 101 W 3rd St, Dayton OH 45402 1814 (100.926 acres).']}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "7FbSeM3q9Zk5",
    "outputId": "3b8ae66b-2086-49f1-ea8f-40323d43cb13"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0: {'input': 'what are the addresses of the 5 largest properties owned by the city?\\nSQLQuery:SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;\\nSQLResult: [(\\'3600 TERMINAL DR STE 300\\', None, \\'VANDALIA OH 45377\\', 879.2031), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 694.565), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 369.013), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 326.38), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 100.926)]\\nAnswer:', 'top_k': '5', 'dialect': 'sqlite', 'table_info': '\\nCREATE TABLE daytonprops (\\n\\t\"PARCELID\" TEXT, \\n\\t\"FULLYRAMTDUE\" REAL, \\n\\t\"NETDELQ\" REAL, \\n\\t\"PARCELLOCATION\" TEXT, \\n\\t\"LEGAL1\" TEXT, \\n\\t\"LEGAL2\" TEXT, \\n\\t\"LEGAL3\" TEXT, \\n\\t\"ACRES\" REAL, \\n\\t\"TXDST\" TEXT, \\n\\t\"CLS\" TEXT, \\n\\t\"LUC\" INTEGER, \\n\\t\"AGLAND\" INTEGER, \\n\\t\"ASMTLAND\" INTEGER, \\n\\t\"ASMTBLDG\" INTEGER, \\n\\t\"ASMTTOTAL\" INTEGER, \\n\\t\"TAXABLELAND\" INTEGER, \\n\\t\"TAXABLEBLDG\" INTEGER, \\n\\t\"PUBLICUTILITY\" INTEGER, \\n\\t\"ROLLTYPE\" TEXT, \\n\\t\"TAXABLETOTAL\" INTEGER, \\n\\t\"OWNERNAME1\" TEXT, \\n\\t\"OWNERNAME2\" TEXT, \\n\\t\"MAILINGNAME1\" TEXT, \\n\\t\"MAILINGNAME2\" TEXT, \\n\\t\"PADDR1\" TEXT, \\n\\t\"PADDR2\" TEXT, \\n\\t\"PADDR3\" TEXT, \\n\\t\"MORTCO\" INTEGER, \\n\\t\"NBHD\" TEXT, \\n\\t\"ASMTWEN\" TEXT, \\n\\t\"AC\" TEXT, \\n\\t\"HMSDLAND\" INTEGER, \\n\\t\"HMSDBLDG\" INTEGER, \\n\\t\"FRCLSR\" TEXT, \\n\\t\"SALESDTE\" TEXT, \\n\\t\"PRICE\" INTEGER, \\n\\t\"YRBL\" INTEGER, \\n\\t\"GROSSRATE\" REAL, \\n\\t\"EFFRATE\" REAL, \\n\\t\"REDRATE\" REAL, \\n\\t\"DUEDATE\" TEXT, \\n\\t\"B\" INTEGER, \\n\\t\"HLF1DELQ\" REAL, \\n\\t\"RENTALREG\" TEXT, \\n\\t\"AETASMTLAND\" INTEGER, \\n\\t\"AETASMTBLDG\" INTEGER, \\n\\t\"AETASMTTOTAL\" INTEGER, \\n\\t\"AETTAXABLELAND\" INTEGER, \\n\\t\"AETTAXABLEBLDG\" INTEGER, \\n\\t\"AETTAXABLETOTAL\" INTEGER, \\n\\t\"CASMTLAND\" INTEGER, \\n\\t\"CTAXABLELAND\" INTEGER, \\n\\t\"HMSDFLAG\" TEXT, \\n\\t\"SQ_FT\" INTEGER, \\n\\t\"DYTNCRDT\" TEXT, \\n\\t\"OWNOCC\" TEXT, \\n\\t\"CITY_TOWNSHIP\" INTEGER, \\n\\t\"SCHOOL_DISTRICT\" INTEGER, \\n\\t\"PARCEL_LOCATION_ZIP\" INTEGER, \\n\\t\"CENSUS_TRACT\" TEXT, \\n\\tlat REAL, \\n\\tlon REAL\\n)\\n\\n/*\\n3 rows from daytonprops table:\\nPARCELID\\tFULLYRAMTDUE\\tNETDELQ\\tPARCELLOCATION\\tLEGAL1\\tLEGAL2\\tLEGAL3\\tACRES\\tTXDST\\tCLS\\tLUC\\tAGLAND\\tASMTLAND\\tASMTBLDG\\tASMTTOTAL\\tTAXABLELAND\\tTAXABLEBLDG\\tPUBLICUTILITY\\tROLLTYPE\\tTAXABLETOTAL\\tOWNERNAME1\\tOWNERNAME2\\tMAILINGNAME1\\tMAILINGNAME2\\tPADDR1\\tPADDR2\\tPADDR3\\tMORTCO\\tNBHD\\tASMTWEN\\tAC\\tHMSDLAND\\tHMSDBLDG\\tFRCLSR\\tSALESDTE\\tPRICE\\tYRBL\\tGROSSRATE\\tEFFRATE\\tREDRATE\\tDUEDATE\\tB\\tHLF1DELQ\\tRENTALREG\\tAETASMTLAND\\tAETASMTBLDG\\tAETASMTTOTAL\\tAETTAXABLELAND\\tAETTAXABLEBLDG\\tAETTAXABLETOTAL\\tCASMTLAND\\tCTAXABLELAND\\tHMSDFLAG\\tSQ_FT\\tDYTNCRDT\\tOWNOCC\\tCITY_TOWNSHIP\\tSCHOOL_DISTRICT\\tPARCEL_LOCATION_ZIP\\tCENSUS_TRACT\\tlat\\tlon\\nR72 00101 0001\\t0.0\\t0.0\\tROBERT DR\\t126 PT RIVERBANK\\t1E(.008) 1E1(.007)\\tEXEMPT\\t1.146\\tR72\\tE\\t640\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tDAYTON CITY OF\\tNone\\tCITY OF DAYTON\\tNone\\t101 W 3RD ST\\tNone\\tDAYTON OH 45401\\t3001\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\t02-JAN-03\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.757987\\t-84.203876\\nR72 00101 0002\\t0.0\\t0.0\\tROBERT DR\\t126 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t670\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.758832\\t-84.204194\\nR72 00101 0003\\t0.0\\t0.0\\tROBERT DR\\t125 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t665\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\tNone\\t39113-001501-1\\t39.759657\\t-84.204154\\n*/', 'stop': ['\\nSQLResult:']}\n",
      "1: SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = 'DAYTON CITY OF' ORDER BY \"ACRES\" DESC LIMIT 5;\n",
      "2: {'sql_cmd': 'SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;'}\n",
      "3: [('3600 TERMINAL DR STE 300', None, 'VANDALIA OH 45377', 879.2031), ('MUNICIPAL BG', None, 'DAYTON OH 45402', 694.565), ('101 W 3RD ST', None, 'DAYTON OH 45402 1814', 369.013), ('MUNICIPAL BG', None, 'DAYTON OH 45402', 326.38), ('101 W 3RD ST', None, 'DAYTON OH 45402 1814', 100.926)]\n",
      "4: {'input': 'what are the addresses of the 5 largest properties owned by the city?\\nSQLQuery:SELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", \"ACRES\" FROM daytonprops WHERE \"OWNERNAME1\" = \\'DAYTON CITY OF\\' ORDER BY \"ACRES\" DESC LIMIT 5;\\nSQLResult: [(\\'3600 TERMINAL DR STE 300\\', None, \\'VANDALIA OH 45377\\', 879.2031), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 694.565), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 369.013), (\\'MUNICIPAL BG\\', None, \\'DAYTON OH 45402\\', 326.38), (\\'101 W 3RD ST\\', None, \\'DAYTON OH 45402 1814\\', 100.926)]\\nAnswer:', 'top_k': '5', 'dialect': 'sqlite', 'table_info': '\\nCREATE TABLE daytonprops (\\n\\t\"PARCELID\" TEXT, \\n\\t\"FULLYRAMTDUE\" REAL, \\n\\t\"NETDELQ\" REAL, \\n\\t\"PARCELLOCATION\" TEXT, \\n\\t\"LEGAL1\" TEXT, \\n\\t\"LEGAL2\" TEXT, \\n\\t\"LEGAL3\" TEXT, \\n\\t\"ACRES\" REAL, \\n\\t\"TXDST\" TEXT, \\n\\t\"CLS\" TEXT, \\n\\t\"LUC\" INTEGER, \\n\\t\"AGLAND\" INTEGER, \\n\\t\"ASMTLAND\" INTEGER, \\n\\t\"ASMTBLDG\" INTEGER, \\n\\t\"ASMTTOTAL\" INTEGER, \\n\\t\"TAXABLELAND\" INTEGER, \\n\\t\"TAXABLEBLDG\" INTEGER, \\n\\t\"PUBLICUTILITY\" INTEGER, \\n\\t\"ROLLTYPE\" TEXT, \\n\\t\"TAXABLETOTAL\" INTEGER, \\n\\t\"OWNERNAME1\" TEXT, \\n\\t\"OWNERNAME2\" TEXT, \\n\\t\"MAILINGNAME1\" TEXT, \\n\\t\"MAILINGNAME2\" TEXT, \\n\\t\"PADDR1\" TEXT, \\n\\t\"PADDR2\" TEXT, \\n\\t\"PADDR3\" TEXT, \\n\\t\"MORTCO\" INTEGER, \\n\\t\"NBHD\" TEXT, \\n\\t\"ASMTWEN\" TEXT, \\n\\t\"AC\" TEXT, \\n\\t\"HMSDLAND\" INTEGER, \\n\\t\"HMSDBLDG\" INTEGER, \\n\\t\"FRCLSR\" TEXT, \\n\\t\"SALESDTE\" TEXT, \\n\\t\"PRICE\" INTEGER, \\n\\t\"YRBL\" INTEGER, \\n\\t\"GROSSRATE\" REAL, \\n\\t\"EFFRATE\" REAL, \\n\\t\"REDRATE\" REAL, \\n\\t\"DUEDATE\" TEXT, \\n\\t\"B\" INTEGER, \\n\\t\"HLF1DELQ\" REAL, \\n\\t\"RENTALREG\" TEXT, \\n\\t\"AETASMTLAND\" INTEGER, \\n\\t\"AETASMTBLDG\" INTEGER, \\n\\t\"AETASMTTOTAL\" INTEGER, \\n\\t\"AETTAXABLELAND\" INTEGER, \\n\\t\"AETTAXABLEBLDG\" INTEGER, \\n\\t\"AETTAXABLETOTAL\" INTEGER, \\n\\t\"CASMTLAND\" INTEGER, \\n\\t\"CTAXABLELAND\" INTEGER, \\n\\t\"HMSDFLAG\" TEXT, \\n\\t\"SQ_FT\" INTEGER, \\n\\t\"DYTNCRDT\" TEXT, \\n\\t\"OWNOCC\" TEXT, \\n\\t\"CITY_TOWNSHIP\" INTEGER, \\n\\t\"SCHOOL_DISTRICT\" INTEGER, \\n\\t\"PARCEL_LOCATION_ZIP\" INTEGER, \\n\\t\"CENSUS_TRACT\" TEXT, \\n\\tlat REAL, \\n\\tlon REAL\\n)\\n\\n/*\\n3 rows from daytonprops table:\\nPARCELID\\tFULLYRAMTDUE\\tNETDELQ\\tPARCELLOCATION\\tLEGAL1\\tLEGAL2\\tLEGAL3\\tACRES\\tTXDST\\tCLS\\tLUC\\tAGLAND\\tASMTLAND\\tASMTBLDG\\tASMTTOTAL\\tTAXABLELAND\\tTAXABLEBLDG\\tPUBLICUTILITY\\tROLLTYPE\\tTAXABLETOTAL\\tOWNERNAME1\\tOWNERNAME2\\tMAILINGNAME1\\tMAILINGNAME2\\tPADDR1\\tPADDR2\\tPADDR3\\tMORTCO\\tNBHD\\tASMTWEN\\tAC\\tHMSDLAND\\tHMSDBLDG\\tFRCLSR\\tSALESDTE\\tPRICE\\tYRBL\\tGROSSRATE\\tEFFRATE\\tREDRATE\\tDUEDATE\\tB\\tHLF1DELQ\\tRENTALREG\\tAETASMTLAND\\tAETASMTBLDG\\tAETASMTTOTAL\\tAETTAXABLELAND\\tAETTAXABLEBLDG\\tAETTAXABLETOTAL\\tCASMTLAND\\tCTAXABLELAND\\tHMSDFLAG\\tSQ_FT\\tDYTNCRDT\\tOWNOCC\\tCITY_TOWNSHIP\\tSCHOOL_DISTRICT\\tPARCEL_LOCATION_ZIP\\tCENSUS_TRACT\\tlat\\tlon\\nR72 00101 0001\\t0.0\\t0.0\\tROBERT DR\\t126 PT RIVERBANK\\t1E(.008) 1E1(.007)\\tEXEMPT\\t1.146\\tR72\\tE\\t640\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tDAYTON CITY OF\\tNone\\tCITY OF DAYTON\\tNone\\t101 W 3RD ST\\tNone\\tDAYTON OH 45401\\t3001\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\t02-JAN-03\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.757987\\t-84.203876\\nR72 00101 0002\\t0.0\\t0.0\\tROBERT DR\\t126 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t670\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\t45409\\t39113-001501-1\\t39.758832\\t-84.204194\\nR72 00101 0003\\t0.0\\t0.0\\tROBERT DR\\t125 PT\\tNone\\tNone\\t0.0\\tR72\\tE\\t665\\t0\\t220\\t0\\t220\\t80\\t0\\t0\\tRP_OH\\t80\\tMIAMI CONSERVANCY DIST\\tNone\\tMIAMI CONSERVANCY DIST\\tNone\\t38 E MONUMENT AVE\\tNone\\tDAYTON OH 45402 1210\\t3121\\tC1500000\\t06-JAN-22\\tN\\t220\\t0\\tNone\\tNone\\tNone\\tNone\\t116.28\\t107.133485\\t0.078659\\t15-JUL-22\\t2\\t0.0\\tN\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\t0\\tN\\t0\\tN\\tN\\tNone\\tNone\\tNone\\t39113-001501-1\\t39.759657\\t-84.204154\\n*/', 'stop': ['\\nSQLResult:']}\n",
      "5: The 5 largest properties owned by the city are located at 3600 Terminal Dr Ste 300, Vandalia OH 45377 (879.2031 acres), Municipal BG, Dayton OH 45402 (694.565 acres), 101 W 3rd St, Dayton OH 45402 1814 (369.013 acres), Municipal BG, Dayton OH 45402 (326.38 acres), and 101 W 3rd St, Dayton OH 45402 1814 (100.926 acres).\n"
     ]
    }
   ],
   "source": [
    "print(\"0:\", result['intermediate_steps'][0])\n",
    "print(\"1:\", result['intermediate_steps'][1])\n",
    "print(\"2:\", result['intermediate_steps'][2])\n",
    "print(\"3:\", result['intermediate_steps'][3])\n",
    "print(\"4:\", result['intermediate_steps'][4])\n",
    "print(\"5:\", result['intermediate_steps'][5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 178
    },
    "id": "d75sDxVRblSp",
    "outputId": "c10393fd-1199-4a83-b039-036105d6d4e4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many properties are owned by the conservancy district?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM daytonprops WHERE \"OWNERNAME1\" = 'MIAMI CONSERVANCY DIST'\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(62,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThe conservancy district owns 62 properties.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "application/vnd.google.colaboratory.intrinsic+json": {
       "type": "string"
      },
      "text/plain": [
       "'The conservancy district owns 62 properties.'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = db_chain(\"How many properties are owned by the conservancy district?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 178
    },
    "id": "SL8H-8n8b-Qc",
    "outputId": "77ecbca1-e514-400e-a42b-86e8a4ebc011"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "What is the address of the property with the most acreage?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT \"PADDR1\", \"PADDR2\", \"PADDR3\", MAX(\"ACRES\") FROM daytonprops;\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('131 N LUDLOW ST STE 1200', None, 'DAYTON OH 45402', 8896.0)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThe property with the most acreage is located at 131 N Ludlow St Ste 1200, Dayton OH 45402.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "application/vnd.google.colaboratory.intrinsic+json": {
       "type": "string"
      },
      "text/plain": [
       "'The property with the most acreage is located at 131 N Ludlow St Ste 1200, Dayton OH 45402.'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = db_chain(\"What is the address of the property with the most acreage?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 178
    },
    "id": "-axifVA3ewD0",
    "outputId": "79f06d08-9dab-472e-99de-0532579e5fd2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many residential properties are in Dayton?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM daytonprops WHERE CLS = 'E' AND LUC = 640;\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(1771,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThere are 1771 residential properties in Dayton.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "application/vnd.google.colaboratory.intrinsic+json": {
       "type": "string"
      },
      "text/plain": [
       "'There are 1771 residential properties in Dayton.'"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = db_chain(\"How many residential properties are in Dayton?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 178
    },
    "id": "EwUGbhvfbxq-",
    "outputId": "a78c82d4-f308-4eed-ce2e-275c6c9a72e1"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many properties with more than an acre are in dayton?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM daytonprops WHERE ACRES > 1;\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(1640,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThere are 1640 properties with more than an acre in dayton.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "application/vnd.google.colaboratory.intrinsic+json": {
       "type": "string"
      },
      "text/plain": [
       "'There are 1640 properties with more than an acre in dayton.'"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = db_chain(\"How many properties with more than an acre are in dayton?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "NkCQyq_-kv5U"
   },
   "outputs": [],
   "source": [
    "custom_table_info = {\n",
    "  \"daytonprops\": \"\"\"CREATE TABLE daytonprops (\"PARCELID\" TEXT,\"FULLYRAMTDUE\" REAL,\"NETDELQ\" REAL,\"PARCELLOCATION\" TEXT,\"ACRES\" REAL,\"CLS\" TEXT,\"LUC\" INTEGER,\"AGLAND\" INTEGER,\"ASMTLAND\" INTEGER,\"ASMTBLDG\" INTEGER,\n",
    "  \"ASMTTOTAL\" INTEGER,\"TAXABLELAND\" INTEGER,\"TAXABLEBLDG\" INTEGER,\"PUBLICUTILITY\" INTEGER,\"ROLLTYPE\" TEXT,\"TAXABLETOTAL\" INTEGER,\n",
    "  \"OWNERNAME1\" TEXT,\"OWNERNAME2\" TEXT,\"MAILINGNAME1\" TEXT,\"MAILINGNAME2\" TEXT,\"PADDR1\" TEXT,\"PADDR2\" TEXT,\"PADDR3\" TEXT,\n",
    "  \"MORTCO\" INTEGER,\"NBHD\" TEXT,\"ASMTWEN\" TEXT,\"AC\" TEXT,\"HMSDLAND\" INTEGER,\"HMSDBLDG\" INTEGER,\"FRCLSR\" TEXT,\"SALESDTE\" TEXT,\n",
    "  \"PRICE\" INTEGER,\"YRBL\" INTEGER,\"GROSSRATE\" REAL,\"EFFRATE\" REAL,\"REDRATE\" REAL,\"DUEDATE\" TEXT,\"HLF1DELQ\" REAL,\n",
    "  \"RENTALREG\" TEXT,\"CASMTLAND\" INTEGER,\"CTAXABLELAND\" INTEGER,\"HMSDFLAG\" TEXT,\"SQ_FT\" INTEGER,\"DYTNCRDT\" TEXT,\"OWNOCC\" TEXT,\n",
    "  \"CITY_TOWNSHIP\" INTEGER,\"SCHOOL_DISTRICT\" INTEGER,\"PARCEL_LOCATION_ZIP\" INTEGER,\"CENSUS_TRACT\" TEXT,\n",
    "  \"lat\" REAL,\"lon\" REAL,\"luc_desc\" TEXT\n",
    "  );\n",
    "Column Descriptions:\n",
    "PARCELID: Parcel Identification Number\n",
    "FULLYRAMTDUE: Full Year Tax Amount Due\n",
    "NETDELQ: Net Delinquent Taxes\n",
    "PARCELLOCATION: Parcel Location (Street)\n",
    "ACRES: Parcel Acreage\n",
    "CLASS: Parcel Class: one of A=Agricultural, C=Commercial, E=Exempt, I=Industrial, R=Residential, U=Utilities\n",
    "LUC: Land Use Code number. Text in luc_desc column\n",
    "AGLAND: CAUV Land Value\n",
    "ASMTLAND: 100% Appraised Land Value\n",
    "ASMTBLDG: 100% Appraised Building Value\n",
    "ASMTTOTAL: 100% Appraised Total Value\n",
    "TAXABLELAND: 35% Taxable Land Value\n",
    "TAXABLEBLDG: 35% Taxable Building Value\n",
    "PUBLICUTILITY: Public Utility Value\n",
    "ROLLTYPE: County Use\n",
    "TAXABLETOTAL: 35% Taxable Total Value\n",
    "OWNERNAME1: Owner Name 1\n",
    "OWNERNAME2: Owner Name 2\n",
    "MAILINGNAME1: Mailing Name 1\n",
    "MAILINGNAME2: Mailing Name 2\n",
    "PADDR1, PADDR2, PADDR3: Mailing Address Line 1-3\n",
    "MORTCO: Mortgage Code (Usually Mortgage Company or Multiple Parcel Owner Who’s Paying Tax Bill)\n",
    "NBHD: Neighborhood Number\n",
    "ASMTWEN: Last Date Value/Class Record Was Updated\n",
    "AC: Appeal Code\n",
    "HMSDLAND: Homestead Land Value\n",
    "HMSDBLDG: Homestead Building Value\n",
    "FRCLSR: Foreclosure Date\n",
    "SALESDTE: Sales Date\n",
    "PRICE: Sales Price\n",
    "YRBLT: Year Built\n",
    "GROSSRATE: Gross Tax Rate\n",
    "EFFRATE: Effective Tax Rate\n",
    "REDRATE: Reduction Factor\n",
    "DUEDATE: Due Date for 1st Half, 2nd Half or Delinquent Cycles\n",
    "HLF1DELQ: Delinquent Plus Unpaid First Half\n",
    "RENTALREG: Registered with the Rental Registration Program\n",
    "CASMTLAND: Land Value if parcel is on CAUV\n",
    "CTAXABLELAND: 35% Land Value if parcel is on CAUV\n",
    "HMSDFLAG: Indicator whether parcel is flagged for Homestead Exemption\n",
    "SQ_FT: Square Footage\n",
    "DYTNCRDT: Indicator whether parcel is flagged for Dayton Credit\n",
    "CITY_TOWNSHIP: City or Township in which the parcel resides\n",
    "SCHOOL_DISTRICT: School District in which the parcel resides\n",
    "PARCEL_LOCATION_ZIP: Zip code\n",
    "CENSUS_TRACT: Census tract id\n",
    "lat: Latitude of the parcel\n",
    "lon: Longitude of the parcel\n",
    "luc_desc: Land use code text description\n",
    "\n",
    "/* Here are 3 sample rows:\n",
    "R72 00101 0001|0.0|0.0|ROBERT DR|1.146|E|640|0|220|0|220|80|0|0|RP_OH|80|DAYTON CITY OF||CITY OF DAYTON||101 W 3RD ST||DAYTON OH 45401|3001|C1500000|06-JAN-22|N|220|0||02-JAN-03|||116.28|107.133485|0.078659|15-JUL-22|0.0|N|0|0|N|0|N|||45409|39113-001501-1|39.757987|-84.203876|E - EXEMPT PROPERTY OWNED BY MUNICIPALS\n",
    "R72 00602 0084|0.0|0.0|30 S PATTERSON BLVD|0.0249|R|510|0|60000|0|60000|21000|0|0|RP_OH|21000|BAUER MATTHEW D AND|KARNES JAMI A|BAUER MATTHEW D AND|KARNES JAMI A|30 S PATTERSON BLVD||DAYTON OH 45402||3100VIEW|06-JAN-22|60000|0||06-SEP-18|400005|2017|116.28|81.091028|0.302623|15-JUL-22|2|0.0|N|0|0|N|1824|N||||39113-001501-1|39.759547|-84.186739|R - SINGLE FAMILY DWELLING, PLATTED LOT\n",
    "R72 00206 0015|0.0|0.0|135 E 2ND ST|0.163|C|449|0|71000|269350|340350|24850|94270|0|RP_OH|119120|MUDLICK TAP HOUSE LLC||MUDLICK TAP HOUSE LLC||135 E SECOND ST||DAYTON OH 45402||C1500000|21-NOV-22|N|71000|0||24-DEC-18|500000|1900|116.28|107.133485|0.078659|15-JUL-22|0.0|N|0|0|N|14788|N|||45402|39113-001501-1|39.762101|-84.189297|C - OFFICE BUILD 3 OR MORE STORIES ELEVATOR\n",
    "*/\"\"\"\n",
    "}\n",
    "\n",
    "db = SQLDatabase.from_uri(\"sqlite:///daytonprops.db\",custom_table_info=custom_table_info)\n",
    "\n",
    "db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, use_query_checker=True, return_intermediate_steps=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many residential properties are in Dayton?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM daytonprops WHERE CLS = 'R';\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(57246,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThere are 57246 residential properties in Dayton.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "result = db_chain(\"How many residential properties are in Dayton?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many single family residential properties are in Dayton?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT COUNT(*) FROM daytonprops WHERE CLS = 'R' AND luc_desc LIKE '%SINGLE FAMILY DWELLING%';\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(43423,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThere are 43423 single family residential properties in Dayton.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "result = db_chain(\"How many single family residential properties are in Dayton?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "what are the names of the owners of the three largest single family dwellings in Dayton?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT \"OWNERNAME1\", \"OWNERNAME2\" FROM daytonprops WHERE \"LUC\" = 510 ORDER BY \"SQ_FT\" DESC LIMIT 3;\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('FOREMAN JAMES W III AND', 'ELLEN C'), ('QUINNCO INVESTMENTS LLC', None), ('ESTRIDGE MARILYN DIANE AND', 'DANNELLE ELIZABETH')]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mThe owners of the three largest single family dwellings in Dayton are James W III and Ellen C Foreman, Quinnco Investments LLC, and Marilyn Diane and Dannelle Elizabeth Estridge.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "result = db_chain(\"what are the names of the owners of the three largest single family dwellings in Dayton?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}