JayDeBeApi triggers java.sql.SQLException: No suitable driver found

Question

I'm trying to connect to a Teradata database in Python 3 using the package JayDeBeApi. This is part of an API project using Flask and Flask-Restplus

Here is a minimal working example to reproduce the issue. In a terminal Window, type the following commands to setup your workstation:

# Install JVM
sudo apt-get install default-jre

# Create Python virtual environment
sudo apt-get install python3-venv
python3 -m venv jdbc
source jdbc/bin/activate

# Install Python packages in virtual environment
pip3 install --upgrade pip
pip3 install jaydebeapi
pip3 install flask

Create a file app.py with the following content:

from flask import Flask
import jaydebeapi

app = Flask(__name__)


def get_jdbc_connection():
    connection = jaydebeapi.connect(
        'com.teradata.jdbc.TeraDriver',
        'jdbc:teradata://edw-dev.company.org',
        {'user': 'LOGIN', 'password': 'PASSWORD', 'tmode': 'TERA', 'charset': 'UTF8'},
        '/home/alexis/teradataDriverJdbc.jar')
    return connection


@app.route('/hello/')
def hello_world():
    print('Init second connection')
    get_jdbc_connection()
    print('Success')
    return 'Hello world!'


if __name__ == '__main__':
    print('Init connection')
    test_connection = get_jdbc_connection()
    test_connection.close()
    print('Init connection closed')
    app.run(host='localhost', port=5000, threaded=True, debug=True)

In your terminal windows with the virtual environment activated, type the following:

python3
>>> import app
>>> app.get_jdbc_connection()
<jaydebeapi.Connection object at 0x7f28ecb2ebe0>

The snippet above works and the method get_jdbc_connection() returns a connection object. Now try running it with Flask. In a terminal window with the virtual environment activated, type the following:

python3 app.py
Init connection
Init connection closed
 * Running on http://localhost:5000/ (Press CTRL+C to quit)
 * Restarting with stat
Init connection
Init connection closed
 * Debugger is active!
 * Debugger PIN: 301-429-987

In your browser, navigate to http://localhost:5000/hello/ You will get the following error message which is the same as the one I pasted in my first message above:

Init second connection
127.0.0.1 - - [08/Mar/2018 16:00:30] "GET /hello/ HTTP/1.1" 500 -
Traceback (most recent call last):
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/alexis/dq/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/alexis/app.py", line 19, in hello_world
    get_jdbc_connection()
  File "/home/alexis/app.py", line 12, in get_jdbc_connection
    '/home/alexis/teradataDriverJdbc.jar')
  File "/home/alexis/dq/lib/python3.5/site-packages/jaydebeapi/__init__.py", line 381, in connect
    jconn = _jdbc_connect(jclassname, url, driver_args, jars, libs)
  File "/home/alexis/dq/lib/python3.5/site-packages/jaydebeapi/__init__.py", line 199, in _jdbc_connect_jpype
    return jpype.java.sql.DriverManager.getConnection(url, *dargs)
jpype._jexception.java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found for jdbc:teradata://edw-dev.company.org

Remark: I have also created an issue on the Github project: https://github.com/baztian/jaydebeapi/issues/66

Edit: Following the recommendation of baztian, I have raised the issue on the jPype Github project: https://github.com/originell/jpype/issues/290

score 7 · Accepted Answer · answered Oct 08 '18 at 13:15

A workaround that will fix your problem is the following:

   def get_jdbc_connection():
        import jpype
        if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
            jpype.attachThreadToJVM()
            jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())
        connection = jaydebeapi.connect(
            'com.teradata.jdbc.TeraDriver',
            'jdbc:teradata://edw-dev.company.org',
            {'user': 'LOGIN', 'password': 'PASSWORD', 'tmode': 'TERA', 'charset': 'UTF8'},
            '/home/alexis/teradataDriverJdbc.jar')
        return connection

This worked for me when I had to reconnect, though, it's not clear to me why. — slaw, Apr 09 '20 at 19:15

bastian · Answer 2 · 2018-03-06T16:24:55.113

2

This is probably a jpype issue. Jpype is the base of JayDeBeApi. There are some open jpype issues on github. Please make sure you are using the lates jpype version. Otherwise you have to provide a PR for jaydebeapi or hope someone fixes it for you. Also consider using Jython together with JayDeBeApi.

EDIT: Also consider using a dedicated python semaphore that encapsulates the access to your modules using JayDeBeApi.

edited Mar 06 '18 at 16:24

answered Mar 05 '18 at 20:15

bastian

1,122
11
23

I confirm that I'm using the last version of JPype1 which is 0.6.2 as of now. What do you mean by using Jython? – Alexis.Rolland Mar 06 '18 at 11:28
JayDeBeApi works with either cPython or [Jython](http://www.jython.org/). Jython is Python implemented in Java. It has perfect capabilities using Java code. That way JayDeBeApi can avoid the use of jpype which is still not 100% mature especially in the case of multithreading. AFAIK Jython does currently only implement Python 2.7. – bastian Mar 06 '18 at 16:23
I'm facing this issue, but only when running python directly - running through notebook doesn't yield such error. What can be the issue? – diman82 Nov 22 '20 at 13:15

JayDeBeApi triggers java.sql.SQLException: No suitable driver found

2 Answers2