valider.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Excel文件已成功转换为CSV文件: mobile_dataset/raw.csv\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取Excel文件\n",
    "excel_file = 'mobile_dataset/raw.xlsx'  # 替换为你的Excel文件路径\n",
    "df = pd.read_excel(excel_file, engine='openpyxl')  # 使用openpyxl引擎读取Excel文件\n",
    "\n",
    "# 将DataFrame转换为CSV文件\n",
    "csv_file = 'mobile_dataset/raw.csv'  # 输出CSV文件的路径\n",
    "df.to_csv(csv_file, index=False)  # 将DataFrame写入CSV文件，不包含索引列\n",
    "\n",
    "print(f'Excel文件已成功转换为CSV文件: {csv_file}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from constants import DATABASE_PATH_PATTERN\n",
    "import util\n",
    "import prompt\n",
    "\n",
    "VALID_INSTRUCTION = \"\"\"\n",
    "Given [Database Schema] and [Foreign Keys], your task is to write a [SQL Query] to answer the [Question].\n",
    "\n",
    "[Database Schema] Every table consists of several columns. Each line describes the column name, column type and optional value examples.\n",
    "{schema}\n",
    "[Foreign keys]\n",
    "{foreign_key}\n",
    "[Question]\n",
    "{question}\n",
    "[Constraints] Your [SQL Query] should satisfy the following constraints:\n",
    "- In `SELECT <column>`, only use the column given in the [Database Schema].\n",
    "- In `FROM <table>` or `JOIN <table>`, only use the table given in the [Database Schema].\n",
    "- In `JOIN`, only use the tables and columns in the [Foreign keys].\n",
    "- Without any specific instructions, Use `ASC` for `ORDER BY` by default, \n",
    "- Consider use `DISTINCT` when you need to eliminate duplicates.\n",
    "- The content in quotes is case sensitive.\n",
    "- Prioritize column whose value examples are more relevant to the [Question].\n",
    "\n",
    "[Wrong SQL Query]\n",
    "{wrong_query}\n",
    "[Error Information] Database server return following error information when execute above [Wrong SQL Query], this may help you write the right SQL query.\n",
    "{error_info}\n",
    "[SQL Query] this right sql query is: \n",
    "\"\"\".lstrip()\n",
    "\n",
    "STEP_2_OUTPUT_PATTERN = \"\"\"\n",
    "[SQL Query]\n",
    "{answer}\n",
    "\"\"\".strip()\n",
    "\n",
    "\n",
    "def execute(db_path: str, sql: str):\n",
    "\n",
    "    conn = sqlite3.connect(db_path)\n",
    "    cursor = conn.cursor()\n",
    "    try:\n",
    "        cursor.execute(sql)\n",
    "        return \"\"\n",
    "    except sqlite3.Error as e:\n",
    "        return str(e)\n",
    "    finally:\n",
    "        if cursor:\n",
    "            cursor.close()\n",
    "        if conn:\n",
    "            conn.close()\n",
    "\n",
    "\n",
    "def valid_prompt(\n",
    "    db_path: str, question: str, comment_map: dict, wrong_query: str, error_info: str\n",
    "):\n",
    "    tables = util.get_sqlite_schema_table_with_type_map(db_path, [])\n",
    "\n",
    "    schema_str = prompt.build_schema_str(db_path, tables, question, comment_map, 3)\n",
    "    foreign_key = prompt.build_foreign_key_str(db_path, list(tables.keys()))\n",
    "    instruction = VALID_INSTRUCTION.format(\n",
    "        schema=schema_str,\n",
    "        foreign_key=foreign_key,\n",
    "        question=question,\n",
    "        wrong_query=wrong_query,\n",
    "        error_info=error_info,\n",
    "    )\n",
    "\n",
    "    return instruction\n",
    "\n",
    "\n",
    "valid_dataset = []\n",
    "for i in range(len(answer_set)):\n",
    "    info = dev_set[i]\n",
    "    db_path = DATABASE_PATH_PATTERN.format(db_id=info[\"db_id\"])\n",
    "\n",
    "    raw_sql = answer_set[i]\n",
    "    error = execute(db_path, raw_sql)\n",
    "    if error != \"\":\n",
    "        instruction = valid_prompt(db_path, info[\"question\"], {}, raw_sql, error)\n",
    "        valid_dataset.append({\"instruction\": instruction, \"index\": i})\n",
    "        # print(instruction)\n",
    "with open(\"./valid_dataset.json\", \"w\") as output_file:\n",
    "    json.dump(valid_dataset, output_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "52\n",
      "101\n",
      "378\n",
      "442\n",
      "471\n",
      "583\n",
      "585\n",
      "588\n",
      "695\n",
      "729\n",
      "731\n",
      "733\n",
      "734\n",
      "737\n",
      "738\n",
      "757\n",
      "758\n",
      "805\n",
      "806\n",
      "809\n",
      "810\n",
      "835\n",
      "901\n",
      "911\n",
      "912\n",
      "952\n",
      "961\n",
      "1033\n",
      "1034\n",
      "1044\n",
      "1157\n",
      "1159\n",
      "1269\n",
      "1349\n",
      "1356\n",
      "1357\n",
      "1388\n",
      "1389\n",
      "1415\n",
      "1508\n",
      "1547\n",
      "1548\n",
      "1705\n",
      "1706\n",
      "1909\n",
      "1919\n",
      "1920\n",
      "1965\n",
      "1966\n",
      "2013\n",
      "2142\n",
      "2145\n",
      "2146\n"
     ]
    }
   ],
   "source": [
    "with open(\"valid_test.json\", \"r\") as input_file:\n",
    "    valid_result = json.load(input_file)\n",
    "print(len(valid_result))\n",
    "for i in range(len(valid_result)):\n",
    "    case = valid_result[i]\n",
    "    sql = case[case.find(\"SELECT \"):]\n",
    "    sql = \" \".join(sql.split())\n",
    "    valid_dataset[i][\"answer\"] = sql\n",
    "    print(valid_dataset[i][\"index\"])\n",
    "    answer_set[valid_dataset[i][\"index\"]] = sql\n",
    "\n",
    "fo = open(\"new_answer.txt\", \"w\")\n",
    "for case in answer_set:\n",
    "    fo.write(case + \"\\n\")\n",
    "fo.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "deepseek",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}