02.3-crawl-account-tx.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData, Table, create_engine\n",
    "from tqdm.notebook import tqdm\n",
    "\n",
    "from config import db_connection_string, etherscan_api_key\n",
    "from utils import get_l1_address_txs, get_zksync_address_txs, get_symbol_decimals_dict, insert_l1_txs, insert_zksync_txs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(db_connection_string)\n",
    "metadata = MetaData()\n",
    "metadata.reflect(engine)\n",
    "tx_table = Table('tx', metadata, autoload=True, autoload_with=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## L1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8ebb890e2209477caf04e1686bb000c8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": "  0%|          | 0/17652 [00:00<?, ?it/s]"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for row in tqdm(engine.execute(\"SELECT DISTINCT `address` FROM `account_address`\").fetchall()):\n",
    "    txs = get_l1_address_txs(row.address, etherscan_api_key)\n",
    "    txs = [tx for tx in txs if tx.get('tokenDecimal')!='']\n",
    "    \n",
    "    insert_l1_txs(txs, engine, tx_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## zksync"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "symbol_decimals_dict = get_symbol_decimals_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5d87c33d57a341008334399599b80ec1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": "  0%|          | 0/17652 [00:00<?, ?it/s]"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for row in tqdm(engine.execute(\"SELECT DISTINCT `address` FROM `account_address`\").fetchall()):\n",
    "    txs = get_zksync_address_txs(row.address)\n",
    "    txs = [tx for tx in txs if tx['tx']['type']=='Transfer' and isinstance(tx['tx']['token'], str)]\n",
    "\n",
    "    insert_zksync_txs(txs, engine, tx_table, symbol_decimals_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## remove duplicate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7fe926776d00>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"\n",
    "    CREATE TABLE tx_temp  LIKE tx;\n",
    "    INSERT INTO tx_temp SELECT DISTINCT * FROM tx;\n",
    "    DROP TABLE tx;\n",
    "    ALTER TABLE tx_temp  RENAME TO tx;\n",
    "\"\"\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit ('3.9.5': pyenv)",
   "name": "python395jvsc74a57bd03e0cc74c63ba0d1852eebf501d0651ff437bde89ea35d6e4a9fed06817a25ece"
  },
  "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.5"
  },
  "metadata": {
   "interpreter": {
    "hash": "3e0cc74c63ba0d1852eebf501d0651ff437bde89ea35d6e4a9fed06817a25ece"
   }
  },
  "orig_nbformat": 2
 },
 "nbformat": 4,
 "nbformat_minor": 2
}