{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "unable to open database file",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mOperationalError\u001b[39m                          Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[8]\u001b[39m\u001b[32m, line 40\u001b[39m\n\u001b[32m     37\u001b[39m         conn.close()\n\u001b[32m     38\u001b[39m         \u001b[38;5;28;01mreturn\u001b[39;00m (\u001b[33m\"\u001b[39m\u001b[33mTable \u001b[39m\u001b[33m'\u001b[39m\u001b[33mlight\u001b[39m\u001b[33m'\u001b[39m\u001b[33m does NOT exist.\u001b[39m\u001b[33m\"\u001b[39m, [])\n\u001b[32m---> \u001b[39m\u001b[32m40\u001b[39m init_message = \u001b[43minit_db\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m     41\u001b[39m check_message, schema_info = check_db()\n\u001b[32m     43\u001b[39m init_message, check_message, schema_info\n",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[8]\u001b[39m\u001b[32m, line 5\u001b[39m, in \u001b[36minit_db\u001b[39m\u001b[34m()\u001b[39m\n\u001b[32m      4\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34minit_db\u001b[39m():\n\u001b[32m----> \u001b[39m\u001b[32m5\u001b[39m     conn = \u001b[43msqlite3\u001b[49m\u001b[43m.\u001b[49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m'\u001b[39;49m\u001b[33;43msrc\u001b[39;49m\u001b[38;5;130;43;01m\\\\\u001b[39;49;00m\u001b[33;43mlight.db\u001b[39;49m\u001b[33;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[32m      6\u001b[39m     c = conn.cursor()\n\u001b[32m      8\u001b[39m     \u001b[38;5;66;03m# Create the 'light' table if it doesn't already exist\u001b[39;00m\n",
      "\u001b[31mOperationalError\u001b[39m: unable to open database file"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Initialize the database and table\n",
    "def init_db():\n",
    "    conn = sqlite3.connect('src\\\\light.db')\n",
    "    c = conn.cursor()\n",
    "\n",
    "    # Create the 'light' table if it doesn't already exist\n",
    "    c.execute('''\n",
    "        CREATE TABLE IF NOT EXISTS light (\n",
    "            id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "            devicename CHARACTER(5) NOT NULL,\n",
    "            light INTEGER NOT NULL,\n",
    "            timestamp DATETIME NOT NULL,\n",
    "            tocloud BOOLEAN DEFAULT 0 NOT NULL\n",
    "        )\n",
    "    ''')\n",
    "\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    return \"Database initialized and table 'light' is ready.\"\n",
    "\n",
    "# Check if the table exists and print the schema\n",
    "def check_db():\n",
    "    conn = sqlite3.connect('src\\\\light.db')\n",
    "    c = conn.cursor()\n",
    "\n",
    "    c.execute(\"SELECT name FROM sqlite_master WHERE type='table' AND name='light';\")\n",
    "    table_exists = c.fetchone()\n",
    "\n",
    "    if table_exists:\n",
    "        c.execute(\"PRAGMA table_info(light);\")\n",
    "        columns = c.fetchall()\n",
    "        conn.close()\n",
    "        return (\"Table 'light' exists.\", columns)\n",
    "    else:\n",
    "        conn.close()\n",
    "        return (\"Table 'light' does NOT exist.\", [])\n",
    "\n",
    "init_message = init_db()\n",
    "check_message, schema_info = check_db()\n",
    "\n",
    "init_message, check_message, schema_info\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:3: SyntaxWarning: invalid escape sequence '\\l'\n",
      "<>:3: SyntaxWarning: invalid escape sequence '\\l'\n",
      "C:\\Users\\LittleLishu\\AppData\\Local\\Temp\\ipykernel_22052\\2500929216.py:3: SyntaxWarning: invalid escape sequence '\\l'\n",
      "  conn = sqlite3.connect('src\\light.db')\n"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "unable to open database file",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mOperationalError\u001b[39m                          Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[4]\u001b[39m\u001b[32m, line 18\u001b[39m\n\u001b[32m     15\u001b[39m         conn.close()\n\u001b[32m     16\u001b[39m         \u001b[38;5;28;01mreturn\u001b[39;00m (\u001b[33m\"\u001b[39m\u001b[33mTable \u001b[39m\u001b[33m'\u001b[39m\u001b[33mlight\u001b[39m\u001b[33m'\u001b[39m\u001b[33m does NOT exist.\u001b[39m\u001b[33m\"\u001b[39m, [])\n\u001b[32m---> \u001b[39m\u001b[32m18\u001b[39m check_message, schema_info = \u001b[43mcheck_db\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m     19\u001b[39m check_message, schema_info\n",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[4]\u001b[39m\u001b[32m, line 3\u001b[39m, in \u001b[36mcheck_db\u001b[39m\u001b[34m()\u001b[39m\n\u001b[32m      2\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34mcheck_db\u001b[39m():\n\u001b[32m----> \u001b[39m\u001b[32m3\u001b[39m     conn = \u001b[43msqlite3\u001b[49m\u001b[43m.\u001b[49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m'\u001b[39;49m\u001b[33;43msrc\u001b[39;49m\u001b[33;43m\\\u001b[39;49m\u001b[33;43mlight.db\u001b[39;49m\u001b[33;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[32m      4\u001b[39m     c = conn.cursor()\n\u001b[32m      6\u001b[39m     c.execute(\u001b[33m\"\u001b[39m\u001b[33mSELECT name FROM sqlite_master WHERE type=\u001b[39m\u001b[33m'\u001b[39m\u001b[33mtable\u001b[39m\u001b[33m'\u001b[39m\u001b[33m AND name=\u001b[39m\u001b[33m'\u001b[39m\u001b[33mlight\u001b[39m\u001b[33m'\u001b[39m\u001b[33m;\u001b[39m\u001b[33m\"\u001b[39m)\n",
      "\u001b[31mOperationalError\u001b[39m: unable to open database file"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "def check_db():\n",
    "    conn = sqlite3.connect('src\\light.db')\n",
    "    c = conn.cursor()\n",
    "\n",
    "    c.execute(\"SELECT name FROM sqlite_master WHERE type='table' AND name='light';\")\n",
    "    table_exists = c.fetchone()\n",
    "\n",
    "    if table_exists:\n",
    "        c.execute(\"PRAGMA table_info(light);\")\n",
    "        columns = c.fetchall()\n",
    "        conn.close()\n",
    "        return (\"Table 'light' exists.\", columns)\n",
    "    else:\n",
    "        conn.close()\n",
    "        return (\"Table 'light' does NOT exist.\", [])\n",
    "\n",
    "check_message, schema_info = check_db()\n",
    "check_message, schema_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables in the database:\n",
      "light\n",
      "sqlite_sequence\n",
      "Empty DataFrame\n",
      "Columns: [id, devicename, light, timestamp, tocloud]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "db_path = r\"D:\\OneDrive - National University of Singapore\\Document\\NUSCourses\\Semester 2\\IS5451 AloT Solutions and Development\\Mock\\src\\lighttest.db\"\n",
    "\n",
    "conn = sqlite3.connect(db_path)\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# 获取所有表名\n",
    "cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "tables = cursor.fetchall()\n",
    "\n",
    "print(\"Tables in the database:\")\n",
    "for table in tables:\n",
    "    print(table[0])\n",
    "\n",
    "df = pd.read_sql_query(\"SELECT * FROM light\", conn)\n",
    "\n",
    "print(df)\n",
    "\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'d:\\\\OneDrive - National University of Singapore\\\\Document\\\\NUSCourses\\\\Semester 2\\\\IS5451 AloT Solutions and Development\\\\Mock\\\\src'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将txt的command写入light.db，在树莓派上运行\n",
    "sqlite3 light.db < sqlite.txt"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "is5451",
   "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.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}