python database jdbc connectors using jaydebeapi

jdbc_connectors.py

from typing import Dict, List
import pandas as pd
import logging
import jaydebeapi

logger = logging.getLogger()

def execute_query_jdbc(connection: Dict[str, str], query: str) -> List:
    """ Method for executing any query using jdbc. 
       Args:
            connection (Dict[str, str]): connection details (jdbc driver: "org.postgresql.Driver", jar filepath: "/XXX/jars/postgresql-42.2.5.jar", jdbc url, usr, passw )
            query (str): query to execute, e.g. insert into db.tbl values ('abc', 123);

        Returns:
           List: result as List - jaydebeapi fails when calling cur.fetch method for other queries then select (result_set is None). Because of that we return row_count for other queries.
    """
    try:
        logger.info(f"execute_query_jdbc(driver: {connection['driver']}, url: {connection['url']}, jar: {connection['jar']}, query: {query})")
        with jaydebeapi.connect(connection['driver'], connection['url'], [connection['username'], connection['password']], connection['jar']) as conn:
            cur = conn.cursor()
            cur.execute(query)
            if not cur.__dict__.get('_rs'):
                # In case of other queries then "select" return [row_count] - result_set is always None
                return [cur.rowcount]
            else:
                # in case of "select" return result
                return cur.fetchall()
    except jaydebeapi.DatabaseError as dberr:
        logger.exception("Invalid query")
    except jaydebeapi.Error as oerr:
        logger.exception("Other error occurred")


def jdbc_to_pandas(connection: Dict[str, str], query: str) -> pd.DataFrame:
    """ Method for fetching data from jdbc to pandas. 
       Args:
            connection (Dict[str, str]): connection details (jdbc driver, jar filepath, jdbc url, usr, passw)
            query (str): select query

        Returns:
           pd.DataFrame: result as Pandas DF
    """
    try:
        logger.info(f"jdbc_to_pandas(driver: {connection['driver']}, url: {connection['url']}, jar: {connection['jar']}, query: {query})")
        with jaydebeapi.connect(connection['driver'], connection['url'], [connection['username'], connection['password']], connection['jar']) as conn:
            df = pd.read_sql(query, conn)
            return df
    except Exception as ex:
        logger.exception("Exception occurred")

I am trying to connect the database MS SQL Server via kerberos connection with jaydebeapi..Its passing in windows but failing in linux environment. Any idea if I need to set JAVA home or any other java path

Yes, you have to set java home before running the script (export JAVA_HOME)