Simple python example to do a Lenses SQL query via API with Lenses 6

Lenses
1

I’m using Lenses Community Edition. Can someone give me a basic python script that runs an SQL query to fetch some data using the new API in Lenses 6.0

2

Lenses uses websockets to query data.

Here’s a basic example (I’m using Lenses Community Edition over unsecured protocol, change to wss instead of ws as necessary)

import os, websockets, json, asyncio

import ssl
#Optional if using self-signed cert with the websocket:
# ssl_context = ssl._create_unverified_context()

async def execute_SQL(environmentName: str, sql: str) -> dict:

    #FOR Secure Websocket use:
    #  url = str("wss://") + os.getenv("HQ_HOST") + "/api/v1/environments/" + environmentName + "/proxy/api/ws/v2/sql/execute"
    url = str("ws://") + os.getenv("HQ_HOST") + "/api/v1/environments/" + environmentName + "/proxy/api/ws/v2/sql/execute"
    headers = {"Authorization": "Bearer " + os.getenv('HQ_TOKEN')}

    data = []
    try:
        request = {
            "sql": sql,
            "live": False,
            "stats": 2
        }

        #FOR WSS use:
        #async with websockets.connect(url, ssl=ssl_context, additional_headers=headers) as websocket:
        async with websockets.connect(url, additional_headers=headers) as websocket:
            await websocket.send(json.dumps(request))
            async for message in websocket:
                response = json.loads(message)
                if response.get('type') == 'RECORD':
                    data.append(response)

    except websockets.exceptions.WebSocketException as e:

        print(f"An error occurred: {e}")
        return None
    print(data)

asyncio.run(execute_SQL("demo", "SELECT * FROM input LIMIT 100;"))

Ensure you set the environment variables (and create a Service Account token in the Lenses HQ)

EXPORT HQ_TOKEN=sa_key_Vypm1D5oLRH9CPiy_T2WxMkFhbVxeDr1fG6QNMtfhHar6aliE
EXPORT HQ_HOST=localhost:9991