{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> <center><a href='https://o-0268-kvnaditya-8620-o.web.app/documentation/NLP__SQL.html'><h3>Executing Query Operations using SQL Query with Natural Language using RAGs</h3></center></a>\n",
    "> <hr>\n",
    "> <center>K.V.N.Aditya</center>\n",
    "> <br>\n",
    "> <center>SurgeClasses || CMR Technical Campus</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>importing modules</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sqlite3 as sql\n",
    "import google.generativeai as genai"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>initializing path</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DMA__NS = os.path.abspath('../../NLP__SQL').replace('\\\\', '/')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>configuring Google 'GeminiAPI Key</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "genai.configure(api_key=input('[ip]::Google \\'Gemini\\' API Key'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>defining the process to convert input to NLP to SQL to output</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def func__ip_p(qp,nq): # {qp : query prompt | nq : nlp query}\n",
    "    ip = genai.GenerativeModel('gemini-pro')\n",
    "    p = ip.generate_content([qp, nq])\n",
    "    return p.text\n",
    "def func__p_op(sq,db): # {sq : sql query | db : input database}\n",
    "    _p_ = sql.connect(f'{DMA__NS}/0/db/{db}.db')\n",
    "    p = _p_.cursor()\n",
    "    p.execute(sq)\n",
    "    op = p.fetchall()\n",
    "    _p_.commit()\n",
    "    _p_.close()\n",
    "    return op"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>evaluating and initializing sql database</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Initialized Databases ::\")\n",
    "for itr,db in enumerate(os.listdir(f'{DMA__NS}/0/db')):\n",
    "    print(f\"\\t{itr+1}:  {db}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = input('[ip]::init database') or 'db'\n",
    "if db not in os.listdir(f'{DMA__NS}/0/db'):\n",
    "    with open(f'{DMA__NS}/0/db/{db}.db', 'w') as f: pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"initialized database : '{db}'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>extracting the saved generated sql quires</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f__sq = open(f'{DMA__NS}/0/quires/sql.quires','r')\n",
    "q__sq = f__sq.read()\n",
    "f__sq.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>initializing prompt pipeline</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# query-prompt\n",
    "qp = f\"\"\"\n",
    "          task :\n",
    "            execute the sql query from sqlite3 `{db}` database by processing nlp into sql by using the memorized sql query executions from `{q__sq}`.\n",
    "          pipeline :\n",
    "            db :: sqlite3\n",
    "            io :: ip -> nlp -> sql -> op\n",
    "            p  :: [nlp -> {db} <-> {q__sq} -> sql || nlp -> {db} -> sql]\n",
    "      \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>passing input and generating output</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ip = input('[ip]::query')\n",
    "p = func__ip_p(qp,ip).strip('```').lstrip('sql')\n",
    "op = func__p_op(p,db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>displaying the generated output</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"==\"*28)\n",
    "print(\"[op]::input [ip -> nlp] :\")\n",
    "print(ip)\n",
    "print(\"--\"*28)\n",
    "print('[op]::processing [nlp -> sql] :')\n",
    "print(p)\n",
    "print(\"--\"*28)\n",
    "print('[op]::output [sql -> op] :')\n",
    "print(op)\n",
    "print(\"==\"*28)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <center>saving the generated queries</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f__sq = open(f'{DMA__NS}/0/quires/sql.quires','a')\n",
    "f__sq.write(p)\n",
    "f__sq.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}