Hi,
I am using sqlalchemy 1.0.5, Airflow 1.7.1.3, Python 2.7 and Oracle 12.
I'm pretty much stuck with the integration of a connexion to Oracle through
sqlalchemy in an Airflow Airbnb script.
Here is my log from Airflow/sqlalchemy.
[2016-10-26 14:51:07,574] {base.py:719} INFO - COMMIT
[2016-10-26 14:51:07,631] {log.py:109} INFO - SELECT USER FROM DUAL
[2016-10-26 14:51:07,631] {log.py:109} INFO - {}
[2016-10-26 14:51:07,633] {log.py:109} INFO - SELECT CAST('test plain
returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
[2016-10-26 14:51:07,633] {log.py:109} INFO - {}
[2016-10-26 14:51:07,634] {log.py:109} INFO - SELECT CAST('test unicode
returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
[2016-10-26 14:51:07,634] {log.py:109} INFO - {}
[2016-10-26 14:51:07,638] {log.py:109} INFO - Disconnection detected on
checkout:
[2016-10-26 14:51:07,638] {log.py:109} INFO - Invalidate connection
<cx_Oracle.Connection to
[MyDataBaseName]@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[MyOracleServerIP])(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=I2B2)))>
(reason: DisconnectionError:)
[2016-10-26 14:51:07,678] {log.py:109} INFO - Disconnection detected on
checkout:
[2016-10-26 14:51:07,678] {log.py:109} INFO - Invalidate connection
<cx_Oracle.Connection to
[MyDataBaseName]@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[MyOracleServerIP])(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=I2B2)))>
(reason: DisconnectionError:)
[2016-10-26 14:51:07,714] {log.py:109} INFO - Reconnection attempts
exhausted on checkout
[2016-10-26 14:51:07,715] {log.py:109} INFO - Invalidate connection
<cx_Oracle.Connection to
[MyDataBaseName]@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[MyOracleServerIP])(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=I2B2)))>
[2016-10-26 14:51:07,716] {models.py:1286} ERROR - This connection is closed
Traceback (most recent call last):
File "/usr/local/lib/python2.7/dist-packages/airflow/models.py", line
1245, in run
result = task_copy.execute(context=context)
File
"/usr/local/lib/python2.7/dist-packages/airflow/operators/python_operator.py",
line 66, in execute
return_value = self.python_callable(*self.op_args, **self.op_kwargs)
File "/root/airflow/dags/debug_py2b2_connect.py", line 41, in test_connect
connection=i2b2data.engine.connect()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
line 2085, in connect
return self._connection_cls(self, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
line 90, in __init__
if connection is not None else engine.raw_connection()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
line 2171, in raw_connection
self.pool.unique_connection, _connection)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
line 2141, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line
328, in unique_connection
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line
804, in _checkout
raise exc.InvalidRequestError("This connection is closed")
InvalidRequestError: This connection is closed
[2016-10-26 14:51:07,718] {models.py:1306} INFO - Marking task as FAILED.
Here is my sql alchemy __init__code in my python class
def __init__(self,connection_uri,params):
if connection_uri.startswith("oracle"):
os.environ['NLS_LANG']= 'AMERICAN_AMERICA.AL32UTF8'
self.engine=sqlalchemy.create_engine(connection_uri, **params)
Session = sessionmaker(bind=self.engine, autoflush=True, autocommit=False)
self.session = Session()
self.metadata = MetaData()
self.metadata.bind=self.engine
This error "This connection is closed" is *not *raised when in airflow
"test" mode. I can connect and update my database and my python script
works perfectly.
However in airflow "run" mode it fails.
I also have this oracle warning,
<msg time='2016-10-17T11:52:47.755+02:00' org_id='xxxxxx' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='xxxxxxx' host_addr='xxxxxx'
module='python@xxxxxxx' pid='8978'> <txt>Using deprecated
SQLNET.ALLOWED_LOGON_VERSION parameter. </txt> </msg>
But it seems unrelated as i get this warning when i execute my script in
"test" (success) and "run" (failure) mode. We are planning to fix this soon.
I have no clue if this error is related to airflow, oracle or sqlalchemy
configuration.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
