Using Dune SQL API in Python - For Dummies
101 guide to getting your first Dune query loaded in Python and visualizing it!
This guide is for absolute beginners. If this is your first time and you’re worried about how to connect Dune to Python and visualize stuff, don’t worry - wegotchu!
First, why use Python with Dune?
Dune visualizations are limited. Getting Dune data into Python lets you produce beautiful viz!
Once you start looking at summary stats and beyond [hello, data science nerds], the need for a 3P platform becomes crucial!
You can publish web apps combining data from multiple sources, with Dune data being a part of it!
Having a skill combo of SQL + a large scale data analysis tool such as Python or even R - puts you in a good position as you’re able to query, transform and load data in your weapon of choice.
Ready? Here are the steps at a very high level
Step 1 and 2: If you already have Python, move to Step 3
Step 1: Download Anaconda
Visit https://anaconda.com/download to download Python for windows/mac along with several data science libraries via Anaconda distribution
Step 2: Launch Spyder Python IDE from Anaconda
Spyder is one of the many IDEs that you can use to manage your Python code. Your choice, I use Spyder
Step 3: Create your Dune API key
Visit https://dune.com/settings/api and create your key. Copy it somewhere as it is onl shown once!
Note: You want to keep the API key a secret and not include it in the code you share on GitHub or even in any screenshot
Step 4: Working with Dune in Python
First things first. In Spyder, create a new Python file called Creds.py and paste this into the code
api_key="your api key from Dune pasted in quote marks"
Save the Creds.py file in a new project folder
In Spyder, create a new Python file for the main script. Remember to save both these Python files in the same folder.
Next, we install the dune client for Python. In the console screen, run
pip install dune-client
The full code is on GitHub [link is at the bottom]
We first import the data analysis/basic viz/Dune libraries into Python and also tell it to use the creds.py for Dune API key [which is a must!]
from dune_client.client import DuneClient
from dune_client.query import QueryBase
from dune_client.types import QueryParameter
import pandas as pd
import matplotlib.pyplot as plt
import creds
dune = DuneClient(api_key=creds.api_key)
Step 5: Access an existing Dune query
I created this Balancer query in Dune on trade volumes for last 7 days, using the dex.trades table from my Dune account
https://dune.com/queries/5800764
SELECT
date_trunc ('day',block_time) AS day,
SUM (amount_usd) AS total_volume_usd, project
FROM dex.trades
WHERE
project = 'balancer'
AND block_time >= now() - INTERVAL '7' DAY
GROUP BY 1,3
ORDER BY day
In Python, this bit of code accesses the query
query = QueryBase(query_id=5800764)
Query id variable in Python is using the dune.com/queries/query_id value from the Dune link
Step 6.1: Loading the data in Python!
Great success! We’re almost there.
In the Python file on GitHub, I’ve added comments in lines using # but here’s a quick run through of what’s happening.
First, the Dune API fetched query data is being put into a df format [data frame format]. Think, df = table format for data analysis in Python.
We then view the first 5 rows of the dataset. Next, we check the datatypes for each column [As in, is it a date, number, string format etc]
df = dune.run_query_dataframe(query=query)
#show first 5 values in the dataset
print(df.head())
#check datatypes of column
print(df.dtypes)
Time for some data clean up. As the total_volume_usd was showing up in exponential format, we converted it to float format.
And also, show the avg. volume for the dataset in the console
#as total_volume_usd is showing in exponential e.g. 4.563793e+07, let's convert to integer
df['total_volume_usd'] = df['total_volume_usd'].astype('int64')
print(df.head())
avg_volume = df['total_volume_usd'].mean()
print(f"Average Daily Volume: ${avg_volume:,.2f}")
Step 6.2: Visualizing the data in Python!
Let’s take it easy for now. For our first viz in Python, we’re not gonna jump into Seaborn library for fancy visuals.
Let’s go with a basic Matplot for this tutorial.
In the below section of the code, we’re plotting a basic X / Y chart where we convert the timestamp to date format.
Next, we figure out the aspect ratio of the chart [10,5] and plot ‘day’ on X axis and ‘total_volume_usd’ on Y axis.
Added a chart title, X and Y axis labels and gave it a command to show the chart!
Voila! You just loaded your first Dune query data in Python.
Was it hard? Did you face any difficulty in repeating the steps? Lmk in comments below or on X https://x.com/blockraptor
#basic plot
df['day']=pd.to_datetime(df['day'])
print(df.dtypes)
#aspect ratio of plot
plt.figure(figsize=(10,5))
#columns to plot
plt.plot(df['day'],df['total_volume_usd'])
#plot title
plt.title('Balancer Daily Trading Volume - last 7 D')
#axis labels for x and y axis
plt.xlabel('Date')
plt.ylabel('Volume USD')
#show y axis labels in regular number, not exponential
plt.ticklabel_format(style='plain', axis='y')
plt.grid(True)
#adjust spacing between elements in case
plt.tight_layout()
#display plot
plt.show()
Link to GitHub code: https://github.com/Block-Raptor/loading-dune-api-query-in-python/blob/main/balancer_analysis.py
Dune Query feature can be accessed from https://docs.dune.com/api-reference/overview/sdks