{
"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": [
""
]
},
"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": [
""
]
},
"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": [
""
]
},
"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"
]
}
]
}
]
}