{"id":380621,"date":"2024-06-29T03:12:15","date_gmt":"2024-06-29T03:12:15","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=380621"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=380621","title":{"rendered":"<span>How to access real-time smart contract data from Python code (using Lido contract as an example)<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>Let\u2019s imagine you need access to the real-time data of some smart contracts on Ethereum (or Polygon, BSC, etc.) like Uniswap or even PEPE coin to analyze its data using the standard data scientist\/analyst tools: Python, Pandas, Matplotlib, etc. In this tutorial, I\u2019ll show you more sophisticated data access tools that are more like a surgical scalpel (The Graph subgraphs) than a well-known Swiss knife (RPC node access) or hammer (ready-to-use APIs). I hope my metaphors don\u2019t scare you ?.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/b70\/ce4\/364\/b70ce43646407c7a042444e4df3b87fe.png\" width=\"878\" height=\"588\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/b70\/ce4\/364\/b70ce43646407c7a042444e4df3b87fe.png\"\/><\/figure>\n<p>There are some different methods how to access the data on Ethereum:<\/p>\n<ul>\n<li>\n<p>Using RPC-node commands like\u00a0<a href=\"https:\/\/docs.chainstack.com\/reference\/ethereum-getblockbynumber\" rel=\"noopener noreferrer nofollow\"><u>getBlockByNumber<\/u><\/a>\u00a0to get the low-level block information, then accessing smart contract data via libraries like\u00a0<a href=\"https:\/\/web3py.readthedocs.io\/en\/stable\/\" rel=\"noopener noreferrer nofollow\"><u>web3.py<\/u><\/a>. It allows you to get the data block by block and then collect it in your own database or CSV file. This way is not really fast, and parsing popular smart contract data using this way usually takes years.<\/p>\n<\/li>\n<li>\n<p>Using some data analytics providers like Dune which can help you with some popular smart contract data, but it is not really real-time. The latency can be about several minutes.<\/p>\n<\/li>\n<li>\n<p>Using some ready-to-use APIs like NFT API\/Token API\/DeFi API. And it can be an excellent option because usually, the latency is low. The only problem that you can face is that the data you need is not available. For instance, not all variables can be available as historical time series.<\/p>\n<\/li>\n<\/ul>\n<p>What if you still want to have real-time data of a smart contract, but are not satisfied by previous solutions, because you want all:<\/p>\n<ul>\n<li>\n<p>you want low-latency data (the data is always up-to-date right after a new block has been mined)<\/p>\n<\/li>\n<li>\n<p>you need a custom slice of data that is not available on any ready-to-use API<\/p>\n<\/li>\n<li>\n<p>you don\u2019t want to hassle with manual processing data block by block along with processing block reorganizations<\/p>\n<\/li>\n<\/ul>\n<p>This is the best use case for subgraphs by The Graph. Essentially, The Graph is a decentralized network to access smart contract data in a decentralized way paying the price for requests in GRT tokens.<\/p>\n<p>But the underlying technology called \u201csubgraphs\u201d allows you to transform your simple description of what variables need to be saved (for real-time access) into the production-grade ETL pipeline which<\/p>\n<ul>\n<li>\n<p>Extract data from the blockchain<\/p>\n<\/li>\n<li>\n<p>Saves it into the database<\/p>\n<\/li>\n<li>\n<p>Making this data accessible via GraphQL interface<\/p>\n<\/li>\n<li>\n<p>Updates the data after each new block is mined on the network<\/p>\n<\/li>\n<li>\n<p>Automatically process the chain reorganizations<\/p>\n<\/li>\n<\/ul>\n<p>This is a big deal. You don\u2019t need to be a highly qualified data engineer experienced in EVM-compatible blockchains to set up the entire workflow.<\/p>\n<p>But let\u2019s start with something ready-to-use. What if somebody has already developed a subgraph that helps to access the data you need?<\/p>\n<p>You can go to The Graph hosted service\u00a0<a href=\"https:\/\/thegraph.com\/hosted-service\" rel=\"noopener noreferrer nofollow\"><u>website<\/u><\/a>, find the community subgraph section and try to get the existing subgraphs on the protocol you need. For instance, let\u2019s find a subgraph to access\u00a0<a href=\"https:\/\/lido.fi\/\" rel=\"noopener noreferrer nofollow\"><u>Lido<\/u><\/a>\u00a0protocol (which allows users to stake their Ethers without limiting the minimum value of 32 ethers, as is usually the case, and apart from it getting the tokens that can be staked again, can you believe this??).<\/p>\n<p>Lido protocol is currently top-1 in terms of TVL (Total Value Locked \u2014 a metric used to measure the total value of digital assets that are locked or staked in a particular DeFi platform or DApp) according to\u00a0<a href=\"https:\/\/defillama.com\/\" rel=\"noopener noreferrer nofollow\"><u>DeFiLlama<\/u><\/a>.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/826\/b99\/23a\/826b9923a1e088c3191f2e5b2b203e8b.png\" width=\"1400\" height=\"621\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/826\/b99\/23a\/826b9923a1e088c3191f2e5b2b203e8b.png\"\/><\/figure>\n<p>And there it is! The subgraph made by Lido team is here.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/9cb\/501\/8ff\/9cb5018ff2736a88dec9e785b6765e0e.png\" width=\"1400\" height=\"1026\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/9cb\/501\/8ff\/9cb5018ff2736a88dec9e785b6765e0e.png\"\/><\/figure>\n<p>Let\u2019s go to the subgraph details page.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d49\/d6b\/8f2\/d49d6b8f2b7a85daeb1eea404e620dcc.png\" width=\"1400\" height=\"956\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/d49\/d6b\/8f2\/d49d6b8f2b7a85daeb1eea404e620dcc.png\"\/><\/figure>\n<p>What can we see here?<\/p>\n<ol>\n<li>\n<p>The IPFS CID of this subgraph \u2014 it is an internal unique identifier of this subgraph pointing to the manifest of this subgraph on the\u00a0<a href=\"https:\/\/ipfs.tech\/\" rel=\"noopener noreferrer nofollow\"><u>IPFS<\/u><\/a>\u00a0(peer-to-peer protocol to find a file by hash \u2014 this is a critically simplified explanation, but you can figure out how it works for yourself).<\/p>\n<\/li>\n<li>\n<p>Query URL \u2014 this is an actual endpoint that we will use in our Python code to access smart contract data.<\/p>\n<\/li>\n<li>\n<p>The indicator of a subgraph sync status. When the subgraph is up-to-date you can query the data, but you should understand that deploying a new subgraph you must wait for a while to sync it. During this process, it will show the number of current block under processing.<\/p>\n<\/li>\n<li>\n<p>The GraphQL query. Each subgraph has its own data structure (the list of tables with data) that need to be considered when creating a GraphQL query. Generally, GraphQL is pretty easy to learn, but if you are struggling with it you can ask ChatGPT to help with it ?.\u00a0<a href=\"https:\/\/medium.com\/p\/5cb4349fbf9e\" rel=\"noopener noreferrer nofollow\"><u>Here<\/u><\/a>\u00a0is an example at the end of the article.<\/p>\n<\/li>\n<li>\n<p>The button which runs the query.<\/p>\n<\/li>\n<li>\n<p>The output window. As you can see the GraphQL response is JSON-like structure.<\/p>\n<\/li>\n<li>\n<p>A switch that allows you to see the data structure of this subgraph.<\/p>\n<\/li>\n<\/ol>\n<p>Let\u2019s get down to business (Let\u2019s uncover our jupyter notebooks?).<\/p>\n<p>Getting raw data:<\/p>\n<pre><code class=\"python\">import pandas as pd import requests  def run_query(uri, query):     request = requests.post(uri, json={'query': query}, headers={\"Content-Type\": \"application\/json\"})     if request.status_code == 200:         return request.json()     else:         raise Exception(f\"Unexpected status code returned: {request.status_code}\")  url = \"https:\/\/api.thegraph.com\/subgraphs\/name\/lidofinance\/lido\" query = \"\"\"{   lidoTransfers(first: 50) {     from     to     value     block     blockTime     transactionHash   } }\"\"\"  result = run_query(url, query)<\/code><\/pre>\n<p>The result variable looks like this:<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/37d\/72f\/001\/37d72f001df4b8d1dd30244f4be7807d.png\" width=\"1400\" height=\"625\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/37d\/72f\/001\/37d72f001df4b8d1dd30244f4be7807d.png\"\/><\/figure>\n<p>And the last transformation (which is only valid for flat JSON response) creates a dataframe:<\/p>\n<pre><code class=\"python\">df = pd.DataFrame(result['data']['lidoTransfers']) df.head()<\/code><\/pre>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e06\/3e2\/1fa\/e063e21faa7dec14433962c8177ba78c.png\" width=\"1400\" height=\"325\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/e06\/3e2\/1fa\/e063e21faa7dec14433962c8177ba78c.png\"\/><\/figure>\n<p>But how to download all data from the table? With GraphQL there are different options on it, and I am picking the following one. Considering that the blocks are ascending let\u2019s scan from the first block querying by 1000 entities each time (1000 is the limit for the graph-node).<\/p>\n<pre><code class=\"python\">query = \"\"\"{   lidoTransfers(orderBy: block, orderDirection: asc, first: 1) {      block   } }\"\"\" # here we get the first block number to start with first_block = int(run_query(url, query)['data']['lidoTransfers'][0]['block']) current_last_block = 17379510  #query template to make consecutive queries query_template = \"\"\"{{   lidoTransfers(where: {{block_gte: {block_x} }}, orderBy: block, orderDirection: asc, first: 1000) {{     from     to     value     block     blockTime     transactionHash   }} }}\"\"\"  result = [] # storing the response offset = first_block # starting from the first found block  while True:      query = query_template.format(block_x=offset) # generate the query     sub_result = run_query(url, query)['data']['lidoTransfers'] # get the data     if len(sub_result)&lt;=1: # break if finished         break     sh = int(sub_result[-1]['block']) - offset # calculate the shift     offset = int(sub_result[-1]['block']) # calculate the new shift     result.extend(sub_result) # append     print(f\"{(offset-first_block)\/(current_last_block - first_block)* 100:.1f}%, got {len(sub_result)} lines, block shift {sh}\" ) #show the log  # convert to the dataframe df = pd.DataFrame(result)<\/code><\/pre>\n<p>Bear in mind that we do overlapping queries because each time we use the last block number from the query to start the next one with. We do this to avoid missing records due to the possible multiple transactions per block.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/967\/426\/fa9\/967426fa99755c95b203defa7f7b4e08.png\" width=\"696\" height=\"520\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/967\/426\/fa9\/967426fa99755c95b203defa7f7b4e08.png\"\/><\/figure>\n<p>As we see each query returns 1000 lines, but the block number is shifting by several tens of thousands. It means that not every block contains at least one Lido transaction. An important step here is to get rid of the duplicates that we collected avoiding missing records:<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f8b\/cbf\/2bd\/f8bcbf2bd7d70ba3c1d01caa3533019e.png\" width=\"626\" height=\"302\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/f8b\/cbf\/2bd\/f8bcbf2bd7d70ba3c1d01caa3533019e.png\"\/><\/figure>\n<p>As we see, there are 9k+ duplicated lines in the dataframe.<\/p>\n<p>Now let\u2019s do some simple EDA.<\/p>\n<pre><code class=\"python\">col = \"from\" df.groupby(col, as_index=False)\\     .agg({'transactionHash': 'count'})\\     .sort_values('transactionHash', ascending=False)\\     .head(5)<\/code><\/pre>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/c39\/b1d\/bd7\/c39b1dbd7b5a0f12e368bf9a577c3561.png\" width=\"916\" height=\"358\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/c39\/b1d\/bd7\/c39b1dbd7b5a0f12e368bf9a577c3561.png\"\/><\/figure>\n<p>If we check the most frequent addresses in the field \u201cfrom\u201d we find \u201c0x0000000000000000000000000000000000000000\u201d address. Usually, it means the issue of new tokens, so we can find a transaction in\u00a0<a href=\"https:\/\/etherscan.io\/tx\/0x63a36a672165dc6b6d3a6f6d6594b79720f0cb844c4876e31b62ed3b6ee79706\" rel=\"noopener noreferrer nofollow\"><u>Etherscan<\/u><\/a>\u00a0and check:<\/p>\n<pre><code class=\"python\">(df[df['from']=='0x0000000000000000000000000000000000000000'].iloc[1000].to,\\ df[df['from']=='0x0000000000000000000000000000000000000000'].iloc[1000].transactionHash, df[df['from']=='0x0000000000000000000000000000000000000000'].iloc[1000].value,)<\/code><\/pre>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/c76\/357\/22b\/c7635722b440805fe83bed66a98b9262.png\" width=\"1208\" height=\"122\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/c76\/357\/22b\/c7635722b440805fe83bed66a98b9262.png\"\/><\/figure>\n<p>We will see the transaction that has the same \u201cvalue\u201d:<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fb7\/81e\/17c\/fb781e17c93522fd5eea326a58d14f7e.png\" width=\"1400\" height=\"771\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/fb7\/81e\/17c\/fb781e17c93522fd5eea326a58d14f7e.png\"\/><\/figure>\n<p>Also, it is interesting to check the most frequent receiver of funds (by the field called \u201cto\u201d):<\/p>\n<pre><code class=\"python\">col = \"to\" df.groupby(col, as_index=False)\\     .agg({'transactionHash': 'count'})\\     .sort_values('transactionHash', ascending=False)\\     .head(5)<\/code><\/pre>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/6c3\/f85\/fb6\/6c3f85fb6dca9991c0d7bb46361f1d9c.png\" width=\"922\" height=\"356\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/6c3\/f85\/fb6\/6c3f85fb6dca9991c0d7bb46361f1d9c.png\"\/><\/figure>\n<p>The address \u201c0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0\u201d can be found on the\u00a0<a href=\"https:\/\/etherscan.io\/address\/0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0#code\" rel=\"noopener noreferrer nofollow\"><u>Etherscan<\/u><\/a>\u00a0as Lido: wrapped stETH Token.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/b89\/997\/b55\/b89997b55ac34b8bf9131c7b6b4c7a3e.png\" width=\"1400\" height=\"755\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/b89\/997\/b55\/b89997b55ac34b8bf9131c7b6b4c7a3e.png\"\/><\/figure>\n<p>Now let\u2019s see the number of transactions per month:<\/p>\n<pre><code class=\"python\">import datetime import matplotlib.pyplot as plt  df[\"blockTime_\"] = df[\"blockTime\"].apply(lambda x: datetime.datetime.fromtimestamp(int(x))) df['ym'] = df['blockTime_'].dt.strftime(\"%Y-%m\") df_time = df.groupby('ym', as_index=False).agg({'transactionHash': 'count'}).sort_values('ym')  fig, ax = plt.subplots(figsize=(12,8)) ax.plot(df_time['ym'].iloc[:-1], df_time['transactionHash'].iloc[:-1]) plt.xticks(rotation=45) plt.xlabel('month') plt.ylabel('number of transactions') plt.grid() plt.show()<\/code><\/pre>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e84\/9b1\/8da\/e849b18da109ec776d5ee357a9f939f8.png\" width=\"1400\" height=\"983\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/e84\/9b1\/8da\/e849b18da109ec776d5ee357a9f939f8.png\"\/><\/figure>\n<p>The number of transactions per month is growing over the years constantly!<\/p>\n<p>You can continue your investigation going forward with the other fields or make other queries to different tables on the same subgraph:<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/8bb\/cfa\/26d\/8bbcfa26d70c692233927bc8df9d23a2.png\" width=\"1400\" height=\"961\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/8bb\/cfa\/26d\/8bbcfa26d70c692233927bc8df9d23a2.png\"\/><\/figure>\n<h2>What else you can do with subgraphs<\/h2>\n<p><strong>Firstly<\/strong>, if you need to access the data of\u00a0<strong>any other smart contract<\/strong>\u00a0<strong>that has no subgraph yet<\/strong>\u00a0(or the data is not enough) you can easily write your own subgraph using these tutorials:<\/p>\n<ol>\n<li>\n<p><a href=\"https:\/\/medium.com\/@balakhonoff_47314\/how-to-access-the-tornado-cash-data-easily-using-the-graphs-subgraphs-a70a7e21449d\" rel=\"noopener noreferrer nofollow\"><u>How to access the Tornado Cash data easily using The Graph\u2019s subgraphs<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/medium.com\/@balakhonoff_47314\/tutorial-how-to-access-transactions-of-pepe-pepe-coin-using-the-graph-subgraphs-and-chatgpt-5cb4349fbf9e\" rel=\"noopener noreferrer nofollow\"><u>How to access transactions of PEPE ($PEPE) coin using The Graph subgraphs and ChatGPT prompts<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-indexing-uniswap-data\" rel=\"noopener noreferrer nofollow\"><u>Indexing Uniswap data with subgraphs<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-a-beginners-guide-to-getting-started-with-the-graph\" rel=\"noopener noreferrer nofollow\"><u>A beginner\u2019s guide to getting started with The Graph<\/u><\/a><\/p>\n<\/li>\n<\/ol>\n<p><strong>Secodly, i<\/strong>f you want to become an advanced subgraphs developer, consider these deep-diving tutorials:<\/p>\n<ol>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-working-with-schemas\" rel=\"noopener noreferrer nofollow\"><u>Explaining Subgraph schemas<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-debug-subgraphs-with-a-local-graph-node\" rel=\"noopener noreferrer nofollow\"><u>Debugging subgraphs with a local Graph Node<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-fetching-subgraph-data-using-javascript\" rel=\"noopener noreferrer nofollow\"><u>Fetching subgraph data using javascript<\/u><\/a><\/p>\n<\/li>\n<\/ol>\n<p><strong>Thirdly<\/strong>, if you are going to use subgraphs real-time data in your production application, you can deploy your subgraph into the\u00a0<a href=\"https:\/\/chainstack.com\/subgraphs\/\" rel=\"noopener noreferrer nofollow\"><u>Chainstack Subgraphs<\/u><\/a>\u00a0hosted service which is multiple times faster and 99.9% reliable.<\/p>\n<p><strong>Then<\/strong>, to write a subgraph is not straightforward for the beginners. So, <a href=\"https:\/\/t.me\/SubgraphGPT_bot\" rel=\"noopener noreferrer nofollow\">SubgraphGPT<\/a> telegram-bot can be helpful to get the ChatGPT-powered answers according to the related The Graph knowledge-base. <\/p>\n<p><strong>Additionally<\/strong>, you can find it helpful to look at all subgraphs resources collected in the <em>awesome-subgraphs<\/em> GitHub <a href=\"https:\/\/github.com\/balakhonoff\/awesome-subgraphs\" rel=\"noopener noreferrer nofollow\">repository<\/a>.<\/p>\n<p><strong>Finally<\/strong>, if you still don\u2019t feel comfortable with subgraphs do not hesitate to ask any questions in the telegram chat \u201c<a href=\"https:\/\/t.me\/+HHP9q2gWFGNlNGYy\" rel=\"noopener noreferrer nofollow\"><u>Subgraphs Experience Sharing<\/u><\/a>\u201d.<\/p>\n<p>\u2014<\/p>\n<p>Kirill Balakhonov \u2014 Product Lead @\u00a0<a href=\"https:\/\/chainstack.com\/\" rel=\"noopener noreferrer nofollow\"><u>chainstack.com<\/u><\/a>\u00a0\u2014\u00a0<a href=\"https:\/\/twitter.com\/balakhonoff\" rel=\"noopener noreferrer nofollow\"><u>Twitter<\/u><\/a>\u00a0<a href=\"https:\/\/t.me\/kirill_balakhonov\" rel=\"noopener noreferrer nofollow\"><u>Telegram<\/u><\/a><\/p>\n<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/752264\/\"> https:\/\/habr.com\/ru\/articles\/752264\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>Let\u2019s imagine you need access to the real-time data of some smart contracts on Ethereum (or Polygon, BSC, etc.) like Uniswap or even PEPE coin to analyze its data using the standard data scientist\/analyst tools: Python, Pandas, Matplotlib, etc. In this tutorial, I\u2019ll show you more sophisticated data access tools that are more like a surgical scalpel (The Graph subgraphs) than a well-known Swiss knife (RPC node access) or hammer (ready-to-use APIs). I hope my metaphors don\u2019t scare you ?.<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>There are some different methods how to access the data on Ethereum:<\/p>\n<ul>\n<li>\n<p>Using RPC-node commands like\u00a0<a href=\"https:\/\/docs.chainstack.com\/reference\/ethereum-getblockbynumber\" rel=\"noopener noreferrer nofollow\"><u>getBlockByNumber<\/u><\/a>\u00a0to get the low-level block information, then accessing smart contract data via libraries like\u00a0<a href=\"https:\/\/web3py.readthedocs.io\/en\/stable\/\" rel=\"noopener noreferrer nofollow\"><u>web3.py<\/u><\/a>. It allows you to get the data block by block and then collect it in your own database or CSV file. This way is not really fast, and parsing popular smart contract data using this way usually takes years.<\/p>\n<\/li>\n<li>\n<p>Using some data analytics providers like Dune which can help you with some popular smart contract data, but it is not really real-time. The latency can be about several minutes.<\/p>\n<\/li>\n<li>\n<p>Using some ready-to-use APIs like NFT API\/Token API\/DeFi API. And it can be an excellent option because usually, the latency is low. The only problem that you can face is that the data you need is not available. For instance, not all variables can be available as historical time series.<\/p>\n<\/li>\n<\/ul>\n<p>What if you still want to have real-time data of a smart contract, but are not satisfied by previous solutions, because you want all:<\/p>\n<ul>\n<li>\n<p>you want low-latency data (the data is always up-to-date right after a new block has been mined)<\/p>\n<\/li>\n<li>\n<p>you need a custom slice of data that is not available on any ready-to-use API<\/p>\n<\/li>\n<li>\n<p>you don\u2019t want to hassle with manual processing data block by block along with processing block reorganizations<\/p>\n<\/li>\n<\/ul>\n<p>This is the best use case for subgraphs by The Graph. Essentially, The Graph is a decentralized network to access smart contract data in a decentralized way paying the price for requests in GRT tokens.<\/p>\n<p>But the underlying technology called \u201csubgraphs\u201d allows you to transform your simple description of what variables need to be saved (for real-time access) into the production-grade ETL pipeline which<\/p>\n<ul>\n<li>\n<p>Extract data from the blockchain<\/p>\n<\/li>\n<li>\n<p>Saves it into the database<\/p>\n<\/li>\n<li>\n<p>Making this data accessible via GraphQL interface<\/p>\n<\/li>\n<li>\n<p>Updates the data after each new block is mined on the network<\/p>\n<\/li>\n<li>\n<p>Automatically process the chain reorganizations<\/p>\n<\/li>\n<\/ul>\n<p>This is a big deal. You don\u2019t need to be a highly qualified data engineer experienced in EVM-compatible blockchains to set up the entire workflow.<\/p>\n<p>But let\u2019s start with something ready-to-use. What if somebody has already developed a subgraph that helps to access the data you need?<\/p>\n<p>You can go to The Graph hosted service\u00a0<a href=\"https:\/\/thegraph.com\/hosted-service\" rel=\"noopener noreferrer nofollow\"><u>website<\/u><\/a>, find the community subgraph section and try to get the existing subgraphs on the protocol you need. For instance, let\u2019s find a subgraph to access\u00a0<a href=\"https:\/\/lido.fi\/\" rel=\"noopener noreferrer nofollow\"><u>Lido<\/u><\/a>\u00a0protocol (which allows users to stake their Ethers without limiting the minimum value of 32 ethers, as is usually the case, and apart from it getting the tokens that can be staked again, can you believe this??).<\/p>\n<p>Lido protocol is currently top-1 in terms of TVL (Total Value Locked \u2014 a metric used to measure the total value of digital assets that are locked or staked in a particular DeFi platform or DApp) according to\u00a0<a href=\"https:\/\/defillama.com\/\" rel=\"noopener noreferrer nofollow\"><u>DeFiLlama<\/u><\/a>.<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>And there it is! The subgraph made by Lido team is here.<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>Let\u2019s go to the subgraph details page.<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>What can we see here?<\/p>\n<ol>\n<li>\n<p>The IPFS CID of this subgraph \u2014 it is an internal unique identifier of this subgraph pointing to the manifest of this subgraph on the\u00a0<a href=\"https:\/\/ipfs.tech\/\" rel=\"noopener noreferrer nofollow\"><u>IPFS<\/u><\/a>\u00a0(peer-to-peer protocol to find a file by hash \u2014 this is a critically simplified explanation, but you can figure out how it works for yourself).<\/p>\n<\/li>\n<li>\n<p>Query URL \u2014 this is an actual endpoint that we will use in our Python code to access smart contract data.<\/p>\n<\/li>\n<li>\n<p>The indicator of a subgraph sync status. When the subgraph is up-to-date you can query the data, but you should understand that deploying a new subgraph you must wait for a while to sync it. During this process, it will show the number of current block under processing.<\/p>\n<\/li>\n<li>\n<p>The GraphQL query. Each subgraph has its own data structure (the list of tables with data) that need to be considered when creating a GraphQL query. Generally, GraphQL is pretty easy to learn, but if you are struggling with it you can ask ChatGPT to help with it ?.\u00a0<a href=\"https:\/\/medium.com\/p\/5cb4349fbf9e\" rel=\"noopener noreferrer nofollow\"><u>Here<\/u><\/a>\u00a0is an example at the end of the article.<\/p>\n<\/li>\n<li>\n<p>The button which runs the query.<\/p>\n<\/li>\n<li>\n<p>The output window. As you can see the GraphQL response is JSON-like structure.<\/p>\n<\/li>\n<li>\n<p>A switch that allows you to see the data structure of this subgraph.<\/p>\n<\/li>\n<\/ol>\n<p>Let\u2019s get down to business (Let\u2019s uncover our jupyter notebooks?).<\/p>\n<p>Getting raw data:<\/p>\n<pre><code class=\"python\">import pandas as pd import requests  def run_query(uri, query):     request = requests.post(uri, json={'query': query}, headers={\"Content-Type\": \"application\/json\"})     if request.status_code == 200:         return request.json()     else:         raise Exception(f\"Unexpected status code returned: {request.status_code}\")  url = \"https:\/\/api.thegraph.com\/subgraphs\/name\/lidofinance\/lido\" query = \"\"\"{   lidoTransfers(first: 50) {     from     to     value     block     blockTime     transactionHash   } }\"\"\"  result = run_query(url, query)<\/code><\/pre>\n<p>The result variable looks like this:<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>And the last transformation (which is only valid for flat JSON response) creates a dataframe:<\/p>\n<pre><code class=\"python\">df = pd.DataFrame(result['data']['lidoTransfers']) df.head()<\/code><\/pre>\n<figure class=\"full-width\"><\/figure>\n<p>But how to download all data from the table? With GraphQL there are different options on it, and I am picking the following one. Considering that the blocks are ascending let\u2019s scan from the first block querying by 1000 entities each time (1000 is the limit for the graph-node).<\/p>\n<pre><code class=\"python\">query = \"\"\"{   lidoTransfers(orderBy: block, orderDirection: asc, first: 1) {      block   } }\"\"\" # here we get the first block number to start with first_block = int(run_query(url, query)['data']['lidoTransfers'][0]['block']) current_last_block = 17379510  #query template to make consecutive queries query_template = \"\"\"{{   lidoTransfers(where: {{block_gte: {block_x} }}, orderBy: block, orderDirection: asc, first: 1000) {{     from     to     value     block     blockTime     transactionHash   }} }}\"\"\"  result = [] # storing the response offset = first_block # starting from the first found block  while True:      query = query_template.format(block_x=offset) # generate the query     sub_result = run_query(url, query)['data']['lidoTransfers'] # get the data     if len(sub_result)&lt;=1: # break if finished         break     sh = int(sub_result[-1]['block']) - offset # calculate the shift     offset = int(sub_result[-1]['block']) # calculate the new shift     result.extend(sub_result) # append     print(f\"{(offset-first_block)\/(current_last_block - first_block)* 100:.1f}%, got {len(sub_result)} lines, block shift {sh}\" ) #show the log  # convert to the dataframe df = pd.DataFrame(result)<\/code><\/pre>\n<p>Bear in mind that we do overlapping queries because each time we use the last block number from the query to start the next one with. We do this to avoid missing records due to the possible multiple transactions per block.<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>As we see each query returns 1000 lines, but the block number is shifting by several tens of thousands. It means that not every block contains at least one Lido transaction. An important step here is to get rid of the duplicates that we collected avoiding missing records:<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>As we see, there are 9k+ duplicated lines in the dataframe.<\/p>\n<p>Now let\u2019s do some simple EDA.<\/p>\n<pre><code class=\"python\">col = \"from\" df.groupby(col, as_index=False)\\     .agg({'transactionHash': 'count'})\\     .sort_values('transactionHash', ascending=False)\\     .head(5)<\/code><\/pre>\n<figure class=\"full-width\"><\/figure>\n<p>If we check the most frequent addresses in the field \u201cfrom\u201d we find \u201c0x0000000000000000000000000000000000000000\u201d address. Usually, it means the issue of new tokens, so we can find a transaction in\u00a0<a href=\"https:\/\/etherscan.io\/tx\/0x63a36a672165dc6b6d3a6f6d6594b79720f0cb844c4876e31b62ed3b6ee79706\" rel=\"noopener noreferrer nofollow\"><u>Etherscan<\/u><\/a>\u00a0and check:<\/p>\n<pre><code class=\"python\">(df[df['from']=='0x0000000000000000000000000000000000000000'].iloc[1000].to,\\ df[df['from']=='0x0000000000000000000000000000000000000000'].iloc[1000].transactionHash, df[df['from']=='0x0000000000000000000000000000000000000000'].iloc[1000].value,)<\/code><\/pre>\n<figure class=\"full-width\"><\/figure>\n<p>We will see the transaction that has the same \u201cvalue\u201d:<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>Also, it is interesting to check the most frequent receiver of funds (by the field called \u201cto\u201d):<\/p>\n<pre><code class=\"python\">col = \"to\" df.groupby(col, as_index=False)\\     .agg({'transactionHash': 'count'})\\     .sort_values('transactionHash', ascending=False)\\     .head(5)<\/code><\/pre>\n<figure class=\"full-width\"><\/figure>\n<p>The address \u201c0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0\u201d can be found on the\u00a0<a href=\"https:\/\/etherscan.io\/address\/0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0#code\" rel=\"noopener noreferrer nofollow\"><u>Etherscan<\/u><\/a>\u00a0as Lido: wrapped stETH Token.<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>Now let\u2019s see the number of transactions per month:<\/p>\n<pre><code class=\"python\">import datetime import matplotlib.pyplot as plt  df[\"blockTime_\"] = df[\"blockTime\"].apply(lambda x: datetime.datetime.fromtimestamp(int(x))) df['ym'] = df['blockTime_'].dt.strftime(\"%Y-%m\") df_time = df.groupby('ym', as_index=False).agg({'transactionHash': 'count'}).sort_values('ym')  fig, ax = plt.subplots(figsize=(12,8)) ax.plot(df_time['ym'].iloc[:-1], df_time['transactionHash'].iloc[:-1]) plt.xticks(rotation=45) plt.xlabel('month') plt.ylabel('number of transactions') plt.grid() plt.show()<\/code><\/pre>\n<figure class=\"full-width\"><\/figure>\n<p>The number of transactions per month is growing over the years constantly!<\/p>\n<p>You can continue your investigation going forward with the other fields or make other queries to different tables on the same subgraph:<\/p>\n<figure class=\"full-width\"><\/figure>\n<h2>What else you can do with subgraphs<\/h2>\n<p><strong>Firstly<\/strong>, if you need to access the data of\u00a0<strong>any other smart contract<\/strong>\u00a0<strong>that has no subgraph yet<\/strong>\u00a0(or the data is not enough) you can easily write your own subgraph using these tutorials:<\/p>\n<ol>\n<li>\n<p><a href=\"https:\/\/medium.com\/@balakhonoff_47314\/how-to-access-the-tornado-cash-data-easily-using-the-graphs-subgraphs-a70a7e21449d\" rel=\"noopener noreferrer nofollow\"><u>How to access the Tornado Cash data easily using The Graph\u2019s subgraphs<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/medium.com\/@balakhonoff_47314\/tutorial-how-to-access-transactions-of-pepe-pepe-coin-using-the-graph-subgraphs-and-chatgpt-5cb4349fbf9e\" rel=\"noopener noreferrer nofollow\"><u>How to access transactions of PEPE ($PEPE) coin using The Graph subgraphs and ChatGPT prompts<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-indexing-uniswap-data\" rel=\"noopener noreferrer nofollow\"><u>Indexing Uniswap data with subgraphs<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-a-beginners-guide-to-getting-started-with-the-graph\" rel=\"noopener noreferrer nofollow\"><u>A beginner\u2019s guide to getting started with The Graph<\/u><\/a><\/p>\n<\/li>\n<\/ol>\n<p><strong>Secodly, i<\/strong>f you want to become an advanced subgraphs developer, consider these deep-diving tutorials:<\/p>\n<ol>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-working-with-schemas\" rel=\"noopener noreferrer nofollow\"><u>Explaining Subgraph schemas<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-debug-subgraphs-with-a-local-graph-node\" rel=\"noopener noreferrer nofollow\"><u>Debugging subgraphs with a local Graph Node<\/u><\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https:\/\/docs.chainstack.com\/docs\/subgraphs-tutorial-fetching-subgraph-data-using-javascript\" rel=\"noopener noreferrer nofollow\"><u>Fetching subgraph data using javascript<\/u><\/a><\/p>\n<\/li>\n<\/ol>\n<p><strong>Thirdly<\/strong>, if you are going to use subgraphs real-time data in<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-380621","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/380621","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=380621"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/380621\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=380621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=380621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=380621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}