This guide explains how to connect Python application and scripts to Kamu.

SQL API: Using ADBC

Coming soon!

SQL API: Using SQLAlchemy and FlightSQL

SQLAlchemy is a popular ORM library for Python that you can use to access data in Kamu in a same way as dozens of other data sources. It is row-oriented, so may be less efficient than using ADBC client for certain use cases. To use it you’ll need to install flightsql-dbapi package.

Example:

import flightsql.sqlalchemy
import sqlalchemy.engine
import pandas as pd

engine = sqlalchemy.engine.create_engine("datafusion+flightsql://kamu:kamu@localhost:50050?insecure=True")

df = pd.read_sql("show tables", engine)
print(df)

df = pd.read_sql("select * from 'co.alphavantage.tickers.daily.spy' limit 10", engine)
print(df)

SQL API: Using DBAPI2 and FlightSQL

DBAPI2 (aka PEP-249) is a standard DB access API in Python, but its row-oriented access to data may be less efficient than using ADBC client. To use it you’ll need to install flightsql-dbapi package.

Example:

from flightsql import connect, FlightSQLClient

client = FlightSQLClient(host='localhost', port=50050, user='kamu', password='kamu', insecure=True)
conn = connect(client)
cursor = conn.cursor()
cursor.execute('select * from "co.alphavantage.tickers.daily.spy" limit 10')
print([r for r in cursor])

SQL API: Using JPype DBAPI2 and JDBC

This method involves running Java process under the hood and in most cases is NOT recommended - prefer using more efficient connectors listed above.

You can connect to Kamu from Python via JDBC protocol using Jpype library.

Example:

import jpype
import jpype.dbapi2
import os

jpype.startJVM(
    "--add-opens=java.base/java.nio=ALL-UNNAMED",
    # Include JDBC connector JAR on the class path
    classpath=os.path.join(os.getcwd(), "./flight-sql-jdbc-driver-13.0.0.jar")
)

conn = jpype.dbapi2.connect(
    "jdbc:arrow-flight-sql://127.0.0.1:50050?useEncryption=false", 
    driver_args={
        'user': 'kamu', 
        'password': 'kamu',
    }
)

cursor = conn.cursor()
res = cursor.execute("show tables").fetchall()

print(res)

cursor.close()
conn.close()