sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Database Connection with Python using SQLite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A database connection is essential for allowing your application (such as a Python program) to interact with a database. Without a connection, your program wouldn't be able to read, write, or manipulate data stored in the database. Here are some of the key reasons why a database connection is needed:\n",
    "\n",
    "1. Data Storage and Retrieval\n",
    "2. Enabling SQL Queries\n",
    "3. Transaction Management\n",
    "4. Concurrency and Multi-user Access\n",
    "5. Security and Authentication\n",
    "6. Efficiency and Performance\n",
    "7. Data Integrity and Consistency\n",
    "8. Communication Between Application and Database\n",
    "9. Managing Complex Queries and Relationships\n",
    "10. External Applications and APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do Database Connection\n",
    "\n",
    "To connect to a database using Python, you'll need to use a database connector or driver specific to the type of database you're working with (e.g., SQLite, MySQL, PostgreSQL).\n",
    "\n",
    "**1. Import required library (SQLite):** SQLite is a lightweight, serverless database, and Python has a built-in library called sqlite3 to connect to SQLite databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Connect to a Database:** It creates a new one if it doesn't exist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect = sqlite3.connect('example.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3. Create a Cursor Object:** Cursor serves as an intermediary between your Python code and the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connect.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4. Create a Table:** Create a new table if doesn't exist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1abd038dec0>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute('''CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY, name VARCHAR NOT NULL, age INTEGER, department VARCHAR)''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5. Insert data into Tables:** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1abd038dec0>"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"INSERT INTO person (name, age, department) VALUES ('nitin', 28, 'Machine Learning')\")\n",
    "\n",
    "cursor.execute(\"INSERT INTO person (name, age, department) VALUES ('ruchita', 30, 'Data Analyst')\")\n",
    "\n",
    "cursor.execute(\"INSERT INTO person (name, age, department) VALUES ('neha', 29, 'Data Engineer')\")\n",
    "\n",
    "cursor.execute(\"INSERT INTO person (name, age, department) VALUES ('timish', 27, 'Cloud Engineer')\")\n",
    "\n",
    "cursor.execute(\"INSERT INTO person (name, age, department) VALUES ('amol', 27, 'Testing Engineer')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6. Commit the changes:** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7. Retrieve Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'nitin', 28, 'Machine Learning')\n",
      "(2, 'ruchita', 30, 'Data Analyst')\n",
      "(3, 'neha', 29, 'Data Engineer')\n",
      "(4, 'timish', 27, 'Cloud Engineer')\n",
      "(5, 'amol', 27, 'Testing Engineer')\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM person\")\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8. Close the Connecction:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Full Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'nitin', 28, 'Machine Learning')\n",
      "(2, 'rucha', 30, 'Data Analyst')\n",
      "(3, 'neha', 29, 'Data Engineer')\n",
      "(4, 'timish', 27, 'Cloud Engineer')\n",
      "(5, 'amol', 27, 'Testing Engineer')\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Connect to a database (it creates a new one if it doesn't exist)\n",
    "connect = sqlite3.connect('example.db')\n",
    "\n",
    "# Create a cursor object\n",
    "cursor = connect.cursor()\n",
    "\n",
    "# Create a table (if it doesn't exist)\n",
    "cursor.execute('''CREATE TABLE IF NOT EXISTS students (\n",
    "                    id INTEGER PRIMARY KEY, \n",
    "                    name TEXT, \n",
    "                    age INTEGER, \n",
    "                    department TEXT)''')\n",
    "\n",
    "# Insert data into the table\n",
    "cursor.execute(\"INSERT INTO students (name, age, department) VALUES ('nitin', 28, 'Machine Learning')\")\n",
    "cursor.execute(\"INSERT INTO students (name, age, department) VALUES ('rucha', 30, 'Data Analyst')\")\n",
    "cursor.execute(\"INSERT INTO students (name, age, department) VALUES ('neha', 29, 'Data Engineer')\")\n",
    "cursor.execute(\"INSERT INTO students (name, age, department) VALUES ('timish', 27, 'Cloud Engineer')\")\n",
    "cursor.execute(\"INSERT INTO students (name, age, department) VALUES ('amol', 27, 'Testing Engineer')\")\n",
    "\n",
    "# Commit the changes\n",
    "connect.commit()\n",
    "\n",
    "# Retrieve data\n",
    "cursor.execute(\"SELECT * FROM students\")\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "# Display the results\n",
    "for row in rows:\n",
    "    print(row)\n",
    "\n",
    "# Close the connection\n",
    "connect.close()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "database_connection",
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}