4.secmaster_data_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook I will show you \n",
    "1. How we can query each entity by embedding sec_master.\n",
    "2. How to get cypher queries from natural language.\n",
    "3. I show how traversal paths from cypher query results generated by LLM can easily be converted to Snowflake (any warehouse or sql supported DB) queries and we can use the entity row identified from the sec_master vector to easily see if that particular entity (ticker/symbol/company) exist in the traversed path in the SQL DB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import os\n",
    "import pandas as pd\n",
    "import textwrap\n",
    "\n",
    "from langchain_community.graphs import Neo4jGraph\n",
    "from langchain_openai import ChatOpenAI\n",
    "from langchain_core.prompts import ChatPromptTemplate,SystemMessagePromptTemplate, PromptTemplate\n",
    "from langchain_community.vectorstores import Neo4jVector\n",
    "from langchain_openai import OpenAIEmbeddings\n",
    "from langchain.text_splitter import RecursiveCharacterTextSplitter\n",
    "from langchain.chains import RetrievalQAWithSourcesChain\n",
    "from langchain import LLMChain, OpenAI\n",
    "from langchain_core.output_parsers import StrOutputParser\n",
    "from langchain_core.runnables import RunnablePassthrough\n",
    "from langchain.chains import GraphCypherQAChain\n",
    "from langchain_community.vectorstores import Chroma\n",
    "from langchain.schema import Document\n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv('.env', override=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "NEO4J_URI = os.getenv('NEO4J_URI')\n",
    "NEO4J_USERNAME = os.getenv('NEO4J_USERNAME')\n",
    "NEO4J_PASSWORD = os.getenv('NEO4J_PASSWORD')\n",
    "NEO4J_DATABASE = os.getenv('NEO4J_DATABASE')\n",
    "\n",
    "OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')\n",
    "OPENAI_ENDPOINT = os.getenv('OPENAI_BASE_URL') + '/embeddings'\n",
    "\n",
    "os.environ[\"LANGCHAIN_TRACING_V2\"]=\"true\"\n",
    "os.environ[\"LANGCHAIN_API_KEY\"]=os.getenv(\"LANGCHAIN_API_KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [],
   "source": [
    "kg = Neo4jGraph(\n",
    "    url=NEO4J_URI, username=NEO4J_USERNAME, password=NEO4J_PASSWORD, database=NEO4J_DATABASE\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Searching for an entity in Secmaster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['4ddb63c1-1b08-4341-9c30-bc0e4ff538c6',\n",
       " 'd1b2d981-9fe2-4c25-bc71-96a2b1f9bbdf',\n",
       " 'db4dc103-746f-4cba-944c-a1822f28771a',\n",
       " '1b05e4d7-840a-4770-a9f9-3a1818d9de6c',\n",
       " '49fa2a7b-9bdb-47b4-a776-10c560f43bd4',\n",
       " 'f4459b6f-6b31-433b-8c12-cf106b5d0521',\n",
       " '88cf2e6b-4bd1-4599-9416-acaeab3b0484']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "persist_dir = os.path.join(os.getcwd(), \"vector_store\")\n",
    "df = pd.read_excel('sample_data.xlsx',sheet_name='sec_master')\n",
    "\n",
    "def create_document(row):\n",
    "    content = \"\\n\".join([f\"{col}: {val}\" for col, val in row.items()])\n",
    "    return Document(page_content=content)\n",
    "\n",
    "documents = df.apply(create_document, axis=1).tolist()\n",
    "\n",
    "# print(documents[0].page_content)\n",
    "\n",
    "vector_store = Chroma(persist_directory=persist_dir,embedding_function=OpenAIEmbeddings())\n",
    "\n",
    "vector_store.add_documents(documents)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date: 2020-01-01 00:00:00\n",
      "ticker: AAPL\n",
      "figi_composite: BBG000B9XRY4\n",
      "figi_share_class: BBG001S5N8V8\n",
      "security_name: Apple Inc\n",
      "entity_name: Apple Inc\n",
      "gics_sector: Information Technology\n",
      "gics_sub_sector: Technology Hardware, Storage & Peripherals\n"
     ]
    }
   ],
   "source": [
    "# query = \"Give me the entity for starbucks\"\n",
    "query = \"Show me the ticker AAPL\"\n",
    "result = vector_store.similarity_search_with_score(query,k=1)\n",
    "print(result[0][0].page_content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date: 2020-01-01 00:00:00\n",
      "ticker: SBUX\n",
      "figi_composite: BBG000CTQBF3\n",
      "figi_share_class: BBG001S72KH6\n",
      "security_name: Starbucks Corp\n",
      "entity_name: Starbucks Corp\n",
      "gics_sector: Consumer Discretionary\n",
      "gics_sub_sector: Restaurants\n"
     ]
    }
   ],
   "source": [
    "query = \"Show me the entity Starbucks\"\n",
    "result = vector_store.similarity_search_with_score(query,k=1)\n",
    "print(result[0][0].page_content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Cypher"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [],
   "source": [
    "CYPHER_GENERATION_TEMPLATE = \"\"\"Task:Generate Cypher statement to query a graph database.\n",
    "Instructions:\n",
    "Use only the provided relationship types and properties in the \n",
    "schema. Do not use any other relationship types or properties that \n",
    "are not provided. When creating the query make the relationships directionless.\n",
    "Schema:\n",
    "{schema}\n",
    "Note: Do not include any explanations or apologies in your responses.\n",
    "Do not respond to any questions that might ask anything else than \n",
    "for you to construct a Cypher statement.\n",
    "Do not include any text except the generated Cypher statement.\n",
    "Examples: Here are a few examples of generated Cypher \n",
    "statements for particular questions:\n",
    "\n",
    "# Show me all the datasets that are connected to each other?\n",
    "MATCH (d1:dataset)-[:HAS_COLUMN]-(:column)-[:RELATED_TO]-(:column)-[:HAS_COLUMN]-(d2:dataset)\n",
    "RETURN d1, d2;\n",
    "\n",
    "# Find paths that start from a dataset named 'foot_traffic', \n",
    "# go through datasets, and return specific table and column names at each step?\n",
    "MATCH path = (d:dataset)-[*]-(d2:dataset)\n",
    "WHERE d.name='foot_trafic'\n",
    "UNWIND nodes(path) as n\n",
    "RETURN n.type, n.table, n.name;\n",
    "\n",
    "The question is:\n",
    "{question}\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "CYPHER_GENERATION_PROMPT = PromptTemplate(\n",
    "    input_variables=[\"schema\", \"question\"], \n",
    "    template=CYPHER_GENERATION_TEMPLATE\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "cypherChain = GraphCypherQAChain.from_llm(\n",
    "    ChatOpenAI(model='gpt-4',temperature=0),\n",
    "    graph=kg,\n",
    "    verbose=True,\n",
    "    cypher_prompt=CYPHER_GENERATION_PROMPT,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "def prettyCypherChain(question: str) -> str:\n",
    "    response = cypherChain.run(question)\n",
    "    print(textwrap.fill(response, 60))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new GraphCypherQAChain chain...\u001b[0m\n",
      "Generated Cypher:\n",
      "\u001b[32;1m\u001b[1;3mMATCH (d:dataset)\n",
      "RETURN d.name;\u001b[0m\n",
      "Full Context:\n",
      "\u001b[32;1m\u001b[1;3m[{'d.name': 'foot_traffic'}, {'d.name': 'web_traffic'}, {'d.name': 'social_media'}, {'d.name': 'sec_master'}, {'d.name': 'weather'}]\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "The available datasets are foot_traffic, web_traffic,\n",
      "social_media, sec_master, and weather.\n"
     ]
    }
   ],
   "source": [
    "prettyCypherChain(\"Show me all the datasets available, I only need the names?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new GraphCypherQAChain chain...\u001b[0m\n",
      "Generated Cypher:\n",
      "\u001b[32;1m\u001b[1;3mMATCH (d:dataset {name: 'weather'})-[:HAS_COLUMN]->(c:column)\n",
      "RETURN c.name, c.col_type;\u001b[0m\n",
      "Full Context:\n",
      "\u001b[32;1m\u001b[1;3m[{'c.name': 'country', 'c.col_type': 'reference'}, {'c.name': 'zip_code', 'c.col_type': 'reference'}, {'c.name': 'latitude', 'c.col_type': 'feature'}, {'c.name': 'longitude', 'c.col_type': 'feature'}, {'c.name': 'feels_like_max', 'c.col_type': 'feature'}, {'c.name': 'rel_hum_avg', 'c.col_type': 'feature'}, {'c.name': 'snow_depth_min', 'c.col_type': 'feature'}, {'c.name': 'month', 'c.col_type': 'feature'}, {'c.name': 'dma_name', 'c.col_type': 'reference'}, {'c.name': 'state_abvtn', 'c.col_type': 'reference'}]\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "The column names connected to the weather dataset along with\n",
      "their types are as follows: 'country' and 'zip_code' are\n",
      "references; 'latitude', 'longitude', 'feels_like_max',\n",
      "'rel_hum_avg', 'snow_depth_min', and 'month' are features;\n",
      "'dma_name' and 'state_abvtn' are also references.\n"
     ]
    }
   ],
   "source": [
    "prettyCypherChain(\"Show me all the column names connected to the weather dataset ? also mention the column type as well\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new GraphCypherQAChain chain...\u001b[0m\n",
      "Generated Cypher:\n",
      "\u001b[32;1m\u001b[1;3mMATCH (d1:dataset)-[:HAS_COLUMN]-(:column)-[:RELATED_TO]-(:column)-[:HAS_COLUMN]-(d2:dataset)\n",
      "RETURN d1.name, d2.name;\u001b[0m\n",
      "Full Context:\n",
      "\u001b[32;1m\u001b[1;3m[{'d1.name': 'foot_traffic', 'd2.name': 'weather'}, {'d1.name': 'weather', 'd2.name': 'foot_traffic'}, {'d1.name': 'foot_traffic', 'd2.name': 'sec_master'}, {'d1.name': 'sec_master', 'd2.name': 'foot_traffic'}, {'d1.name': 'web_traffic', 'd2.name': 'social_media'}, {'d1.name': 'social_media', 'd2.name': 'web_traffic'}, {'d1.name': 'web_traffic', 'd2.name': 'sec_master'}, {'d1.name': 'sec_master', 'd2.name': 'web_traffic'}]\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "foot_traffic, weather, sec_master, social_media, web_traffic\n"
     ]
    }
   ],
   "source": [
    "prettyCypherChain(\"Show me all the datasets that are connected to each other?, only need the names\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new GraphCypherQAChain chain...\u001b[0m\n",
      "Generated Cypher:\n",
      "\u001b[32;1m\u001b[1;3mMATCH path = (d:dataset {name: 'sec_master'})-[*]-(d2:dataset)\n",
      "UNWIND nodes(path) as n\n",
      "RETURN n.name, n.type;\u001b[0m\n",
      "Full Context:\n",
      "\u001b[32;1m\u001b[1;3m[{'n.name': 'sec_master', 'n.type': 'dataset'}, {'n.name': 'ticker', 'n.type': 'column'}, {'n.name': 'symbol', 'n.type': 'column'}, {'n.name': 'foot_traffic', 'n.type': 'dataset'}, {'n.name': 'sec_master', 'n.type': 'dataset'}, {'n.name': 'entity_name', 'n.type': 'column'}, {'n.name': 'website_owner', 'n.type': 'column'}, {'n.name': 'web_traffic', 'n.type': 'dataset'}, {'n.name': 'sec_master', 'n.type': 'dataset'}, {'n.name': 'entity_name', 'n.type': 'column'}]\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "From the 'sec_master' dataset, you can navigate to the\n",
      "'ticker' and 'symbol' columns. You can also reach the\n",
      "'foot_traffic' and 'web_traffic' datasets. Additionally, you\n",
      "can access the 'entity_name' and 'website_owner' columns.\n"
     ]
    }
   ],
   "source": [
    "prettyCypherChain(\"Show me how I can navigate to different datasets from sec_master? show me only the name and type of the entities in the path\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new GraphCypherQAChain chain...\u001b[0m\n",
      "Generated Cypher:\n",
      "\u001b[32;1m\u001b[1;3mMATCH path = (d:dataset {name: 'sec_master'})-[*]-(d2:dataset)\n",
      "UNWIND nodes(path) as n\n",
      "RETURN n.type, n.name, n.table;\u001b[0m\n",
      "Full Context:\n",
      "\u001b[32;1m\u001b[1;3m[{'n.type': 'dataset', 'n.name': 'sec_master', 'n.table': 'graph_db.public.sec_master'}, {'n.type': 'column', 'n.name': 'ticker', 'n.table': None}, {'n.type': 'column', 'n.name': 'symbol', 'n.table': None}, {'n.type': 'dataset', 'n.name': 'foot_traffic', 'n.table': 'graph_db.public.foot_traffic'}, {'n.type': 'dataset', 'n.name': 'sec_master', 'n.table': 'graph_db.public.sec_master'}, {'n.type': 'column', 'n.name': 'entity_name', 'n.table': None}, {'n.type': 'column', 'n.name': 'website_owner', 'n.table': None}, {'n.type': 'dataset', 'n.name': 'web_traffic', 'n.table': 'graph_db.public.web_traffic'}, {'n.type': 'dataset', 'n.name': 'sec_master', 'n.table': 'graph_db.public.sec_master'}, {'n.type': 'column', 'n.name': 'entity_name', 'n.table': None}]\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "You can use the following Cypher query:  MATCH\n",
      "(n:dataset)-[:RELATED_TO]->(m:dataset) WHERE n.name =\n",
      "'sec_master' RETURN n.type as Type, n.name as Name, n.table\n",
      "as Table, m.type as Related_Type, m.name as Related_Name,\n",
      "m.table as Related_Table\n"
     ]
    }
   ],
   "source": [
    "prettyCypherChain(\"\"\"\n",
    "I need a cypher query to traverse from the sec_master dataset to related datasets? \n",
    "In the cypher query return the type, name and underlying table.\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"MATCH path = (d:dataset {name: 'sec_master'})-[*]-(d2:dataset)\n",
    "UNWIND nodes(path) as n\n",
    "RETURN n.type, n.name, n.table;\"\"\"\n",
    "result = kg.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(result)\n",
    "df.columns = [c.replace(\"n.\",\"\") for c in df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "paths = []\n",
    "start = 0 \n",
    "for i,row in df.iterrows():\n",
    "    if i != 0 and row['name']=='sec_master':\n",
    "        paths.append(df.iloc[start:i])\n",
    "        start = i\n",
    "    elif i == len(df)-1:\n",
    "        paths.append(df.iloc[start:i+1])\n",
    "    else:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>table</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dataset</td>\n",
       "      <td>sec_master</td>\n",
       "      <td>graph_db.public.sec_master</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>column</td>\n",
       "      <td>ticker</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>column</td>\n",
       "      <td>symbol</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dataset</td>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>graph_db.public.foot_traffic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      type          name                         table\n",
       "0  dataset    sec_master    graph_db.public.sec_master\n",
       "1   column        ticker                          None\n",
       "2   column        symbol                          None\n",
       "3  dataset  foot_traffic  graph_db.public.foot_traffic"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paths[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>table</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>dataset</td>\n",
       "      <td>sec_master</td>\n",
       "      <td>graph_db.public.sec_master</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>column</td>\n",
       "      <td>ticker</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>column</td>\n",
       "      <td>symbol</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>dataset</td>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>graph_db.public.foot_traffic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>column</td>\n",
       "      <td>post_code</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>column</td>\n",
       "      <td>zip_code</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>dataset</td>\n",
       "      <td>weather</td>\n",
       "      <td>graph_db.public.weather</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       type          name                         table\n",
       "15  dataset    sec_master    graph_db.public.sec_master\n",
       "16   column        ticker                          None\n",
       "17   column        symbol                          None\n",
       "18  dataset  foot_traffic  graph_db.public.foot_traffic\n",
       "19   column     post_code                          None\n",
       "20   column      zip_code                          None\n",
       "21  dataset       weather       graph_db.public.weather"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paths[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now any of the above paths can be easily converted to Snowflake SQL queries if we want to know if the certain entities exist in the path.\n",
    "Its very easy to take the above paths and converts it into a SQL - code below.\n",
    "\n",
    "~~~sql\n",
    "-- For path1\n",
    "select count(*)\n",
    "from graph_db.public.sec_master a \n",
    "inner join graph_db.public.foot_traffic b on a.ticker = b.symbol\n",
    "where a.ticker='SBUX'\n",
    "~~~\n",
    "\n",
    "~~~sql\n",
    "-- For path3\n",
    "select count(*)\n",
    "from graph_db.public.sec_master a \n",
    "inner join graph_db.public.foot_traffic b on a.ticker = b.symbol\n",
    "inner join graph_db.public.foot_traffic c on b.post_code = c.zip_code\n",
    "where a.ticker='SBUX'\n",
    "~~~"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [],
   "source": [
    "def paths_to_sql(df_path):\n",
    "    dataset_ls = list(df_path[df_path['type']=='dataset']['table'])\n",
    "    col_ls = list(df_path[df_path['type']=='column']['name'])\n",
    "\n",
    "    def gen_fun(lst): # creating generator\n",
    "        for item in lst:\n",
    "            yield item\n",
    "\n",
    "    dataset_gen = gen_fun(dataset_ls)\n",
    "    col_gen = gen_fun(col_ls)\n",
    "\n",
    "    i = 0\n",
    "    query = \"select count(*)\\nfrom \"\n",
    "    while i < len(dataset_ls):\n",
    "        col_count = 0 \n",
    "        if i == 0:\n",
    "            query += f\"{next(dataset_gen)} a \"\n",
    "        else:\n",
    "            alias = chr(ord('a') + i)\n",
    "            prev_alias = chr(ord('a') + i - 1)\n",
    "            query += f\"\\ninner join {next(dataset_gen)} {alias} on {prev_alias}.{next(col_gen)} = {alias}.{next(col_gen)}\"\n",
    "        i+=1\n",
    "    return query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select count(*)\n",
      "from graph_db.public.sec_master a \n",
      "inner join graph_db.public.foot_traffic b on a.ticker = b.symbol\n"
     ]
    }
   ],
   "source": [
    "print(paths_to_sql(paths[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select count(*)\n",
      "from graph_db.public.sec_master a \n",
      "inner join graph_db.public.foot_traffic b on a.ticker = b.symbol\n",
      "inner join graph_db.public.weather c on b.post_code = c.zip_code\n"
     ]
    }
   ],
   "source": [
    "print(paths_to_sql(paths[3]))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda38",
   "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.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}