postgresql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Database Connection with Python using PostgreSQL"
   ]
  },
  {
   "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:** First, you need to install the psycopg2 package:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install psycopg2\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Establish the connection to the PostgreSQL database:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect = psycopg2.connect(\n",
    "    host='localhost',\n",
    "    dbname='your_database',\n",
    "    user='your_database',\n",
    "    password='your_password'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3. Create the Cursor Object:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connect.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4. Create the Table:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(100))''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5. Insert Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"INSERT INTO users (name) VALUES ('nitin')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('rucha')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('neha')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('timish')\")\n",
    "cursor.execute(\"INSERT INTO users (name) VALUES ('amol')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6. Commit the changes:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7. Retrieve Data:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT * FROM users\")\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8. Close the connection:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "connect.close()"
   ]
  }
 ],
 "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
}