Upload new project to DB.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "00af786c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine,text\n",
    "import psycopg2\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "81870b01",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_project_data(project, project_id):\n",
    "    tables = ['Posts','Votes','Comments']\n",
    "    cols_to_del = [['OwnerUserId','LastEditorUserId','OwnerDisplayName','LastEditorDisplayName'],\n",
    "                   ['UserId'],\n",
    "                   ['UserId','UserDisplayName']]\n",
    "    # соединяемся с БД\n",
    "    conn_string = 'postgresql://postgres:11111@localhost:5432/sinya'\n",
    "    db = create_engine(conn_string)\n",
    "    conn = db.connect()\n",
    "\n",
    "    for i,table in enumerate(tables):\n",
    "        path = f'/Users/sinya/Desktop/CS центр/JB Text Analysis/Data/xml_originals/{project}/{table}.xml'\n",
    "        # читаем текущую табличку \n",
    "        cur_data = pd.read_xml(path, parser = 'etree')\n",
    "        # удаляем лишние столбцы\n",
    "        for col_to_del in cols_to_del[i]:\n",
    "            cur_data.drop(col_to_del, axis = 1, inplace=True)\n",
    "        # записываем столбец с номером текущего проекта\n",
    "        cur_data['ProjectId'] = np.ones(cur_data.shape[0])*project_id\n",
    "        # добавляем данные в нужную таблицу в БД\n",
    "        cur_data.to_sql(table.lower(), con=conn, if_exists='append', index=False)\n",
    "    # закрываем соединение\n",
    "    conn.close()\n",
    "    db.dispose()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3c3fbe93",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_project_tags(project_id):\n",
    "    conn_string = 'postgresql://postgres:11111@localhost:5432/sinya'\n",
    "    db = create_engine(conn_string)\n",
    "    connection = psycopg2.connect(dbname='sinya', user='postgres', \n",
    "                        password='11111', host='127.0.0.1')\n",
    "    cursor = connection.cursor()\n",
    "    #почистим теги от скобочек и разделим строки по тегам\n",
    "    cursor.execute(f'''\n",
    "    SELECT t1.* FROM \t\n",
    "        (SELECT posts.\"Id\",posts.\"ViewCount\",posts.\"Title\",posts.\"CreationDate\", \n",
    "            posts.\"ProjectId\", lists.\"SplitTags\"\n",
    "        FROM posts \n",
    "        INNER JOIN \n",
    "            (SELECT \"Id\",\"ProjectId\",regexp_split_to_table(trim( t,'<>'), '><') as \"SplitTags\"\n",
    "            FROM (\n",
    "                    SELECT \"Id\",\"ProjectId\",\"Tags\" as t\n",
    "                    FROM posts\n",
    "                    WHERE \"ProjectId\"={project_id}\n",
    "                 ) as tags\n",
    "            ) as lists\n",
    "        ON \t\n",
    "            posts.\"Id\" = lists.\"Id\"\n",
    "            AND posts.\"ProjectId\" = lists.\"ProjectId\") as t1;\n",
    "    ''')\n",
    "    cols = ['Id','ViewCount','Title','CreationDate','ProjectId','SplitTags']\n",
    "    records = pd.DataFrame(cursor.fetchall(), columns = cols)\n",
    "    cursor.close()\n",
    "    connection.close()\n",
    "    # ничего красиво не заработало, ни executemany, ни execute_values\n",
    "    conn_string = 'postgresql://postgres:11111@localhost:5432/sinya'\n",
    "    db = create_engine(conn_string)\n",
    "    conn = db.connect()\n",
    "    records.to_sql('tags', con=conn, if_exists='append', index=False)\n",
    "    # закрываем соединение\n",
    "    conn.close()\n",
    "    db.dispose()\n",
    "   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5c121477",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_project(project,lang,type_id, pub_date):\n",
    "    connection = psycopg2.connect(dbname='sinya', user='postgres', \n",
    "                        password='11111', host='127.0.0.1')\n",
    "    cursor = connection.cursor()\n",
    "    # найдем максимальный id уже имеющегося проекта\n",
    "    cursor.execute('SELECT MAX(\"ProjectId\") FROM posts;')\n",
    "    records = cursor.fetchall()\n",
    "    # присвоим новому проекту следующий id\n",
    "    #TODO: обработать ситуацию, когда он уже есть, потому что пытались добавить, но случилась ошибка\n",
    "    project_id = records[0][0] + 1\n",
    "    # будем считать именем все до первой точки(будут проблемы у ru.stackoverflow)\n",
    "    name = project[:project.find('.')]\n",
    "    # запишем в projects новую запись\n",
    "    ins = f'INSERT INTO projects (\"Id\",\"Language\",\"Name\",\"ProjectTypeId\",\"PublicationDate\") VALUES ({project_id},\\'{lang}\\',\\'{name}\\',{type_id},\\'{pub_date}\\');'\n",
    "    cursor.execute(ins)\n",
    "    connection.commit()\n",
    "    cursor.close()\n",
    "    connection.close()\n",
    "    # добавим данные нового проекта\n",
    "    add_project_data(project, project_id)\n",
    "    # добавим теги нового проекта в таблицу тегов\n",
    "    add_project_tags(project_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "78f52e26",
   "metadata": {},
   "outputs": [],
   "source": [
    "project = 'movies.stackexchange.com'\n",
    "lang = 'en'\n",
    "type_id = '4'\n",
    "pub_date = '2022-03-07'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0698448a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!!!! Не запускать просто так\n",
    "add_project(project, lang, type_id, pub_date)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eefbb5d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "projects = ['ru.stackoverflow.com','datascience.stackexchange.com','arduino.stackexchange.com',\n",
    "            'cstheory.stackexchange.com','sqa.stackexchange.com','softwareengineering.stackexchange.com',\n",
    "            'stats.stackexchange.com', 'ai.stackexchange.com','cseducators.stackexchange.com',\n",
    "            'android.stackexchange.com','travel.stackexchange.com','movies.stackexchange.com']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63285f3d",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}