Data Model Exercise Part 1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "56dc665d",
   "metadata": {},
   "source": [
    "## Lesson 1 Exercise 1: Creating a Table with PostgreSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17f92a9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46004eee",
   "metadata": {},
   "source": [
    "## Import the library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8a148d09",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00f7a6a2",
   "metadata": {},
   "source": [
    "## Create a connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c2191466",
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    conn = psycopg2.connect(\"host=127.0.0.1 dbname=postgres user=postgres password=root\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not make connection to the Postgres database\")\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1055bff6",
   "metadata": {},
   "source": [
    "## Use the connection to get a cursor that can be used to execute queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2a893200",
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur = conn.cursor()\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not get curser to the Database\")\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d779c966",
   "metadata": {},
   "source": [
    "## Set automatic commit to be true so that each action is committed without having to call conn.commit() after each command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "13305c08",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8e46fa3",
   "metadata": {},
   "source": [
    "## Create a database to do the work in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7063598d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "database \"myfirstdb\" already exists\n",
      "\n"
     ]
    }
   ],
   "source": [
    "try: \n",
    "    cur.execute(\"create database myfirstdb\")\n",
    "except psycopg2.Error as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fb0a512",
   "metadata": {},
   "source": [
    "## Add the database name in the connect statement. Let's close our connection to the default database, reconnect to the Udacity database, and get a new cursor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9fd39615",
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    conn.close()\n",
    "except psycopg2.Error as e:\n",
    "    print(e)\n",
    "    \n",
    "try: \n",
    "    conn = psycopg2.connect(\"host=127.0.0.1 dbname=myfirstdb user=postgres password=root\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not make connection to the Postgres database\")\n",
    "    print(e)\n",
    "    \n",
    "try: \n",
    "    cur = conn.cursor()\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not get curser to the Database\")\n",
    "    print(e)\n",
    "\n",
    "conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "530a7702",
   "metadata": {},
   "source": [
    "## Create Table for students which includes below columns\n",
    "\n",
    "student_id <br>\n",
    "name <br>\n",
    "age <br>\n",
    "gender <br>\n",
    "subject <br>\n",
    "marks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "25549b7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS students (student_id int, name varchar,\\\n",
    "    age int, gender varchar, subject varchar, marks int);\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "807ee3b5",
   "metadata": {},
   "source": [
    "### Insert the following two rows in the table\n",
    "First Row:  1, \"Raj\", 23, \"Male\", \"Python\", 85\n",
    "\n",
    "Second Row: 2, \"Priya\", 22, \"Female\", \"Python\", 86"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b06a6be8",
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"INSERT INTO students (student_id, name, age, gender, subject, marks) \\\n",
    "                 VALUES (%s, %s, %s, %s, %s, %s)\", \\\n",
    "                 (1, \"Raj\", 23, \"Male\", \"Python\", 85))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute(\"INSERT INTO students (student_id, name, age, gender, subject, marks) \\\n",
    "                  VALUES (%s, %s, %s, %s, %s, %s)\",\n",
    "                  ( 2, \"Priya\", 22, \"Female\", \"Python\", 86))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "830f62a8",
   "metadata": {},
   "source": [
    "## Validate your data was inserted into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "39875456",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Raj', 23, 'Male', 'Python', 85)\n",
      "(2, 'Priya', 22, 'Female', 'Python', 86)\n"
     ]
    }
   ],
   "source": [
    "try: \n",
    "    cur.execute(\"SELECT * FROM students;\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: select *\")\n",
    "    print (e)\n",
    "\n",
    "row = cur.fetchone()\n",
    "while row:\n",
    "    print(row)\n",
    "    row = cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dc5483a",
   "metadata": {},
   "source": [
    "##  And finally close your cursor and connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a95e6d61",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25622897",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}