alyssa_mysql_problems.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "0ddb985a-6020-442d-a688-17e60b2d9b04",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pymysql.cursors\n",
    "import pandas as pd\n",
    "\n",
    "my_creds = []\n",
    "with open('../../../staff/admin_mysql_credentials.txt', 'r') as f:\n",
    "    for line in f.readlines():\n",
    "        my_creds.append(line.strip())\n",
    "        \n",
    "connection = pymysql.connect(host='mysql',\n",
    "                             user=my_creds[0],\n",
    "                             database='PHYS7332',\n",
    "                             password=my_creds[1],\n",
    "                             charset='utf8mb4',\n",
    "                             cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "00badc68-2a91-4dfd-a230-4290cdae4456",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating FL bay species table\n",
    "with connection.cursor() as cursor:\n",
    "    sql = \"\"\"\n",
    "    CREATE TABLE `fla_species` (\n",
    "        `node_id` int(11) NOT NULL,\n",
    "        `name` varchar(255)  COLLATE utf8mb4_bin NOT NULL,\n",
    "        `my_group` varchar(255) COLLATE utf8mb4_bin NULL,\n",
    "    PRIMARY KEY (`node_id`))\n",
    "    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;\n",
    "    \"\"\"\n",
    "    cursor.execute(sql)\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9a1ba255-a494-4d03-9bb0-df5f8417a203",
   "metadata": {},
   "outputs": [],
   "source": [
    "# populating FL bay species\n",
    "df1 = pd.read_csv('./data/Florida-bay-meta.csv')\n",
    "insert_sql = \"\"\"\n",
    "INSERT INTO `fla_species` (node_id, name, my_group) \n",
    "values (%s, %s, %s);\n",
    "\"\"\"\n",
    "for _, row in df1.iterrows():\n",
    "    sp_name = row['name']\n",
    "    node_id = row['node_id']\n",
    "    if row['group'] == row['group']:\n",
    "        group = row['group']\n",
    "    else:\n",
    "        group = 'NULL'\n",
    "    with connection.cursor() as cursor:\n",
    "        cursor.execute(insert_sql, (node_id, sp_name, group))\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "25a5814d-4bc1-46b8-83ae-be252567b7a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating food web table\n",
    "with connection.cursor() as cursor:\n",
    "    # sql = \"\"\"DROP TABLE `fla_foodweb`;\"\"\"\n",
    "    # cursor.execute(sql)\n",
    "    sql = \"\"\"\n",
    "    CREATE TABLE `fla_foodweb` (\n",
    "        `RowId` int(11) NOT NULL,\n",
    "        `FromNodeId` int(11) NOT NULL,\n",
    "        `ToNodeId` int(11)  NOT NULL,\n",
    "    PRIMARY KEY (`RowId`))\n",
    "    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;\n",
    "    \"\"\"\n",
    "    cursor.execute(sql)\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "51f22d9a-d193-45d4-8801-c2efeaacd3cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# populating food web table\n",
    "df2 = pd.read_csv('data/Florida-bay.txt', sep=' ', header=4)\n",
    "df2.columns = ['FromNodeId', 'ToNodeId', 'Extra']\n",
    "insert_sql = \"\"\"\n",
    "INSERT INTO `fla_foodweb` (RowId, FromNodeId, ToNodeId) \n",
    "values (%s, %s, %s);\n",
    "\"\"\"\n",
    "for idx, row in df2.iterrows():\n",
    "    row_id = idx\n",
    "    from_node_id = row['FromNodeId']\n",
    "    to_node_id = row['ToNodeId']\n",
    "    with connection.cursor() as cursor:\n",
    "        cursor.execute(insert_sql, (row_id, from_node_id, to_node_id))\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "e849ede0-8bd0-4ec3-af5e-adaf064aff43",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_body = pd.read_csv('../data_not_in_git/soc-redditHyperlinks-body.tsv', sep='\\t')\n",
    "df_head = pd.read_csv('../data_not_in_git/soc-redditHyperlinks-title.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "a0d09274-fa52-4db2-a166-eca8254829be",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "my_creds = []\n",
    "with open('../../../staff/admin_mysql_credentials.txt', 'r') as f:\n",
    "    for line in f.readlines():\n",
    "        my_creds.append(line.strip())\n",
    "\n",
    "hostname=\"mysql-0005\"\n",
    "dbname=\"PHYS7332\"\n",
    "uname=my_creds[0]\n",
    "pwd=my_creds[1]\n",
    "\n",
    "engine = create_engine(\"mysql+pymysql://{user}:{pw}@{host}/{db}\"\n",
    "                           .format(host=hostname, db=dbname,\n",
    "                                   user=uname,pw=pwd),\n",
    "                      connect_args={'connect_timeout': 100},\n",
    "                       pool_pre_ping=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "81e4a1e0-646a-4f6a-8184-5c9680bbef85",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_body['link_source'] = 'body'\n",
    "df_head['link_source'] = 'title'\n",
    "df_both = pd.concat([df_body, df_head])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "0ab45bab-2b1f-49b2-b2f4-f1ad96b485f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    ix = 0\n",
    "    while ix < len(df_both):\n",
    "        tran = conn.begin()\n",
    "        try:\n",
    "            df_both[[\n",
    "                'SOURCE_SUBREDDIT', \n",
    "                'TARGET_SUBREDDIT', \n",
    "                'POST_ID', \n",
    "                'TIMESTAMP', \n",
    "                'LINK_SENTIMENT', \n",
    "                'link_source']\n",
    "            ].iloc[ix: ix + 100].to_sql('reddit_hyperlinks', conn, if_exists='append')\n",
    "            tran.commit()\n",
    "            ix += 100\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            tran.rollback()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91c4d385-a846-4f64-989c-efe6da139f64",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}