I'm currently able to connect to the denodo db utilizing a denodo driver that I compiled and use with pyodbc and I can also connect and query the table if I directly use psycopg2. However, it would be ideal if I can utilize Sqlalchemy's ORM. The driver is based on postgresql, but currently sqlalchemy does not support that combination of dialect and DBAPI. This led me to trying the dialect mysql+pyodbc pointing to the driver I compilied as well as trying psycopg2.
I should mention my intention is to perform SELECT statements against only one table within the database.
With mysql+pyodbc I get the error:
qlalchemy.engine.base.Engine.DENODO-READER b"SHOW VARIABLES LIKE 'character_set%%'"
2020-06-21 02:56:52,972 INFO sqlalchemy.engine.base.Engine.DENODO-READER ()
[2020-06-21 02:56:52 +0000] [8] [ERROR] Exception in worker process
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/gunicorn/arbiter.py", line 583, in spawn_worker
worker.init_process()
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/base.py", line 119, in init_process
self.load_wsgi()
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/base.py", line 144, in load_wsgi
self.wsgi = self.app.wsgi()
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/base.py", line 67, in wsgi
self.callable = self.load()
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/wsgiapp.py", line 49, in load
return self.load_wsgiapp()
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/wsgiapp.py", line 39, in load_wsgiapp
return util.import_app(self.app_uri)
File "/usr/local/lib/python3.7/site-packages/gunicorn/util.py", line 358, in import_app
mod = importlib.import_module(module)
File "/usr/lib64/python3.7/importlib/__init__.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "<frozen importlib._bootstrap>", line 1006, in _gcd_import
File "<frozen importlib._bootstrap>", line 983, in _find_and_load
File "<frozen importlib._bootstrap>", line 967, in _find_and_load_unlocked
File "<frozen importlib._bootstrap>", line 677, in _load_unlocked
File "<frozen importlib._bootstrap_external>", line 728, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/home/decomm_reader/wsgi.py", line 5, in <module>
APP = create_app()
File "/home/decomm_reader/app/__init__.py", line 33, in create_app
DB.metadata.reflect(bind=DB.engine)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4356, in reflect
with bind.connect() as conn:
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2272, in connect
return self._connection_cls(self, **kwargs)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 104, in __init__
else engine.raw_connection()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2379, in raw_connection
self.pool.unique_connection, _connection
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2345, in _wrap_pool_connect
return fn()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 304, in unique_connection
return _ConnectionFairy._checkout(self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
rec = pool._do_get()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
self._dec_overflow()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
return self._create_connection()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
).exec_once_unless_exception(self.connection, self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
self._exec_once_impl(True, *args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
self(*args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
fn(*args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 1513, in go
return once_fn(*arg, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 199, in first_connect
dialect.initialize(c)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2472, in initialize
self._connection_charset = self._detect_charset(connection)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/mysql/pyodbc.py", line 89, in _detect_charset
rs = connection.execute("SHOW VARIABLES LIKE 'character_set%%'")
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 1012, in execute
return self._execute_text(object_, multiparams, params)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 1187, in _execute_text
parameters,
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 1324, in _execute_context
e, statement, parameters, cursor, context
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 1521, in _handle_dbapi_exception
util.raise_(exc_info[1], with_traceback=exc_info[2])
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
cursor.execute(statement, parameters)
TypeError: The first argument to execute must be a string or unicode query.
With postgres+psycopg2 I get the following exception:
[2020-06-21 03:03:01 +0000] [8] [ERROR] Exception in worker process
Traceback (most recent call last):
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2345, in _wrap_pool_connect
return fn()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 304, in unique_connection
return _ConnectionFairy._checkout(self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
rec = pool._do_get()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
self._dec_overflow()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
return self._create_connection()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
).exec_once_unless_exception(self.connection, self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
self._exec_once_impl(True, *args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
self(*args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
fn(*args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 189, in on_connect
do_on_connect(conn)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 865, in on_connect
fn(conn)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 832, in on_connect
hstore_oids = self._hstore_oids(conn)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 904, in oneshot
result = fn(self, *args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 922, in _hstore_oids
oids = extras.HstoreAdapter.get_oids(conn)
File "/usr/local/lib64/python3.7/site-packages/psycopg2/extras.py", line 929, in get_oids
""" % typarray)
psycopg2.DatabaseError: View 'pg_type' not found
DETAIL: java.sql.SQLException: View 'pg_type' not found
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/gunicorn/arbiter.py", line 583, in spawn_worker
worker.init_process()
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/base.py", line 119, in init_process
self.load_wsgi()
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/base.py", line 144, in load_wsgi
self.wsgi = self.app.wsgi()
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/base.py", line 67, in wsgi
self.callable = self.load()
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/wsgiapp.py", line 49, in load
return self.load_wsgiapp()
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/wsgiapp.py", line 39, in load_wsgiapp
return util.import_app(self.app_uri)
File "/usr/local/lib/python3.7/site-packages/gunicorn/util.py", line 358, in import_app
mod = importlib.import_module(module)
File "/usr/lib64/python3.7/importlib/__init__.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "<frozen importlib._bootstrap>", line 1006, in _gcd_import
File "<frozen importlib._bootstrap>", line 983, in _find_and_load
File "<frozen importlib._bootstrap>", line 967, in _find_and_load_unlocked
File "<frozen importlib._bootstrap>", line 677, in _load_unlocked
File "<frozen importlib._bootstrap_external>", line 728, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/home/decomm_reader/wsgi.py", line 5, in <module>
APP = create_app()
File "/home/decomm_reader/app/__init__.py", line 33, in create_app
DB.metadata.reflect(bind=DB.engine)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4356, in reflect
with bind.connect() as conn:
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2272, in connect
return self._connection_cls(self, **kwargs)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 104, in __init__
else engine.raw_connection()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2379, in raw_connection
self.pool.unique_connection, _connection
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2349, in _wrap_pool_connect
e, dialect, self
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 1591, in _handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/base.py", line 2345, in _wrap_pool_connect
return fn()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 304, in unique_connection
return _ConnectionFairy._checkout(self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
rec = pool._do_get()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
self._dec_overflow()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
return self._create_connection()
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
).exec_once_unless_exception(self.connection, self)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
self._exec_once_impl(True, *args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
self(*args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
fn(*args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 189, in on_connect
do_on_connect(conn)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 865, in on_connect
fn(conn)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 832, in on_connect
hstore_oids = self._hstore_oids(conn)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 904, in oneshot
result = fn(self, *args, **kw)
File "/usr/local/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 922, in _hstore_oids
oids = extras.HstoreAdapter.get_oids(conn)
File "/usr/local/lib64/python3.7/site-packages/psycopg2/extras.py", line 929, in get_oids
""" % typarray)
sqlalchemy.exc.DatabaseError: (psycopg2.DatabaseError) View 'pg_type' not found
DETAIL: java.sql.SQLException: View 'pg_type' not found
(Background on this error at: http://sqlalche.me/e/4xp6)
From the documentation: “SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database, and includes support for the most popular databases.”
PostgreSQL MySQL SQLite Oracle Microsoft SQL Server Firebird Sybase There are a number of additional externally supported drivers such as DB2, Snowflake, and redshift. Connecting to Denodo with SQLAlchemy would require developing and maintaining a new dialect. As far as I can tell, there is no support for any jdbc driver.