ExampleAnalysisDatabase.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Accessing data from the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import Session\n",
    "from tiktok_research_api_helper import models\n",
    "from tiktok_research_api_helper.models import Video\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "engine = models.get_sqlite_engine_and_create_tables(Path(\"test.db\"))\n",
    "# alternatively, you can use a different database like postgresql\n",
    "# engine = models.get_engine_and_create_tables(\"postgresql://user:password@localhost:5432/dbname\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "session = Session(engine)\n",
    "query = session.query(Video)\n",
    "\n",
    "_data = [\n",
    "    {\n",
    "        **{c: getattr(vid, c) for c in Video.__table__.columns.keys()},\n",
    "        \"hashtag_names\": vid.hashtag_names,\n",
    "    }\n",
    "    for vid in query.all()\n",
    "]\n",
    "\n",
    "tiktok_df = pd.DataFrame(_data)\n",
    "del _data\n",
    "\n",
    "tiktok_df[\"create_time\"] = pd.to_datetime(\n",
    "    tiktok_df[\"create_time\"], unit=\"s\", origin=\"unix\"\n",
    ")\n",
    "tiktok_df.sort_values(by=\"crawled_at\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>create_time</th>\n",
       "      <th>username</th>\n",
       "      <th>region_code</th>\n",
       "      <th>video_description</th>\n",
       "      <th>music_id</th>\n",
       "      <th>like_count</th>\n",
       "      <th>comment_count</th>\n",
       "      <th>share_count</th>\n",
       "      <th>view_count</th>\n",
       "      <th>effect_ids</th>\n",
       "      <th>playlist_id</th>\n",
       "      <th>voice_to_text</th>\n",
       "      <th>crawled_at</th>\n",
       "      <th>crawled_updated_at</th>\n",
       "      <th>source</th>\n",
       "      <th>extra_data</th>\n",
       "      <th>hashtag_names</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7048379512228203822</td>\n",
       "      <td>2022-01-01 18:12:19</td>\n",
       "      <td>shaybear85</td>\n",
       "      <td>US</td>\n",
       "      <td>#dog #snoopy #pet</td>\n",
       "      <td>7048379480339139374</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>251</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-04-15 21:01:28</td>\n",
       "      <td>None</td>\n",
       "      <td>[Testing]</td>\n",
       "      <td>None</td>\n",
       "      <td>[dog, snoopy, pet]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>7048073470013394223</td>\n",
       "      <td>2021-12-31 22:24:43</td>\n",
       "      <td>heather.heaven</td>\n",
       "      <td>US</td>\n",
       "      <td>You fw the shower curtain 🥰 #snoopy #EveryKiss...</td>\n",
       "      <td>6963273839560231686</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>402</td>\n",
       "      <td>[0]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-04-15 21:01:28</td>\n",
       "      <td>None</td>\n",
       "      <td>[Testing]</td>\n",
       "      <td>None</td>\n",
       "      <td>[newyear, FritoLayRickRoll, snoopy, makeup, Ev...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>7048088459356998917</td>\n",
       "      <td>2021-12-31 23:22:53</td>\n",
       "      <td>dailypeanuts</td>\n",
       "      <td>US</td>\n",
       "      <td>One person party tonight for me. Happy new yea...</td>\n",
       "      <td>6752683472239740929</td>\n",
       "      <td>191</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>1768</td>\n",
       "      <td>[0]</td>\n",
       "      <td>7.088155e+18</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-04-15 21:01:28</td>\n",
       "      <td>None</td>\n",
       "      <td>[Testing]</td>\n",
       "      <td>None</td>\n",
       "      <td>[snooby, FritoLayRickRoll, snoopy, peanuts, Ev...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>7048106788071296262</td>\n",
       "      <td>2022-01-01 00:34:06</td>\n",
       "      <td>embracethechaosx4</td>\n",
       "      <td>US</td>\n",
       "      <td>He has ear mites, ear infection, skin infectio...</td>\n",
       "      <td>7048106734732200710</td>\n",
       "      <td>507</td>\n",
       "      <td>2</td>\n",
       "      <td>43</td>\n",
       "      <td>3792</td>\n",
       "      <td>[0]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-04-15 21:01:28</td>\n",
       "      <td>None</td>\n",
       "      <td>[Testing]</td>\n",
       "      <td>None</td>\n",
       "      <td>[sickpup, snoopy, furever, dogsofttiktok]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7048112433109454127</td>\n",
       "      <td>2022-01-01 00:55:55</td>\n",
       "      <td>michigantastereviews</td>\n",
       "      <td>US</td>\n",
       "      <td>#fyp #EveryKiss #snoopy</td>\n",
       "      <td>7048112788757580591</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>308</td>\n",
       "      <td>[0]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-04-15 21:01:28</td>\n",
       "      <td>None</td>\n",
       "      <td>[Testing]</td>\n",
       "      <td>None</td>\n",
       "      <td>[snoopy, EveryKiss, fyp]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id         create_time              username region_code  \\\n",
       "0   7048379512228203822 2022-01-01 18:12:19            shaybear85          US   \n",
       "16  7048073470013394223 2021-12-31 22:24:43        heather.heaven          US   \n",
       "15  7048088459356998917 2021-12-31 23:22:53          dailypeanuts          US   \n",
       "14  7048106788071296262 2022-01-01 00:34:06     embracethechaosx4          US   \n",
       "13  7048112433109454127 2022-01-01 00:55:55  michigantastereviews          US   \n",
       "\n",
       "                                    video_description             music_id  \\\n",
       "0                                   #dog #snoopy #pet  7048379480339139374   \n",
       "16  You fw the shower curtain 🥰 #snoopy #EveryKiss...  6963273839560231686   \n",
       "15  One person party tonight for me. Happy new yea...  6752683472239740929   \n",
       "14  He has ear mites, ear infection, skin infectio...  7048106734732200710   \n",
       "13                            #fyp #EveryKiss #snoopy  7048112788757580591   \n",
       "\n",
       "    like_count  comment_count  share_count  view_count effect_ids  \\\n",
       "0            1              0            0         251         []   \n",
       "16          24              1            0         402        [0]   \n",
       "15         191              3           13        1768        [0]   \n",
       "14         507              2           43        3792        [0]   \n",
       "13          18              2            1         308        [0]   \n",
       "\n",
       "     playlist_id voice_to_text          crawled_at crawled_updated_at  \\\n",
       "0            NaN          None 2024-04-15 21:01:28               None   \n",
       "16           NaN          None 2024-04-15 21:01:28               None   \n",
       "15  7.088155e+18          None 2024-04-15 21:01:28               None   \n",
       "14           NaN          None 2024-04-15 21:01:28               None   \n",
       "13           NaN          None 2024-04-15 21:01:28               None   \n",
       "\n",
       "       source extra_data                                      hashtag_names  \n",
       "0   [Testing]       None                                 [dog, snoopy, pet]  \n",
       "16  [Testing]       None  [newyear, FritoLayRickRoll, snoopy, makeup, Ev...  \n",
       "15  [Testing]       None  [snooby, FritoLayRickRoll, snoopy, peanuts, Ev...  \n",
       "14  [Testing]       None          [sickpup, snoopy, furever, dogsofttiktok]  \n",
       "13  [Testing]       None                           [snoopy, EveryKiss, fyp]  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tiktok_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}