Notes_Youtube_Data_Harvesting.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "from googleapiclient.discovery import build\n",
        "import googleapiclient.discovery\n",
        "import pymongo\n",
        "import psycopg2\n",
        "import pandas as pd\n",
        "import streamlit as st\n",
        "\n",
        "# Importing necessary libraries:\n",
        "# googleapiclient: For interacting with the YouTube Data API.\n",
        "# pymongo: For working with MongoDB.\n",
        "# psycopg2: For interacting with PostgreSQL.\n",
        "# pandas: For data manipulation and analysis.\n",
        "# streamlit: For creating the web application interface."
      ],
      "metadata": {
        "id": "MxErOCHtsUDt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "api_key=\"AIzaSyAUHaoZIpdp3irZt8sWJSMpSLfxjEdrMXs\"\n",
        "#Creating a variable as api_key and assigns it a value of \"AIzaSyAUHaoZIpdp3irZt8sWJSMpSLfxjEdrMXs\",\n",
        "#API key used for accessing the YouTube Data API. This key obtained from the Google Developer Console.\n",
        "\n",
        "api_service_name=\"youtube\"\n",
        "#This specifies the service that will be used with the API, in this case, the YouTube Data API.\n",
        "\n",
        "api_version=\"v3\"\n",
        "#This specifies the version of the YouTube Data API that will be used.)\n",
        "\n",
        "youtube = googleapiclient.discovery.build(api_service_name, api_version, developerKey=api_key)\n",
        "\n",
        "\n",
        "#Creating a YouTube API service client using the googleapiclient.discovery.build method.\n",
        "#It takes the api_service_name, api_version, and developerKey (API key) as arguments and creates a client object named youtube.\n",
        "#This client object is used to make requests to the YouTube API."
      ],
      "metadata": {
        "id": "mRIu-UDusyL0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def get_channel_data(channel_id):\n",
        "        request=youtube.channels().list(\n",
        "                                part=\"snippet,contentDetails,statistics\",\n",
        "                                id=channel_id\n",
        "        )\n",
        "        response=request.execute()\n",
        "\n",
        "#Defining a Python function named get_channel_data that takes one argument, channel_id.\n",
        "#This function is created to fetch data for a YouTube channel based on the provided channel_id.\n",
        "#And Creating a request object by calling the list method on the youtube client object.\n",
        "#It prepares to make a request to the channels endpoint of the YouTube API to retrieve information about a channel and specify the parameters for the request.\n",
        "#part: Specifies which parts of the channel resource should be included in the API response.\n",
        "#In this case, it includes \"snippet\", \"contentDetails\", and \"statistics\".\n",
        "#id: Specifies the ID of the YouTube channel for which the data is being requested.\n",
        "#This ID is provided as an argument to the get_channel_data function.\n",
        "#And executes the request to the YouTube API using the execute() method on the request object.\n",
        "#It sends the request with the specified parameters to the API and retrieves the response.\n",
        "\n",
        "\n",
        "for i in response ['items']:\n",
        "   data=dict(Channel_Name=i[\"snippet\"][\"title\"],\n",
        "             Channel_Id=i[\"id\"],\n",
        "             Subscribers=i[\"statistics\"][\"subscriberCount\"],\n",
        "             Views=i[\"statistics\"][\"viewCount\"],\n",
        "             Total_videos=i[\"statistics\"][\"videoCount\"],\n",
        "             Channel_Description=i[\"snippet\"][\"description\"],\n",
        "             Playlist_Id=i[\"contentDetails\"][\"relatedPlaylists\"][\"uploads\"])\n",
        "\n",
        "#This code processes the API response.\n",
        "#(It iterates through the items in the response (which represent channels in this case),extracts relevant information, and stores it in a dictionary named data.)\n",
        "#Channel_Name: Extracts the channel's title from the \"snippet\" part of the API response.\n",
        "#Channel_Id: Extracts the channel's ID.\n",
        "#Subscribers: Extracts the number of subscribers from the \"statistics\" part of the API response.\n",
        "#Views: Extracts the total number of views from the \"statistics\" part of the API response.\n",
        "#Total_videos: Extracts the total number of videos uploaded by the channel from the \"statistics\" part of the API response.\n",
        "#Channel_Description: Extracts the channel's description from the \"snippet\" part of the API response.\n",
        "#Playlist_Id: Extracts the ID of the channel's upload playlist from the \"contentDetails\" part of the API response.\n",
        "\n",
        "#Finally, this line returns the data dictionary containing the extracted information about the YouTube channel.\n",
        "#This data can then be used or processed further as needed by the calling code."
      ],
      "metadata": {
        "id": "b-peRuFfyZRu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python script defines a function get_channel_data(channel_id) to fetch data about a YouTube channel. It uses the YouTube Data API to gather information such as the channel's name, ID, subscriber count, view count, video count, description, and upload playlist ID. The function returns this data as a dictionary."
      ],
      "metadata": {
        "id": "uhUljmbc0JH4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def get_video_ids(channel_id):\n",
        "    video_ids=[]\n",
        "    response=youtube.channels().list(id=channel_id,\n",
        "                                    part='contentDetails').execute()\n",
        "    Playlist_Id=response['items'][0]['contentDetails']['relatedPlaylists']['uploads']\n",
        "\n",
        "    next_page_token=None\n",
        "\n",
        "#This function defines named get_video_ids that takes channel_id as a parameter.\n",
        "#This function is designed to collect the video IDs of all the videos uploaded to a specific YouTube channel.\n",
        "#Initializing an empty list named video_ids. This list will be used to store the video IDs retrieved from the channel's upload playlist.\n",
        "#Making a request to the YouTube Data API to retrieve information about the specified channel_id.\n",
        "#It specifically requests the contentDetails part of the channel.\n",
        "#The response will contain details about the channel, including the ID of its upload playlist.\n",
        "#And extracting the ID of the channel's upload playlist from the API response.\n",
        "#It accesses the nested structure of the response to find the upload playlist ID.\n",
        "#initializes a variable next_page_token to None. This token will be used to paginate through the list of videos in the upload playlist.\n",
        "\n",
        "    while True: (#starting with a while loop that will continue indefinitely until explicitly stopped.\n",
        "#This loop will be used to fetch all pages of videos from the upload playlist.)\n",
        "\n",
        "            #Inside the loop, this line makes a request to the playlistItems endpoint of the YouTube Data API.\n",
        "            #It specifies the snippet part of each video, the Playlist_Id obtained earlier, and requests a maximum of 50 results per page.\n",
        "            #The pageToken parameter is used for pagination, starting with next_page_token.\n",
        "        response1=youtube.playlistItems().list(\n",
        "                                            part='snippet',\n",
        "                                            playlistId=Playlist_Id,\n",
        "                                            maxResults=50,\n",
        "                                            pageToken=next_page_token).execute()\n",
        "\n",
        "\n",
        "          #This iterates through each item (video) in the API response and extracts the videoId of each video.\n",
        "          #It then appends these video IDs to the video_ids list.\n",
        "        for i in range (len(response1['items'])):\n",
        "            video_ids.append(response1['items'][i]['snippet']['resourceId']['videoId'])\n",
        "\n",
        "\n",
        "        next_page_token=response1.get('nextPageToken')  #This retrieves the nextPageToken from the API response.\n",
        "                                                        #This token is used to request the next page of results from the playlist.\n",
        "\n",
        "        if next_page_token is None: #This `if` statement checks if there is no `nextPageToken`, indicating that there are no more pages of videos in the playlist.\n",
        "                                    #If there is no next page, the loop breaks, ending the process of fetching video IDs.\n",
        "\n",
        "            break\n",
        "    return video_ids\n",
        "#Finally, it returns the video_ids list containing all the video IDs from the channel's upload playlist.\n",
        "\n"
      ],
      "metadata": {
        "id": "A7lq-KWh0Vdf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python script defines a function get_video_ids(channel_id) to collect the video IDs of all videos uploaded to a specified YouTube channel. It fetches the upload playlist ID of the channel, then paginates through the playlist items, extracting the video IDs and storing them in a list, which is then returned."
      ],
      "metadata": {
        "id": "DXSgFr-o5L5b"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def get_video_data(video_ids):\n",
        "    video_data=[]\n",
        "    for video_id in video_ids:\n",
        "        request=youtube.videos().list(\n",
        "            part=\"snippet,contentDetails,statistics\",\n",
        "            id=video_id\n",
        "        )\n",
        "        response=request.execute()\n",
        "\n",
        "#This function defines get_video_data that takes a list of video_ids as its input parameter.\n",
        "#The function is designed to retrieve detailed information about each video specified by its ID.\n",
        "\n",
        "#Initializing an empty list named video_data.\n",
        "#This list will be used to store dictionaries, with each dictionary representing detailed data for a single video.\n",
        "\n",
        "#Starting a for loop that iterates through each video_id in the video_ids list.\n",
        "#For each iteration, the loop will fetch data for the video corresponding to that video_id.\n",
        "#Inside the loop, the block of code creates a request object to fetch details about a specific video using the youtube.videos().list method.\n",
        "#It specifies the part parameter to include the \"snippet\", \"contentDetails\", and \"statistics\" parts of the video resource.\n",
        "#The video_id specifies which video's data is being requested.\n",
        "\n",
        "#And then executing the request to the YouTube API and stores the response in the response variable.\n",
        "#It retrieves detailed information about the video based on the provided video_id.\n",
        "        for item in response[\"items\"]:\n",
        "            data=dict(Channel_Name=item['snippet']['channelTitle'],\n",
        "                    Channel_Id=item['snippet']['channelId'],\n",
        "                    Video_Id=item['id'],\n",
        "                    Title=item['snippet']['title'],\n",
        "                    Tags=item['snippet'].get('tags'),\n",
        "                    Thumbnail=item['snippet']['thumbnails']['default']['url'],\n",
        "                    Description=item['snippet'].get('description'),\n",
        "                    Published_Date=item['snippet']['publishedAt'],\n",
        "                    Duration=item['contentDetails']['duration'],\n",
        "                    Views=item['statistics'].get('viewCount'),\n",
        "                    Likes=item['statistics'].get('likeCount'),\n",
        "                    Comments=item['statistics'].get('commentCount'),\n",
        "                    Favourite_Count=item['statistics']['favoriteCount'],\n",
        "                    Definition=item['contentDetails']['definition'],\n",
        "                    Caption_status=item['contentDetails']['caption']\n",
        "                    )\n",
        "            video_data.append(data)\n",
        "    return video_data\n",
        "\n",
        "#Starting another for loop to iterate through each item in the items list of the API response.\n",
        "#Each item represents a video.\n",
        "\n",
        "#Creating a dictionary data for each video, containing various details extracted from the API response for that video.\n",
        "#Channel_Name: Extracts the channel's name from the video's \"snippet\" part.\n",
        "#Channel_Id: Extracts the channel's ID.\n",
        "#Video_Id: Extracts the ID of the video.\n",
        "#Title: Extracts the title of the video.\n",
        "#Tags: Extracts the tags associated with the video (if available).\n",
        "#Thumbnail: Extracts the URL of the video's default thumbnail.\n",
        "#Description: Extracts the video's description (if available).\n",
        "#Published_Date: Extracts the date when the video was published.\n",
        "#Duration: Extracts the duration of the video.\n",
        "#Views: Extracts the number of views the video has received (if available).\n",
        "#Likes: Extracts the number of likes the video has received (if available).\n",
        "#Comments: Extracts the number of comments the video has received (if available).\n",
        "#Favourite_Count: Extracts the number of times the video has been added to someone's favorites.\n",
        "#Definition: Extracts the video's definition (e.g., \"hd\", \"sd\", etc.).\n",
        "#Caption_status: Extracts the caption status of the video.\n",
        "\n",
        "#Appends the data dictionary, representing details of the current video, to the video_data list.\n",
        "\n",
        "#Finally, after iterating through all the videos and collecting their data, returns the video_data list containing dictionaries,\n",
        "#where each dictionary represents detailed information about a single video."
      ],
      "metadata": {
        "id": "HU7uz-Ok0qDK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python function get_video_data(video_ids) retrieves detailed information about each video in the video_ids list. It fetches data such as the video's title, channel name, ID, description, tags, thumbnail URL, published date, duration, views, likes, comments, favorite count, definition, and caption status from the YouTube Data API for each video. It then stores this information in dictionaries and returns a list video_data containing these dictionaries, one for each video in the input list."
      ],
      "metadata": {
        "id": "3Eb9nWE-Jj2Y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def get_comment_data(Video_ids):\n",
        "    Comment_data=[]\n",
        "    try:\n",
        "        for video_id in Video_ids:\n",
        "            request=youtube.commentThreads().list(\n",
        "                part=\"snippet\",\n",
        "                videoId=video_id,\n",
        "                maxResults=50\n",
        "            )\n",
        "            response=request.execute()\n",
        "\n",
        "#This function defines get_comment_data that takes a list of Video_ids as its input parameter.\n",
        "#The function is intended to retrieve data about comments for each video specified by its ID.\n",
        "#Initializes an empty list named Comment_data. This list will be used to store dictionaries, with each dictionary representing data for a single comment.\n",
        "#Starting with a try block, which is used to handle exceptions that may occur during the execution of the code within the block.\n",
        "#Creating a for loop that iterates through each video_id in the Video_ids list.\n",
        "#For each iteration, the loop will fetch comments for the video corresponding to that video_id.\n",
        "#Inside the loop, creating a request object to fetch comments associated with a specific video using the youtube.commentThreads().list method.\n",
        "#It specifies the part parameter to include only the \"snippet\" part of the commentThread resource.\n",
        "#The videoId parameter specifies which video's comments are being requested, and maxResults sets the maximum number of comments to retrieve per request to 50.\n",
        "#And then executing the request to the YouTube API and stores the response in the response variable.\n",
        "#It retrieves the comments associated with the current video_id.\n",
        "\n",
        "            for item in response['items']:\n",
        "                data=dict(Comment_Id=item['snippet']['topLevelComment']['id'],\n",
        "                        Video_Id=item['snippet']['topLevelComment']['snippet']['videoId'],\n",
        "                        Comment_Text=item['snippet']['topLevelComment']['snippet']['textDisplay'],\n",
        "                        Comment_Author=item['snippet']['topLevelComment']['snippet']['authorDisplayName'],\n",
        "                        Comment_Published=item['snippet']['topLevelComment']['snippet']['publishedAt'])\n",
        "\n",
        "                Comment_data.append(data)\n",
        "    except:\n",
        "        pass\n",
        "    return Comment_data\n",
        "\n",
        "#Creating another for loop to iterate through each item in the items list of the API response. Each item represents a comment associated with the video.\n",
        "#Creating a dictionary data for each comment, containing various details extracted from the API response for that comment.\n",
        "#Comment_Id: Extracts the ID of the comment.\n",
        "#Video_Id: Extracts the ID of the video to which the comment belongs.\n",
        "#Comment_Text: Extracts the text content of the comment.\n",
        "#Comment_Author: Extracts the display name of the author of the comment.\n",
        "#Comment_Published: Extracts the date and time when the comment was published.\n",
        "#Appending the data dictionary, representing details of the current comment, to the Comment_data list.\n",
        "\n",
        "#An exception handler. If an error occurs during the execution of the code within the try block, the except block catches the exception.\n",
        "#In this case, if any error occurs while fetching comments for a video, the pass statement is executed, which means the script will continue without raising an error.\n",
        "#This helps the script to continue processing other videos even if there's an issue with one.\n",
        "\n",
        "#Finally, after iterating through all the videos and collecting their associated comments, returns the `Comment_data` list containing dictionaries,\n",
        "#where each dictionary represents data about a single comment for a video.\n"
      ],
      "metadata": {
        "id": "eJmPgkrYJB1K"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python function `get_comment_data(Video_ids)` retrieves data about comments for each video in the `Video_ids` list. It uses the YouTube Data API to fetch comments associated with each video, extracts details such as comment ID, video ID, text content, author's display name, and published date. It then stores this information in dictionaries and returns a list `Comment_data` containing these dictionaries, one for each comment in the input list of video IDs."
      ],
      "metadata": {
        "id": "vl8zdjuvL7xI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def get_playlist_details(channel_id):\n",
        "\n",
        "    next_page_token=None\n",
        "    All_data=[]\n",
        "    while True:\n",
        "        request=youtube.playlists().list(\n",
        "            part='snippet,contentDetails',\n",
        "            channelId=channel_id,\n",
        "            maxResults=50,\n",
        "            pageToken=next_page_token\n",
        "        )\n",
        "        response=request.execute()\n",
        "\n",
        "#This function defines get_playlist_details that takes a channel_id as its input parameter.\n",
        "#The purpose of this function is to retrieve details about all the playlists associated with a specific YouTube channel.\n",
        "#initialize two variables:\n",
        "#next_page_token: This variable is set to None initially. It will be used to paginate through the list of playlists.\n",
        "#All_data: This variable is an empty list that will be used to store dictionaries, with each dictionary representing details of a single playlist.\n",
        "#Creating a while loop that will continue indefinitely until explicitly stopped. This loop will fetch all pages of playlists associated with the channel.\n",
        "#Inside the loop, creating a request object to fetch details about playlists associated with a specific channel using the youtube.playlists().list method.\n",
        "#It specifies the part parameter to include the \"snippet\" and \"contentDetails\" parts of the playlist resource.\n",
        "#The channelId parameter specifies which channel's playlists are being requested,\n",
        "#maxResults sets the maximum number of playlists to retrieve per request to 50, and pageToken specifies the token for the next page of results.\n",
        "#executing the request to the YouTube API and stores the response in the response variable.\n",
        "#It retrieves the details of playlists associated with the channel based on the provided channel_id.\n",
        "\n",
        "        for item in response['items']:\n",
        "            data=dict(Playlist_Id=item['id'],\n",
        "                    Title=item['snippet']['title'],\n",
        "                    Channel_Id=item['snippet']['channelId'],\n",
        "                    Channel_Name=item['snippet']['channelTitle'],\n",
        "                    Published_At=item['snippet']['publishedAt'],\n",
        "                    Video_Counts=item['contentDetails']['itemCount'])\n",
        "            All_data.append(data)\n",
        "\n",
        "        next_page_token=response.get('nextPageToken')\n",
        "        if next_page_token is None:\n",
        "            break\n",
        "    return All_data\n",
        "\n",
        "#Creating a another for loop to iterate through each item in the items list of the API response.\n",
        "#Each item represents a playlist associated with the channel.\n",
        "#Creating a dictionary data for each playlist, containing various details extracted from the API response for that playlist.\n",
        "#Playlist_Id: Extracts the ID of the playlist.\n",
        "#Title: Extracts the title of the playlist.\n",
        "#Channel_Id: Extracts the ID of the channel to which the playlist belongs.\n",
        "#Channel_Name: Extracts the name of the channel.\n",
        "#Published_At: Extracts the date and time when the playlist was published.\n",
        "#Video_Counts: Extracts the number of videos in the playlist.\n",
        "#Appending the data dictionary, representing details of the current playlist, to the All_data list.\n",
        "#Retrieving the nextPageToken from the API response. This token is used to request the next page of results from the playlists.\n",
        "\n",
        "#The `if` statement checks if there is no `nextPageToken`, indicating that there are no more playlists to fetch.\n",
        "#If there is no next page, the loop breaks, ending the process of fetching playlists.\n",
        "\n",
        "#Finally, after iterating through all the playlists and collecting their details, returns the All_data list containing dictionaries,\n",
        "#where each dictionary represents details of a single playlist associated with the input channel.\n"
      ],
      "metadata": {
        "id": "hA5tejDxMWgz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python function get_playlist_details(channel_id) retrieves details about all the playlists associated with a specific YouTube channel. It uses the YouTube Data API to fetch information such as playlist ID, title, channel ID, channel name, published date, and the number of videos in each playlist. It then stores this information in dictionaries and returns a list All_data containing these dictionaries, one for each playlist associated with the input channel."
      ],
      "metadata": {
        "id": "RPS2WIFyNz0a"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Then Connecting with MongoDB Server with username and password. Creating a Database in the name of Youtube_data"
      ],
      "metadata": {
        "id": "kl98Mi8Udj_n"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def channel_details(channel_id):\n",
        "    ch_data=get_channel_data(channel_id)\n",
        "    pl_details=get_playlist_details(channel_id)\n",
        "    vi_id=get_video_ids(channel_id)\n",
        "    vi_data=get_video_data(vi_id)\n",
        "    com_data=get_comment_data(vi_id)\n",
        "\n",
        "    coll1=db['channel_details']\n",
        "    coll1.insert_one({\"channel_information\":ch_data,\"playlist_information\":pl_details,\n",
        "                      \"video_information\":vi_data,\"comment_information\":com_data})\n",
        "\n",
        "    return \"upload completed successfully\"\n",
        "\n",
        "#This function defines channel_details that takes a channel_id as its input parameter.\n",
        "#The purpose of this function is to gather various information about a YouTube channel,\n",
        "#including its details, playlists, videos, and comments, and then store this information in a MongoDB database.\n",
        "\n",
        "#Calling the get_channel_data(channel_id) function to retrieve information about the specified YouTube channel.\n",
        "#It collects data such as the channel's name, ID, subscriber count, view count, video count, description, and upload playlist ID.\n",
        "#The returned data is stored in the ch_data variable.\n",
        "\n",
        "#Calling the get_playlist_details(channel_id) function to fetch details about all the playlists associated with the specified YouTube channel.\n",
        "#It collects data such as playlist IDs, titles, channel IDs, channel names, published dates, and the number of videos in each playlist.\n",
        "#The returned data is stored in the pl_details variable.\n",
        "\n",
        "#Calling the get_video_ids(channel_id) function to gather the video IDs of all the videos uploaded to the specified YouTube channel.\n",
        "#The vi_id variable will contain a list of these video IDs.\n",
        "\n",
        "#Calling the get_video_data(vi_id) function to retrieve detailed information about each video in the vi_id list.\n",
        "#It collects data such as\n",
        "#video IDs, titles, channel names, descriptions, tags, thumbnails, published dates, durations, views, likes, comments, favorite counts, definitions, and caption statuses.\n",
        "#The returned data is stored in the vi_data variable.\n",
        "\n",
        "#Calling the get_comment_data(vi_id) function to gather data about comments for each video in the vi_id list.\n",
        "#It collects data such as comment IDs, video IDs, comment text, comment authors, and comment published dates.\n",
        "#The returned data is stored in the com_data variable.\n",
        "\n",
        "#Establishing a connection to a MongoDB database and selects a collection named 'channel_details'.\n",
        "#The db variable likely represents the MongoDB database instance, where 'channel_details' is the collection within that database.\n",
        "\n",
        "#Inserting a new document into the 'channel_details' collection of the MongoDB database. The document contains the collected information about the YouTube channel:\n",
        "#\"channel_information\": Contains the data from ch_data, which includes details about the channel itself (name, ID, subscribers, views, etc.).\n",
        "#\"playlist_information\": Contains the data from pl_details, which includes details about all playlists associated with the channel.\n",
        "#\"video_information\": Contains the data from vi_data, which includes detailed information about each video uploaded to the channel.\n",
        "#\"comment_information\": Contains the data from com_data, which includes details about comments for each video.\n",
        "\n",
        "#Finally, after storing all the collected information in the MongoDB database,\n",
        "#it returns a string \"upload completed successfully\" as an indication that the data upload process was successful."
      ],
      "metadata": {
        "id": "2CbySjY9eMlS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python function channel_details(channel_id) collects various information about a specified YouTube channel, including its details, playlists, videos, and comments. It then stores this collected information as a single document in a MongoDB database collection named 'channel_details'. Each piece of information, such as channel data, playlist details, video details, and comment details, is stored under its respective key in the document."
      ],
      "metadata": {
        "id": "O2EZUat7fg0u"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def channels_table(selected_channels):\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                        user=\"postgres\",\n",
        "                        password=\"teddycoco\",\n",
        "                        database=\"postgres\",\n",
        "                        port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines channels_table that takes selected_channels as its input parameter.\n",
        "#The purpose of this function is to create a table in a PostgreSQL database to store information about selected YouTube channels.\n",
        "\n",
        "#Establishing connection to a PostgreSQL database using the psycopg2 library.\n",
        "#It specifies the host, user, password, database name, and port to connect to.\n",
        "#Then, it creates a cursor object cursor that allows executing SQL commands in the database.\n",
        "\n",
        "    create_query='''create table if not exists channels(Channel_Name varchar(100),\n",
        "                                                        Channel_Id varchar(80) primary key,\n",
        "                                                        Subscribers bigint,\n",
        "                                                        Views bigint,\n",
        "                                                        Total_videos int,\n",
        "                                                        Channel_Description text,\n",
        "                                                        Playlist_Id varchar(80))'''\n",
        "    cursor.execute(create_query)\n",
        "    mydb.commit()\n",
        "\n",
        "#This code defines an SQL query create_query to create a table named channels in the PostgreSQL database if it doesn't already exist.\n",
        "#The table has the following columns:\n",
        "# Channel_Name (varchar): Name of the YouTube channel.\n",
        "# Channel_Id (varchar primary key): ID of the YouTube channel (primary key).\n",
        "# Subscribers (bigint): Number of subscribers of the channel.\n",
        "# Views (bigint): Total number of views of the channel.\n",
        "# Total_videos (int): Total number of videos uploaded to the channel.\n",
        "# Channel_Description (text): Description of the channel.\n",
        "# Playlist_Id (varchar): ID of the channel's upload playlist.\n",
        "# The cursor.execute(create_query) executes this SQL query to create the channels table in the database.\n",
        "# mydb.commit() commits the changes made to the database, making the table creation permanent.\n",
        "\n",
        "    ch_list=[]\n",
        "    db=client[\"Youtube_data\"]\n",
        "    coll1=db[\"channel_details\"]\n",
        "    ch_data=coll1.find_one({\"channel_information.Channel_Name\":selected_channels},{\"_id\":0,\"channel_information\":1})\n",
        "    ch_list.append(ch_data[\"channel_information\"])\n",
        "    df=pd.DataFrame(ch_list)\n",
        "\n",
        "#Then initializing an empty list ch_list. This list will be used to store dictionaries containing information about the selected YouTube channels.\n",
        "#Establishing a connection to a MongoDB database named \"Youtube_data\".\n",
        "#Then, it selects a collection named \"channel_details\" from that database.\n",
        "#This collection likely contains information about various YouTube channels.\n",
        "\n",
        "#Querying the MongoDB collection \"channel_details\" to find information about the YouTube channels whose names match the selected_channels list.\n",
        "#It retrieves the information under the \"channel_information\" key for the matching channels and appends it to the ch_list.\n",
        "\n",
        "#Creating a pandas DataFrame df from the ch_list. This DataFrame will be used to iterate through the information about the selected YouTube channels.\n",
        "\n",
        "    for index,row in df.iterrows():\n",
        "        insert_query='''insert into channels(Channel_Name,\n",
        "                                            Channel_Id,\n",
        "                                            Subscribers,\n",
        "                                            Views,\n",
        "                                            Total_videos,\n",
        "                                            Channel_Description,\n",
        "                                            Playlist_Id)\n",
        "\n",
        "                                            values(%s,%s,%s,%s,%s,%s,%s)'''\n",
        "        values=(row['Channel_Name'],\n",
        "                row['Channel_Id'],\n",
        "                row['Subscribers'],\n",
        "                row['Views'],\n",
        "                row['Total_videos'],\n",
        "                row['Channel_Description'],\n",
        "                row['Playlist_Id'])\n",
        "\n",
        "        cursor.execute(insert_query,values)\n",
        "        mydb.commit()\n",
        "\n",
        "#Starting with a for loop to iterate through each row (row) in the pandas DataFrame df.\n",
        "#Each row represents information about a selected YouTube channel.\n",
        "\n",
        "#Inside the for loop, prepares an SQL query insert_query to insert the information about each YouTube channel into the channels table in the PostgreSQL database.\n",
        "#Then specifies the values for each column in the table using the values tuple.\n",
        "#cursor.execute(insert_query, values) executes the SQL insert_query with the specified values for the current channel.\n",
        "#mydb.commit() commits the changes to the database after each channel's information is inserted.\n",
        "\n",
        "#Finally, after inserting information about all the selected YouTube channels into the `channels` table, this line returns a string `\"upload completed successfully\"` as an indication that the data upload process was successful."
      ],
      "metadata": {
        "id": "k6J1TRJvejn_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This Python function `channels_table(selected_channels)` creates a table named `channels` in a PostgreSQL database to store information about selected YouTube channels. It retrieves information about the selected channels from a MongoDB collection named `\"channel_details\"`, creates a DataFrame from this information, and then iterates through each channel's data to insert it into the `channels` table. Each row in the `channels` table corresponds to a YouTube channel, with columns for the channel's name, ID, subscribers, views, total videos, description, and upload playlist ID.\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "54Ai5JdpiXqn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def playlist_table(selected_Channels):\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines playlist_table that takes selected_Channels as its input parameter.\n",
        "#The purpose of this function is to create a table in a PostgreSQL database to store information about playlists associated with selected YouTube channels.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#It specifies the host, user, password, database name, and port to connect to.\n",
        "#Then, it creates a cursor object cursor that allows executing SQL commands in the database.\n",
        "\n",
        "    create_query='''create table if not exists playlists(Playlist_Id varchar(100) primary key,\n",
        "                                                    Title varchar(100),\n",
        "                                                    Channel_Id varchar(100),\n",
        "                                                    Channel_Name varchar(100),\n",
        "                                                    Published_At timestamp,\n",
        "                                                    Video_Counts int\n",
        "                                                    )'''\n",
        "\n",
        "    cursor.execute(create_query)\n",
        "    mydb.commit()\n",
        "\n",
        "#This block of code defines an SQL query create_query to create a table named playlists in the PostgreSQL database if it doesn't already exist.\n",
        "#The table has the following columns:\n",
        "#Playlist_Id (varchar primary key): ID of the playlist (primary key).\n",
        "#Title (varchar): Title of the playlist.\n",
        "#Channel_Id (varchar): ID of the channel to which the playlist belongs.\n",
        "#Channel_Name (varchar): Name of the channel.\n",
        "#Published_At (timestamp): Date and time when the playlist was published.\n",
        "#Video_Counts (int): Total number of videos in the playlist.\n",
        "#The cursor.execute(create_query) executes this SQL query to create the playlists table in the database.\n",
        "#mydb.commit() commits the changes made to the database, making the table creation permanent.\n",
        "\n",
        "    pl_list=[]\n",
        "    db=client[\"Youtube_data\"]\n",
        "    coll1=db[\"channel_details\"]\n",
        "    for pl_data in coll1.find({\"channel_information.Channel_Name\":selected_Channels},{\"_id\":0,\"playlist_information\":1}):\n",
        "        for i in range(len(pl_data[\"playlist_information\"])):\n",
        "            pl_list.append(pl_data[\"playlist_information\"][i])\n",
        "    df1=pd.DataFrame(pl_list)\n",
        "\n",
        "#Initializing an empty list pl_list.\n",
        "#This list will be used to store dictionaries containing information about the playlists associated with the selected YouTube channels.\n",
        "\n",
        "#Establishing a connection to a MongoDB database named \"Youtube_data\".\n",
        "#Then, it selects a collection named \"channel_details\" from that database.\n",
        "#This collection likely contains information about various YouTube channels, including their playlists.\n",
        "\n",
        "#iterating through the playlists associated with the selected YouTube channels.\n",
        "# The coll1.find() method queries the MongoDB collection \"channel_details\" to find playlists whose channel names match the selected_Channels list.\n",
        "# For each matching playlist, it appends the playlist information, stored under the \"playlist_information\" key, to the pl_list.\n",
        "\n",
        "#Then creating a pandas DataFrame df1 from the pl_list. This DataFrame will be used to iterate through the information about the playlists.\n",
        "\n",
        "    for index,row in df1.iterrows():\n",
        "        insert_query='''insert into playlists(Playlist_Id,\n",
        "                                            Title,\n",
        "                                            Channel_Id,\n",
        "                                            Channel_Name,\n",
        "                                            Published_At,\n",
        "                                            Video_Counts\n",
        "                                            )\n",
        "\n",
        "                                            values(%s,%s,%s,%s,%s,%s)'''\n",
        "        values=(row['Playlist_Id'],\n",
        "                row['Title'],\n",
        "                row['Channel_Id'],\n",
        "                row['Channel_Name'],\n",
        "                row['Published_At'],\n",
        "                row['Video_Counts'])\n",
        "        try:\n",
        "            cursor.execute(insert_query,values)\n",
        "            mydb.commit()\n",
        "        except psycopg2.IntegrityError:\n",
        "            print(\"Already exist\")\n",
        "\n",
        "#Inside the for loop, prepares an SQL query insert_query to insert the information about each playlist into the playlists table in the PostgreSQL database.\n",
        "#It specifies the values for each column in the table using the values tuple.\n",
        "# cursor.execute(insert_query, values) executes the SQL insert_query with the specified values for the current playlist.\n",
        "# mydb.commit() commits the changes to the database after each playlist's information is inserted.\n",
        "\n",
        "# The try block catches any IntegrityError that might occur, indicating a duplicate primary key (Playlist_Id).\n",
        "#If such an error occurs, it prints \"Already exist\" and continues to the next playlist.\n",
        "\n",
        "#Finally, after inserting information about all the playlists associated with the selected YouTube channels into the `playlists` table,\n",
        "#this line returns a string `\"upload completed successfully\"` as an indication that the data upload process was successful.\n"
      ],
      "metadata": {
        "id": "yx5xUD0PirxG"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This function playlist_table(selected_Channels) connects to a PostgreSQL database, creates a playlists table if it doesn't exist, retrieves playlist information from a MongoDB collection for the specified channels, and inserts this data into the playlists table. It handles any integrity errors that might occur during the insertion process.\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "2sNKANrekE-v"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def videos_table(selected_channels):\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines videos_table that takes selected_channels as its input parameter.\n",
        "#The purpose of this function is to create a table in a PostgreSQL database to store information about videos from selected YouTube channels.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#It specifies the host, user, password, database name, and port to connect to.\n",
        "#Then, it creates a cursor object cursor that allows executing SQL commands in the database.\n",
        "\n",
        "    create_query='''create table if not exists videos(Channel_Name varchar(100),\n",
        "                                                    Channel_Id varchar(100),\n",
        "                                                    Video_Id varchar(30) primary key,\n",
        "                                                    Title varchar(150),\n",
        "                                                    Tags text,\n",
        "                                                    Thumbnail varchar(250),\n",
        "                                                    Description text,\n",
        "                                                    Published_Date timestamp,\n",
        "                                                    Duration interval,\n",
        "                                                    Views bigint,\n",
        "                                                    Likes bigint,\n",
        "                                                    Comments int,\n",
        "                                                    Favourite_Count int,\n",
        "                                                    Definition varchar(10),\n",
        "                                                    Caption_status varchar(50)\n",
        "                                                    )'''\n",
        "\n",
        "    cursor.execute(create_query)\n",
        "    mydb.commit()\n",
        "\n",
        "#Defining an SQL query create_query to create a table named videos in the PostgreSQL database if it doesn't already exist.\n",
        "#The table has the following columns:\n",
        "# Channel_Name (varchar): Name of the YouTube channel.\n",
        "# Channel_Id (varchar): ID of the YouTube channel.\n",
        "# Video_Id (varchar primary key): ID of the video (primary key).\n",
        "# Title (varchar): Title of the video.\n",
        "# Tags (text): Tags associated with the video.\n",
        "# Thumbnail (varchar): URL of the video's thumbnail image.\n",
        "# Description (text): Description of the video.\n",
        "# Published_Date (timestamp): Date and time when the video was published.\n",
        "# Duration (interval): Duration of the video.\n",
        "# Views (bigint): Total number of views of the video.\n",
        "# Likes (bigint): Number of likes on the video.\n",
        "# Comments (int): Number of comments on the video.\n",
        "# Favourite_Count (int): Number of times the video has been added to favorites.\n",
        "# Definition (varchar): Definition of the video quality.\n",
        "# Caption_status (varchar): Status of captions for the video.\n",
        "# The cursor.execute(create_query) executes this SQL query to create the videos table in the database.\n",
        "# mydb.commit() commits the changes made to the database, making the table creation permanent.\n",
        "\n",
        "    vi_list=[]\n",
        "    db=client[\"Youtube_data\"]\n",
        "    coll1=db[\"channel_details\"]\n",
        "    for vi_data in coll1.find({\"channel_information.Channel_Name\":selected_channels},{\"_id\":0,\"video_information\":1}):\n",
        "        for i in range(len(vi_data[\"video_information\"])):\n",
        "            vi_list.append(vi_data[\"video_information\"][i])\n",
        "    df2=pd.DataFrame(vi_list)\n",
        "\n",
        "#Initializing an empty list vi_list.\n",
        "#This list will be used to store dictionaries containing information about the videos associated with the selected YouTube channels.\n",
        "#Establishing a connection to a MongoDB database named \"Youtube_data\".\n",
        "#Then, it selects a collection named \"channel_details\" from that database.\n",
        "#This collection likely contains information about various YouTube channels, including their videos.\n",
        "\n",
        "#Iterating through the videos associated with the selected YouTube channels.\n",
        "# The coll1.find() method queries the MongoDB collection \"channel_details\" to find videos whose channel names match the selected_channels list.\n",
        "# For each matching video, it appends the video information, stored under the \"video_information\" key, to the vi_list.\n",
        "#Then creating a pandas DataFrame df2 from the vi_list. This DataFrame will be used to iterate through the information about the videos.\n",
        "\n",
        "    for index,row in df2.iterrows():\n",
        "        insert_query='''insert into videos(Channel_Name,\n",
        "                                                Channel_Id,\n",
        "                                                Video_Id,\n",
        "                                                Title,\n",
        "                                                Thumbnail,\n",
        "                                                Description,\n",
        "                                                Published_Date,\n",
        "                                                Duration,\n",
        "                                                Views,\n",
        "                                                Likes,\n",
        "                                                Comments,\n",
        "                                                Favourite_Count,\n",
        "                                                Definition,\n",
        "                                                Caption_status\n",
        "                                                )\n",
        "\n",
        "                                            values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''\n",
        "\n",
        "        values=(row['Channel_Name'],\n",
        "                row['Channel_Id'],\n",
        "                row['Video_Id'],\n",
        "                row['Title'],\n",
        "                row['Thumbnail'][0:50],\n",
        "                row['Description'],\n",
        "                row['Published_Date'],\n",
        "                row['Duration'],\n",
        "                row['Views'],\n",
        "                row['Likes'],\n",
        "                row['Comments'],\n",
        "                row['Favourite_Count'],\n",
        "                row['Definition'],\n",
        "                row['Caption_status'])\n",
        "\n",
        "        cursor.execute(insert_query,values)\n",
        "        mydb.commit()\n",
        "\n",
        "#Starting a for loop to iterate through each row (row) in the pandas DataFrame df2.\n",
        "#Each row represents information about a video associated with the selected YouTube channels.\n",
        "\n",
        "# Inside the for loop, this block of code prepares an SQL query insert_query to insert the information about each video into the videos table in the PostgreSQL database.\n",
        "# It specifies the values for each column in the table using the values tuple.\n",
        "# The Thumbnail URL is truncated to the first 50 characters to fit into the varchar(250) column.\n",
        "# cursor.execute(insert_query, values) executes the SQL insert_query with the specified values for the current video.\n",
        "# mydb.commit() commits the changes to the database after each video's information is inserted."
      ],
      "metadata": {
        "id": "ZyhGhjhOlLMr"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This function videos_table(selected_channels) compiles detailed video information from specified YouTube channels. This includes channel name, ID, video details, and metrics like views and likes. It inserts this data into a PostgreSQL videos table, handling any insertion errors for seamless storage. All changes are then committed for future retrieval."
      ],
      "metadata": {
        "id": "Pzg_81C1mX_6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def comments_table(selected_channels):\n",
        "\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines comments_table that takes selected_channels as its input parameter.\n",
        "#The purpose of this function is to create a table in a PostgreSQL database to store information about comments on videos from selected YouTube channels.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#It specifies the host, user, password, database name, and port to connect to.\n",
        "#Then, it creates a cursor object cursor that allows executing SQL commands in the database.\n",
        "\n",
        "    create_query='''create table if not exists comments(Comment_Id varchar(100) primary key,\n",
        "                                                        Video_Id varchar(50),\n",
        "                                                        Comment_Text text,\n",
        "                                                        Comment_Author varchar(150),\n",
        "                                                        Comment_Published timestamp\n",
        "                                                    )'''\n",
        "\n",
        "    cursor.execute(create_query)\n",
        "    mydb.commit()\n",
        "\n",
        "# Defining an SQL query create_query to create a table named comments in the PostgreSQL database if it doesn't already exist.\n",
        "# The table has the following columns:\n",
        "# Comment_Id (varchar): ID of the comment (primary key).\n",
        "# Video_Id (varchar): ID of the video to which the comment belongs.\n",
        "# Comment_Text (text): The actual text content of the comment.\n",
        "# Comment_Author (varchar): The display name of the comment author.\n",
        "# Comment_Published (timestamp): The timestamp when the comment was published.\n",
        "# The cursor.execute(create_query) executes this SQL query to create the comments table in the database.\n",
        "# mydb.commit() commits the changes made to the database, making the table creation permanent.\n",
        "\n",
        "    com_list=[]\n",
        "    db=client[\"Youtube_data\"]\n",
        "    coll1=db[\"channel_details\"]\n",
        "    for com_data in coll1.find({\"channel_information.Channel_Name\":selected_channels},{\"_id\":0,\"comment_information\":1}):\n",
        "        for i in range(len(com_data[\"comment_information\"])):\n",
        "            com_list.append(com_data[\"comment_information\"][i])\n",
        "    df3=pd.DataFrame(com_list)\n",
        "\n",
        "#Then initializes an empty list com_list.\n",
        "#This list will be used to store dictionaries containing information about the comments associated with the selected YouTube channels.\n",
        "\n",
        "#The existence of a MongoDB client client connected to a database named \"Youtube_data\".\n",
        "#It then selects a collection named \"channel_details\" from that database.\n",
        "#This collection likely contains information about various YouTube channels, including their comments.\n",
        "\n",
        "#Iterating through the comments associated with the selected YouTube channels.\n",
        "# The coll1.find() method queries the MongoDB collection \"channel_details\" to find comments whose channel names match the selected_channels list.\n",
        "# For each matching comment, it appends the comment information, stored under the \"comment_information\" key, to the com_list.\n",
        "\n",
        "#Then creating a pandas DataFrame df3 from the com_list. This DataFrame will be used to iterate through the information about the comments.\n",
        "\n",
        "    for index,row in df3.iterrows():\n",
        "            insert_query='''insert into comments(Comment_Id,\n",
        "                                                Video_Id,\n",
        "                                                Comment_Text,\n",
        "                                                Comment_Author,\n",
        "                                                Comment_Published\n",
        "                                                )\n",
        "\n",
        "                                                values(%s,%s,%s,%s,%s)'''\n",
        "\n",
        "\n",
        "            values=(row['Comment_Id'],\n",
        "                    row['Video_Id'],\n",
        "                    row['Comment_Text'],\n",
        "                    row['Comment_Author'],\n",
        "                    row['Comment_Published']\n",
        "                    )\n",
        "\n",
        "            try:\n",
        "                    cursor.execute(insert_query,values)\n",
        "                    mydb.commit()\n",
        "            except psycopg2.IntegrityError:\n",
        "                mydb.rollback()\n",
        "                pass\n",
        "\n",
        "#Inside the for loop, it prepares an SQL query insert_query to insert the information about each comment into the comments table in the PostgreSQL database.\n",
        "#It specifies the values for each column in the table using the values tuple.\n",
        "# cursor.execute(insert_query, values) executes the SQL insert_query with the specified values for the current comment.\n",
        "# In case of an integrity error (e.g., duplicate primary key), it rolls back the transaction (mydb.rollback()) and continues to the next comment using pass."
      ],
      "metadata": {
        "id": "4zeohm00nzKf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This function comments_table(selected_channels) connects to a PostgreSQL database, creates a comments table if it doesn't exist, retrieves comment information from a MongoDB collection for the specified channels, and inserts this data into the comments table. It handles any potential integrity errors during insertion and commits the changes to the database after each comment's information is inserted."
      ],
      "metadata": {
        "id": "CQKYlz9NpKAY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def tables(selected_channels):\n",
        "    if not check_channel_exists(selected_channels):\n",
        "        channels_table(selected_channels)\n",
        "        playlist_table(selected_channels)\n",
        "        videos_table(selected_channels)\n",
        "        comments_table(selected_channels)\n",
        "        st.success(\"Table Created Successfully\")\n",
        "    else:\n",
        "        st.error(\"Channel already exist\")\n",
        "\n",
        "#This function defines tables that takes selected_channels as its input parameter.\n",
        "#The purpose of this function is to create tables in a database for storing various information related to the specified YouTube channels.\n",
        "\n",
        "#If the specified channels already exist in the database by calling the check_channel_exists(selected_channels) function.\n",
        "#The check_channel_exists function likely verifies whether the given channels are already present in the database.\n",
        "\n",
        "#If the channels do not already exist, it calls the channels_table(selected_channels) function.\n",
        "#This function is responsible for creating a table to store information about the specified YouTube channels, such as channel name, ID, subscribers, views, total videos, description, and playlist ID.\n",
        "\n",
        "#Next, it calls the playlist_table(selected_channels) function.\n",
        "#This function creates a table to store details about playlists associated with the specified channels, including playlist ID, title, channel ID, channel name, publication date, and the number of videos in each playlist.\n",
        "\n",
        "#The videos_table(selected_channels) function.\n",
        "#This function is responsible for creating a table to store comprehensive information about videos from the specified channels.\n",
        "#This includes details such as channel name, channel ID, video ID (primary key), title, tags, thumbnail URL, description, publication date, duration, views, likes, comments, favorite count, video definition, and caption status.\n",
        "\n",
        "#The comments_table(selected_channels) function.\n",
        "#This function creates a table to store information about comments on videos from the specified channels.\n",
        "#It includes columns such as comment ID (primary key), video ID, comment text, author, and publication timestamp.\n",
        "\n",
        "#If all tables are successfully created, it displays a success message using the st.success() function.\n",
        "#This message indicates that the tables were created without any issues.\n",
        "\n",
        "#If the specified channels already exist in the database (as determined by check_channel_exists(selected_channels)),\n",
        "#this displays an error message using the st.error() function.\n",
        "#This message informs the user that the channels already exist, and the tables were not recreated."
      ],
      "metadata": {
        "id": "uL5HWgF6pkwT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# The tables(selected_channels) function checks if the specified YouTube channels already exist in the database. If not, it creates tables to store detailed information about the channels, their playlists, videos, and comments. It then displays a success message if the tables are created successfully, or an error message if the channels already exist in the database."
      ],
      "metadata": {
        "id": "1zlMb_2Aq4Q4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def check_channel_exists(selected_channels):\n",
        "    mydb = psycopg2.connect(\n",
        "        host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "        user=\"postgres\",\n",
        "        password=\"teddycoco\",\n",
        "        database=\"postgres\",\n",
        "        port=\"5432\"\n",
        "    )\n",
        "    cursor = mydb.cursor()\n",
        "\n",
        "#This function defines check_channel_exists that takes selected_channels as its input parameter.\n",
        "#The purpose of this function is to check if the specified YouTube channels already exist in the database.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#The connection parameters include the host, user, password, database name, and port number.\n",
        "#A cursor object cursor is then created, which allows executing SQL commands in the database.\n",
        "\n",
        "    try:\n",
        "        select_query = \"SELECT COUNT(*) FROM channels WHERE Channel_Name = %s\"\n",
        "        cursor.execute(select_query, (selected_channels,))\n",
        "        count = cursor.fetchone()[0]\n",
        "\n",
        "        cursor.close()\n",
        "        mydb.close()\n",
        "    except:\n",
        "        print(\"Table Created\")\n",
        "        count=1\n",
        "\n",
        "    return count > 0\n",
        "\n",
        "#Inside the try block, defining an SQL query select_query to count the number of rows where the Channel_Name column in the channels table matches the selected_channels parameter.\n",
        "\n",
        "# The cursor.execute(select_query, (selected_channels,)) statement executes the SQL query with the value of selected_channels.\n",
        "#This is a parameterized query to prevent SQL injection.\n",
        "\n",
        "# cursor.fetchone()[0] fetches the first (and only) result of the query, which is the count of rows where the Channel_Name matches selected_channels.\n",
        "#This count is stored in the variable count.\n",
        "\n",
        "#Closing the cursor and the database connection to release the resources after the SQL operation is complete.\n",
        "\n",
        "#If an exception occurs during the execution of the SQL query (e.g., if the table does not exist), the code inside the except block is executed.\n",
        "#In this case, it prints \"Table Created\" to indicate that the channels table was not found, and sets count to 1.\n",
        "#This is done to ensure that the function returns True (indicating the channel exists) in this exceptional case.\n",
        "\n",
        "#Finally, the function returns True if count is greater than 0, indicating that the specified YouTube channels already exist in the channels table of the database.\n",
        "#Otherwise, it returns False."
      ],
      "metadata": {
        "id": "MKTLzHYeqbL2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# The check_channel_exists(selected_channels) function connects to a PostgreSQL database, executes a query to count the rows where the Channel_Name matches the selected_channels, and returns True if any matches are found (indicating the channels exist in the database). If an exception occurs during the query execution (e.g., if the table does not exist), it returns True by default to prevent errors when creating the tables.\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "su1xUgeP3Kib"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def show_channel_table():\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines show_channel_table().\n",
        "#The purpose of this function is to retrieve and display all the data from the channels table in a PostgreSQL database.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#The connection parameters include the host, user, password, database name, and port number.\n",
        "#A cursor object cursor is then created, which allows executing SQL commands in the database.\n",
        "\n",
        "    create_query=\"select * from channels\"\n",
        "    cursor.execute(create_query)\n",
        "    result = cursor.fetchall()\n",
        "    df = pd.DataFrame(result, columns=[desc[0] for desc in cursor.description])\n",
        "\n",
        "    return df\n",
        "\n",
        "#Defining an SQL query create_query to select all rows and columns (*) from the channels table in the database.\n",
        "#This query will retrieve all the data present in the channels table.\n",
        "\n",
        "#Then executing the SQL query create_query using the cursor. It fetches all the rows from the channels table in the PostgreSQL database.\n",
        "\n",
        "#After executing the SQL query, fetching all the rows returned by the query from the cursor.\n",
        "#The fetchall() method retrieves all the rows as a list of tuples, where each tuple represents a row of data from the channels table.\n",
        "\n",
        "#Creating a pandas DataFrame df from the fetched result. It takes the result, which is a list of tuples representing rows of data, and converts it into a DataFrame.\n",
        "#The columns parameter is set to [desc[0] for desc in cursor.description].\n",
        "#This part extracts the column names from the cursor's description attribute, which provides information about the columns returned by the query.\n",
        "#It ensures that the DataFrame columns have the correct names.\n",
        "\n",
        "#Finally, returns the pandas DataFrame df that contains all the data from the channels table in the PostgreSQL database."
      ],
      "metadata": {
        "id": "lnXYACUa2WWx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# The show_channel_table() function connects to a PostgreSQL database, executes an SQL query to select all rows from the channels table, fetches the result, and converts it into a pandas DataFrame. This DataFrame is then returned, containing all the data from the channels table, ready for display or further processing."
      ],
      "metadata": {
        "id": "nzwq5rtX4-J5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def show_playlist_table():\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines show_playlist_table().\n",
        "#The purpose of this function is to retrieve and display all the data from the playlists table in a PostgreSQL database.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#The connection parameters include the host, user, password, database name, and port number.\n",
        "#A cursor object cursor is then created, which allows executing SQL commands in the database.\n",
        "\n",
        "    create_query=\"select * from playlists\"\n",
        "    cursor.execute(create_query)\n",
        "    result = cursor.fetchall()\n",
        "    df1=pd.DataFrame(result, columns=[desc[0] for desc in cursor.description])\n",
        "\n",
        "    return df1\n",
        "\n",
        "#Defining an SQL query create_query to select all rows and columns (*) from the playlists table in the database.\n",
        "#This query will retrieve all the data present in the playlists table.\n",
        "\n",
        "#Executing the SQL query create_query using the cursor. It fetches all the rows from the playlists table in the PostgreSQL database.\n",
        "\n",
        "#After executing the SQL query, fetching all the rows returned by the query from the cursor.\n",
        "#The fetchall() method retrieves all the rows as a list of tuples, where each tuple represents a row of data from the playlists table.\n",
        "\n",
        "#Creating a pandas DataFrame df1 from the fetched result.\n",
        "#It takes the result, which is a list of tuples representing rows of data, and converts it into a DataFrame.\n",
        "#The columns parameter is set to [desc[0] for desc in cursor.description].\n",
        "#This part extracts the column names from the cursor's description attribute, which provides information about the columns returned by the query.\n",
        "#It ensures that the DataFrame columns have the correct names.\n",
        "\n",
        "#Finally, returns the pandas DataFrame df1 that contains all the data from the playlists table in the PostgreSQL database."
      ],
      "metadata": {
        "id": "NMv8LDGy45zU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# The show_playlist_table() function connects to a PostgreSQL database, executes an SQL query to select all rows from the playlists table, fetches the result, and converts it into a pandas DataFrame. This DataFrame is then returned, containing all the data from the playlists table, ready for display or further processing."
      ],
      "metadata": {
        "id": "yje8kgYh6ISx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def show_video_table():\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines show_video_table().\n",
        "#The purpose of this function is to retrieve and display all the data from the videos table in a PostgreSQL database.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#The connection parameters include the host, user, password, database name, and port number.\n",
        "#A cursor object cursor is then created, which allows executing SQL commands in the database.\n",
        "\n",
        "    create_query=\"select * from videos\"\n",
        "    cursor.execute(create_query)\n",
        "    result = cursor.fetchall()\n",
        "    df2=pd.DataFrame(result, columns=[desc[0] for desc in cursor.description])\n",
        "\n",
        "    return df2\n",
        "\n",
        "#Defining an SQL query create_query to select all rows and columns (*) from the videos table in the database.\n",
        "#This query will retrieve all the data present in the videos table.\n",
        "\n",
        "#Executing the SQL query create_query using the cursor. It fetches all the rows from the videos table in the PostgreSQL database.\n",
        "\n",
        "#After executing the SQL query, fetching all the rows returned by the query from the cursor.\n",
        "#The fetchall() method retrieves all the rows as a list of tuples, where each tuple represents a row of data from the videos table.\n",
        "\n",
        "#Creating a pandas DataFrame df2 from the fetched result.\n",
        "#It takes the result, which is a list of tuples representing rows of data, and converts it into a DataFrame.\n",
        "#The columns parameter is set to [desc[0] for desc in cursor.description].\n",
        "#This part extracts the column names from the cursor's description attribute, which provides information about the columns returned by the query.\n",
        "#It ensures that the DataFrame columns have the correct names.\n",
        "\n",
        "#Finally, returns the pandas DataFrame df2 that contains all the data from the videos table in the PostgreSQL database."
      ],
      "metadata": {
        "id": "LCQLJ_XN6UHA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# The show_video_table() function connects to a PostgreSQL database, executes an SQL query to select all rows from the videos table, fetches the result, and converts it into a pandas DataFrame. This DataFrame is then returned, containing all the data from the videos table, ready for display or further processing."
      ],
      "metadata": {
        "id": "Rnhjd1BM7YPu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def show_comment_table():\n",
        "    mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                            user=\"postgres\",\n",
        "                            password=\"teddycoco\",\n",
        "                            database=\"postgres\",\n",
        "                            port=\"5432\")\n",
        "    cursor=mydb.cursor()\n",
        "\n",
        "#This function defines show_comment_table().\n",
        "#The purpose of this function is to retrieve and display all the data from the comments table in a PostgreSQL database.\n",
        "\n",
        "#Establishing a connection to a PostgreSQL database using the psycopg2 library.\n",
        "#The connection parameters include the host, user, password, database name, and port number.\n",
        "#A cursor object cursor is then created, which allows executing SQL commands in the database.\n",
        "\n",
        "    create_query=\"select * from comments\"\n",
        "    cursor.execute(create_query)\n",
        "    result = cursor.fetchall()\n",
        "    df3=pd.DataFrame(result, columns=[desc[0] for desc in cursor.description])\n",
        "\n",
        "    return df3\n",
        "\n",
        "#Defining an SQL query create_query to select all rows and columns (*) from the comments table in the database.\n",
        "#This query will retrieve all the data present in the comments table.\n",
        "\n",
        "#Executing the SQL query create_query using the cursor. It fetches all the rows from the comments table in the PostgreSQL database.\n",
        "\n",
        "#After executing the SQL query, fetching all the rows returned by the query from the cursor.\n",
        "#The fetchall() method retrieves all the rows as a list of tuples, where each tuple represents a row of data from the comments table.\n",
        "\n",
        "#Creating a pandas DataFrame df3 from the fetched result. It takes the result, which is a list of tuples representing rows of data, and converts it into a DataFrame.\n",
        "#The columns parameter is set to [desc[0] for desc in cursor.description].\n",
        "#This part extracts the column names from the cursor's description attribute, which provides information about the columns returned by the query.\n",
        "#It ensures that the DataFrame columns have the correct names.\n",
        "\n",
        "#Finally, returns the pandas DataFrame df3 that contains all the data from the comments table in the PostgreSQL database."
      ],
      "metadata": {
        "id": "FAF5ECdV7ngS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# The show_comment_table() function connects to a PostgreSQL database, executes an SQL query to select all rows from the comments table, fetches the result, and converts it into a pandas DataFrame. This DataFrame is then returned, containing all the data from the comments table, ready for display or further processing."
      ],
      "metadata": {
        "id": "t1S_5n918O2Q"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with st.sidebar:\n",
        "    st.title(\":red[YOUTUBE DATA HARVESTING AND WAREHOUSING]\")\n",
        "    st.header(\"Expertise Gain\")\n",
        "    st.caption(\"Python Scripting\")\n",
        "    st.caption(\"Data Collection\")\n",
        "    st.caption(\"MongoDB\")\n",
        "    st.caption(\"API Integration\")\n",
        "    st.caption(\"Data Management using MongoDB and SQL\")\n",
        "\n",
        "channel_id=st.text_input(\"Enter the channel ID\")\n",
        "\n",
        "#Creating a sidebar using Streamlit's st.sidebar function.\n",
        "#It includes a title, header, and captions to provide information about the purpose of the application and the expertise gained.\n",
        "#The titles, headers, and captions are displayed in the sidebar.\n",
        "\n",
        "#Then creating a text input field where the user can enter a YouTube channel ID.\n",
        "#The entered channel ID will be used to collect and store data for the specified YouTube channel.\n",
        "\n",
        "if st.button(\"collect and store data\"):\n",
        "    ch_ids=[]\n",
        "    db=client[\"Youtube_data\"]\n",
        "    coll1=db[\"channel_details\"]\n",
        "    for ch_data in coll1.find({},{\"_id\":0,\"channel_information\":1}):\n",
        "        ch_ids.append(ch_data[\"channel_information\"][\"Channel_Id\"])\n",
        "\n",
        "    if channel_id in ch_ids:\n",
        "        st.success(\"Channel Details of the given channel id is already exists\")\n",
        "\n",
        "    else:\n",
        "        insert=channel_details(channel_id)\n",
        "        st.success(insert)\n",
        "\n",
        "#Checks if a button with the label \"collect and store data\" is clicked.\n",
        "#The subsequent code will be executed when the button is clicked.\n",
        "\n",
        "#Initializing an empty list ch_ids and connects to the MongoDB database named \"Youtube_data\".\n",
        "#It also accesses the \"channel_details\" collection within the database.\n",
        "\n",
        "#The for loop fetches all channel IDs (Channel_Id) from the \"channel_details\" collection in MongoDB.\n",
        "#It appends these channel IDs to the ch_ids list.\n",
        "\n",
        "#Checks if the entered channel_id already exists in the ch_ids list.\n",
        "#If the channel ID is found in the list, it displays a success message indicating that the channel details already exist in the database.\n",
        "\n",
        "#If the entered channel_id is not found in the ch_ids list, it calls the channel_details() function to collect and store data for the specified channel.\n",
        "#It then displays a success message indicating that the channel details have been successfully inserted into the database.\n",
        "\n",
        "ch_name=[]\n",
        "db=client[\"Youtube_data\"]\n",
        "coll1=db[\"channel_details\"]\n",
        "for ch_data in coll1.find({},{\"_id\":0,\"channel_information\":1}):\n",
        "    ch_name.append(ch_data[\"channel_information\"][\"Channel_Name\"])\n",
        "selected_channel=st.selectbox(\"choose channel\", ch_name)\n",
        "if st.button(\"Migrate to Sql\"):\n",
        "\n",
        "    Table=tables(selected_channel)\n",
        "\n",
        "#Again initializing an empty list ch_name and connects to the MongoDB database named \"Youtube_data\".\n",
        "#It also accesses the \"channel_details\" collection within the database.\n",
        "\n",
        "#The for loop fetches all channel names (Channel_Name) from the \"channel_details\" collection in MongoDB.\n",
        "#It appends these channel names to the ch_name list.\n",
        "\n",
        "#Creating a select box (`st.selectbox`) where the user can choose a channel name from the `ch_name` list.\n",
        "#The selected channel will be used to display specific data from the tables.\n",
        "\n",
        "#Then checks if a button with the label \"Migrate to Sql\" is clicked.\n",
        "#The subsequent code will be executed when the button is clicked.\n",
        "\n",
        "#Then calls the `tables()` function with the `selected_channel` as an argument.\n",
        "#The `tables()` function is responsible for creating and migrating data to SQL tables based on the selected channel.\n",
        "\n",
        "show_table=st.radio(\"SELECT THE TABLE FOR VIEW\",(\"CHANNELS\",\"PLAYLISTS\",\"VIDEOS\",\"COMMENTS\"))\n",
        "\n",
        "if show_table==\"CHANNELS\":\n",
        "    dataframe=show_channel_table()\n",
        "    st.write(dataframe)\n",
        "\n",
        "elif show_table==\"PLAYLISTS\":\n",
        "    dataframe=show_playlist_table()\n",
        "    st.write(dataframe)\n",
        "\n",
        "elif show_table==\"VIDEOS\":\n",
        "    dataframe=show_video_table()\n",
        "    st.write(dataframe)\n",
        "\n",
        "elif show_table==\"COMMENTS\":\n",
        "    dataframe=show_comment_table()\n",
        "    st.write(dataframe)\n",
        "\n",
        "#Then creating a radio button (st.radio) where the user can select the table they want to view.\n",
        "#The options are \"CHANNELS\", \"PLAYLISTS\", \"VIDEOS\", and \"COMMENTS\".\n",
        "\n",
        "#If the \"CHANNELS\" option is selected, it calls the `show_channel_table()` function to retrieve data from the \"channels\" table in the SQL database.\n",
        "#The retrieved data is then displayed using `st.write()`.\n",
        "\n",
        "#If the \"PLAYLISTS\" option is selected, it calls the show_playlist_table() function to retrieve data from the \"playlists\" table in the SQL database.\n",
        "#The retrieved data is then displayed using st.write()\n",
        "\n",
        "#If the \"VIDEOS\" option is selected, it calls the `show_video_table()` function to retrieve data from the \"videos\" table in the SQL database.\n",
        "#The retrieved data is then displayed using `st.write()`.\n",
        "\n",
        "#If the \"COMMENTS\" option is selected, it calls the show_comment_table() function to retrieve data from the \"comments\" table in the SQL database.\n",
        "#The retrieved data is then displayed using st.write().\n"
      ],
      "metadata": {
        "id": "3JygstTF8dlY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This code creates a Streamlit web application with a sidebar containing information about the project and expertise gained. It allows users to input a YouTube channel ID, collect and store data for the specified channel, and view the data from different tables (\"CHANNELS\", \"PLAYLISTS\", \"VIDEOS\", \"COMMENTS\"). The data is fetched from the corresponding SQL tables using functions such as show_channel_table(), show_playlist_table(), show_video_table(), and show_comment_table(), and displayed in the Streamlit app based on the user's selection. Additionally, it checks if the entered channel ID already exists in the database to avoid duplicate entries."
      ],
      "metadata": {
        "id": "pOLUD66U_bcq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "mydb=psycopg2.connect(host=\"database-1.ct0uu0e2edw8.eu-north-1.rds.amazonaws.com\",\n",
        "                        user=\"postgres\",\n",
        "                        password=\"teddycoco\",\n",
        "                        database=\"postgres\",\n",
        "                        port=\"5432\")\n",
        "cursor=mydb.cursor()\n",
        "\n",
        "#Establishing a connection to the PostgreSQL database hosted at the specified host, using the provided credentials.\n",
        "#Creating a cursor object cursor to interact with the database.\n",
        "\n",
        "question=st.selectbox(\"Select your question\",(\"1. All the videos and the channel name\",\n",
        "                                              \"2. Channels with most number of videos\",\n",
        "                                              \"3. 10 most viewed videos\",\n",
        "                                              \"4. Comments in each videos\",\n",
        "                                              \"5. Videos with highest likes\",\n",
        "                                              \"6. Likes of all videos\",\n",
        "                                              \"7. Views of each channel\",\n",
        "                                              \"8. Videos publieshed in the year of 2022\",\n",
        "                                              \"9. Avearage duration of all videos in each channel\",\n",
        "                                              \"10. Videos with highest number of comments\"))\n",
        "\n",
        "#Then creating a Streamlit selectbox question for the user to choose a specific query.\n",
        "\n",
        "if question==\"1. All the videos and the channel name\":\n",
        "    query1='''select title as videos,channel_name as channelname from videos'''\n",
        "    cursor.execute(query1)\n",
        "    mydb.commit()\n",
        "    t1=cursor.fetchall()\n",
        "    df=pd.DataFrame(t1,columns=[\"video title\",\"channel name\"])\n",
        "    st.write(df)\n",
        "\n",
        "#If the user selects the first question, it executes a SQL query to fetch all video titles and corresponding channel names from the \"videos\" table.\n",
        "#The results are fetched using cursor.fetchall() and converted into a Pandas DataFrame df.\n",
        "#The DataFrame df is then displayed using st.write().\n",
        "\n",
        "elif question==\"2. Channels with most number of videos\":\n",
        "    query2='''select channel_name as channelname,total_videos as no_videos from channels\n",
        "                order by total_videos desc'''\n",
        "    cursor.execute(query2)\n",
        "    mydb.commit()\n",
        "    t2=cursor.fetchall()\n",
        "    df2=pd.DataFrame(t2,columns=[\"channel name\",\"No of videos\"])\n",
        "    st.write(df2)\n",
        "\n",
        "#If the user selects the second question, it executes a SQL query to fetch channel names and the total number of videos for each channel from the \"channels\" table.\n",
        "#The results are fetched, converted into a DataFrame df2, and displayed.\n",
        "\n",
        "#This pattern continues for the rest of the elif blocks, where each block corresponds to a specific question selected by the user.\n",
        "#The selected SQL query is executed, results are fetched, converted into DataFrames, and then displayed using st.write().\n",
        "\n",
        "elif question==\"3. 10 most viewed videos\":\n",
        "    query3='''select views as views, channel_name as channelname,title as videotitle from videos\n",
        "                where views is not null order by views desc limit 10'''\n",
        "    cursor.execute(query3)\n",
        "    mydb.commit()\n",
        "    t3=cursor.fetchall()\n",
        "    df3=pd.DataFrame(t3,columns=[\"views\",\"channel name\",\"videotitle\"])\n",
        "    st.write(df3)\n",
        "\n",
        "elif question==\"4. Comments in each videos\":\n",
        "    query4='''select comments as no_comments,title as videotitle from videos where comments is not null'''\n",
        "    cursor.execute(query4)\n",
        "    mydb.commit()\n",
        "    t4=cursor.fetchall()\n",
        "    df4=pd.DataFrame(t4,columns=[\"no of comments\",\"videotitle\",])\n",
        "    st.write(df4)\n",
        "\n",
        "elif question==\"5. Videos with highest likes\":\n",
        "    query5='''select title as viedeotitle,channel_name as channelname,likes as likecount\n",
        "                from videos where likes is not null order by likes desc'''\n",
        "    cursor.execute(query5)\n",
        "    mydb.commit()\n",
        "    t5=cursor.fetchall()\n",
        "    df5=pd.DataFrame(t5,columns=[\"videotitle\",\"channelname\",\"likecount\"])\n",
        "    st.write(df5)\n",
        "\n",
        "elif question==\"6. Likes of all videos\":\n",
        "    query6='''select likes as likecount,title as videotitle from videos'''\n",
        "    cursor.execute(query6)\n",
        "    mydb.commit()\n",
        "    t6=cursor.fetchall()\n",
        "    df6=pd.DataFrame(t6,columns=[\"likecount\",\"videotitle\"])\n",
        "    st.write(df6)\n",
        "\n",
        "elif question==\"7. Views of each channel\":\n",
        "    query7='''select channel_name as channelname,views as totalviews from channels'''\n",
        "    cursor.execute(query7)\n",
        "    mydb.commit()\n",
        "    t7=cursor.fetchall()\n",
        "    df7=pd.DataFrame(t7,columns=[\"channel name\",\"totalviews\"])\n",
        "    st.write(df7)\n",
        "\n",
        "elif question==\"8. Videos publieshed in the year of 2022\":\n",
        "    query8='''select title as video_title,published_date as videorelease,channel_name as channelname from videos\n",
        "                where extract(year from published_date)=2022'''\n",
        "    cursor.execute(query8)\n",
        "    mydb.commit()\n",
        "    t8=cursor.fetchall()\n",
        "    df8=pd.DataFrame(t8,columns=[\"videotitle\",\"published_date\",\"channelname\"])\n",
        "    st.write(df8)\n",
        "\n",
        "elif question==\"9. Avearage duration of all videos in each channel\":\n",
        "    query9='''select channel_name as channelname,AVG(duration) as averageduration from videos group by channel_name'''\n",
        "    cursor.execute(query9)\n",
        "    mydb.commit()\n",
        "    t9=cursor.fetchall()\n",
        "    df9=pd.DataFrame(t9,columns=[\"channelname\",\"averageduration\"])\n",
        "\n",
        "    T9=[]\n",
        "    for index,row in df9.iterrows():\n",
        "        channel_title=row[\"channelname\"]\n",
        "        average_duration=row[\"averageduration\"]\n",
        "        average_duration_str=str(average_duration)\n",
        "        T9.append(dict(channeltitle=channel_title,avgduration=average_duration_str))\n",
        "    df1=pd.DataFrame(T9)\n",
        "    st.write(df1)\n",
        "\n",
        "elif question==\"10. Videos with highest number of comments\":\n",
        "    query10='''select title as videotitle, channel_name as channelname, comments as comments from videos where comments is\n",
        "                not null order by comments desc'''\n",
        "    cursor.execute(query10)\n",
        "    mydb.commit()\n",
        "    t10=cursor.fetchall()\n",
        "    df10=pd.DataFrame(t10,columns=[\"video title\",\"channel name\",\"comments\"])\n",
        "    st.write(df10)\n",
        "\n",
        "#Finally, the cursor and mydb connections are closed after all the queries have been executed."
      ],
      "metadata": {
        "id": "l3PXCXiK_aA8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# This code provides a Streamlit interface for users to select different questions about the YouTube data stored in a PostgreSQL database. Depending on the selected question, corresponding SQL queries are executed to fetch the required data. The retrieved data is then displayed using Pandas DataFrames in the Streamlit app."
      ],
      "metadata": {
        "id": "9qv7fpDGA1Cz"
      }
    }
  ]
}