Connecting CrateDB using JDBC driver from python

import jaydebeapi

print("LOADING.....")
conn = jaydebeapi.connect(
    "io.crate.client.jdbc.CrateDriver",
    "jdbc:crate://127.0.0.1:2000/",
    ["user", "password"],
    "/home/test/test/test/spark_demo/crate-jdbc-standalone-2.7.0.jar")

print("CONNECTED.....")
curs = conn.cursor()
curs.execute("SELECT * FROM doc.test LIMIT 5")
print("FETCHING.....")
result = curs.fetchall()
print(result)
curs.close()
conn.close()

I am trying to connect CrateDB using jdbc as part of implementing spark, but it is not establishing the connection, it is stuck after printing loading, there is no error as well, here I am unable to fetch or update something to DB.

Crate version 5.2.8

DB is running and accessible on loacl host on the given port, there is no network restrictions, when I tried on colleague's laptop, getting the same

I would be grateful if anyone can help

Advise from those who connected CrateDB using JDBC

Solution

My CrateDB service was on Kubernetes, and I was port forwarding 4200 to 2000 on my local, since Crate supports Postgres protocol, did the following,

forwarded the port 5432 on crate service to 2001 on my local using

kubectl port-forward svc/service-url 2001:5432 -n crate

Then edited the connection string on python code as follows

driver = "io.crate.client.jdbc.CrateDriver"
table = "doc.test"  
url = "jdbc:crate://127.0.0.1:2001/?user=username"
print("TABLE NAME: ", table)

#Initialize SparkSession
spark = SparkSession.builder \
    .appName("CrateDBIntegration") \
    .config("spark.jars", "/path/to/jar/file/crate-jdbc-standalone-2.7.0.jar") \
    .getOrCreate()
    
    
df = spark.read.format("jdbc").option("url", url).option("driver", driver) \
.option("dbtable", table).load()


#when using jaydebeapi, connection string used as follows

conn = jaydebeapi.connect(
    "io.crate.client.jdbc.CrateDriver",
    "jdbc:crate://127.0.0.1:2001/",
    ["user", "password"],
    "/path/to/jar file/crate-jdbc-standalone-2.7.0.jar")