bot_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2d6499f6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-08T04:53:40.433717Z",
     "start_time": "2023-03-08T04:53:39.740960Z"
    }
   },
   "outputs": [],
   "source": [
    "#libraries\n",
    "import sqlite3\n",
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "062664fe",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-08T04:58:42.220953Z",
     "start_time": "2023-03-08T04:58:42.203999Z"
    }
   },
   "outputs": [],
   "source": [
    "timeframe = '2005-12'\n",
    "sql_transaction = []\n",
    "connection = sqlite3.connect('{}.db'.format(timeframe))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2ade1bee",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-08T05:03:17.176044Z",
     "start_time": "2023-03-08T05:03:17.163079Z"
    }
   },
   "outputs": [],
   "source": [
    "c = connection.cursor() #create a cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a862260c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-08T05:57:05.728506Z",
     "start_time": "2023-03-08T05:57:05.709557Z"
    }
   },
   "outputs": [],
   "source": [
    "#db table\n",
    "def create_table():\n",
    "    c.execute('''CREATE TABLE IF NOT EXISTS parent_reply(\n",
    "        parent_id TEXT PRIMARY KEY,\n",
    "        comment_id TEXT UNIQUE,\n",
    "        parent TEXT,\n",
    "        comment TEXT,\n",
    "        subreddit TEXT,\n",
    "        unix INT,\n",
    "        score INT\n",
    "        )\n",
    "    ''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bfd8c9f9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-08T06:35:56.430546Z",
     "start_time": "2023-03-08T06:35:56.417578Z"
    }
   },
   "outputs": [],
   "source": [
    "def format_data(data):\n",
    "    data = data.replace('\\n', ' newlinechar ').replace('\\r', ' newlinechar ').replace('\"', \"'\")\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "afd766d8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-08T06:46:11.316815Z",
     "start_time": "2023-03-08T06:46:11.297856Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "def find_parent(pid):\n",
    "    try:  \n",
    "        sql = \"SELECT comment FROM parent_reply WHERE comment_id = '{}' LIMIT 1\".format(pid)\n",
    "        c.execute(sql)\n",
    "        result = c.fetchone()\n",
    "        if result != None:\n",
    "            return result[0]\n",
    "        else:\n",
    "            return False\n",
    "    except Exception as e:\n",
    "        #print('find_parent', e)\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1aaf30f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "if __name__ == \"__main__\":\n",
    "    create_table()\n",
    "    row_counter = 0\n",
    "    paired_counter = 0\n",
    "    \n",
    "    with open('F:/Research/Chat Bot/reddit_comment_data/{}/RC_{}'.format(timeframe.split('-')[0], timeframe), buffer=1000) as f:\n",
    "        for row in f:\n",
    "            row_counter += 1\n",
    "            row = json.loads(row)\n",
    "            parent_id = row['parent_id']\n",
    "            body = format_data(row['body'])\n",
    "            created_utc = row['created_utc']\n",
    "            score = row['score']\n",
    "            subreddit = row['subreddit']\n",
    "            \n",
    "            parent_data = find_parent(parent_id)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mlenv",
   "language": "python",
   "name": "mlenv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}