explore-chembl-using-a-sql-agent.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f5c9d2c",
   "metadata": {
    "papermill": {
     "duration": 0.00374,
     "end_time": "2023-10-29T12:47:23.979966",
     "exception": false,
     "start_time": "2023-10-29T12:47:23.976226",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Let's investigate how we can use a Langchain SQL Agent to explore ChEMBL database.  \n",
    "\n",
    "## What is ChEMBL?\n",
    "\n",
    "Excerpt from https://www.ebi.ac.uk/chembl/ site: ChEMBL is a manually curated database of bioactive molecules with drug-like properties. It brings together chemical, bioactivity and genomic data to aid the translation of genomic information into effective new drugs. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ffec0a6",
   "metadata": {
    "papermill": {
     "duration": 0.002934,
     "end_time": "2023-10-29T12:47:23.987752",
     "exception": false,
     "start_time": "2023-10-29T12:47:23.984818",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Packages installation and import\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6894a91b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:47:23.995750Z",
     "iopub.status.busy": "2023-10-29T12:47:23.995418Z",
     "iopub.status.idle": "2023-10-29T12:47:57.196211Z",
     "shell.execute_reply": "2023-10-29T12:47:57.195049Z"
    },
    "papermill": {
     "duration": 33.207734,
     "end_time": "2023-10-29T12:47:57.198627",
     "exception": false,
     "start_time": "2023-10-29T12:47:23.990893",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!pip install -q -U langchain\n",
    "!pip install -q -U openai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "395d7ccc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:47:57.206836Z",
     "iopub.status.busy": "2023-10-29T12:47:57.206341Z",
     "iopub.status.idle": "2023-10-29T12:47:59.771824Z",
     "shell.execute_reply": "2023-10-29T12:47:59.770648Z"
    },
    "papermill": {
     "duration": 2.571636,
     "end_time": "2023-10-29T12:47:59.773802",
     "exception": false,
     "start_time": "2023-10-29T12:47:57.202166",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from langchain.agents import create_sql_agent \n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit \n",
    "from langchain.sql_database import SQLDatabase \n",
    "from langchain.llms.openai import OpenAI \n",
    "from langchain.agents import AgentExecutor \n",
    "from langchain.agents.agent_types import AgentType\n",
    "from langchain.chat_models import ChatOpenAI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1aa4bffe",
   "metadata": {
    "papermill": {
     "duration": 0.002976,
     "end_time": "2023-10-29T12:47:59.780193",
     "exception": false,
     "start_time": "2023-10-29T12:47:59.777217",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Setup the database connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "308552eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:47:59.787931Z",
     "iopub.status.busy": "2023-10-29T12:47:59.787468Z",
     "iopub.status.idle": "2023-10-29T12:48:02.166190Z",
     "shell.execute_reply": "2023-10-29T12:48:02.165553Z"
    },
    "papermill": {
     "duration": 2.385093,
     "end_time": "2023-10-29T12:48:02.168446",
     "exception": false,
     "start_time": "2023-10-29T12:47:59.783353",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "sqlite_path = \"/kaggle/input/chembl-database/chembl_33.db\"\n",
    "sqlite_uri = f\"sqlite:///{sqlite_path}\"\n",
    "db = SQLDatabase.from_uri(sqlite_uri)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bbb4dd0",
   "metadata": {
    "papermill": {
     "duration": 0.003222,
     "end_time": "2023-10-29T12:48:02.176793",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.173571",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Configure ChatOpenAI   \n",
    "\n",
    "We initialize the LLM that will be used to power the SQLAgent. For this experiment, we will use ChatGPT (gpt-3.5-turbo)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4088d0d9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:02.184766Z",
     "iopub.status.busy": "2023-10-29T12:48:02.184338Z",
     "iopub.status.idle": "2023-10-29T12:48:02.453755Z",
     "shell.execute_reply": "2023-10-29T12:48:02.452876Z"
    },
    "papermill": {
     "duration": 0.275578,
     "end_time": "2023-10-29T12:48:02.455779",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.180201",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from kaggle_secrets import UserSecretsClient\n",
    "user_secrets = UserSecretsClient()\n",
    "llm = ChatOpenAI(temperature=0, \n",
    "                 openai_api_key=user_secrets.get_secret(\"OPENAI_API_KEY\"), \n",
    "                 model_name='gpt-3.5-turbo')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91dbf1db",
   "metadata": {
    "papermill": {
     "duration": 0.002962,
     "end_time": "2023-10-29T12:48:02.462257",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.459295",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Prepare the Langchain SQLAgent  \n",
    "\n",
    "The Agent executor is initialized with `create_sql_agent` from Langchain, setting the LLM parameter, the toolkit parameter, and the agent type. We want to be able to monitor the entire process, therefore we set the parameter `verbose` to `True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9b20ec30",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:02.469605Z",
     "iopub.status.busy": "2023-10-29T12:48:02.469313Z",
     "iopub.status.idle": "2023-10-29T12:48:02.473849Z",
     "shell.execute_reply": "2023-10-29T12:48:02.472987Z"
    },
    "papermill": {
     "duration": 0.010217,
     "end_time": "2023-10-29T12:48:02.475590",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.465373",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "toolkit = SQLDatabaseToolkit(db=db, \n",
    "                             llm=llm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1a2739ab",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:02.483345Z",
     "iopub.status.busy": "2023-10-29T12:48:02.483001Z",
     "iopub.status.idle": "2023-10-29T12:48:02.488627Z",
     "shell.execute_reply": "2023-10-29T12:48:02.487607Z"
    },
    "papermill": {
     "duration": 0.01153,
     "end_time": "2023-10-29T12:48:02.490426",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.478896",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "agent_executor = create_sql_agent(\n",
    "    llm=llm,\n",
    "    toolkit=toolkit,\n",
    "    verbose=True,\n",
    "    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65422d6b",
   "metadata": {
    "papermill": {
     "duration": 0.002897,
     "end_time": "2023-10-29T12:48:02.496443",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.493546",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Test the agent  \n",
    "\n",
    "\n",
    "Now we will test the agent with few simple tasks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f8262f75",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:02.503880Z",
     "iopub.status.busy": "2023-10-29T12:48:02.503650Z",
     "iopub.status.idle": "2023-10-29T12:48:09.284622Z",
     "shell.execute_reply": "2023-10-29T12:48:09.283136Z"
    },
    "papermill": {
     "duration": 6.787073,
     "end_time": "2023-10-29T12:48:09.286559",
     "exception": false,
     "start_time": "2023-10-29T12:48:02.499486",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3maction_type, activities, activity_properties, activity_smid, activity_stds_lookup, activity_supp, activity_supp_map, assay_class_map, assay_classification, assay_parameters, assay_type, assays, atc_classification, binding_sites, bio_component_sequences, bioassay_ontology, biotherapeutic_components, biotherapeutics, cell_dictionary, chembl_id_lookup, chembl_release, component_class, component_domains, component_go, component_sequences, component_synonyms, compound_properties, compound_records, compound_structural_alerts, compound_structures, confidence_score_lookup, curation_lookup, data_validity_lookup, defined_daily_dose, docs, domains, drug_indication, drug_mechanism, drug_warning, formulations, frac_classification, go_classification, hrac_classification, indication_refs, irac_classification, ligand_eff, mechanism_refs, metabolism, metabolism_refs, molecule_atc_classification, molecule_dictionary, molecule_frac_classification, molecule_hierarchy, molecule_hrac_classification, molecule_irac_classification, molecule_synonyms, organism_class, patent_use_codes, predicted_binding_domains, product_patents, products, protein_class_synonyms, protein_classification, relationship_type, research_companies, research_stem, site_components, source, structural_alert_sets, structural_alerts, target_components, target_dictionary, target_relations, target_type, tissue_dictionary, usan_stems, variant_sequences, version, warning_refs\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThere are many tables in the database. I should query the schema of the \"activities\" table to see what columns it has.\n",
      "Action: sql_db_schema\n",
      "Action Input: \"activities\"\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE activities (\n",
      "\tactivity_id BIGINT NOT NULL, \n",
      "\tassay_id BIGINT NOT NULL, \n",
      "\tdoc_id BIGINT, \n",
      "\trecord_id BIGINT NOT NULL, \n",
      "\tmolregno BIGINT, \n",
      "\tstandard_relation VARCHAR(50), \n",
      "\tstandard_value NUMERIC, \n",
      "\tstandard_units VARCHAR(100), \n",
      "\tstandard_flag SMALLINT, \n",
      "\tstandard_type VARCHAR(250), \n",
      "\tactivity_comment VARCHAR(4000), \n",
      "\tdata_validity_comment VARCHAR(30), \n",
      "\tpotential_duplicate SMALLINT, \n",
      "\tpchembl_value NUMERIC(4, 2), \n",
      "\tbao_endpoint VARCHAR(11), \n",
      "\tuo_units VARCHAR(10), \n",
      "\tqudt_units VARCHAR(70), \n",
      "\ttoid INTEGER, \n",
      "\tupper_value NUMERIC, \n",
      "\tstandard_upper_value NUMERIC, \n",
      "\tsrc_id INTEGER, \n",
      "\ttype VARCHAR(250) NOT NULL, \n",
      "\trelation VARCHAR(50), \n",
      "\tvalue NUMERIC, \n",
      "\tunits VARCHAR(100), \n",
      "\ttext_value VARCHAR(1000), \n",
      "\tstandard_text_value VARCHAR(1000), \n",
      "\taction_type VARCHAR(50), \n",
      "\tCONSTRAINT pk_act_activity_id PRIMARY KEY (activity_id), \n",
      "\tCONSTRAINT fk_act_action_type FOREIGN KEY(action_type) REFERENCES action_type (action_type) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_assay_id FOREIGN KEY(assay_id) REFERENCES assays (assay_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_bao_endpoint FOREIGN KEY(bao_endpoint) REFERENCES bioassay_ontology (bao_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_doc_id FOREIGN KEY(doc_id) REFERENCES docs (doc_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_molregno FOREIGN KEY(molregno) REFERENCES molecule_dictionary (molregno) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_record_id FOREIGN KEY(record_id) REFERENCES compound_records (record_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_src_id FOREIGN KEY(src_id) REFERENCES source (src_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_data_val_comm FOREIGN KEY(data_validity_comment) REFERENCES data_validity_lookup (data_validity_comment) ON DELETE CASCADE, \n",
      "\tCONSTRAINT ck_potential_dup CHECK (POTENTIAL_DUPLICATE IN (0,1)), \n",
      "\tCONSTRAINT ck_stand_flag CHECK (standard_flag in (0,1)), \n",
      "\tCONSTRAINT ck_stand_relation CHECK (standard_relation in ('>','<','=','~','<=','>=','<<','>>'))\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from activities table:\n",
      "activity_id\tassay_id\tdoc_id\trecord_id\tmolregno\tstandard_relation\tstandard_value\tstandard_units\tstandard_flag\tstandard_type\tactivity_comment\tdata_validity_comment\tpotential_duplicate\tpchembl_value\tbao_endpoint\tuo_units\tqudt_units\ttoid\tupper_value\tstandard_upper_value\tsrc_id\ttype\trelation\tvalue\tunits\ttext_value\tstandard_text_value\taction_type\n",
      "31863\t54505\t6424\t206172\t180094\t>\t100000.0000000000\tnM\t1\tIC50\tNone\tNone\t0\tNone\tBAO_0000190\tUO_0000065\thttp://www.openphacts.org/units/Nanomolar\tNone\tNone\tNone\t1\tIC50\t>\t100.0000000000\tuM\tNone\tNone\tNone\n",
      "31864\t83907\t6432\t208970\t182268\t=\t2500.0000000000\tnM\t1\tIC50\tNone\tNone\t1\t5.60\tBAO_0000190\tUO_0000065\thttp://www.openphacts.org/units/Nanomolar\tNone\tNone\tNone\t1\tIC50\t=\t2.5000000000\tuM\tNone\tNone\tNone\n",
      "31865\t88152\t6432\t208970\t182268\t>\t50000.0000000000\tnM\t1\tIC50\tNone\tNone\t0\tNone\tBAO_0000190\tUO_0000065\thttp://www.openphacts.org/units/Nanomolar\tNone\tNone\tNone\t1\tIC50\t>\t50.0000000000\tuM\tNone\tNone\tNone\n",
      "*/\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe \"activities\" table has columns such as activity_id, assay_id, doc_id, record_id, molregno, standard_relation, standard_value, standard_units, standard_flag, standard_type, activity_comment, data_validity_comment, potential_duplicate, pchembl_value, bao_endpoint, uo_units, qudt_units, toid, upper_value, standard_upper_value, src_id, type, relation, value, units, text_value, standard_text_value, and action_type.\n",
      "\n",
      "I can use the \"activities\" table to answer the question.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM activities\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(20334684,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe query returned a count of 20,334,684 activities in the database.\n",
      "Final Answer: There are 20,334,684 activities.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'There are 20,334,684 activities.'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "question = \"How many activities are?\"\n",
    "agent_executor.run(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "35017322",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:09.296110Z",
     "iopub.status.busy": "2023-10-29T12:48:09.295752Z",
     "iopub.status.idle": "2023-10-29T12:48:12.330954Z",
     "shell.execute_reply": "2023-10-29T12:48:12.329740Z"
    },
    "papermill": {
     "duration": 3.042127,
     "end_time": "2023-10-29T12:48:12.332760",
     "exception": false,
     "start_time": "2023-10-29T12:48:09.290633",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3maction_type, activities, activity_properties, activity_smid, activity_stds_lookup, activity_supp, activity_supp_map, assay_class_map, assay_classification, assay_parameters, assay_type, assays, atc_classification, binding_sites, bio_component_sequences, bioassay_ontology, biotherapeutic_components, biotherapeutics, cell_dictionary, chembl_id_lookup, chembl_release, component_class, component_domains, component_go, component_sequences, component_synonyms, compound_properties, compound_records, compound_structural_alerts, compound_structures, confidence_score_lookup, curation_lookup, data_validity_lookup, defined_daily_dose, docs, domains, drug_indication, drug_mechanism, drug_warning, formulations, frac_classification, go_classification, hrac_classification, indication_refs, irac_classification, ligand_eff, mechanism_refs, metabolism, metabolism_refs, molecule_atc_classification, molecule_dictionary, molecule_frac_classification, molecule_hierarchy, molecule_hrac_classification, molecule_irac_classification, molecule_synonyms, organism_class, patent_use_codes, predicted_binding_domains, product_patents, products, protein_class_synonyms, protein_classification, relationship_type, research_companies, research_stem, site_components, source, structural_alert_sets, structural_alerts, target_components, target_dictionary, target_relations, target_type, tissue_dictionary, usan_stems, variant_sequences, version, warning_refs\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mI can query the \"molecule_dictionary\" table to get the SMILES codes.\n",
      "Action: sql_db_schema\n",
      "Action Input: \"molecule_dictionary\"\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE molecule_dictionary (\n",
      "\tmolregno BIGINT NOT NULL, \n",
      "\tpref_name VARCHAR(255), \n",
      "\tchembl_id VARCHAR(20) NOT NULL, \n",
      "\tmax_phase NUMERIC(2, 1), \n",
      "\ttherapeutic_flag SMALLINT NOT NULL, \n",
      "\tdosed_ingredient SMALLINT NOT NULL, \n",
      "\tstructure_type VARCHAR(10) NOT NULL, \n",
      "\tchebi_par_id BIGINT, \n",
      "\tmolecule_type VARCHAR(30), \n",
      "\tfirst_approval INTEGER, \n",
      "\toral SMALLINT NOT NULL, \n",
      "\tparenteral SMALLINT NOT NULL, \n",
      "\ttopical SMALLINT NOT NULL, \n",
      "\tblack_box_warning SMALLINT NOT NULL, \n",
      "\tfirst_in_class SMALLINT NOT NULL, \n",
      "\tchirality SMALLINT NOT NULL, \n",
      "\tprodrug SMALLINT NOT NULL, \n",
      "\tinorganic_flag SMALLINT NOT NULL, \n",
      "\tusan_year INTEGER, \n",
      "\tavailability_type SMALLINT, \n",
      "\tusan_stem VARCHAR(50), \n",
      "\tpolymer_flag SMALLINT, \n",
      "\tusan_substem VARCHAR(50), \n",
      "\tusan_stem_definition VARCHAR(1000), \n",
      "\tindication_class VARCHAR(1000), \n",
      "\twithdrawn_flag SMALLINT NOT NULL, \n",
      "\tchemical_probe SMALLINT NOT NULL, \n",
      "\tnatural_product SMALLINT, \n",
      "\tCONSTRAINT pk_moldict_molregno PRIMARY KEY (molregno), \n",
      "\tCONSTRAINT fk_moldict_chembl_id FOREIGN KEY(chembl_id) REFERENCES chembl_id_lookup (chembl_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT uk_moldict_chemblid UNIQUE (chembl_id), \n",
      "\tCONSTRAINT ck_moldict_app CHECK (first_approval < 2050 and first_approval > 1900), \n",
      "\tCONSTRAINT ck_moldict_bbw CHECK (black_box_warning in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_chi CHECK (chirality in (-1,0,1,2)), \n",
      "\tCONSTRAINT ck_moldict_dosed CHECK (dosed_ingredient in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_fic CHECK (first_in_class in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_inor CHECK (inorganic_flag in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_oral CHECK (oral in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_par CHECK (parenteral in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_polyflag CHECK (polymer_flag IN (0, 1, null)), \n",
      "\tCONSTRAINT ck_moldict_pro CHECK (prodrug in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_strtype CHECK (structure_type in ('NONE','MOL','SEQ','BOTH')), \n",
      "\tCONSTRAINT ck_moldict_theraflag CHECK (therapeutic_flag IN (0, 1)), \n",
      "\tCONSTRAINT ck_moldict_top CHECK (topical in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_usanyear CHECK (usan_year > 1900 and usan_year < 2050), \n",
      "\tCONSTRAINT ck_moldict_withd CHECK (WITHDRAWN_FLAG in (0,1))\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from molecule_dictionary table:\n",
      "molregno\tpref_name\tchembl_id\tmax_phase\ttherapeutic_flag\tdosed_ingredient\tstructure_type\tchebi_par_id\tmolecule_type\tfirst_approval\toral\tparenteral\ttopical\tblack_box_warning\tfirst_in_class\tchirality\tprodrug\tinorganic_flag\tusan_year\tavailability_type\tusan_stem\tpolymer_flag\tusan_substem\tusan_stem_definition\tindication_class\twithdrawn_flag\tchemical_probe\tnatural_product\n",
      "1\tNone\tCHEMBL6329\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "2\tNone\tCHEMBL6328\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "3\tNone\tCHEMBL265667\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "*/\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mI can query the \"molecule_dictionary\" table to get the number of SMILES codes.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT COUNT(*) FROM molecule_dictionary\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(2399743,)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe number of SMILES codes in the database is 2,399,743.\n",
      "Final Answer: 2,399,743\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'2,399,743'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "question = \"How many SMILES codes are?\"\n",
    "agent_executor.run(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5c3a260a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:12.343072Z",
     "iopub.status.busy": "2023-10-29T12:48:12.342772Z",
     "iopub.status.idle": "2023-10-29T12:48:16.396462Z",
     "shell.execute_reply": "2023-10-29T12:48:16.395563Z"
    },
    "papermill": {
     "duration": 4.060795,
     "end_time": "2023-10-29T12:48:16.398246",
     "exception": false,
     "start_time": "2023-10-29T12:48:12.337451",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3maction_type, activities, activity_properties, activity_smid, activity_stds_lookup, activity_supp, activity_supp_map, assay_class_map, assay_classification, assay_parameters, assay_type, assays, atc_classification, binding_sites, bio_component_sequences, bioassay_ontology, biotherapeutic_components, biotherapeutics, cell_dictionary, chembl_id_lookup, chembl_release, component_class, component_domains, component_go, component_sequences, component_synonyms, compound_properties, compound_records, compound_structural_alerts, compound_structures, confidence_score_lookup, curation_lookup, data_validity_lookup, defined_daily_dose, docs, domains, drug_indication, drug_mechanism, drug_warning, formulations, frac_classification, go_classification, hrac_classification, indication_refs, irac_classification, ligand_eff, mechanism_refs, metabolism, metabolism_refs, molecule_atc_classification, molecule_dictionary, molecule_frac_classification, molecule_hierarchy, molecule_hrac_classification, molecule_irac_classification, molecule_synonyms, organism_class, patent_use_codes, predicted_binding_domains, product_patents, products, protein_class_synonyms, protein_classification, relationship_type, research_companies, research_stem, site_components, source, structural_alert_sets, structural_alerts, target_components, target_dictionary, target_relations, target_type, tissue_dictionary, usan_stems, variant_sequences, version, warning_refs\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThere are many tables in the database, but the most relevant ones for this question seem to be \"activities\" and \"molecule_dictionary\". I should query the schema of these tables to see what columns I can use to find the active molecules for Parkinson.\n",
      "\n",
      "Action: sql_db_schema\n",
      "Action Input: \"activities, molecule_dictionary\"\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE activities (\n",
      "\tactivity_id BIGINT NOT NULL, \n",
      "\tassay_id BIGINT NOT NULL, \n",
      "\tdoc_id BIGINT, \n",
      "\trecord_id BIGINT NOT NULL, \n",
      "\tmolregno BIGINT, \n",
      "\tstandard_relation VARCHAR(50), \n",
      "\tstandard_value NUMERIC, \n",
      "\tstandard_units VARCHAR(100), \n",
      "\tstandard_flag SMALLINT, \n",
      "\tstandard_type VARCHAR(250), \n",
      "\tactivity_comment VARCHAR(4000), \n",
      "\tdata_validity_comment VARCHAR(30), \n",
      "\tpotential_duplicate SMALLINT, \n",
      "\tpchembl_value NUMERIC(4, 2), \n",
      "\tbao_endpoint VARCHAR(11), \n",
      "\tuo_units VARCHAR(10), \n",
      "\tqudt_units VARCHAR(70), \n",
      "\ttoid INTEGER, \n",
      "\tupper_value NUMERIC, \n",
      "\tstandard_upper_value NUMERIC, \n",
      "\tsrc_id INTEGER, \n",
      "\ttype VARCHAR(250) NOT NULL, \n",
      "\trelation VARCHAR(50), \n",
      "\tvalue NUMERIC, \n",
      "\tunits VARCHAR(100), \n",
      "\ttext_value VARCHAR(1000), \n",
      "\tstandard_text_value VARCHAR(1000), \n",
      "\taction_type VARCHAR(50), \n",
      "\tCONSTRAINT pk_act_activity_id PRIMARY KEY (activity_id), \n",
      "\tCONSTRAINT fk_act_action_type FOREIGN KEY(action_type) REFERENCES action_type (action_type) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_assay_id FOREIGN KEY(assay_id) REFERENCES assays (assay_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_bao_endpoint FOREIGN KEY(bao_endpoint) REFERENCES bioassay_ontology (bao_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_doc_id FOREIGN KEY(doc_id) REFERENCES docs (doc_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_molregno FOREIGN KEY(molregno) REFERENCES molecule_dictionary (molregno) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_record_id FOREIGN KEY(record_id) REFERENCES compound_records (record_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_act_src_id FOREIGN KEY(src_id) REFERENCES source (src_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT fk_data_val_comm FOREIGN KEY(data_validity_comment) REFERENCES data_validity_lookup (data_validity_comment) ON DELETE CASCADE, \n",
      "\tCONSTRAINT ck_potential_dup CHECK (POTENTIAL_DUPLICATE IN (0,1)), \n",
      "\tCONSTRAINT ck_stand_flag CHECK (standard_flag in (0,1)), \n",
      "\tCONSTRAINT ck_stand_relation CHECK (standard_relation in ('>','<','=','~','<=','>=','<<','>>'))\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from activities table:\n",
      "activity_id\tassay_id\tdoc_id\trecord_id\tmolregno\tstandard_relation\tstandard_value\tstandard_units\tstandard_flag\tstandard_type\tactivity_comment\tdata_validity_comment\tpotential_duplicate\tpchembl_value\tbao_endpoint\tuo_units\tqudt_units\ttoid\tupper_value\tstandard_upper_value\tsrc_id\ttype\trelation\tvalue\tunits\ttext_value\tstandard_text_value\taction_type\n",
      "31863\t54505\t6424\t206172\t180094\t>\t100000.0000000000\tnM\t1\tIC50\tNone\tNone\t0\tNone\tBAO_0000190\tUO_0000065\thttp://www.openphacts.org/units/Nanomolar\tNone\tNone\tNone\t1\tIC50\t>\t100.0000000000\tuM\tNone\tNone\tNone\n",
      "31864\t83907\t6432\t208970\t182268\t=\t2500.0000000000\tnM\t1\tIC50\tNone\tNone\t1\t5.60\tBAO_0000190\tUO_0000065\thttp://www.openphacts.org/units/Nanomolar\tNone\tNone\tNone\t1\tIC50\t=\t2.5000000000\tuM\tNone\tNone\tNone\n",
      "31865\t88152\t6432\t208970\t182268\t>\t50000.0000000000\tnM\t1\tIC50\tNone\tNone\t0\tNone\tBAO_0000190\tUO_0000065\thttp://www.openphacts.org/units/Nanomolar\tNone\tNone\tNone\t1\tIC50\t>\t50.0000000000\tuM\tNone\tNone\tNone\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE molecule_dictionary (\n",
      "\tmolregno BIGINT NOT NULL, \n",
      "\tpref_name VARCHAR(255), \n",
      "\tchembl_id VARCHAR(20) NOT NULL, \n",
      "\tmax_phase NUMERIC(2, 1), \n",
      "\ttherapeutic_flag SMALLINT NOT NULL, \n",
      "\tdosed_ingredient SMALLINT NOT NULL, \n",
      "\tstructure_type VARCHAR(10) NOT NULL, \n",
      "\tchebi_par_id BIGINT, \n",
      "\tmolecule_type VARCHAR(30), \n",
      "\tfirst_approval INTEGER, \n",
      "\toral SMALLINT NOT NULL, \n",
      "\tparenteral SMALLINT NOT NULL, \n",
      "\ttopical SMALLINT NOT NULL, \n",
      "\tblack_box_warning SMALLINT NOT NULL, \n",
      "\tfirst_in_class SMALLINT NOT NULL, \n",
      "\tchirality SMALLINT NOT NULL, \n",
      "\tprodrug SMALLINT NOT NULL, \n",
      "\tinorganic_flag SMALLINT NOT NULL, \n",
      "\tusan_year INTEGER, \n",
      "\tavailability_type SMALLINT, \n",
      "\tusan_stem VARCHAR(50), \n",
      "\tpolymer_flag SMALLINT, \n",
      "\tusan_substem VARCHAR(50), \n",
      "\tusan_stem_definition VARCHAR(1000), \n",
      "\tindication_class VARCHAR(1000), \n",
      "\twithdrawn_flag SMALLINT NOT NULL, \n",
      "\tchemical_probe SMALLINT NOT NULL, \n",
      "\tnatural_product SMALLINT, \n",
      "\tCONSTRAINT pk_moldict_molregno PRIMARY KEY (molregno), \n",
      "\tCONSTRAINT fk_moldict_chembl_id FOREIGN KEY(chembl_id) REFERENCES chembl_id_lookup (chembl_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT uk_moldict_chemblid UNIQUE (chembl_id), \n",
      "\tCONSTRAINT ck_moldict_app CHECK (first_approval < 2050 and first_approval > 1900), \n",
      "\tCONSTRAINT ck_moldict_bbw CHECK (black_box_warning in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_chi CHECK (chirality in (-1,0,1,2)), \n",
      "\tCONSTRAINT ck_moldict_dosed CHECK (dosed_ingredient in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_fic CHECK (first_in_class in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_inor CHECK (inorganic_flag in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_oral CHECK (oral in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_par CHECK (parenteral in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_polyflag CHECK (polymer_flag IN (0, 1, null)), \n",
      "\tCONSTRAINT ck_moldict_pro CHECK (prodrug in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_strtype CHECK (structure_type in ('NONE','MOL','SEQ','BOTH')), \n",
      "\tCONSTRAINT ck_moldict_theraflag CHECK (therapeutic_flag IN (0, 1)), \n",
      "\tCONSTRAINT ck_moldict_top CHECK (topical in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_usanyear CHECK (usan_year > 1900 and usan_year < 2050), \n",
      "\tCONSTRAINT ck_moldict_withd CHECK (WITHDRAWN_FLAG in (0,1))\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from molecule_dictionary table:\n",
      "molregno\tpref_name\tchembl_id\tmax_phase\ttherapeutic_flag\tdosed_ingredient\tstructure_type\tchebi_par_id\tmolecule_type\tfirst_approval\toral\tparenteral\ttopical\tblack_box_warning\tfirst_in_class\tchirality\tprodrug\tinorganic_flag\tusan_year\tavailability_type\tusan_stem\tpolymer_flag\tusan_substem\tusan_stem_definition\tindication_class\twithdrawn_flag\tchemical_probe\tnatural_product\n",
      "1\tNone\tCHEMBL6329\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "2\tNone\tCHEMBL6328\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "3\tNone\tCHEMBL265667\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "*/\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe relevant columns in the \"activities\" table are \"molregno\" and \"standard_value\". The relevant columns in the \"molecule_dictionary\" table are \"molregno\" and \"pref_name\". I can join these two tables on the \"molregno\" column to get the molecules that are active for Parkinson.\n",
      "\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT md.pref_name, a.standard_value FROM activities a JOIN molecule_dictionary md ON a.molregno = md.molregno WHERE md.pref_name IS NOT NULL AND a.standard_value IS NOT NULL AND md.pref_name != '' AND a.standard_value != '' AND md.indication_class LIKE '%Parkinson%' LIMIT 3\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[('ROPINIROLE', 7.2), ('ROPINIROLE', 19), ('ROPINIROLE', 1706)]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe query returned 3 molecules that are active for Parkinson: ROPINIROLE with standard values of 7.2, 19, and 1706.\n",
      "\n",
      "Final Answer: The 3 molecules that are active for Parkinson are ROPINIROLE with standard values of 7.2, 19, and 1706.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'The 3 molecules that are active for Parkinson are ROPINIROLE with standard values of 7.2, 19, and 1706.'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "question = \"List 3 molecules actives for Parkinson\"\n",
    "agent_executor.run(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "bacb8dce",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-10-29T12:48:16.409257Z",
     "iopub.status.busy": "2023-10-29T12:48:16.408976Z",
     "iopub.status.idle": "2023-10-29T12:48:20.412753Z",
     "shell.execute_reply": "2023-10-29T12:48:20.411551Z"
    },
    "papermill": {
     "duration": 4.011725,
     "end_time": "2023-10-29T12:48:20.414871",
     "exception": false,
     "start_time": "2023-10-29T12:48:16.403146",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
      "Action Input: \"\"\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3maction_type, activities, activity_properties, activity_smid, activity_stds_lookup, activity_supp, activity_supp_map, assay_class_map, assay_classification, assay_parameters, assay_type, assays, atc_classification, binding_sites, bio_component_sequences, bioassay_ontology, biotherapeutic_components, biotherapeutics, cell_dictionary, chembl_id_lookup, chembl_release, component_class, component_domains, component_go, component_sequences, component_synonyms, compound_properties, compound_records, compound_structural_alerts, compound_structures, confidence_score_lookup, curation_lookup, data_validity_lookup, defined_daily_dose, docs, domains, drug_indication, drug_mechanism, drug_warning, formulations, frac_classification, go_classification, hrac_classification, indication_refs, irac_classification, ligand_eff, mechanism_refs, metabolism, metabolism_refs, molecule_atc_classification, molecule_dictionary, molecule_frac_classification, molecule_hierarchy, molecule_hrac_classification, molecule_irac_classification, molecule_synonyms, organism_class, patent_use_codes, predicted_binding_domains, product_patents, products, protein_class_synonyms, protein_classification, relationship_type, research_companies, research_stem, site_components, source, structural_alert_sets, structural_alerts, target_components, target_dictionary, target_relations, target_type, tissue_dictionary, usan_stems, variant_sequences, version, warning_refs\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThere are many tables in the database. I should query the schema of the most relevant tables to find the information I need.\n",
      "Action: sql_db_schema\n",
      "Action Input: \"drug_indication, molecule_dictionary\"\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE drug_indication (\n",
      "\tdrugind_id BIGINT NOT NULL, \n",
      "\trecord_id BIGINT NOT NULL, \n",
      "\tmolregno BIGINT, \n",
      "\tmax_phase_for_ind NUMERIC(2, 1), \n",
      "\tmesh_id VARCHAR(20) NOT NULL, \n",
      "\tmesh_heading VARCHAR(200) NOT NULL, \n",
      "\tefo_id VARCHAR(20), \n",
      "\tefo_term VARCHAR(200), \n",
      "\tCONSTRAINT drugind_pk PRIMARY KEY (drugind_id), \n",
      "\tCONSTRAINT drugind_molregno_fk FOREIGN KEY(molregno) REFERENCES molecule_dictionary (molregno) ON DELETE CASCADE, \n",
      "\tCONSTRAINT drugind_rec_fk FOREIGN KEY(record_id) REFERENCES compound_records (record_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT drugind_uk UNIQUE (record_id, mesh_id, efo_id)\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from drug_indication table:\n",
      "drugind_id\trecord_id\tmolregno\tmax_phase_for_ind\tmesh_id\tmesh_heading\tefo_id\tefo_term\n",
      "22606\t1390869\t675774\t2.0\tD045743\tScleroderma, Diffuse\tEFO:0000404\tdiffuse scleroderma\n",
      "22607\t1390869\t675774\t4.0\tD001172\tArthritis, Rheumatoid\tEFO:0000685\trheumatoid arthritis\n",
      "22608\t1390869\t675774\t2.0\tD008180\tLupus Erythematosus, Systemic\tEFO:0002690\tsystemic lupus erythematosus\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE molecule_dictionary (\n",
      "\tmolregno BIGINT NOT NULL, \n",
      "\tpref_name VARCHAR(255), \n",
      "\tchembl_id VARCHAR(20) NOT NULL, \n",
      "\tmax_phase NUMERIC(2, 1), \n",
      "\ttherapeutic_flag SMALLINT NOT NULL, \n",
      "\tdosed_ingredient SMALLINT NOT NULL, \n",
      "\tstructure_type VARCHAR(10) NOT NULL, \n",
      "\tchebi_par_id BIGINT, \n",
      "\tmolecule_type VARCHAR(30), \n",
      "\tfirst_approval INTEGER, \n",
      "\toral SMALLINT NOT NULL, \n",
      "\tparenteral SMALLINT NOT NULL, \n",
      "\ttopical SMALLINT NOT NULL, \n",
      "\tblack_box_warning SMALLINT NOT NULL, \n",
      "\tfirst_in_class SMALLINT NOT NULL, \n",
      "\tchirality SMALLINT NOT NULL, \n",
      "\tprodrug SMALLINT NOT NULL, \n",
      "\tinorganic_flag SMALLINT NOT NULL, \n",
      "\tusan_year INTEGER, \n",
      "\tavailability_type SMALLINT, \n",
      "\tusan_stem VARCHAR(50), \n",
      "\tpolymer_flag SMALLINT, \n",
      "\tusan_substem VARCHAR(50), \n",
      "\tusan_stem_definition VARCHAR(1000), \n",
      "\tindication_class VARCHAR(1000), \n",
      "\twithdrawn_flag SMALLINT NOT NULL, \n",
      "\tchemical_probe SMALLINT NOT NULL, \n",
      "\tnatural_product SMALLINT, \n",
      "\tCONSTRAINT pk_moldict_molregno PRIMARY KEY (molregno), \n",
      "\tCONSTRAINT fk_moldict_chembl_id FOREIGN KEY(chembl_id) REFERENCES chembl_id_lookup (chembl_id) ON DELETE CASCADE, \n",
      "\tCONSTRAINT uk_moldict_chemblid UNIQUE (chembl_id), \n",
      "\tCONSTRAINT ck_moldict_app CHECK (first_approval < 2050 and first_approval > 1900), \n",
      "\tCONSTRAINT ck_moldict_bbw CHECK (black_box_warning in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_chi CHECK (chirality in (-1,0,1,2)), \n",
      "\tCONSTRAINT ck_moldict_dosed CHECK (dosed_ingredient in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_fic CHECK (first_in_class in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_inor CHECK (inorganic_flag in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_oral CHECK (oral in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_par CHECK (parenteral in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_polyflag CHECK (polymer_flag IN (0, 1, null)), \n",
      "\tCONSTRAINT ck_moldict_pro CHECK (prodrug in (-1,0,1)), \n",
      "\tCONSTRAINT ck_moldict_strtype CHECK (structure_type in ('NONE','MOL','SEQ','BOTH')), \n",
      "\tCONSTRAINT ck_moldict_theraflag CHECK (therapeutic_flag IN (0, 1)), \n",
      "\tCONSTRAINT ck_moldict_top CHECK (topical in (0,1)), \n",
      "\tCONSTRAINT ck_moldict_usanyear CHECK (usan_year > 1900 and usan_year < 2050), \n",
      "\tCONSTRAINT ck_moldict_withd CHECK (WITHDRAWN_FLAG in (0,1))\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from molecule_dictionary table:\n",
      "molregno\tpref_name\tchembl_id\tmax_phase\ttherapeutic_flag\tdosed_ingredient\tstructure_type\tchebi_par_id\tmolecule_type\tfirst_approval\toral\tparenteral\ttopical\tblack_box_warning\tfirst_in_class\tchirality\tprodrug\tinorganic_flag\tusan_year\tavailability_type\tusan_stem\tpolymer_flag\tusan_substem\tusan_stem_definition\tindication_class\twithdrawn_flag\tchemical_probe\tnatural_product\n",
      "1\tNone\tCHEMBL6329\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "2\tNone\tCHEMBL6328\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "3\tNone\tCHEMBL265667\tNone\t0\t0\tMOL\tNone\tSmall molecule\tNone\t0\t0\t0\t0\t-1\t-1\t-1\t-1\tNone\t-1\tNone\t0\tNone\tNone\tNone\t0\t0\t0\n",
      "*/\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe relevant tables for this question are \"drug_indication\" and \"molecule_dictionary\". I can query the \"drugind_id\", \"mesh_heading\", and \"efo_term\" columns from the \"drug_indication\" table and the \"pref_name\" column from the \"molecule_dictionary\" table.\n",
      "Action: sql_db_query\n",
      "Action Input: \"SELECT drugind_id, mesh_heading, efo_term FROM drug_indication WHERE molregno IN (SELECT molregno FROM molecule_dictionary WHERE pref_name = 'ROPINIROLE') LIMIT 10\"\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m[(48340, 'Restless Legs Syndrome', 'restless legs syndrome'), (55629, 'Stroke', 'stroke'), (55630, 'Cocaine-Related Disorders', 'cocaine dependence'), (55631, 'Fibromyalgia', 'fibromyalgia'), (55632, 'Hyperprolactinemia', 'hyperprolactinemia'), (136841, 'Parkinson Disease', 'Parkinson disease'), (136842, 'Unverricht-Lundborg Syndrome', 'Unverricht-Lundborg syndrome')]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe active disease for ROPINIROLE is Restless Legs Syndrome, Stroke, Cocaine-Related Disorders, Fibromyalgia, Hyperprolactinemia, Parkinson Disease, and Unverricht-Lundborg Syndrome.\n",
      "Final Answer: Restless Legs Syndrome, Stroke, Cocaine-Related Disorders, Fibromyalgia, Hyperprolactinemia, Parkinson Disease, and Unverricht-Lundborg Syndrome.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Restless Legs Syndrome, Stroke, Cocaine-Related Disorders, Fibromyalgia, Hyperprolactinemia, Parkinson Disease, and Unverricht-Lundborg Syndrome.'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "question = \"For what disease is active ROPINIROLE?\"\n",
    "agent_executor.run(question)"
   ]
  }
 ],
 "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.10.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 59.660126,
   "end_time": "2023-10-29T12:48:21.040413",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2023-10-29T12:47:21.380287",
   "version": "2.4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}