Python example on using the database

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

Required Python modules

  • aiochclient (to talk to the database)
  • matplotlib (to plot)
  • pandas (for easy data conversion)
In [1]:
from aiochclient import ChClient
from aiohttp import ClientSession

# create connection to wsprlive clickhouse server
s = ClientSession()
client = ChClient(s, url="")

# check connection state
if not await client.is_alive():
    print("database conneciton failed!")
    print("database ready")
database ready
In [2]:
# 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
In [3]:
import pandas as pd

# convert list or rows to table
dd = pd.DataFrame(all_rows)
In [5]:
import matplotlib.pyplot as plt

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