db_upload.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# text snippets etc to database"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-13T17:56:20.330271Z",
     "iopub.status.busy": "2023-08-13T17:56:20.327741Z",
     "iopub.status.idle": "2023-08-13T17:58:25.446944Z",
     "shell.execute_reply": "2023-08-13T17:58:25.443520Z",
     "shell.execute_reply.started": "2023-08-13T17:56:20.330211Z"
    },
    "tags": []
   },
   "source": [
    "%%time\n",
    "%pip install moviepy\n",
    "%pip install ffmpeg-python\n",
    "%pip install --upgrade opencv-python-headless\n",
    "%pip install -U openai-whisper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %env CUDA_VISIBLE_DEVICES = 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import ffmpeg\n",
    "import cv2\n",
    "import sqlalchemy\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from moviepy.editor import VideoFileClip\n",
    "from nist_database import MSSQLDatabase\n",
    "from video_tools import generate_audio, generate_gps, get_checksum\n",
    "import os\n",
    "import json\n",
    "import whisper\n",
    "import pandas as pd\n",
    "from io import StringIO\n",
    "from datetime import datetime\n",
    "from pytz import timezone\n",
    "import time\n",
    "import logging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "COMMIT_EVERYTHING = False   # whether to commit to db\n",
    "DELETE_DUPS = True         # whether to exclude files with checksums already in db\n",
    "FOLDER_TO_ADD = 'assets/data/video/'\n",
    "MODEL_TYPE = \"medium.en\"    # tiny.en, base.en, small.en, medium.en, large\n",
    "\n",
    "assert FOLDER_TO_ADD[-1:] == '/'\n",
    "logging.basicConfig(format='', filename='nist.log', level=logging.INFO)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Batching Video Files & Deleting Dup's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 823 µs, sys: 1.94 ms, total: 2.76 ms\n",
      "Wall time: 1.47 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "local_files = os.listdir(FOLDER_TO_ADD)\n",
    "local_vid_files = [vid for vid in local_files if vid.split('.')[-1].lower() == 'mp4']\n",
    "video_files = [FOLDER_TO_ADD + vid for vid in local_vid_files]\n",
    "# video_files = [vid for vid in video_files if '093' in vid] # delete after\n",
    "\n",
    "for vid in video_files:\n",
    "    assert os.path.exists(vid), f'Video file {vid} does not exist'\n",
    "\n",
    "audio_files = [generate_audio(vid_file, \"wav\") or vid_file[:-3] + 'wav' for vid_file in video_files]\n",
    "gps_files = [generate_gps(vid_file, \"go-pro\") or vid_file[:-3] + 'csv' for vid_file in video_files]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "assets/data/video/hernandez-perfect-game-2012.mp4 has a duplicate called \n",
      "assets/data/video/hernandez-perfect-game-2012.mp4\n",
      "\n",
      "assets/data/video/usa-japan-championship-2023.mp4 has a duplicate called \n",
      "assets/data/video/usa-japan-championship-2023.mp4\n",
      "\n",
      "assets/data/video/henderson-hr-june-11-2023.mp4 has a duplicate called \n",
      "assets/data/video/henderson-hr-june-11-2023.mp4\n",
      "\n",
      "assets/data/video/VIDEO_7152.mp4 has a duplicate called \n",
      "assets/data/video/VIDEO_7152.mp4\n",
      "\n",
      "assets/data/video/budavari_GX_0101.MP4 has a duplicate called \n",
      "assets/data/video/budavari_GX_0101.MP4\n",
      "\n",
      "assets/data/video/budavari_GX_0120.MP4 has a duplicate called \n",
      "assets/data/video/budavari_GX_0120.MP4\n",
      "\n",
      "assets/data/video/budavari_GX_0124.MP4 has a duplicate called \n",
      "assets/data/video/budavari_GX_0124.MP4\n",
      "\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "ffmpeg_binaries = '/home/idies/workspace/nist_ai/extras/ffmpeg-6.0-amd64-static'\n",
    "assert os.path.exists(ffmpeg_binaries)\n",
    "os.environ[\"PATH\"] = os.environ[\"PATH\"] + f\":{ffmpeg_binaries}\"\n",
    "with open('/home/idies/workspace/nist_ai/Henry/nist-ai.json','r') as f:\n",
    "    AUTH = json.load(f)\n",
    "DB=MSSQLDatabase(AUTH,'NIST_AI')\n",
    "\n",
    "# delete duplicate videos by checksum\n",
    "unique_video_files, unique_audio_files = [], []\n",
    "for video_file, audio_file in zip(video_files, audio_files):\n",
    "    checksum = get_checksum(video_file)\n",
    "    df = DB.execute_query(f\"select * from video where checksum='{checksum}'\")\n",
    "    if len(df.index) > 0:\n",
    "        dup_names = []\n",
    "        for i in df.index:\n",
    "            dup_names.append(df.loc[0]['filename'])\n",
    "        print(f\"{video_file} has {'duplicates' if len(dup_names) > 1 else 'a duplicate'} called \\n\" \\\n",
    "              f\"{dup_names[0] if len(dup_names) == 1 else ', '.join(dup_names)}\\n\")\n",
    "        continue\n",
    "    unique_video_files.append(video_file)\n",
    "    unique_audio_files.append(audio_file)\n",
    "if DELETE_DUPS: video_files, audio_files = unique_video_files, unique_audio_files\n",
    "print(video_files)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying Tables"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "tags": []
   },
   "source": [
    "sql = \"select * from gps where timestamp between '2023-07-07 09:30:00' AND '2023-07-07 09:31:30'\"    # check which tables are contained in the database\n",
    "sql = \"select * from gps where video_id=42 and timestamp between '2023-07-06 13:25:19' and '2023-07-07 13:41:59'\"\n",
    "sql = \"select * from video\"\n",
    "df = DB.execute_query(sql)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### deleting video"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "tags": []
   },
   "source": [
    "sql = \"delete from video where id in (45)\"\n",
    "DB.execute_update(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-07-10T17:22:49.919640Z",
     "iopub.status.busy": "2023-07-10T17:22:49.919089Z",
     "iopub.status.idle": "2023-07-10T17:22:49.927763Z",
     "shell.execute_reply": "2023-07-10T17:22:49.925522Z",
     "shell.execute_reply.started": "2023-07-10T17:22:49.919589Z"
    }
   },
   "source": [
    "### adding table row"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-07-10T17:22:18.104260Z",
     "iopub.status.busy": "2023-07-10T17:22:18.103379Z",
     "iopub.status.idle": "2023-07-10T17:22:19.112823Z",
     "shell.execute_reply": "2023-07-10T17:22:19.111085Z",
     "shell.execute_reply.started": "2023-07-10T17:22:18.104086Z"
    },
    "tags": []
   },
   "source": [
    "video_ids = []\n",
    "text_segment_ids = DB.execute_query(\"select * from text_segment\")['id'].tolist()\n",
    "transcription_ids = DB.execute_query(\"select * from text_segment\")['transcription_id'].tolist()\n",
    "for transcription_id in transcription_ids:\n",
    "    audio_id = DB.execute_query(f\"select * from transcription where id={transcription_id}\")['audio_id'].tolist()[0]\n",
    "    video_id = DB.execute_query(f\"select * from audio where id={audio_id}\")['video_id'].tolist()[0]\n",
    "    video_ids.append(video_id)\n",
    "    \n",
    "for text_segment_id, video_id in zip(text_segment_ids, video_ids):\n",
    "    if not video_id: continue\n",
    "    DB.execute_update(f\"update text_segment set video_id={video_id} where id={text_segment_id}\")\n",
    "    \n",
    "# df.to_json(orient='records')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### changing constraints"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# give tables on cascade constraint\n",
    "sql = \"\"\"\n",
    "ALTER TABLE word_segment DROP CONSTRAINT fk_word_segment_text_run;\n",
    "ALTER TABLE word_segment add constraint fk_word_segment_text_run foreign key (transcription_id) REFERENCES transcription(id) ON DELETE CASCADE\n",
    "\"\"\"\n",
    "df = DB.execute_update(sql)\n",
    "df"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "tags": []
   },
   "source": [
    "sql = \"\"\"\n",
    "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n",
    "WHERE TABLE_NAME='word_segment'\n",
    "\"\"\"\n",
    "df = DB.execute_query(sql)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare database & Whisper Model"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# BE CAREFULL!\n",
    "Base.metadata.drop_all(bind = DB.ENGINE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check if tables already exist\n",
    "sql_table = lambda x: f\"\"\"\n",
    "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n",
    "WHERE TABLE_NAME='{x}'\n",
    "\"\"\"\n",
    "\n",
    "table_df = DB.execute_query(sql_table('video'))\n",
    "if len(table_df.index) == 0:\n",
    "    ddl = \"\"\"\n",
    "    create table video (\n",
    "        id bigint identity(1,1) not null\n",
    "      , filename varchar(1024) not null\n",
    "      , checksum varchar(36)\n",
    "      , metadata varchar(max) null    -- JSON encoded dict metadata\n",
    "      , constraint pk_gemdmodel primary key(id)\n",
    "    )\"\"\"\n",
    "    DB.execute_update(ddl)\n",
    "\n",
    "table_df = DB.execute_query(sql_table('audio'))\n",
    "if len(table_df.index) == 0:\n",
    "    ddl = \"\"\"\n",
    "        create table audio (\n",
    "        id bigint identity(1,1) not null\n",
    "      , video_id bigint\n",
    "      , filename varchar(1024) not null\n",
    "      , checksum varchar(36)\n",
    "      , constraint pk_audio primary key(id)\n",
    "      , constraint fk_audio_video foreign key (video_id) REFERENCES video(id) ON DELETE CASCADE\n",
    "    )\"\"\"\n",
    "    DB.execute_update(ddl)\n",
    "\n",
    "table_df = DB.execute_query(sql_table('transcription'))\n",
    "if len(table_df.index) == 0:\n",
    "    ddl = \"\"\"\n",
    "      CREATE TABLE transcription (\n",
    "      id bigint identity(1,1) not null\n",
    "    , audio_id bigint not null\n",
    "    , config varchar(max)          -- JSON string \n",
    "    , constraint pk_text_run primary key(id)\n",
    "    , constraint fk_text_run_audio foreign key (audio_id) REFERENCES audio(id) ON DELETE CASCADE\n",
    "    )\"\"\"\n",
    "    DB.execute_update(ddl)\n",
    "\n",
    "table_df = DB.execute_query(sql_table('text_segment'))\n",
    "if len(table_df.index) == 0:\n",
    "    ddl = \"\"\"\n",
    "      CREATE TABLE text_segment(\n",
    "      id bigint identity(1,1) not null\n",
    "    , transcription_id bigint not null\n",
    "    , video_id bigint null\n",
    "    , segment varchar(max)\n",
    "    , temperature real\n",
    "    , time_start real\n",
    "    , time_end real\n",
    "    , constraint pk_text_segment primary key(id)\n",
    "    , constraint fk_text_segment_text_run foreign key (transcription_id) REFERENCES transcription(id) ON DELETE CASCADE\n",
    "    )\"\"\"\n",
    "    DB.execute_update(ddl)\n",
    "\n",
    "table_df = DB.execute_query(sql_table('word_segment'))\n",
    "if len(table_df.index) == 0:\n",
    "    ddl = \"\"\"\n",
    "      CREATE TABLE word_segment(\n",
    "      id bigint identity(1,1) not null\n",
    "    , transcription_id bigint not null\n",
    "    , word varchar(max)\n",
    "    , probability real\n",
    "    , time_start real\n",
    "    , time_end real\n",
    "    , constraint pk_word_segment primary key(id)\n",
    "    , constraint fk_word_segment_text_run foreign key (transcription_id) REFERENCES transcription(id) ON DELETE CASCADE\n",
    "    )\"\"\"\n",
    "    DB.execute_update(ddl)\n",
    "\n",
    "table_df = DB.execute_query(sql_table('gps'))\n",
    "if len(table_df.index) == 0:\n",
    "    ddl = \"\"\"\n",
    "      CREATE TABLE gps(\n",
    "      id bigint identity(1,1) not null\n",
    "    , video_id bigint not null\n",
    "    , location varchar(max) not null\n",
    "    , timestamp datetime\n",
    "    , latitude float\n",
    "    , longitude float\n",
    "    , altitude float\n",
    "    , constraint pk_gps primary key(id)\n",
    "    , constraint fk_gps foreign key (video_id) REFERENCES video(id) ON DELETE CASCADE\n",
    "    )\"\"\"\n",
    "    DB.execute_update(ddl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "Base = automap_base()\n",
    "Base.prepare(DB.ENGINE, reflect=True)\n",
    "# for c in Base.classes:\n",
    "#     print(c)\n",
    "\n",
    "Video = Base.classes.video\n",
    "Audio = Base.classes.audio\n",
    "Transcription = Base.classes.transcription\n",
    "TextSegment = Base.classes.text_segment\n",
    "WordSegment = Base.classes.word_segment\n",
    "GPSPing = Base.classes.gps\n",
    "session = Session(DB.ENGINE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sun Aug 27 19:25:27 2023       \n",
      "+-----------------------------------------------------------------------------+\n",
      "| NVIDIA-SMI 450.66       Driver Version: 450.66       CUDA Version: 11.0     |\n",
      "|-------------------------------+----------------------+----------------------+\n",
      "| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |\n",
      "| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |\n",
      "|                               |                      |               MIG M. |\n",
      "|===============================+======================+======================|\n",
      "|   0  Tesla V100-SXM2...  Off  | 00000000:61:00.0 Off |                    0 |\n",
      "| N/A   32C    P0    54W / 300W |      0MiB / 16160MiB |      0%      Default |\n",
      "|                               |                      |                  N/A |\n",
      "+-------------------------------+----------------------+----------------------+\n",
      "|   1  Tesla V100-SXM2...  Off  | 00000000:62:00.0 Off |                    0 |\n",
      "| N/A   33C    P0    54W / 300W |      0MiB / 16160MiB |      0%      Default |\n",
      "|                               |                      |                  N/A |\n",
      "+-------------------------------+----------------------+----------------------+\n",
      "|   2  Tesla V100-SXM2...  Off  | 00000000:89:00.0 Off |                    0 |\n",
      "| N/A   33C    P0    55W / 300W |      0MiB / 16160MiB |      0%      Default |\n",
      "|                               |                      |                  N/A |\n",
      "+-------------------------------+----------------------+----------------------+\n",
      "|   3  Tesla V100-SXM2...  Off  | 00000000:8A:00.0 Off |                    0 |\n",
      "| N/A   35C    P0    55W / 300W |      0MiB / 16160MiB |      0%      Default |\n",
      "|                               |                      |                  N/A |\n",
      "+-------------------------------+----------------------+----------------------+\n",
      "                                                                               \n",
      "+-----------------------------------------------------------------------------+\n",
      "| Processes:                                                                  |\n",
      "|  GPU   GI   CI        PID   Type   Process name                  GPU Memory |\n",
      "|        ID   ID                                                   Usage      |\n",
      "|=============================================================================|\n",
      "|  No running processes found                                                 |\n",
      "+-----------------------------------------------------------------------------+\n"
     ]
    }
   ],
   "source": [
    "!nvidia-smi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 36.6 s, sys: 12.3 s, total: 48.9 s\n",
      "Wall time: 36.1 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "model = whisper.load_model(MODEL_TYPE, download_root='/home/idies/workspace/nist_ai/extras/whisper-models')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Videos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# add each video file to db\n",
    "videos = []\n",
    "for video_file in video_files:\n",
    "    metadata_dict = ffmpeg.probe(video_file)\n",
    "    metadata = json.dumps(metadata_dict)\n",
    "    checksum = get_checksum(video_file)\n",
    "    videos.append(Video(checksum=checksum, filename=video_file, metadata=metadata))\n",
    "    if COMMIT_EVERYTHING:\n",
    "        session.add(videos[-1])\n",
    "        session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Audio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# add each audio file to db\n",
    "audios = []\n",
    "for audio_file, video in zip(audio_files, videos):\n",
    "    audios.append(Audio(video=video, filename=audio_file, checksum=get_checksum(audio_file)))\n",
    "    if COMMIT_EVERYTHING:\n",
    "        session.add(audios[-1])\n",
    "        session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transcription, TextSegment, WordSegment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 2 µs, total: 4 µs\n",
      "Wall time: 7.87 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# add each transcription to db\n",
    "start_time = time.time()\n",
    "results, transcriptions = [], []\n",
    "for audio in audios:\n",
    "    results.append(model.transcribe(audio.filename, word_timestamps=True))\n",
    "    config_obj = {'model': 'whisper-net', 'load_model': MODEL_TYPE}\n",
    "    transcriptions.append(Transcription(audio=audio, config=json.dumps(config_obj)))\n",
    "    if COMMIT_EVERYTHING:\n",
    "        session.add(transcriptions[-1])\n",
    "        session.commit()\n",
    "    logging.info(f\"{datetime.now(timezone('EST')).strftime('%m/%d/%Y %H:%M:%S')}: transcribed \\\"{audio.filename.split('/')[-1]}\\\"\")\n",
    "    logging.info(f\"... w/ {MODEL_TYPE} ({round(time.time() - start_time, 1)} secs)\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3 µs, sys: 2 µs, total: 5 µs\n",
      "Wall time: 8.34 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# add text- and word- segments to db\n",
    "for result, video, transcription, video_file in zip(results, videos, transcriptions, video_files):\n",
    "    vidcap = cv2.VideoCapture(video_file)\n",
    "    for segment in result[\"segments\"]:\n",
    "        \n",
    "        # add word segments\n",
    "        for word_dict in segment['words']:\n",
    "            \n",
    "            # make utf-8 solution\n",
    "            pretty_word = ''.join([c.lower() for c in word_dict['word'].strip() if 97 <= ord(c.lower()) <= 97 + 25])\n",
    "            if not pretty_word: continue\n",
    "            start, end, prob = word_dict['start'], word_dict['end'], word_dict['probability']\n",
    "            # print(f\"|{word_dict['word']}| -> |{pretty_word}|\")\n",
    "            print(pretty_word, end=' ')\n",
    "            word_row = WordSegment(transcription=transcription, word=pretty_word, probability=prob, \\\n",
    "                        time_start=start, time_end=end)\n",
    "            if COMMIT_EVERYTHING: session.add(word_row)\n",
    "            \n",
    "        # get thumbnail binary\n",
    "        photo_bytes = None\n",
    "        milliseconds = int(segment['start'] * 1000)\n",
    "        vidcap.set(cv2.CAP_PROP_POS_MSEC, milliseconds)\n",
    "        success, image = vidcap.read()\n",
    "        if success:\n",
    "            success, buffer = cv2.imencode('.jpg', image)  # get image as binary\n",
    "            photo_bytes = buffer.tobytes()  # convert to bytes\n",
    "        \n",
    "        # add text segment\n",
    "        text_segment_row = TextSegment(transcription=transcription, video_id = video.id, thumbnail=photo_bytes, time_start=segment['start'], \\\n",
    "                    time_end=segment['end'], segment=segment['text'])\n",
    "        if COMMIT_EVERYTHING: session.add(text_segment_row)\n",
    "    if COMMIT_EVERYTHING: session.commit()\n",
    "    print('\\n\\n', '-' * 3, '\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-07-25T03:40:44.346376Z",
     "iopub.status.busy": "2023-07-25T03:40:44.345218Z",
     "iopub.status.idle": "2023-07-25T03:40:44.354086Z",
     "shell.execute_reply": "2023-07-25T03:40:44.352485Z",
     "shell.execute_reply.started": "2023-07-25T03:40:44.346323Z"
    }
   },
   "source": [
    "# GPS Metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# add gps metadata to db\n",
    "for gps_file, video, video_file in zip(gps_files, videos, video_files):\n",
    "    if not gps_file: continue\n",
    "    if not os.path.exists(gps_file): \n",
    "        if COMMIT_EVERYTHING: \n",
    "            pass\n",
    "        logging.warning(\"... no csv file w/ gps points found\")\n",
    "        continue\n",
    "\n",
    "    with open(gps_file, 'r') as f:\n",
    "        # delete all lines that start with '#' or ' ', add to df\n",
    "        lines = [line for line in f.readlines() if line[0] not in ['#', ' ']]\n",
    "        df = pd.read_csv(StringIO(''.join(lines)))\n",
    "        if COMMIT_EVERYTHING: \n",
    "            pass\n",
    "        logging.info(f\"... found {df.shape[0]} gps points in csv\")\n",
    "\n",
    "        # for every row in df, add json string of its elements\n",
    "        gps_info = []\n",
    "        for i in df.index:\n",
    "            gps_info.append(json.dumps(df.loc[i].to_dict()))\n",
    "        \n",
    "        # get timestamps from pd series\n",
    "        gps_timestamps_epoch = df['UTC Time'].values\n",
    "        gps_timestamps = []\n",
    "        for i, timestamp in enumerate(gps_timestamps_epoch):\n",
    "            timestamp = datetime.fromtimestamp(timestamp) # defaults correctly to utc\n",
    "            timestamp = timestamp.strftime('%Y-%m-%d %H:%M:%S.%f')[:-3]\n",
    "            gps_timestamps.append(timestamp)\n",
    "        latitudes, longitudes, altitudes = df['Latitude'], df['Longitude'], df['Altitude (m)']\n",
    "\n",
    "        # append items in df to gps table\n",
    "        for info_dict, timestamp, lat, lon, alt in zip(gps_info, gps_timestamps, latitudes, longitudes, altitudes):\n",
    "            gps_ping = GPSPing(video=video, location=info_dict, timestamp=timestamp, \\\n",
    "                                       latitude=lat, longitude=lon, altitude=alt)\n",
    "            if COMMIT_EVERYTHING: session.add(gps_ping)\n",
    "        if COMMIT_EVERYTHING: session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "myenv",
   "language": "python",
   "name": "myenv"
  },
  "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}