318 lines
8.4 KiB
Plaintext
318 lines
8.4 KiB
Plaintext
{
|
|
"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
|
|
}
|