sample_oracle_loader.py - sqlalchemy.exc.ObjectNotExecutableError

[csolt654]

Trying to load some data into our newly installed instance of Amundsen using the sample_oracle_loader.py, but running into an exception in the sqlalchemy library. Only thing I've changed in the code is the connection string to our database.

Expected Behavior

Program completes successfully and data is (hopefully) loaded into Amundsen

Current Behavior

Raises an exception on loading_job.launch()

Traceback

File "C:\Users...\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1410, in execute
meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users...\venv\lib\site-packages\databuilder\job\job.py", line 76, in launch
raise e
File "C:\Users...\venv\lib\site-packages\databuilder\job\job.py", line 64, in launch
self._init()
File "C:\Users...\venv\lib\site-packages\databuilder\job\job.py", line 51, in _init
self.task.init(self.conf)
File "C:\Users...\venv\lib\site-packages\databuilder\task\task.py", line 45, in init
self.extractor.init(Scoped.get_scoped_conf(conf, self.extractor.get_scope()))
File "C:\Users...\venv\lib\site-packages\databuilder\extractor\oracle_metadata_extractor.py", line 58, in init
self._alchemy_extractor.init(sql_alch_conf)
File "C:\Users...\venv\lib\site-packages\databuilder\extractor\sql_alchemy_extractor.py", line 40, in init
self._execute_query()
File "C:\Users...\venv\lib\site-packages\databuilder\extractor\sql_alchemy_extractor.py", line 65, in _execute_query
self.results = self.connection.execute(self.extract_sql)
File "C:\Users...\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1412, in execute
raise exc.ObjectNotExecutableError(statement) from err

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: '\n SELECT\n 'master' as "cluster",\n lower(c.owner) as "schema",\n lower(c.table_name) as "name",\n tc.comments as "description",\n lwer(c.column_name) as "col_name",\n lower(c.data_type) as "col_type",\n cc.comments as "col_description",\n lower(c.column_id) as "col_sort_order"\n FROM\n all_tab_columns c\n LEFT JOIN\n all_tab_comments tc ON c.owner=tc.owner AND c.table_name=tc.table_name\n LEFT JOIN\n all_col_comments cc ON c.owner=cc.owner AND c.table_name=cc.table_name AND c.column_name=cc.column_name\n \nwhere table_schema = 'public'\n\n ORDER BY "cluster", "schema", "name", "col_sort_order"\n '
python-BaseException

Screenshots

I was curious to see if this query would run outside of Python, and it doesn't - However, if you exclude the WHERE clause, it runs fine. Not sure if this is related or not.

Thanks!

[boring-cyborg]

Thanks for opening your first issue here!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

[bgreenawald-vt]

I am receiving the same issue trying to implement sample_postgres_loader.py

Traceback (most recent call last):
  File "/home/ubuntu/amundsen/databuilder/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1410, in execute
    meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/lib/python3.10/runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib/python3.10/runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "/home/ubuntu/amundsen/databuilder/custom/custom_postgres_importer.py", line 184, in <module>
    loading_job.launch()
  File "/home/ubuntu/amundsen/databuilder/databuilder/job/job.py", line 76, in launch
    raise e
  File "/home/ubuntu/amundsen/databuilder/databuilder/job/job.py", line 64, in launch
    self._init()
  File "/home/ubuntu/amundsen/databuilder/databuilder/job/job.py", line 51, in _init
    self.task.init(self.conf)
  File "/home/ubuntu/amundsen/databuilder/databuilder/task/task.py", line 45, in init
    self.extractor.init(Scoped.get_scoped_conf(conf, self.extractor.get_scope()))
  File "/home/ubuntu/amundsen/databuilder/databuilder/extractor/base_postgres_metadata_extractor.py", line 68, in init
    self._alchemy_extractor.init(sql_alch_conf)
  File "/home/ubuntu/amundsen/databuilder/databuilder/extractor/sql_alchemy_extractor.py", line 40, in init
    self._execute_query()
  File "/home/ubuntu/amundsen/databuilder/databuilder/extractor/sql_alchemy_extractor.py", line 65, in _execute_query
    self.results = self.connection.execute(self.extract_sql)
  File "/home/ubuntu/amundsen/databuilder/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object:

[bgreenawald-vt]

It looks like this is related to some changes in SqlAlchemy 2.0. I was able to resolve the issue by pinning the version of SqlAlchemy to a prior version in the requirements.txt file

sqlalchemy==1.4.47

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

[stale]

This issue has been automatically closed for inactivity. If you still wish to make these changes, please open a new pull request or reopen this one.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.