QuestDB ODBC ILP

odbQuest.py

# configuring ODBC https://solutions.posit.co/connections/db/best-practices/drivers/
# using ODBC from python https://github.com/mkleehammer/pyodbc/wiki/Getting-started
import pyodbc

con = pyodbc.connect(
  driver = 'PostgreSQL Driver',
  database = 'qdb',
  server = 'localhost',
  port = 8812,
  uid = 'admin',
  pwd = 'quest'
)

cursor = con.cursor()
cursor.execute("select * from ilp_test limit 5")

while True:
    row = cursor.fetchone()
    if not row:
        break
    print(row)

packages.sh

pip install questdb
pip install "psycopg[binary]"
pip install --no-binary :all: pyodbc --force

simple_pg_query.pg

import psycopg as pg
import time

conn_str = 'user=admin password=quest host=127.0.0.1 port=8812 dbname=qdb'
with pg.connect(conn_str) as connection:
    with connection.cursor() as cur:
        cur.execute('SELECT * FROM ilp_test limit 5;')
        records = cur.fetchall()
        for row in records:
            print(row)

tsbs_send.py

from questdb.ingress import Sender, IngressError, TimestampNanos
import os
import sys
import random
import time

HOST = 'localhost'
PORT = 9009
DEVICE_TYPES = ["blue", "red", "green", "yellow"]
ITER = 100
BATCH = 10
DELAY = 1
MIN_LAT = 19.50139
MAX_LAT = 64.85694
MIN_LON = -161.75583
MAX_LON = -68.01197


def send():
    try:
        # auth = (os.environ['QDB_KID'], os.environ['QDB_D'], os.environ['QDB_X'], os.environ['QDB_Y'])
        # with Sender(HOST, PORT, auth=auth, tls=True) as sender:
        with Sender(HOST, PORT) as sender:
            for it in range(ITER):
                for i in range(BATCH):
                    sender.row(
                        'ilp_test',
                        symbols={'device_type': random.choice(DEVICE_TYPES)},
                        columns={
                                    'duration_ms': random.randint(0, 4000),
                                    "lat": random.uniform(MIN_LAT, MAX_LAT),
                                    "lon": random.uniform(MIN_LON, MAX_LON),
                                    "measure1": random.randint(-2147483648, 2147483647),
                                    "measure2": random.randint(-2147483648, 2147483647),
                                    "speed": random.randint(0, 100)
                        },
                        at=TimestampNanos.now())
                sender.flush()
                time.sleep(DELAY)
    except IngressError as e:
        sys.stderr.write(f'Got error: {e}')


if __name__ == '__main__':
    send()