Authentication to mssql database using Python node does not work

[ksrai]

I have an mssql database, the data from which has to be processed in a certain way. For that, I have own python packages that does the authentication and does all the required processing (there are multiple functions) on the data to give the output. For my overall workflow, I want to use these functions, without having to create a redundant workflow that does the same functions as the python packages. So I created a Conda environment with python 3.10 and installed these packages into them.

When I test the environment with VS code, it works perfectly, i.e. it can access the data and do the processing as needed. However, the same does not work in Knime.
Here are the things I did:

• I am using the Python legacy node
• I set the preferences to use the new Conda environment that I created (both in legacy and scripting)

This is the error that I get:

type object ‘java.sql.Types’ has no attribute ‘javaclass’
Traceback (most recent call last):
File “”, line 8, in
File “C:\Users\me\AppData\Local\miniforge3\envs\knime_python_venv\lib\site-packages\my_package_1\db.py”, line 21, in init
self.db_conn = self._conn(path_to_jar, path_to_auth_dll)
File “C:\Users\me\AppData\Local\miniforge3\envs\knime_python_venv\lib\site-packages\my_package_1\db.py”, line 25, in _conn
return jaydebeapi.connect(self.conf.jdbc_class, self.conf.jdbc_url, self.params, path_to_jar, path_to_auth_dll)
File “C:\Program Files\Knime AP 5.2.0\plugins\org.knime.python2_5.2.0.v202311211447\py\jaydebeapi.py”, line 384, in connect
jconn = _jdbc_connect(jclassname, url, driver_args, jars, libs)
File “C:\Program Files\Knime AP 5.2.0\plugins\org.knime.python2_5.2.0.v202311211447\py\jaydebeapi.py”, line 186, in _jdbc_connect_jpype
for i in types.javaclass.getClassFields():
AttributeError: type object ‘java.sql.Types’ has no attribute ‘javaclass’. Did you mean: ‘class’?

I dug a bit deeper as it seems like the problem lies with the Jaydebeapi.py which apparently is responsible for the authentication. I used the following script in the Knime python node to check:

import imp

#location_check default
print("default loaction is: ", imp.find_module(‘pandas’))

#location of jaydebeapi
print(imp.find_module(‘JayDeBeApi’))
print(imp.find_module(‘JPype’))

The output is:

default loaction is: (None, ‘C:\Users\me\AppData\Local\miniforge3\envs\knime_python_venv\lib\site-packages\pandas’, (‘’, ‘’, 5))

(<_io.TextIOWrapper name=‘C:\Program Files\Knime AP 5.2.0\plugins\org.knime.python2_5.2.0.v202311211447\py\JayDeBeApi.py’ mode=‘r’ encoding=‘utf-8’>, ‘C:\Program Files\Knime AP 5.2.0\plugins\org.knime.python2_5.2.0.v202311211447\py\JayDeBeApi.py’, (‘.py’, ‘r’, 1))

(None, ‘C:\Users\me\AppData\Local\miniforge3\envs\knime_python_venv\lib\site-packages\JPype’, (‘’, ‘’, 5))

Fo some reason, while pandas and jpype are correctly pointed, for the Jaydebeapi.py it does not go into my python environment, but into a default one from Knime. And that too, it seems like in Python 2 (from the folder name).

I checked everywhere in the preferences, Python 2 has not been set anywhere.

What could be the problem here? Is there some setting that I am missing?

[steffen_KNIME]

Hi @ksrai,

what about the current Python node?
There might be a version conflict in the legacy integration resulting in the error you see.

If you format code, it becomes more readable:

Thanks
Steffen

[ksrai]

Hi Steffen,

thanks for the tip! I can’t seem to find how to re-edit the original issue, but here is the python script I am using:

import datetime
import pandas as pd
from my_package_1.sql import SQL

sql = SQL()

sql_string = f"""

my_sql_string

"""
result = sql.execute(sql=sql_string)

This is just one of the many functions of the package. The exact same script works when I use the knime_python_environment in the VS code. But it does not work with the same environment in the python node in knime. That is when it throws the errors as I posted above.

Thanks!

[steffen_KNIME]

Hi @ksrai,

thanks, please try the current Python Script node: KNIME Python Integration Guide

Maybe that helps already?

Best regards
Steffen

[ksrai]

Thanks, Steffen!

I had checked everything in the Python integration guide before, the thing is, I cannot install the standard conda install directly within the organization, as it is blocked. So I had to build it all up by downloading individual packages.

I finally came across this post a few minutes ago, and though it is not directly related to my issue, it worked perfectly!:

Execution failed: Could not connect to the Python process

If you are setting up a manual environment… To test this out, I installed python 3.9 into a folder as follows: c:\apps\python39 [image] and then made sure that python 3.9 was on the path: [image] In KNIME I set my python folder manually: [image] It complained about numpy and pandas so in a command prompt I executed the following: pip install numpy pip install pandas [image] I created a new workflow and on it placed an Empty Table Creator and a Python Script node. But executing t…

I had to install some more Python packages mentioned and everything fell in place!

Thanks again for your support.

[steffen_KNIME]

Thanks for sharing @ksrai !