CODEWITHSUNDEEP
pythonoracle-databasesqlalchemyairflow
V. Benoit
V. Benoit

Reputation: 13

Airflow "This connection is closed" in run but not in test

I am using airflow 1.7.1.3 and python 2.7

I created a DAG that works perfectly when i run each task separately using

airflow test [myDAG] [myTask] 2016-10-14

However,

airflow trigger_dag [myDAG]

or

airflow run [myDAG] [myTask] 2016-10-14

both raise a "This connection is closed" SQLalchemy error.

[...]
    with self.engine.connect() as connection:
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2016, in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 72, in __init__
    if connection is not None else engine.raw_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2102, in raw_connection
    self.pool.unique_connection, _connection)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2072, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 318, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 746, in _checkout
    raise exc.InvalidRequestError("This connection is closed")
InvalidRequestError: This connection is closed
[2016-10-14 15:49:30,704] {models.py:1306} INFO - Marking task as FAILED.
[2016-10-14 15:49:30,712] {models.py:1327} ERROR - This connection is closed

This is a connection to an Oracle 12 database through SQLalchemy, and this error is raised when i use session.commit() in my script.

Does someone have any clue of what could explain this difference and error?

Upvotes: 1

Views: 1837

Answers (1)

SeekingAlpha
SeekingAlpha

Reputation: 7817

This is a known bug as outlined here. You will not be able to connect to Oracle via SQLAlchemy until this bug is fixed.

The issue is due to some SQL syntax in the source code. You can't say "SELECT 1" in Oracle, you need to say "SELECT 1 FROM DUAL".

Maybe look into using one of the hooks in Airflow: https://github.com/apache/incubator-airflow/tree/master/airflow/hooks

There appears to be an oracle_hook that may help you out. Good luck.

Upvotes: 1

Related Questions