Skip to content

Instantly share code, notes, and snippets.

@mbeale
Created May 6, 2020 10:48
Show Gist options
  • Save mbeale/41f0593492e2d28674675a00dd7d73cc to your computer and use it in GitHub Desktop.
Save mbeale/41f0593492e2d28674675a00dd7d73cc to your computer and use it in GitHub Desktop.
Read a QuestDB query into a Pandas DF
import pandas as pd
import requests
def read_questdb(query, host, per_page = 10000, max_records = None):
"""
Sends a SQL query to a specified host.
Parameters
----------------
query : string
A SQL query
host : string
A host location in the form of a URL. http://host:port
per_page : int
The limit of results to return per page.
max_records : int
The maximum amount of records to return (soft limit).
"""
data = []
offset = 1
page = 1
get_more = True
columns = None
params = {"query": query, "limit": f"{offset},{per_page * page}","count": "true"}
while get_more:
r = requests.get(f"{host}exec", params=params)
jsond = r.json()
if r.status_code != 200:
raise ValueError(jsond['error'])
if not columns:
columns = [x['name'] for x in jsond['columns']]
if jsond['dataset'] != []:
data = data + jsond['dataset']
if jsond["count"] < (per_page * page) or (max_records != None and (per_page*page) >= max_records):
get_more = False
else:
offset = (per_page * page) + 1
page += 1
params['limit'] = f"{offset},{per_page * page}"
params['nm'] = 'true'
return pd.DataFrame(data=data, columns=columns)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment