Polymarket/database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4cae6bf1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, text\n",
    "import pandas as pd\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f5040527",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful\n"
     ]
    }
   ],
   "source": [
    "### MYSQL ###\n",
    "engine = create_engine('mysql+pymysql://root:pwd@localhost/polymarket')\n",
    "try:\n",
    "    with engine.connect() as conn:\n",
    "        print(\"Connection successful\")\n",
    "except Exception as e:\n",
    "    print(f\"Connection failed: {e}\")   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "72059b3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful\n"
     ]
    }
   ],
   "source": [
    "### MYSQL ###\n",
    "engine_inter_storage = create_engine('mysql+pymysql://root:pwd@100.84.226.40/polymarket')\n",
    "try:\n",
    "    with engine.connect() as conn:\n",
    "        print(\"Connection successful\")\n",
    "except Exception as e:\n",
    "    print(f\"Connection failed: {e}\")   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "b723a51f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# with engine.connect() as conn:\n",
    "#     print(\"Connection successful\")\n",
    "#     sql = text(\"TRUNCATE TABLE coinbase_btcusd_trades;\")\n",
    "#     conn.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c23110d",
   "metadata": {},
   "outputs": [],
   "source": [
    "q_binance = '''\n",
    "SELECT * FROM binance_btcusd_trades;\n",
    "'''\n",
    "q_coinbase = '''\n",
    "SELECT * FROM coinbase_btcusd_trades;\n",
    "'''\n",
    "q_rtds = '''\n",
    "SELECT * FROM poly_rtds_cl_btcusd;\n",
    "'''\n",
    "q_clob = '''\n",
    "SELECT * FROM poly_btcusd_trades;\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a866e9ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_binance = pd.read_sql(q_binance, con=engine)\n",
    "df_coinbase = pd.read_sql(q_coinbase, con=engine)\n",
    "df_rtds = pd.read_sql(q_rtds, con=engine)\n",
    "df_clob = pd.read_sql(q_clob, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "954a3c3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_binance['timestamp_arrival'] = pd.to_datetime(df_binance['timestamp_arrival'], unit='ms')\n",
    "df_coinbase['timestamp_arrival'] = pd.to_datetime(df_coinbase['timestamp_arrival'], unit='ms')\n",
    "df_rtds['timestamp_arrival'] = pd.to_datetime(df_rtds['timestamp_arrival'], unit='ms')\n",
    "df_clob['timestamp_arrival'] = pd.to_datetime(df_clob['timestamp_arrival'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "50c6339f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def copy_table_data_btw_servers(df, table_name, engine_destination) -> None:\n",
    "    rows_imported = df.to_sql(name=table_name, con=engine_destination, if_exists='append')\n",
    "    if rows_imported == len(df):\n",
    "        print(f'SUCCESS: COPIED {rows_imported} to table \"{table_name}\" on INTERSERVER_STORAGE')\n",
    "    else:\n",
    "        raise ValueError(f'FAILED: COPIED {rows_imported} rows to table {table_name} on INTERSERVER_STORAGE; EXPECTED {len(df)}')\n",
    "    \n",
    "def truncate_table(engine, table):\n",
    "    with engine.connect() as conn:\n",
    "        sql = text(f\"TRUNCATE TABLE {table};\")\n",
    "        conn.execute(sql)\n",
    "        conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "d0399a96",
   "metadata": {},
   "outputs": [],
   "source": [
    "def backup_all_tables(engine_origin, engine_destination, tables_to_copy):\n",
    "    for t in tables_to_copy:\n",
    "        q = f'''\n",
    "        SELECT * FROM {t};\n",
    "        '''\n",
    "        df = pd.read_sql(q, con=engine_origin)\n",
    "        print('-------------------------------------------------------------------------')\n",
    "        print(f'Loaded Data for Table: {t}...Attempting to Transfer to Destination Server')\n",
    "        copy_table_data_btw_servers(\n",
    "            df=df,\n",
    "            table_name=t,\n",
    "            engine_destination=engine_destination,\n",
    "        )\n",
    "        print(f'Attempting to Truncate Table: {t}...')\n",
    "        \n",
    "        ### FOR REALTIME - instead of truncate, need to delete rows using a conditon (e.g. delete all rows <= max timestamp arrival in the DF)\n",
    "        \n",
    "        truncate_table(\n",
    "            engine=engine_origin,\n",
    "            table=t,\n",
    "        )\n",
    "        print(f'...Successfully Truncated Table: {t}')\n",
    "        print(f'Done Transferring Data for Table: {t}')\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "0de1629a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables_to_copy = [\n",
    "    # 'binance_btcusd_trades',\n",
    "    # 'coinbase_btcusd_trades',\n",
    "    'poly_btcusd_trades',\n",
    "    'poly_rtds_cl_btcusd',\n",
    "    # 'user_stream_orders',\n",
    "    # 'user_stream_trades',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------------------------------------------------\n",
      "Loaded Data for Table: poly_btcusd_trades...Attempting to Transfer to Destination Server\n",
      "SUCCESS: COPIED 720568 to table \"poly_btcusd_trades\" on INTERSERVER_STORAGE\n",
      "Attempting to Truncate Table: poly_btcusd_trades...\n",
      "...Successfully Truncated Table: poly_btcusd_trades\n",
      "Done Transferring Data for Table: poly_btcusd_trades\n",
      "-------------------------------------------------------------------------\n",
      "Loaded Data for Table: poly_rtds_cl_btcusd...Attempting to Transfer to Destination Server\n",
      "SUCCESS: COPIED 73771 to table \"poly_rtds_cl_btcusd\" on INTERSERVER_STORAGE\n",
      "Attempting to Truncate Table: poly_rtds_cl_btcusd...\n",
      "...Successfully Truncated Table: poly_rtds_cl_btcusd\n",
      "Done Transferring Data for Table: poly_rtds_cl_btcusd\n"
     ]
    }
   ],
   "source": [
    "backup_all_tables(\n",
    "    engine_origin=engine,\n",
    "    engine_destination=engine_inter_storage,\n",
    "    tables_to_copy=tables_to_copy\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd0b40d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SUCCESS COPIED 326007 to binance_btcusd_trades to INTERSERVER_STORAGE\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48b47799",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad030f88",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cafc5060",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ba7be5f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py_313",
   "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.13.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}