08a-codes_query_execution_and_selection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CodeS Query Execution\n",
    "The purpose of this notebook is to execute all of the predicted CodeS queries in order to emulate the CodeS method of testing multiple inferences against a single NL question and selecting the first one that does not throw some sort of SQL engine error.\n",
    "\n",
    "We have to do this with our own implementation because the benchmarks in the CodeS code base use Sqlite databases, whereas SNAILS uses MS SQL Server databases.\n",
    "\n",
    "#### Prerequisites:\n",
    "You must download and unzip the SNAILS Sqlite instances into the ```db/snails_sqlite``` folder using the link in ```db/snails_sqlite/snails_sqlite_download_link.txt```.\n",
    "\n",
    "#### License\n",
    "```\n",
    "Copyright 2024 Kyle Luoma\n",
    "\n",
    "Licensed under the Apache License, Version 2.0 (the \"License\");\n",
    "you may not use this file except in compliance with the License.\n",
    "You may obtain a copy of the License at\n",
    "\n",
    "    http://www.apache.org/licenses/LICENSE-2.0\n",
    "\n",
    "Unless required by applicable law or agreed to in writing, software\n",
    "distributed under the License is distributed on an \"AS IS\" BASIS,\n",
    "WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
    "See the License for the specific language governing permissions and\n",
    "limitations under the License.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/kyle/SNAILS/venv/lib/python3.12/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html\n",
      "  from .autonotebook import tqdm as notebook_tqdm\n"
     ]
    }
   ],
   "source": [
    "import multiprocessing as mp\n",
    "mp.set_start_method(\"spawn\")\n",
    "import src.util.db_util_sqlite as db_util_sqlite\n",
    "import pandas as pd\n",
    "import src.nl_to_sql_inference_and_prompt_generation as pgn\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "NUM_PROCESSES = 32\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "nat_levels = [\"Native\", \"Regular\", \"Low\", \"Least\"]\n",
    "nat_label_lookup = {\n",
    "    \"Native\": \"NATIVE\",\n",
    "    \"Regular\": \"N1\",\n",
    "    \"Low\": \"N2\",\n",
    "    \"Least\": \"N3\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mp_denat_f(query_data: tuple) -> tuple:\n",
    "    db = query_data[0]\n",
    "    q_num = query_data[1]\n",
    "    q_num_ix = query_data[2]\n",
    "    query = query_data[3]\n",
    "    nat_level = query_data[4]\n",
    "    denat_query = pgn.denaturalize_query(\n",
    "        query=query,\n",
    "        naturalness={\n",
    "                    \"table\": nat_level,\n",
    "                    \"column\": nat_level\n",
    "                },\n",
    "        db_name=db.split(\"-\")[0],\n",
    "        syntax=\"sqlite\"\n",
    "    )\n",
    "    return (db, q_num, q_num_ix, denat_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SBODemoUS-Service\n",
      "Running mp_denat_f for nat_level Regular\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 40/40 [00:38<00:00,  1.05it/s]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished mp_denat_f\n",
      "SBODemoUS-Service\n",
      "Running mp_denat_f for nat_level Low\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 40/40 [00:38<00:00,  1.05it/s]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished mp_denat_f\n",
      "Error: no such column: CONTRACTID\n",
      "Error: no such column: CUSTOMER\n",
      "SBODemoUS-Service\n",
      "Running mp_denat_f for nat_level Least\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 40/40 [00:35<00:00,  1.12it/s]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished mp_denat_f\n",
      "SBODemoUS-Service\n"
     ]
    }
   ],
   "source": [
    "for nat_level in nat_levels:\n",
    "    codes_df = pd.read_excel(f\"./db/queries/codes_predictions/snails_{nat_level}_CodeS_predictions.xlsx\")\n",
    "    codes_df = codes_df.query(\"database=='SBODemoUS-Service'\")\n",
    "    database = []\n",
    "    number = []\n",
    "    question = []\n",
    "    hints = []\n",
    "    notes = []\n",
    "    query_gold = []\n",
    "    schema_pruning = []\n",
    "    prompt = []\n",
    "    query_predicted = []\n",
    "    query_predicted_on_naturalized_schema = []\n",
    "    col_naturalness_modifier = []\n",
    "    tab_naturalness_modifier = []\n",
    "    completed_queries = []\n",
    "\n",
    "    denat_query_lookup = {}\n",
    "    if nat_level != \"Native\":\n",
    "        pool = mp.Pool(processes=NUM_PROCESSES)\n",
    "        mp_query_list = [\n",
    "            (\n",
    "                row.database, \n",
    "                row.number, \n",
    "                row.predicted_sql_ix,\n",
    "                row.query_predicted, \n",
    "                nat_label_lookup[nat_level][1]\n",
    "            ) \n",
    "                for row in codes_df.itertuples()\n",
    "        ]\n",
    "        print(f\"Running mp_denat_f for nat_level {nat_level}\")\n",
    "        # denaturalized_queries = pool.map(\n",
    "        #     mp_denat_f,\n",
    "        #     mp_query_list\n",
    "        #     )\n",
    "        denaturalized_queries = []\n",
    "        for q_tuple in tqdm(mp_query_list):\n",
    "            denaturalized_queries.append(mp_denat_f(q_tuple))\n",
    "        print(\"Finished mp_denat_f\")\n",
    "        \n",
    "        for q in denaturalized_queries:\n",
    "            denat_query_lookup[(q[0], q[1], q[2])] = q[3]\n",
    "\n",
    "    for row in codes_df.itertuples():\n",
    "        db_name = row.database.split(\"-\")[0]\n",
    "        if nat_level == \"Native\":\n",
    "            query = row.query_predicted\n",
    "        else:\n",
    "            query = denat_query_lookup[(row.database, row.number, row.predicted_sql_ix)]\n",
    "        if (row.database, row.number) not in completed_queries:\n",
    "            try:\n",
    "                res_df = db_util_sqlite.do_query(\n",
    "                    query=query,\n",
    "                    database_name=db_name,\n",
    "                    db_list_file=\"./.local/dbinfo_sqlite.json\"\n",
    "                    )\n",
    "            except db_util_sqlite.sqlite3.OperationalError as e:\n",
    "                print(\"Error:\", e)\n",
    "                res_df = None\n",
    "            if type(res_df) == pd.DataFrame or row.predicted_sql_ix == 3:\n",
    "                completed_queries.append((row.database, row.number))\n",
    "                database.append(row.database)\n",
    "                number.append(row.number)\n",
    "                question.append(row.question)\n",
    "                hints.append([])\n",
    "                notes.append([])\n",
    "                query_gold.append(row.query_gold)\n",
    "                schema_pruning.append(1)\n",
    "                prompt.append(\"CodeS prompt\")\n",
    "                query_predicted_on_naturalized_schema.append(row.query_predicted)\n",
    "                query_predicted.append(query)\n",
    "                col_naturalness_modifier.append(nat_label_lookup[row.col_naturalness_modifier])\n",
    "                tab_naturalness_modifier.append(nat_label_lookup[row.tab_naturalness_modifier])\n",
    "    predict_df = pd.DataFrame({\n",
    "        \"database\": database,\n",
    "        \"number\": number,\n",
    "        \"question\": question,\n",
    "        \"hints\": hints,\n",
    "        \"notes\": notes,\n",
    "        \"query_gold\": query_gold,\n",
    "        \"schema_pruning\": schema_pruning,\n",
    "        \"prompt\": prompt,\n",
    "        \"query_predicted_on_naturalized_schema\": query_predicted_on_naturalized_schema,\n",
    "        \"query_predicted\": query_predicted,\n",
    "        \"col_naturalness_modifier\": col_naturalness_modifier,\n",
    "        \"tab_naturalness_modifier\": tab_naturalness_modifier\n",
    "    })\n",
    "    for db_name in predict_df.database.unique():\n",
    "        print(db_name)\n",
    "        predict_df.query(f\"database == '{db_name}'\").to_excel(\n",
    "            f\"./db/queries/predicted/{db_name}-{nat_label_lookup[nat_level]}-{nat_label_lookup[nat_level]}-CodeS-queries-predicted.xlsx\"\n",
    "        )\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "snails_dbs = [\n",
    "    \"ASIS_20161108_HerpInv_Database\",\n",
    "    \"ATBI\",\n",
    "    \"CratersWildlifeObservations\",\n",
    "    \"KlamathInvasiveSpecies\",\n",
    "    \"NorthernPlainsFireManagement\",\n",
    "    \"NTSB\",\n",
    "    \"NYSED_SRC2022\",\n",
    "    \"PacificIslandLandbirds\",\n",
    "    \"SBODemoUS-Banking\",\n",
    "    \"SBODemoUS-Business Partners\",\n",
    "    \"SBODemoUS-Finance\",\n",
    "    \"SBODemoUS-General\",\n",
    "    \"SBODemoUS-Human Resources\",\n",
    "    \"SBODemoUS-Inventory and Production\",\n",
    "    \"SBODemoUS-Reports\",\n",
    "    \"SBODemoUS-Sales Opportunities\",\n",
    "    \"SBODemoUS-Service\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_modifications = {\n",
    "    (\"ATBI\", 7): [\n",
    "        (\"select TOP 1 Event_ID\", \"select Event_ID\"), \n",
    "        (\"count(*) desc\", \"count(*) desc limit 1\")\n",
    "    ],\n",
    "    (\"ATBI\", 9): [\n",
    "        (\"SELECT TOP 1 Event_ID\", \"SELECT Event_ID\"),\n",
    "        (\"order by count(*)\", \"order by count(*) limit 1\")\n",
    "    ],\n",
    "    (\"ATBI\", 36): [\n",
    "        (\"select top 1 decay\", \"select decay\"),\n",
    "        (\"order by MPD asc\", \"order by MPD asc limit 1\")\n",
    "    ],\n",
    "    (\"ATBI\", 37): [\n",
    "        (\"select top 1 decay\", \"select decay\"),\n",
    "        (\"order by Length asc\", \"order by Length asc limit 1\")\n",
    "    ],\n",
    "    (\"KlamathInvasiveSpecies\", 1): [\n",
    "        (\"and YEAR(Start_Date) = 2011\", \"\"),\n",
    "        (\"and MONTH(Start_Date) = 8\", \"\"),\n",
    "        (\"where DAY(Start_Date) = 5\", \"WHERE date(Start_Date) between date('2011-08-05') AND date('2011-08-05')\")\n",
    "    ],\n",
    "    (\"KlamathInvasiveSpecies\", 2): [\n",
    "        (\"and YEAR(Start_Date) = 2011\", \"\"),\n",
    "        (\"and MONTH(Start_Date) = 8\", \"\"),\n",
    "        (\"where DAY(Start_Date) = 5\", \"WHERE date(Start_Date) between date('2011-08-05') AND date('2011-08-05')\")\n",
    "    ],\n",
    "    (\"KlamathInvasiveSpecies\", 8): [\n",
    "        (\n",
    "            \"where year(Start_Date) = 2009 and month(Start_Date) = 7 and day(Start_Date) = 28\",\n",
    "            \"WHERE date(Start_Date) BETWEEN date('2009-07-28') AND date('2009-07-28')\"\n",
    "        ),\n",
    "        (\"top 1 \", \"\"),\n",
    "        (\"order by Elevation desc\", \"order by Elevation desc limit 1\")\n",
    "    ],\n",
    "    (\"KlamathInvasiveSpecies\", 21): [\n",
    "        (\"year(Revision_Date)\", \"strftime('%Y', Revision_Date)\")\n",
    "    ],\n",
    "    (\"KlamathInvasiveSpecies\", 27): [\n",
    "        (\n",
    "            \"where year(Start_Date) = 2021 and month(Start_Date) = 8\", \n",
    "            \"where date(Start_Date) >= date('2021-08-01') and date(Start_Date) <= date('2021-08-31')\"\n",
    "        )\n",
    "    ],\n",
    "    (\"KlamathInvasiveSpecies\", 39): [\n",
    "        (\n",
    "            \"where year(Start_Date) = 2015 and month(Start_Date) = 7\",\n",
    "            \"where date(Start_Date) >= date('2015-07-01') and date(Start_Date) <= date('2015-07-31')\"\n",
    "        )\n",
    "    ],\n",
    "    (\"NorthernPlainsFireManagement\", 1): [\n",
    "        (\"year(Event_Date)\", \"strftime('%Y', Event_Date)\")\n",
    "    ],\n",
    "    (\"NorthernPlainsFireManagement\", 4): [\n",
    "        (\"year(event_date) = 2001\", \"strftime('%Y', Event_Date) = '2001'\")\n",
    "    ],\n",
    "    (\"NorthernPlainsFireManagement\", 24): [\n",
    "        (\"where year(release_date) = 2015\", \"where date(release_date) >= date('2015-01-01') AND date(release_date) <= date('2015-12-31')\")\n",
    "    ],\n",
    "    (\"NorthernPlainsFireManagement\", 26): [\n",
    "        (\"year(Event_Date) = 1999\", \"strftime('%Y', Event_Date) = '1999'\")\n",
    "    ],\n",
    "    (\"NorthernPlainsFireManagement\", 27): [\n",
    "        (\"month(e.Event_Date)\", \"strftime('%m', e.Event_Date)\")\n",
    "    ],\n",
    "    (\"NorthernPlainsFireManagement\", 35): [\n",
    "        (\"month(Event_Date)\", \"strftime('%m', Event_Date)\"),\n",
    "        (\"top 1\", \"\"),\n",
    "        (\"desc\", \"desc limit 1\")\n",
    "    ],\n",
    "    (\"NTSB\", 71): [\n",
    "        (\"ORDER BY CASEID\", \"ORDER BY EV.CASEID\")\n",
    "    ],\n",
    "    (\"NYSED_SRC2022\", 12): [\n",
    "        (\"where not exists \", \"where p.ENTITY_CD NOT IN\"),\n",
    "        (\"and p.ENTITY_CD = q.ENTITY_CD\", \"\")\n",
    "    ],\n",
    "    (\"SBODemoUS-Banking\", 5): [\n",
    "        (\"year(CreateDate) = 2012\", \"date(CreateDate) >= date('2012-01-01' and date(CreateDate) <= date('2012-12-31'))\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Banking\", 9): [\n",
    "        (\"where DeposDate = '2012-01-31'\", \"where date(DeposDate) = '2012-01-31'\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Business Partners\", 7): [\n",
    "        (\"Year(CreateDate) = '2021'\", \"date(CreateDate) >= date('2021-01-01' and date(CreateDate) <= date('2021-12-31'))\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Finance\", 2): [\n",
    "        (\"year(DueDate) = '2021'\", \"date(DueDate) >= date('2021-01-01' and date(DueDate) <= date('2021-12-31'))\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Finance\", 6): [\n",
    "        (\"year(RateDate)\", \"strftime('%Y', RateDate)\"),\n",
    "        (\"top 1 \", \"\"),\n",
    "        (\"desc\", \"desc limit 1\")\n",
    "    ],\n",
    "    (\"SBODemoUS-Finance\", 7): [\n",
    "        (\"year(NextDeu)\", \"strftime('%Y', NextDeu)\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Finance\", 10): [\n",
    "        (\"year(ValidFrom) > 2020\", \"date(ValidFrom) > date('2020-12-31')\") \n",
    "    ],\n",
    "    (\"SBODemoUS-General\", 1): [\n",
    "        (\"year < '2021-10-20'\", \"date(START) < date('2021-10-20')\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Sales Opportunities\", 4): [\n",
    "        (\"year(CloseDate) = 2014\", \"date(CloseDate) >= date('2014-01-01' and date(CloseDate) <= date('2014-12-31'))\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Sales Opportunities\", 5): [\n",
    "        (\"year(ValidFrom) = 2021\", \"date(ValidFrom) >= date('2021-01-01' and date(ValidFrom) <= date('2021-12-31'))\") \n",
    "    ],\n",
    "    (\"SBODemoUS-Sales Opportunities\", 10): [\n",
    "        (\"year(CreateDate)\", \"strftime('%Y', CreateDate)\") \n",
    "    ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify gold queries for Sqlite compatibility"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "snails_dbs = [\n",
    "    \"ASIS_20161108_HerpInv_Database\",\n",
    "    \"ATBI\",\n",
    "    \"CratersWildlifeObservations\",\n",
    "    \"KlamathInvasiveSpecies\",\n",
    "    \"NorthernPlainsFireManagement\",\n",
    "    \"NTSB\",\n",
    "    \"NYSED_SRC2022\",\n",
    "    \"PacificIslandLandbirds\",\n",
    "    \"SBODemoUS-Banking\",\n",
    "    \"SBODemoUS-Business Partners\",\n",
    "    \"SBODemoUS-Finance\",\n",
    "    \"SBODemoUS-General\",\n",
    "    \"SBODemoUS-Human Resources\",\n",
    "    \"SBODemoUS-Inventory and Production\",\n",
    "    \"SBODemoUS-Reports\",\n",
    "    \"SBODemoUS-Sales Opportunities\",\n",
    "    \"SBODemoUS-Service\"\n",
    "]\n",
    "for db_name in snails_dbs:\n",
    "    for nat_level in [\"NATIVE\", \"N1\", \"N2\", \"N3\"]:\n",
    "        sql_file_text = \"\"\n",
    "        limit_queries = []\n",
    "        pred_df = pd.read_excel(f\"./db/queries/predicted/archive/codes_with_top_in_gold/{db_name}-{nat_level}-{nat_level}-CodeS-queries-predicted.xlsx\")\n",
    "        for row in pred_df.itertuples():\n",
    "            sql_file_text += f\"-- {row.number}: {row.question}\\n\"\n",
    "            if \"top \" in row.query_gold.lower() and (row.database, row.number) not in query_modifications:\n",
    "                top_num = row.query_gold.lower().split(\"top\")[1].split()[0]\n",
    "                limit_query = row.query_gold.replace(f\"top {top_num}\", \"\")\n",
    "                limit_query = limit_query.replace(f\"TOP {top_num}\", \"\")\n",
    "                limit_query += f\"limit {top_num}\"\n",
    "                limit_queries.append(limit_query)\n",
    "                sql_file_text += f\"{limit_query}\\n;\\n\\n\"\n",
    "            elif (row.database, row.number) in query_modifications:\n",
    "                limit_query = row.query_gold\n",
    "                for repl_tuple in query_modifications[(row.database, row.number)]:\n",
    "                    limit_query = limit_query.replace(repl_tuple[0], repl_tuple[1])\n",
    "                limit_queries.append(limit_query)\n",
    "                sql_file_text += f\"{limit_query}\\n;\\n\\n\"\n",
    "            else:\n",
    "                limit_queries.append(row.query_gold)\n",
    "                sql_file_text += f\"{row.query_gold}\\n;\\n\\n\"\n",
    "        pred_df[\"query_gold\"] = limit_queries\n",
    "        pred_df[\"notes\"] = [\"Gold query modified from top to limit for sqlite compatibility\" for i in range(0, pred_df.shape[0])]\n",
    "        pred_df.to_excel(f\"./db/queries/predicted/{db_name}-{nat_level}-{nat_level}-CodeS-queries-predicted.xlsx\")\n",
    "        with open(f\"./db/queries/snails_sqlite_queries/{db_name}_Native.sql\", \"w\") as f:\n",
    "            f.write(sql_file_text)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Sqlite-compatible gold queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SBODemoUS-Human Resources 2 Has zero-length result!!!\n",
      "select StrDate, EndDate, Rmrks from HLD1 where StrDate >= '20140101 00:00:00.000' \tand EndDate <= '20141231 23:59:59.000' \n",
      "SBODemoUS-Human Resources 11 Has zero-length result!!!\n",
      "select teamID from HTM1  join OHEM on HTM1.empID = OHEM.empID where CPF = '51B' \n",
      "SBODemoUS-Human Resources 16 Has zero-length result!!!\n",
      "select firstName, lastName, homeCity, workCity from OHEM where emplCost > 4000 \n",
      "SBODemoUS-Human Resources 19 Has zero-length result!!!\n",
      "select pymmeth, exemptcurr, addicurr  from OHEM where TaxOName = 'baltimore' \n"
     ]
    }
   ],
   "source": [
    "import src.util.db_util_sqlite as db_util_sqlite\n",
    "import pandas as pd\n",
    "snails_dbs = [\n",
    "    \"ASIS_20161108_HerpInv_Database\",\n",
    "    \"ATBI\",\n",
    "    \"CratersWildlifeObservations\",\n",
    "    \"KlamathInvasiveSpecies\",\n",
    "    \"NorthernPlainsFireManagement\",\n",
    "    \"NTSB\",\n",
    "    \"NYSED_SRC2022\",\n",
    "    \"PacificIslandLandbirds\",\n",
    "    \"SBODemoUS-Banking\",\n",
    "    \"SBODemoUS-Business Partners\",\n",
    "    \"SBODemoUS-Finance\",\n",
    "    \"SBODemoUS-General\",\n",
    "    \"SBODemoUS-Human Resources\",\n",
    "    \"SBODemoUS-Inventory and Production\",\n",
    "    \"SBODemoUS-Reports\",\n",
    "    \"SBODemoUS-Sales Opportunities\",\n",
    "    \"SBODemoUS-Service\"\n",
    "]\n",
    "for db_name in snails_dbs:\n",
    "    for nat_level in [\"NATIVE\"]:\n",
    "        limit_queries = []\n",
    "        pred_df = pd.read_excel(f\"./db/queries/predicted/{db_name}-{nat_level}-{nat_level}-CodeS-queries-predicted.xlsx\")\n",
    "        for row in pred_df.itertuples():\n",
    "            # print(nat_level, row.database, row.number, row.query_gold)\n",
    "            res = db_util_sqlite.do_query(\n",
    "                row.query_gold,\n",
    "                row.database.split(\"-\")[0],\n",
    "                db_list_file=\"./.local/dbinfo_sqlite.json\"\n",
    "            )\n",
    "            if res.shape[0] == 0:\n",
    "                print(row.database, row.number, \"Has zero-length result!!!\")\n",
    "                print(row.query_gold)\n",
    "            "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}