Python example on using the wspr.live database

This example shows how to use the aiochclient python module to query wspr spot data from wspr.live.

Required Python modules

• aiochclient (to talk to the database)
• matplotlib (to plot)
• pandas (for easy data conversion)

from aiochclient import ChClient
from aiohttp import ClientSession

# create connection to wsprlive clickhouse server
s = ClientSession()
client = ChClient(s, url="https://db1.wspr.live")

# check connection state
if not await client.is_alive():
    print("database conneciton failed!")
else:
    print("database ready")

database ready

# fetch data
all_rows = await client.fetch("SELECT anyLast(time) as t, count(*) as spots "\
                              "FROM wspr.rx "\
                              "WHERE time > '2010-01-01' "\
                              "GROUP BY toYYYYMM(time) ORDER BY t")
# print result info
print("got %i lines back"%len(all_rows))

got 136 lines back

import pandas as pd

# convert list or rows to table
dd = pd.DataFrame(all_rows)

import matplotlib.pyplot as plt

# plot data using matplotlib
plt.plot("t", "spots", data=dd);