update_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Socket exception: An existing connection was forcibly closed by the remote host (10054)\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "Summary:\n",
    "Establishes an SSH connection to a remote computer and a connection to a MySQL database.\n",
    "For each .MP4 file in a specified directory on the remote computer:\n",
    "- Uses FFmpeg commands to obtain video properties.\n",
    "- Uses file system commands to obtain the creation date and time of the video.\n",
    "- Inserts these properties into the 'video' table of the MySQL database.\n",
    "Finally, closes all open connections.\n",
    "\n",
    "Dependencies:\n",
    "- paramiko\n",
    "- mysql.connector\n",
    "\"\"\"\n",
    "\n",
    "import paramiko\n",
    "import mysql.connector\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# === Part 1: Connect to remote computer and database ===\n",
    "# SSH connection parameters\n",
    "hostname = 'gate.bss.phy.cam.ac.uk'\n",
    "port = 22\n",
    "username = 'yz655'\n",
    "password = 'gDlzQXwR'\n",
    "\n",
    "# Establish an SSH connection\n",
    "ssh_client = paramiko.SSHClient()\n",
    "ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())\n",
    "ssh_client.connect(hostname, port, username, password)\n",
    "\n",
    "# Establish a connection to the database\n",
    "cnx = mysql.connector.connect(user='root', password='dronevideos', host='localhost', database='crop_videos')\n",
    "cursor = cnx.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Entry added for video: DJI_0004.MP4\n",
      "Entry added for video: DJI_0012.MP4\n",
      "Entry added for video: DJI_0013.MP4\n",
      "Entry added for video: DJI_0014.MP4\n",
      "Entry added for video: DJI_0015.MP4\n",
      "Entry added for video: DJI_0016.MP4\n",
      "Entry added for video: DJI_0017.MP4\n",
      "Entry added for video: DJI_0018.MP4\n",
      "Entry added for video: DJI_0019.MP4\n",
      "Entry added for video: DJI_0024.MP4\n",
      "Entry added for video: DJI_0025.MP4\n",
      "Entry added for video: DJI_0026.MP4\n",
      "Entry added for video: DJI_0028.MP4\n",
      "Entry added for video: DJI_0029.MP4\n",
      "Entry added for video: DJI_0030.MP4\n",
      "Entry added for video: DJI_0031.MP4\n",
      "Entry added for video: DJI_0032.MP4\n",
      "Entry added for video: DJI_0033.MP4\n",
      "Entry added for video: DJI_0034.MP4\n",
      "Entry added for video: DJI_0035.MP4\n",
      "Entry added for video: DJI_0036.MP4\n",
      "Entry added for video: DJI_0037.MP4\n",
      "Entry added for video: DJI_0038.MP4\n",
      "Entry added for video: DJI_0039.MP4\n",
      "Entry added for video: DJI_0040.MP4\n",
      "Entry added for video: DJI_0041.MP4\n",
      "Entry added for video: DJI_0042.MP4\n",
      "Entry added for video: DJI_0043.MP4\n",
      "Entry added for video: DJI_0044.MP4\n",
      "Entry added for video: DJI_0045.MP4\n",
      "Entry added for video: DJI_0046.MP4\n",
      "Entry added for video: DJI_0047.MP4\n",
      "Entry added for video: DJI_0048.MP4\n",
      "Entry added for video: DJI_0049.MP4\n",
      "Entry added for video: DJI_0050.MP4\n",
      "Entry added for video: DJI_0051.MP4\n",
      "Entry added for video: DJI_0052.MP4\n"
     ]
    }
   ],
   "source": [
    "# -----------------------------------------\n",
    "# 2. Extract video data from the Excel file and populate the database\n",
    "# -----------------------------------------\n",
    "file_path = \"C:\\\\Users\\\\46596\\\\Desktop\\\\Multi DDM\\\\manage_videos\\\\summary.xlsx\"\n",
    "df = pd.read_excel(file_path)\n",
    "\n",
    "# Delete all existing entries from video table\n",
    "cursor.execute(\"DELETE FROM video\")\n",
    "# cursor.execute(\"ALTER TABLE video AUTO_INCREMENT = 1;\")\n",
    "\n",
    "# Iterate through each row in the dataframe and populate the database\n",
    "for index, row in df.iterrows():\n",
    "    \n",
    "    create_time = row['Created Time'].strftime('%H:%M:%S')\n",
    "    \n",
    "    duration_parts = row['Duration'].split(':')\n",
    "    duration_seconds = float(duration_parts[0]) * 3600 + float(duration_parts[1]) * 60 + float(duration_parts[2])\n",
    "\n",
    "    video_properties = {\n",
    "        \"filename\": row[\"FileName\"],\n",
    "        \"location\": \"/cicutagroup/crop_data/wheat_videos/drone_videos/original/\" + row[\"FileName\"],\n",
    "        \"create_date\": row[\"Created Date\"],\n",
    "        \"create_time\": row['Created Time'],\n",
    "        \"duration\": row['Duration'],\n",
    "        \"format\": \"MP4\",\n",
    "        \"frame_width\": row[\"Width\"],\n",
    "        \"frame_height\": row[\"Height\"],\n",
    "        \"frame_rate\": row[\"Framerate\"],\n",
    "        \"frame_number\": row[\"NumFrames\"],\n",
    "        \"color_space\": row[\"Color MD\"]\n",
    "    }\n",
    "\n",
    "    add_video = (\"INSERT INTO video \"\n",
    "                 \"(filename, location, create_date, create_time, duration, format, frame_width, frame_height, frame_rate, frame_number, color_space) \"\n",
    "                 \"VALUES (%(filename)s, %(location)s, %(create_date)s, %(create_time)s, %(duration)s, %(format)s, %(frame_width)s, %(frame_height)s, %(frame_rate)s, %(frame_number)s, %(color_space)s)\")\n",
    "\n",
    "    cursor.execute(add_video, video_properties)\n",
    "    cnx.commit()\n",
    "    print(f\"Entry added for video: {row['FileName']}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import mysql.connector\n",
    "import paramiko\n",
    "import re\n",
    "\n",
    "# # 1. Connect to remote computer and database\n",
    "# hostname = 'gate.bss.phy.cam.ac.uk'\n",
    "# port = 22\n",
    "# username = 'yz655'\n",
    "# password = 'gDlzQXwR'\n",
    "\n",
    "# ssh_client = paramiko.SSHClient()\n",
    "# ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())\n",
    "# ssh_client.connect(hostname=hostname, port=port, username=username, password=password)\n",
    "\n",
    "base_dir = \"../../../cicutagroup/crop_data/wheat_videos/drone_videos/\"\n",
    "folders_to_check = [\"rescale_crop_fps10\", \"crop_fps10\", \"fps10\", \"stabilized_rescale_crop_fps10\"]\n",
    "\n",
    "# Manual mapping of video properties for each folder\n",
    "folder_video_properties = {\n",
    "    \"fps10\": {\"frame_width\": 3840, \"frame_height\": 2160, \"frame_rate\": 10, \"color_space\": \"YUV\"},\n",
    "    \"crop_fps10\": {\"frame_width\": 2160, \"frame_height\": 2160, \"frame_rate\": 10, \"color_space\": \"YUV\"},\n",
    "    \"rescale_crop_fps10\": {\"frame_width\": 1024, \"frame_height\": 1024, \"frame_rate\": 10, \"color_space\": \"YUV\"},\n",
    "    \"stabilized_rescale_crop_fps10\": {\"frame_width\": 1024, \"frame_height\": 1024, \"frame_rate\": 10, \"color_space\": \"YUV\"}\n",
    "}\n",
    "\n",
    "# cnx = mysql.connector.connect(user='root', password='dronevideos', host='localhost', database='crop_videos')\n",
    "# cursor = cnx.cursor()\n",
    "\n",
    "for folder in folders_to_check:\n",
    "    directory = os.path.join(base_dir, folder)\n",
    "    stdin, stdout, stderr = ssh_client.exec_command(f\"ls {directory}\")\n",
    "    files = stdout.read().decode().splitlines()\n",
    "\n",
    "    for file in files:\n",
    "        \n",
    "        # Check if video already exists in the processed_videos table\n",
    "        cursor.execute(f\"SELECT 1 FROM processed_videos WHERE filename='{file}'\")\n",
    "        if cursor.fetchone():\n",
    "            continue  # skip this video if it's already in the database\n",
    "\n",
    "        # Extract the original video name using regex\n",
    "        original_file_name_match = re.search(r'DJI_\\d{4}\\.MP4', file)\n",
    "        if not original_file_name_match:\n",
    "            continue  # skip this file if it doesn't match the pattern\n",
    "        original_file_name = original_file_name_match.group()\n",
    "        \n",
    "        # Fetch details from the original video entry\n",
    "        cursor.execute(f\"SELECT video_id, create_date, create_time, duration FROM video WHERE filename='{original_file_name}'\")\n",
    "        video_id, create_date, create_time, duration = cursor.fetchone()\n",
    "\n",
    "        # Convert create_time to \"hh:mm:ss\" format\n",
    "        total_seconds = create_time.total_seconds()\n",
    "        hours = int(total_seconds // 3600)\n",
    "        minutes = int((total_seconds % 3600) // 60)\n",
    "        seconds = int(total_seconds % 60)\n",
    "        formatted_time = \"{:02}:{:02}:{:02}\".format(hours, minutes, seconds)\n",
    "        \n",
    "        video_properties = {\n",
    "            \"video_id\": video_id,\n",
    "            \"filename\": file,\n",
    "            \"location\": os.path.join(directory, file),\n",
    "            \"create_date\": create_date,\n",
    "            \"create_time\": formatted_time, # use the formatted time\n",
    "            \"duration\": duration.total_seconds(), # convert timedelta to total seconds\n",
    "            \"format\": \"MP4\",\n",
    "            \"frame_width\": folder_video_properties[folder][\"frame_width\"],\n",
    "            \"frame_height\": folder_video_properties[folder][\"frame_height\"],\n",
    "            \"frame_rate\": folder_video_properties[folder][\"frame_rate\"],\n",
    "            \"color_space\": folder_video_properties[folder][\"color_space\"],\n",
    "            \"processing_type\": folder\n",
    "        }\n",
    "        \n",
    "        # Insert into the 'processed_videos' table\n",
    "        add_video = (\"INSERT INTO processed_videos \"\n",
    "                    \"(video_id, filename, location, create_date, create_time, duration, format, frame_width, frame_height, frame_rate, color_space, processing_type) \"\n",
    "                    \"VALUES (%(video_id)s, %(filename)s, %(location)s, %(create_date)s, %(create_time)s, %(duration)s, %(format)s, %(frame_width)s, %(frame_height)s, %(frame_rate)s, %(color_space)s, %(processing_type)s)\")\n",
    "        cursor.execute(add_video, video_properties)\n",
    "        cnx.commit()\n",
    "\n",
    "\n",
    "# # 3. Close connection\n",
    "# cursor.close()\n",
    "# cnx.close()\n",
    "# ssh_client.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Socket exception: An existing connection was forcibly closed by the remote host (10054)\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import mysql.connector\n",
    "import pandas as pd\n",
    "import re\n",
    "import numpy as np\n",
    "\n",
    "# Step 2: Read the summary Excel file\n",
    "summary_df = pd.read_excel(\"C:\\\\Users\\\\46596\\\\Desktop\\\\Multi DDM\\\\manage_videos\\\\summary.xlsx\")\n",
    "summary_excel_path = \"C:\\\\Users\\\\46596\\\\Desktop\\\\Multi DDM\\\\manage_videos\\\\summary.xlsx\"\n",
    "\n",
    "# Define function to get video_id\n",
    "def get_video_id(filename):\n",
    "    cursor.execute(f\"SELECT video_id FROM video WHERE filename='{filename}'\")\n",
    "    return cursor.fetchone()[0]\n",
    "\n",
    "# Define function to get processed_video_id\n",
    "def get_processed_video_id(filename):\n",
    "    cursor.execute(f\"SELECT processed_video_id FROM processed_videos WHERE filename='{filename}'\")\n",
    "    result = cursor.fetchone()\n",
    "    return result[0] if result else None\n",
    "\n",
    "def get_video_summary_from_excel(filename, video_name):\n",
    "    df = pd.read_excel(filename)\n",
    "    video_summary = df[df[\"FileName\"] == video_name].iloc[0].to_dict()\n",
    "\n",
    "    # Convert numpy types to native Python types\n",
    "    for key, value in video_summary.items():\n",
    "        if isinstance(value, (np.int64, np.int32)):\n",
    "            video_summary[key] = int(value)\n",
    "        elif isinstance(value, np.float64):\n",
    "            video_summary[key] = float(value)\n",
    "        else:\n",
    "            continue\n",
    "\n",
    "    return video_summary\n",
    "\n",
    "\n",
    "# Step 3: Iterate through the files\n",
    "analysis_directory = \"../../../cicutagroup/crop_data/wheat_videos/drone_videos/stabilized_drone_videos_to_run_Analysis\"\n",
    "# directory = os.path.join(base_dir, folder)\n",
    "stdin, stdout, stderr = ssh_client.exec_command(f\"ls {analysis_directory}\")\n",
    "files = stdout.read().decode().splitlines()\n",
    "    \n",
    "for file in files:\n",
    "    if file.endswith(\".mat\"):\n",
    "        # Extract the original video name using regex\n",
    "        original_file_name_match = re.search(r'DJI_\\d{4}.mat', file)\n",
    "        if original_file_name_match:\n",
    "            original_file_name = original_file_name_match.group().replace('.mat', '.MP4')\n",
    "            # Extract additional information\n",
    "            video_id = get_video_id(original_file_name)\n",
    "            processed_video_id = get_processed_video_id(file)\n",
    "            \n",
    "            # Step 4: Extract details from the Excel summary\n",
    "            # video_summary = summary_df[summary_df[\"FileName\"] == original_file_name].iloc[0]\n",
    "            # Get video summary from Excel\n",
    "            video_summary = get_video_summary_from_excel(summary_excel_path, original_file_name)\n",
    "\n",
    "\n",
    "            # Step 5: Insert into the 'ddm_data' table\n",
    "            ddm_data_entry = {\n",
    "                \"video_id\": video_id,\n",
    "                \"processed_video_id\": processed_video_id,\n",
    "                \"DDM_data_file_location\": os.path.join(analysis_directory, file),\n",
    "                \"DDM_data_nonAvgIqtau_file_location\": None,\n",
    "                \"DDM_data_nonAvgIqtau_angle_Iqtau_file_location\": None,\n",
    "                \"MedianFrequencyVec\": video_summary[\"MedianFrequencyVec\"],\n",
    "                \"Major_q\": video_summary[\"Major_q\"],\n",
    "                \"Major_q_amplitude\": video_summary[\"Major_q_amplitude\"],\n",
    "                \"Major_q_frequency\": video_summary[\"Major_q_frequency\"],\n",
    "                \"Major_q_damping\": video_summary[\"Major_q_damping\"],\n",
    "                \"Major_q_offset\": video_summary[\"Major_q_offset\"],\n",
    "                \"Major_q_gof\": video_summary[\"Major_q_gof\"]\n",
    "            }\n",
    "\n",
    "            add_ddm_data = (\"INSERT INTO ddm_data \"\n",
    "                            \"(video_id, processed_video_id, DDM_data_file_location, DDM_data_nonAvgIqtau_file_location, \"\n",
    "                            \"DDM_data_nonAvgIqtau_angle_Iqtau_file_location, MedianFrequencyVec, Major_q, Major_q_amplitude, \"\n",
    "                            \"Major_q_frequency, Major_q_damping, Major_q_offset, Major_q_gof) \"\n",
    "                            \"VALUES (%(video_id)s, %(processed_video_id)s, %(DDM_data_file_location)s, \"\n",
    "                            \"%(DDM_data_nonAvgIqtau_file_location)s, %(DDM_data_nonAvgIqtau_angle_Iqtau_file_location)s, \"\n",
    "                            \"%(MedianFrequencyVec)s, %(Major_q)s, %(Major_q_amplitude)s, %(Major_q_frequency)s, \"\n",
    "                            \"%(Major_q_damping)s, %(Major_q_offset)s, %(Major_q_gof)s)\")\n",
    "\n",
    "            cursor.execute(add_ddm_data, ddm_data_entry)\n",
    "            cnx.commit()\n",
    "\n",
    "# cursor.close()\n",
    "# cnx.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added filename column.\n"
     ]
    }
   ],
   "source": [
    "# 2. Use the ALTER TABLE command to add the filename column\n",
    "try:\n",
    "    cursor.execute(\"ALTER TABLE ddm_data ADD filename VARCHAR(255)\")\n",
    "    print(\"Added filename column.\")\n",
    "except mysql.connector.Error as err:\n",
    "    if \"duplicate column name\" in str(err).lower():\n",
    "        print(\"Column 'filename' already exists.\")\n",
    "    else:\n",
    "        print(f\"Error: {err}\")\n",
    "        cursor.close()\n",
    "        cnx.close()\n",
    "        exit()\n",
    "\n",
    "# 3. Update the filename values\n",
    "cursor.execute(\"SELECT DDM_data_id, DDM_data_file_location FROM ddm_data\")\n",
    "for ddm_data_id, file_location in cursor.fetchall():\n",
    "    filename = os.path.basename(file_location)\n",
    "    cursor.execute(\"UPDATE ddm_data SET filename = %s WHERE DDM_data_id = %s\", (filename, ddm_data_id))\n",
    "\n",
    "# Commit the changes\n",
    "cnx.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Socket exception: An existing connection was forcibly closed by the remote host (10054)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Fetch all entries from the ddm_data table\n",
    "cursor.execute(\"SELECT DDM_data_id, filename FROM ddm_data WHERE processed_video_id IS NULL\")\n",
    "entries = cursor.fetchall()\n",
    "\n",
    "for entry_id, filename in entries:\n",
    "    # Extract the original filename from the DDM data file name\n",
    "    original_file_name = filename.replace('.mat', '.MP4')\n",
    "\n",
    "    # Fetch processed_video_id from the processed_videos table for the corresponding video\n",
    "    cursor.execute(f\"SELECT processed_video_id FROM processed_videos WHERE filename='{original_file_name}'\")\n",
    "    processed_video_id_row = cursor.fetchone()\n",
    "\n",
    "    # If a match is found, update the ddm_data entry with the processed_video_id\n",
    "    if processed_video_id_row:\n",
    "        processed_video_id = processed_video_id_row[0]\n",
    "        cursor.execute(f\"UPDATE ddm_data SET processed_video_id={processed_video_id} WHERE DDM_data_id={entry_id}\")\n",
    "        cnx.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # === Part 2: Enter the video entry into the database ===\n",
    "\n",
    "# # List all .MP4 files in the directory\n",
    "# command = \"ls ../../../cicutagroup/crop_data/wheat_videos/drone_videos/original/*.MP4\"\n",
    "# stdin, stdout, stderr = ssh_client.exec_command(command)\n",
    "# files = stdout.readlines()\n",
    "\n",
    "# for file in files:\n",
    "#     file = file.strip()\n",
    "    \n",
    "#     # Using FFprobe to get video properties\n",
    "#     ffprobe_cmd = f\"ffprobe -v error -select_streams v:0 -show_entries stream=width,height,r_frame_rate,duration,nb_frames,pix_fmt -of csv=p=0 {file}\"\n",
    "#     stdin, stdout, stderr = ssh_client.exec_command(ffprobe_cmd)\n",
    "#     video_info = stdout.read().decode().split(',')\n",
    "    \n",
    "#     # Use 'stat' to get the file's creation date and time\n",
    "#     date_command = f\"stat -c %y {file}\"\n",
    "#     stdin, stdout, stderr = ssh_client.exec_command(date_command)\n",
    "#     date_output = stdout.read().decode().strip()\n",
    "#     create_date, create_time = date_output.split()[0], date_output.split()[1].split('.')[0]\n",
    "\n",
    "#     video_properties = {\n",
    "#         \"filename\": os.path.basename(file),\n",
    "#         \"location\": file,\n",
    "#         \"create_date\": create_date,\n",
    "#         \"create_time\": create_time,\n",
    "#         \"duration\": video_info[3],\n",
    "#         \"format\": \"MP4\",\n",
    "#         \"frame_width\": video_info[0],\n",
    "#         \"frame_height\": video_info[1],\n",
    "#         \"frame_rate\": float(video_info[2].split('/')[0]) / float(video_info[2].split('/')[1]),\n",
    "#         \"frame_number\": video_info[4],\n",
    "#         \"color_space\": video_info[5]\n",
    "#     }\n",
    "    \n",
    "#     # SQL command to insert the data\n",
    "#     add_video = (\"INSERT INTO video \"\n",
    "#                  \"(filename, location, create_date, create_time, duration, format, frame_width, frame_height, frame_rate, frame_number, color_space) \"\n",
    "#                  \"VALUES (%(filename)s, %(location)s, %(create_date)s, %(create_time)s, %(duration)s, %(format)s, %(frame_width)s, %(frame_height)s, %(frame_rate)s, %(frame_number)s, %(color_space)s)\")\n",
    "    \n",
    "#     # Execute the command and commit to save changes\n",
    "#     cursor.execute(add_video, video_properties)\n",
    "#     cnx.commit()\n",
    "    \n",
    "#     # Print a message indicating an entry has been added\n",
    "#     print(f\"Entry added for video: {video_properties['filename']}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Video Entries:\n",
      "---------------------------------------------------------------------------------------------\n",
      "(1, 'DJI_0004.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0004.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=44650), datetime.timedelta(seconds=54), 'MP4', 1024, 1024, 10.0, 546, 'default', None, None, None, None, None, None, None)\n",
      "(2, 'DJI_0012.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0012.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=49874), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 111, 'default', None, None, None, None, None, None, None)\n",
      "(3, 'DJI_0013.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0013.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=49898), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 113, 'default', None, None, None, None, None, None, None)\n",
      "(4, 'DJI_0014.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0014.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=49920), datetime.timedelta(seconds=12), 'MP4', 1024, 1024, 10.0, 120, 'default', None, None, None, None, None, None, None)\n",
      "(5, 'DJI_0015.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0015.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=49964), datetime.timedelta(seconds=12), 'MP4', 1024, 1024, 10.0, 128, 'default', None, None, None, None, None, None, None)\n",
      "(6, 'DJI_0016.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0016.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=50016), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 112, 'default', None, None, None, None, None, None, None)\n",
      "(7, 'DJI_0017.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0017.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=50060), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 111, 'default', None, None, None, None, None, None, None)\n",
      "(8, 'DJI_0018.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0018.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=50086), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 114, 'default', None, None, None, None, None, None, None)\n",
      "(9, 'DJI_0019.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0019.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=50212), datetime.timedelta(seconds=12), 'MP4', 1024, 1024, 10.0, 122, 'default', None, None, None, None, None, None, None)\n",
      "(10, 'DJI_0024.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0024.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=51252), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 111, 'default', None, None, None, None, None, None, None)\n",
      "(11, 'DJI_0025.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0025.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=51290), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 110, 'default', None, None, None, None, None, None, None)\n",
      "(12, 'DJI_0026.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0026.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=51350), datetime.timedelta(seconds=21), 'MP4', 1024, 1024, 10.0, 218, 'default', None, None, None, None, None, None, None)\n",
      "(13, 'DJI_0028.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0028.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=51546), datetime.timedelta(seconds=12), 'MP4', 1024, 1024, 10.0, 122, 'default', None, None, None, None, None, None, None)\n",
      "(14, 'DJI_0029.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0029.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=51594), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 120, 'default', None, None, None, None, None, None, None)\n",
      "(15, 'DJI_0030.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0030.MP4', datetime.date(2022, 7, 9), datetime.timedelta(seconds=51640), datetime.timedelta(seconds=11), 'MP4', 1024, 1024, 10.0, 114, 'default', None, None, None, None, None, None, None)\n",
      "(16, 'DJI_0031.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0031.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61318), datetime.timedelta(seconds=24), 'MP4', 1024, 1024, 10.0, 243, 'default', None, None, None, None, None, None, None)\n",
      "(17, 'DJI_0032.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0032.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61352), datetime.timedelta(seconds=16), 'MP4', 1024, 1024, 10.0, 170, 'default', None, None, None, None, None, None, None)\n",
      "(18, 'DJI_0033.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0033.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61384), datetime.timedelta(seconds=18), 'MP4', 1024, 1024, 10.0, 186, 'default', None, None, None, None, None, None, None)\n",
      "(19, 'DJI_0034.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0034.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61414), datetime.timedelta(seconds=16), 'MP4', 1024, 1024, 10.0, 164, 'default', None, None, None, None, None, None, None)\n",
      "(20, 'DJI_0035.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0035.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61446), datetime.timedelta(seconds=19), 'MP4', 1024, 1024, 10.0, 190, 'default', None, None, None, None, None, None, None)\n",
      "(21, 'DJI_0036.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0036.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61470), datetime.timedelta(seconds=16), 'MP4', 1024, 1024, 10.0, 169, 'default', None, None, None, None, None, None, None)\n",
      "(22, 'DJI_0037.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0037.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61530), datetime.timedelta(seconds=23), 'MP4', 1024, 1024, 10.0, 230, 'default', None, None, None, None, None, None, None)\n",
      "(23, 'DJI_0038.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0038.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61548), datetime.timedelta(seconds=15), 'MP4', 1024, 1024, 10.0, 159, 'default', None, None, None, None, None, None, None)\n",
      "(24, 'DJI_0039.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0039.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61572), datetime.timedelta(seconds=14), 'MP4', 1024, 1024, 10.0, 144, 'default', None, None, None, None, None, None, None)\n",
      "(25, 'DJI_0040.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0040.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61622), datetime.timedelta(seconds=16), 'MP4', 1024, 1024, 10.0, 166, 'default', None, None, None, None, None, None, None)\n",
      "(26, 'DJI_0041.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0041.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61654), datetime.timedelta(seconds=15), 'MP4', 1024, 1024, 10.0, 155, 'default', None, None, None, None, None, None, None)\n",
      "(27, 'DJI_0042.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0042.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61718), datetime.timedelta(seconds=26), 'MP4', 1024, 1024, 10.0, 266, 'default', None, None, None, None, None, None, None)\n",
      "(28, 'DJI_0043.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0043.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61740), datetime.timedelta(seconds=16), 'MP4', 1024, 1024, 10.0, 163, 'default', None, None, None, None, None, None, None)\n",
      "(29, 'DJI_0044.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0044.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61776), datetime.timedelta(seconds=17), 'MP4', 1024, 1024, 10.0, 175, 'default', None, None, None, None, None, None, None)\n",
      "(30, 'DJI_0045.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0045.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61808), datetime.timedelta(seconds=21), 'MP4', 1024, 1024, 10.0, 220, 'default', None, None, None, None, None, None, None)\n",
      "(31, 'DJI_0046.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0046.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61862), datetime.timedelta(seconds=16), 'MP4', 1024, 1024, 10.0, 160, 'default', None, None, None, None, None, None, None)\n",
      "(32, 'DJI_0047.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0047.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61894), datetime.timedelta(seconds=20), 'MP4', 1024, 1024, 10.0, 206, 'default', None, None, None, None, None, None, None)\n",
      "(33, 'DJI_0048.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0048.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61932), datetime.timedelta(seconds=21), 'MP4', 1024, 1024, 10.0, 211, 'default', None, None, None, None, None, None, None)\n",
      "(34, 'DJI_0049.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0049.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=61972), datetime.timedelta(seconds=22), 'MP4', 1024, 1024, 10.0, 225, 'default', None, None, None, None, None, None, None)\n",
      "(35, 'DJI_0050.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0050.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=62004), datetime.timedelta(seconds=26), 'MP4', 1024, 1024, 10.0, 267, 'default', None, None, None, None, None, None, None)\n",
      "(36, 'DJI_0051.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0051.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=62030), datetime.timedelta(seconds=21), 'MP4', 1024, 1024, 10.0, 213, 'default', None, None, None, None, None, None, None)\n",
      "(37, 'DJI_0052.MP4', '/cicutagroup/crop_data/wheat_videos/drone_videos/original/DJI_0052.MP4', datetime.date(2022, 7, 20), datetime.timedelta(seconds=62068), datetime.timedelta(seconds=21), 'MP4', 1024, 1024, 10.0, 214, 'default', None, None, None, None, None, None, None)\n"
     ]
    }
   ],
   "source": [
    "# Retrieve all video entries\n",
    "query = \"SELECT * FROM video;\"\n",
    "cursor.execute(query)\n",
    "\n",
    "# Print the entries\n",
    "print(\"Video Entries:\")\n",
    "print(\"---------------------------------------------------------------------------------------------\")\n",
    "for entry in cursor:\n",
    "    print(entry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns in 'video' table:\n",
      "DDM_data_id\n",
      "video_id\n",
      "processed_video_id\n",
      "DDM_data_file_location\n",
      "DDM_data_nonAvgIqtau_file_location\n",
      "DDM_data_nonAvgIqtau_angle_Iqtau_file_location\n",
      "MedianFrequencyVec\n",
      "Major_q\n",
      "Major_q_amplitude\n",
      "Major_q_frequency\n",
      "Major_q_damping\n",
      "Major_q_offset\n",
      "Major_q_gof\n"
     ]
    }
   ],
   "source": [
    "# Retrieve column names for the 'video' table\n",
    "query = \"\"\"\n",
    "SELECT COLUMN_NAME\n",
    "FROM information_schema.COLUMNS\n",
    "WHERE TABLE_SCHEMA = 'crop_videos' AND TABLE_NAME = 'ddm_data';\n",
    "\"\"\"\n",
    "cursor.execute(query)\n",
    "\n",
    "# Print the column names\n",
    "print(\"Columns in 'video' table:\")\n",
    "for (column_name,) in cursor:\n",
    "    print(column_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Socket exception: An existing connection was forcibly closed by the remote host (10054)\n"
     ]
    }
   ],
   "source": [
    "# Insert entries into video_group table\n",
    "video_group_entries = [\n",
    "    (\"DJI_videos_1\", \"Pietro Cicuta\"),\n",
    "    (\"DJI_videos_2\", \"Yuyi Zhang\")\n",
    "]\n",
    "\n",
    "insert_video_group = \"INSERT INTO video_group (group_name, creator) VALUES (%s, %s)\"\n",
    "cursor.executemany(insert_video_group, video_group_entries)\n",
    "\n",
    "# Insert entries into operator table\n",
    "operator_entries = [\n",
    "    (\"Pietro Cicuta\",),\n",
    "    (\"Yuyi Zhang\",)\n",
    "]\n",
    "\n",
    "insert_operator = \"INSERT INTO operator (operator_name) VALUES (%s)\"\n",
    "cursor.executemany(insert_operator, operator_entries)\n",
    "\n",
    "# Insert entry into device table\n",
    "insert_device = \"INSERT INTO device (device_type) VALUES (%s)\"\n",
    "cursor.execute(insert_device, (\"DJI Mavic 3\",))\n",
    "\n",
    "# Commit the changes\n",
    "cnx.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetch current frame_number for all entries\n",
    "cursor.execute(\"SELECT video_id, frame_number FROM video\")\n",
    "results = cursor.fetchall()\n",
    "\n",
    "# Update the entries as required\n",
    "for row in results:\n",
    "    video_id = row[0]\n",
    "    frame_number = row[1]\n",
    "    new_frame_number = frame_number / 10 * 59.94\n",
    "\n",
    "    update_query = \"\"\"\n",
    "        UPDATE video \n",
    "        SET frame_width = 3840, \n",
    "            frame_height = 2160, \n",
    "            frame_rate = 59.94, \n",
    "            frame_number = %s\n",
    "        WHERE video_id = %s\n",
    "    \"\"\"\n",
    "    cursor.execute(update_query, (new_frame_number, video_id))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# === Part 3: Close connection ===\n",
    "cursor.close()\n",
    "cnx.close()\n",
    "ssh_client.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}