database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2 as ps\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def connect_to_db(host_name, dbname, port, username, password):\n",
    "    try:\n",
    "        conn = ps.connect(host=host_name, database=dbname, user=username, password=password, port=port)\n",
    "    except ps.OperationalError as e:\n",
    "        raise e\n",
    "    else:\n",
    "        print('Connected!')\n",
    "        return conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_table(curr):\n",
    "    create_table_command = (\"\"\"CREATE TABLE IF NOT EXISTS videos (\n",
    "                    video_id VARCHAR(255) PRIMARY KEY,\n",
    "                    video_title TEXT NOT NULL,\n",
    "                    upload_date DATE NOT NULL DEFAULT CURRENT_DATE,\n",
    "                    view_count BIGINT NOT NULL,\n",
    "                    like_count BIGINT NOT NULL,\n",
    "                    dislike_count BIGINT NOT NULL,\n",
    "                    comment_count BIGINT NOT NULL\n",
    "            )\"\"\")\n",
    "    curr.execute(create_table_command)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_into_table(curr, video_id, video_title, upload_date, view_count, like_count, dislike_count, comment_count):\n",
    "    insert_into_videos = (\"\"\"INSERT INTO videos (video_id, video_title, upload_date,\n",
    "                        view_count, like_count, dislike_count,comment_count)\n",
    "    VALUES(%s,%s,%s,%s,%s,%s,%s);\"\"\")\n",
    "    row_to_insert = (video_id, video_title, upload_date, view_count, like_count, dislike_count, comment_count)\n",
    "    curr.execute(insert_into_videos, row_to_insert)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_row(curr, video_id, video_title, view_count, like_count, dislike_count, comment_count):\n",
    "    query = (\"\"\"UPDATE videos\n",
    "            SET video_title = %s,\n",
    "                view_count = %s,\n",
    "                like_count = %s,\n",
    "                dislike_count = %s,\n",
    "                comment_count = %s\n",
    "            WHERE video_id = %s;\"\"\")\n",
    "    vars_to_update = (video_title, view_count, like_count, dislike_count, comment_count, video_id)\n",
    "    curr.execute(query, vars_to_update)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_if_video_exists(curr, video_id): \n",
    "    query = (\"\"\"SELECT video_id FROM VIDEOS WHERE video_id = %s\"\"\")\n",
    "    curr.execute(query, (video_id,))\n",
    "    return curr.fetchone() is not None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# def truncate_table(curr):\n",
    "#     truncate_table = (\"\"\"TRUNCATE TABLE videos\"\"\")\n",
    "#     curr.execute(truncate_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def append_from_df_to_db(curr,df):\n",
    "    for i, row in df.iterrows():\n",
    "        insert_into_table(curr, row['video_id'], row['video_title'], row['upload_date'], row['view_count']\n",
    "                          , row['like_count'], row['dislike_count'], row['comment_count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_db(curr,df):\n",
    "    tmp_df = pd.DataFrame(columns=['video_id', 'video_title', 'upload_date', 'view_count',\n",
    "                                   'like_count', 'dislike_count', 'comment_count'])\n",
    "    for i, row in df.iterrows():\n",
    "        if check_if_video_exists(curr, row['video_id']): # If video already exists then we will update\n",
    "            update_row(curr,row['video_id'],row['video_title'],row['view_count'],row['like_count'],row['dislike_count'],row['comment_count'])\n",
    "        else: # The video doesn't exists so we will add it to a temp df and append it using append_from_df_to_db\n",
    "            tmp_df = tmp_df.append(row)\n",
    "    return tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>video_id</th>\n",
       "      <th>video_title</th>\n",
       "      <th>upload_date</th>\n",
       "      <th>view_count</th>\n",
       "      <th>like_count</th>\n",
       "      <th>dislike_count</th>\n",
       "      <th>comment_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>kLkmGUTwG-s</td>\n",
       "      <td>Comment | Java for Beginners | Bangla Tutorial...</td>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>67</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>baGEPw5XMog</td>\n",
       "      <td>How To Run A Java Program | Java for Beginners...</td>\n",
       "      <td>2020-10-18</td>\n",
       "      <td>155</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47y_BeKHIPs</td>\n",
       "      <td>C++ vs Java | Java for Beginners | Bangla Tuto...</td>\n",
       "      <td>2020-09-28</td>\n",
       "      <td>123</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sKUZAeyF_QU</td>\n",
       "      <td>Overview of Java | Java for Beginners | Bangla...</td>\n",
       "      <td>2020-09-03</td>\n",
       "      <td>100</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>hl4eq1ToX3U</td>\n",
       "      <td>How To Add A Calendar To A Website | Bangla We...</td>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>70</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      video_id                                        video_title upload_date  \\\n",
       "0  kLkmGUTwG-s  Comment | Java for Beginners | Bangla Tutorial...  2021-01-05   \n",
       "1  baGEPw5XMog  How To Run A Java Program | Java for Beginners...  2020-10-18   \n",
       "2  47y_BeKHIPs  C++ vs Java | Java for Beginners | Bangla Tuto...  2020-09-28   \n",
       "3  sKUZAeyF_QU  Overview of Java | Java for Beginners | Bangla...  2020-09-03   \n",
       "4  hl4eq1ToX3U  How To Add A Calendar To A Website | Bangla We...  2020-08-31   \n",
       "\n",
       "   view_count  like_count  dislike_count  comment_count  \n",
       "0          67           4              0              0  \n",
       "1         155           6              0              1  \n",
       "2         123           2              0              1  \n",
       "3         100           7              0              2  \n",
       "4          70          10              0              4  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data.csv', index_col=0)\n",
    "df['upload_date'] = df['upload_date'] = pd.to_datetime(df['upload_date'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n"
     ]
    }
   ],
   "source": [
    "print(type(df['upload_date'][1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected!\n"
     ]
    }
   ],
   "source": [
    "#database credentials\n",
    "host_name = 'x.rds.amazonaws.com'\n",
    "dbname = 'db'\n",
    "port = '5432'\n",
    "username = 'xyz'\n",
    "password = 'xyz'\n",
    "\n",
    "#establish a connection to db\n",
    "conn = connect_to_db(host_name, dbname, port, username, password)\n",
    "curr = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table(curr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_vid_df = update_db(curr, df)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "append_from_df_to_db(curr, new_vid_df)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inse = \"\"\"INSERT INTO videos (video_id, video_title, upload_date,\n",
    "#                         view_count, like_count, dislike_count,comment_count)\n",
    "#     VALUES('1212sdssfsad','nach meri rani','2022-12-30',122,10,0,2);\"\"\"\n",
    "# curr.execute(inse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('kLkmGUTwG-s', 'Comment | Java for Beginners | Bangla Tutorial | #5', datetime.date(2021, 1, 5), 67, 4, 0, 0), ('baGEPw5XMog', 'How To Run A Java Program | Java for Beginners | Bangla Tutorial | #4', datetime.date(2020, 10, 18), 155, 6, 0, 1), ('47y_BeKHIPs', 'C++ vs Java | Java for Beginners | Bangla Tutorial | #3', datetime.date(2020, 9, 28), 123, 2, 0, 1), ('sKUZAeyF_QU', 'Overview of Java | Java for Beginners | Bangla Tutorial | #2', datetime.date(2020, 9, 3), 100, 7, 0, 2), ('hl4eq1ToX3U', 'How To Add A Calendar To A Website | Bangla Web Development Tricks | CS Bangla', datetime.date(2020, 8, 31), 70, 10, 0, 4), ('g_Gei7Ss2Ow', 'Top 10 Programming Languages in 2020 | CS Bangla', datetime.date(2020, 8, 27), 296, 16, 0, 5), ('3c0h8hvDsEw', 'Introduction | Java for Beginners | Bangla Tutorial | #1', datetime.date(2020, 8, 24), 249, 12, 0, 10), ('Koz-r8a1lXQ', 'C++ Class and Object | Bangla Tutorial | Part-18', datetime.date(2020, 8, 17), 613, 21, 0, 3), ('zt6G2Cqxrzs', 'C++ Function Overloading | Bangla Tutorial | Part-17', datetime.date(2020, 7, 26), 79, 6, 0, 0), ('7eA70JjiZ9c', 'Basics of Object-Oriented Programming | Bangla Tutorial | CS Bangla', datetime.date(2020, 7, 18), 9042, 276, 0, 18), ('Q6dGxaRJzKQ', 'Fix accidentally cursor change in CodeBlocks | CS Bangla', datetime.date(2020, 7, 16), 487, 28, 0, 4), ('gbvlDcR7qc4', 'Hard Coding  User Input in Python | Python for Beginners | Bangla Tutorial | #8', datetime.date(2020, 7, 15), 99, 11, 0, 1), ('vKilOL3kJCY', 'C++ Functions | Parameters  | Return Type | Bangla Tutorial | Part-16', datetime.date(2020, 7, 13), 94, 7, 0, 0), ('32Z9cPBjbac', '2D Array | Nested Loop  | C++ for Beginners | Bangla Tutorial | Part-15', datetime.date(2020, 7, 10), 54, 5, 0, 0), ('kDgfh_ESYyw', 'Variables in Python | Python for Beginners | Bangla Tutorial | #7', datetime.date(2020, 7, 7), 70, 7, 0, 0), ('juFDDUm79Tg', 'C++ Array Problem | Find index | Find max value from array |  C++ for Beginners  | Part-14', datetime.date(2020, 7, 5), 1276, 18, 0, 3), ('A1RBvgtctts', 'C++ Array | Bangla Tutorial | Part-13', datetime.date(2020, 7, 3), 73, 7, 0, 0), ('nFEwZtfSfuM', 'AI vs Machine Learning vs Deep Learning | Bangla Tutorial | CS Bangla', datetime.date(2020, 7, 1), 8336, 237, 0, 12), ('8R-EerE_CNM', 'Loop problem solve | Prime or not | Continue | Break | Infinite Loop | Bangla Tutorial | Part-12', datetime.date(2020, 6, 27), 70, 8, 0, 0), ('n6JlRpYO0oI', 'Create A Website Using HTML  CSS | Bangla Tutorial | CS Bangla', datetime.date(2020, 6, 25), 17596, 428, 0, 24), ('TNnpzXtHNtM', 'LOOP | While loop | Do While loop | For loop | Bangla Tutorial | Part-11', datetime.date(2020, 6, 18), 80, 5, 0, 0), ('7m4Sv6OwUAY', 'Data Types  Type Conversion Functions | Python for Beginners | Bangla Tutorial | #6', datetime.date(2020, 6, 17), 94, 7, 0, 0), ('X8kvctYAXVg', 'Switch Case | Calculator | Bangla Tutorial | Part-10', datetime.date(2020, 6, 16), 39, 6, 0, 0), ('MWKPl_15Y28', 'Become a Programmer without a Computer | CS Bangla', datetime.date(2020, 6, 15), 170, 20, 0, 6), ('9-ctzwrufUw', 'Conditions Problem |  | Multi-line Comments | Bangla Tutorial | Part-9', datetime.date(2020, 6, 13), 68, 7, 0, 0), ('u5F9Bm1B5xY', 'Function Calls in Python | Python for Beginners | Bangla Tutorial | #5', datetime.date(2020, 6, 11), 63, 7, 0, 1), ('6gL_ythd-pU', 'Conditions | Simple  Condition Problem | C++ for Beginners | Bangla Tutorial | Part-8', datetime.date(2020, 6, 11), 71, 7, 0, 0), ('EA0m1Pc4sts', 'Operator | Simple Calculator | C++ for Beginners | Bangla Tutorial | Part-7', datetime.date(2020, 6, 10), 80, 7, 0, 2), ('S7l6OukTOqs', 'Values, Operators  Operands in Python | Bangla Tutorail | #4', datetime.date(2020, 6, 9), 204, 10, 0, 1), ('E2NzK_31rgE', 'Data Type | Single line Comments | C++ for Beginners | Bangla Tutorial | Part-6', datetime.date(2020, 6, 9), 81, 9, 0, 6), ('UHbwn7FVXaI', 'Variables | User Input | C++ for Beginners | Bangla Tutorial | Part-5', datetime.date(2020, 6, 8), 102, 10, 0, 6), ('yyfdwA6DLWE', 'Top 10 Visual Studio Code Extension for Python | Bangla Tutorial | CS Bangla', datetime.date(2020, 6, 7), 521, 21, 0, 6), ('fKREKuKeVsA', 'C++ Basics | New Line  Line Gap | C++ for Beginners | Bangla Tutorial | Part-4', datetime.date(2020, 6, 6), 114, 9, 0, 2), ('fXNLwoLMKSQ', 'CS Bangla || Programming tutorials in Bangla', datetime.date(2020, 6, 6), 71, 2, 0, 0), ('xItdAY91CiE', 'How to Install  Setup Visual Studio Code for Python Programming | Bangla Tutorial | #3', datetime.date(2020, 6, 6), 2132, 29, 0, 8), ('G3oH2-XtU5w', 'First Code | Hello World | C++ for Beginners | Bangla Tutorial | Part-3', datetime.date(2020, 6, 5), 175, 8, 0, 0), ('hU7c7u2PjBA', 'How to Download  Install Python 3.8.3 on Windows 10 | Bangla Tutorial | #2', datetime.date(2020, 6, 4), 904, 14, 0, 0), ('WVTsBd_5liM', 'Codeblocks Installation | IDE | C++ for Beginners | Bangla Tutorial | Part-2', datetime.date(2020, 6, 4), 220, 7, 0, 2), ('9rhjt8bsPaM', 'Introduction | C++ for Beginners | Bangla Tutorial | Part-1', datetime.date(2020, 6, 3), 1299, 48, 0, 13), ('ErDeTl-DM0s', 'Introduction | Python for Beginners | Bangla Tutorial | #1', datetime.date(2020, 6, 3), 436, 26, 0, 8)]\n"
     ]
    }
   ],
   "source": [
    "#view data in db table\n",
    "curr.execute(\"SELECT * FROM VIDEOS\")\n",
    "print(curr.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.9.6 (tags/v3.9.6:db3ff76, Jun 28 2021, 15:26:21) [MSC v.1929 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "7459fc3de7a9cfbb16956cc8e27f933e0ea1512a8cf97ca2195aa9f74a47b2b0"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}