{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "#SQLite inbuilt python module\n",
        "\n",
        "* it also know as server less database"
      ],
      "metadata": {
        "id": "yiyC3G2jm9uu"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "##Assignment\n",
        "\n",
        "1. Create a database\n",
        "2. Create a table\n",
        "3. Insert data into the table\n",
        "4. Query data from the table\n",
        "5. Close the database connection\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "2_AFUFAqnPR3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Step 1: Import the sqlite3 Module"
      ],
      "metadata": {
        "id": "U1HnNEDtpSp4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3"
      ],
      "metadata": {
        "id": "M0vDRgzXp7aR"
      },
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 2: Connect to the Database"
      ],
      "metadata": {
        "id": "Kzh2sQt4paT1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "conn = sqlite3.connect('first_db.db')\n"
      ],
      "metadata": {
        "id": "X1GGtxOmp-Dl"
      },
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 3: Create a Cursor Object"
      ],
      "metadata": {
        "id": "CtSjwcoVpebO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor = conn.cursor()\n"
      ],
      "metadata": {
        "id": "G4wjmDLLqEi8"
      },
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 4: Execute SQL Queries"
      ],
      "metadata": {
        "id": "Y8fw63odphpl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute('''CREATE TABLE IF NOT EXISTS users\n",
        "                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vgMY2LIIqG_N",
        "outputId": "637f9807-f435-40e1-a34b-b5ad6c6c0e34"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7cd02f048140>"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 5: Insert Data"
      ],
      "metadata": {
        "id": "9PvZUuYEplwd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"INSERT INTO users (name, age) VALUES (?, ?)\", ('John', 30))\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "w8Bou4rkqJZn",
        "outputId": "b3de9b22-9115-4a38-cd4f-76090dc4e304"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7cd02f048140>"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 6: Commit Changes"
      ],
      "metadata": {
        "id": "cuCV0p11psx1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "conn.commit()\n"
      ],
      "metadata": {
        "id": "uPnHP5krqLpf"
      },
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 7: Query Data"
      ],
      "metadata": {
        "id": "wdi7M94Yp0vN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"SELECT * FROM users\")\n",
        "rows = cursor.fetchall()\n",
        "for row in rows:\n",
        "    print(row)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L9RGOxkeqNaN",
        "outputId": "3eca626f-41e5-4eb0-e7d0-4990c159694c"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'John', 30)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Step 8: Close the Cursor and Connection"
      ],
      "metadata": {
        "id": "eIf03LFMp3k-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.close()\n",
        "conn.close()\n"
      ],
      "metadata": {
        "id": "49Upc8zzqPi-"
      },
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#1st Database Created Using SQLite"
      ],
      "metadata": {
        "id": "RyTIYKwew26m"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "\n",
        "connection = sqlite3.connect('obitorin.db') #it will create new or if exist it will open\n",
        "\n",
        "cursor = connection.cursor() #create object for cursor\n",
        "\n",
        "cursor.execute(\"create table if not exists konaha_ninjas(Name varchar(30) primary key,age int not null,place varchar(30) default 'Konaha')\")\n",
        "\n",
        "cursor.execute(\"insert into konaha_ninjas(Name,age) values('Itachi Uchiha',14),('Sasuke Uchiha',12)\")\n",
        "\n",
        "connection.commit()\n",
        "\n",
        "cursor.execute(\"SELECT * FROM konaha_ninjas\")\n",
        "rows = cursor.fetchall()\n",
        "\n",
        "for row in rows:\n",
        "  print(row)\n",
        "\n",
        "cursor.close()\n",
        "connection.close()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PQi1JjBlnFNc",
        "outputId": "c77ae0c2-c48a-43b1-98b5-4c3b84d7a2b7"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Itachi Uchiha', 14, 'Konaha')\n",
            "('Sasuke Uchiha', 12, 'Konaha')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"Select * from konaha_ninjas where Name ='Itachi Uchiha'\")\n",
        "data = cursor.fetchall()\n",
        "for i in data:\n",
        "  print(i)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5jAT86SGsttj",
        "outputId": "30f8ad8b-f0ac-4386-980c-95d14812668a"
      },
      "execution_count": 64,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Itachi Uchiha', 14, 'Konaha')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#  Retrieve data into Pandas"
      ],
      "metadata": {
        "id": "T97gq82a1HbU"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "import pandas as pd\n",
        "\n",
        "# Connect to the SQLite database\n",
        "connection = sqlite3.connect('obitorin.db')\n",
        "\n",
        "# Define your SQL query\n",
        "sql_query = \"SELECT * FROM konaha_ninjas\"\n",
        "\n",
        "# Execute the SQL query and read the results into a DataFrame\n",
        "df = pd.read_sql_query(sql_query, connection)\n",
        "\n",
        "# Close the connection\n",
        "connection.close()\n",
        "\n",
        "# Display the DataFrame\n",
        "print(df)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gxU_wUk21JPD",
        "outputId": "4fde230e-86ac-4628-d4fd-9a28f321647b"
      },
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "            Name  age   place\n",
            "0  Itachi Uchiha   14  Konaha\n",
            "1  Sasuke Uchiha   12  Konaha\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "import pandas as pd\n",
        "connection =sqlite3.connect('obitorin.db')\n",
        "query = \"SELECT * FROM konaha_ninjas \"\n",
        "data =pd.read_sql_query(query, connection)\n",
        "\n",
        "connection.close()\n",
        "\n",
        "print(data)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "omC8YbZD1rtk",
        "outputId": "8e876a44-d49f-46d2-a208-dc3ef4f4fcc8"
      },
      "execution_count": 83,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "            Name  age   place\n",
            "0  Itachi Uchiha   14  Konaha\n",
            "1  Sasuke Uchiha   12  Konaha\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "GM-DqjX-6qKh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Create second database"
      ],
      "metadata": {
        "id": "piRRvXIm7OxP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "conn = sqlite3.connect(\"new_db.db\")\n",
        "cursor = conn.cursor()\n",
        "cursor.execute(\"create table if not exists konaha_ninjas(Name varchar(30) primary key,age int not null,place varchar(30) default 'Konaha')\")\n",
        "cursor.execute(\"insert into konaha_ninjas(Name,age) values('Itachi Uchiha',14),('Sasuke Uchiha',12),('Hinata Hyuga',14),('Naruto Usumaki',15)\")\n",
        "\n",
        "connection.commit()\n",
        "\n",
        "cursor.execute(\"SELECT * FROM konaha_ninjas\")\n",
        "rows = cursor.fetchall()\n",
        "\n",
        "for row in rows:\n",
        "  print(row)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "k7CY_h_-7QuI",
        "outputId": "5b996397-eea1-4169-a1da-3bec32530541"
      },
      "execution_count": 89,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('Itachi Uchiha', 14, 'Konaha')\n",
            "('Sasuke Uchiha', 12, 'Konaha')\n",
            "('Hinata Hyuga', 14, 'Konaha')\n",
            "('Naruto Usumaki', 15, 'Konaha')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"insert into konaha_ninjas (Name,age) values('Obito Uchiha',18)\")"
      ],
      "metadata": {
        "id": "Ut7l0I9e8sqI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"insert into konaha_ninjas (Name,age) values('Rin Obito Uchiha',18)\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qvRFUO3s9Ft8",
        "outputId": "072532c9-dfa7-4745-ecfc-b6751b3bd2b1"
      },
      "execution_count": 98,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7cd0289a30c0>"
            ]
          },
          "metadata": {},
          "execution_count": 98
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "query = \"select * from konaha_ninjas\"\n",
        "df = pd.read_sql_query(query,conn)\n",
        "print(df)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZJ5qTaYp76m7",
        "outputId": "313a2e72-3a41-4dd3-b2f0-7e06a20569d8"
      },
      "execution_count": 99,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "               Name  age   place\n",
            "0     Itachi Uchiha   14  Konaha\n",
            "1     Sasuke Uchiha   12  Konaha\n",
            "2      Hinata Hyuga   14  Konaha\n",
            "3    Naruto Usumaki   15  Konaha\n",
            "4      Obito Uchiha   18  Konaha\n",
            "5  Rin Obito Uchiha   18  Konaha\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "query = \"select * from konaha_ninjas where age>15\"\n",
        "df = pd.read_sql_query(query,conn)\n",
        "print(df)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Y8KM71Z38m_h",
        "outputId": "958b1aa4-660d-4bf4-8fc9-3a8404bfb3d9"
      },
      "execution_count": 100,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "               Name  age   place\n",
            "0      Obito Uchiha   18  Konaha\n",
            "1  Rin Obito Uchiha   18  Konaha\n"
          ]
        }
      ]
    }
  ]
}