Thanks for this answer.
As you suggested i tried to connect, with success, to my database through
cx_oracle.
ip = '[MyOracleServerIP]'
port = 1521
service_name = '[MyServiceName]'
dsn = cx_Oracle.makedsn(ip, port, service_name=service_name)
db = cx_Oracle.connect('[login]', '[password]', dsn)
print db.version
db.close()
Output : 12.1.0.2.0
Functional both in "test" and "run".
Le mercredi 26 octobre 2016 15:45:59 UTC+2, Mike Bayer a écrit :
>
> This is connectivity issues, I have no idea what airflow is, however if
> you're dropping connections this would be something to email the cx_oracle
> list about . The error message looks like you're not able to establish a
> connection in the first place. You might want to create a plain cx_oracle
> test script at least to make the options and the error clear.
>
> On Oct 26, 2016 9:22 AM, "Vincent B." <mr.benoi...@gmail.com <javascript:>>
> wrote:
>
>> 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+...@googlegroups.com <javascript:>.
>> To post to this group, send email to sqlal...@googlegroups.com
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
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.
