You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2022/07/13 14:49:30 UTC
[GitHub] [airflow] notniknot opened a new issue, #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
notniknot opened a new issue, #25024:
URL: https://github.com/apache/airflow/issues/25024
### Apache Airflow version
2.3.3 (latest released)
### What happened
I create a DAG that accesses an Oracle database via sqlalchemy. Therefore I call `get_sqlalchemy_engine()` on the OracleHook-Object. I get a `ORA-12154: TNS:could not resolve the connect identifier specified` error message. Details and reason stated below.
### What you think should happen instead
The Connection-URI in `get_sqlalchemy_engine` should be returned unquoted.
### How to reproduce
I am using `apache/airflow:2.3.3-python3.9` with `apache-airflow-providers-oracle==2.2.3` to connect to Oracle databases.
Therefore I add an Oracle connection with a dsn in the host field, such as:
```
(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=1521)))(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=XXX)))
```
I am retrieving the connection in the DAG as follows:
```python
oracle = OracleHook(oracle_conn_id='XXX')
engine = oracle.get_sqlalchemy_engine(engine_kwargs={'max_identifier_length': 128})
```
Unfortunately, the Connection-URI is returned quoted, such as:
```
'%28DESCRIPTION%3D%28ADDRESS_LIST%3D%28LOAD_BALANCE%3Don%29%28ADDRESS%3D%28PROTOCOL%3DTCP%29%28HOST%3DXXX%29%28PORT%3D1521%29%29%29%28ADDRESS_LIST%3D%28LOAD_BALANCE%3Don%29%28ADDRESS%3D%28PROTOCOL%3DTCP%29%28HOST%3DXXX%29%28PORT%3D1521%29%29%29%28CONNECT_DATA%3D%28SERVICE_NAME%3DXXX%29%29%29'
```
which leads to the error message: `ORA-12154: TNS:could not resolve the connect identifier specified`
I had to manually unqote the string to fix the issue.
### Operating System
Debian GNU/Linux 11 (bullseye)
### Versions of Apache Airflow Providers
apache-airflow-providers-oracle==2.2.3 with cx_Oracle-8.3.0
### Deployment
Docker-Compose
### Deployment details
Custom Dockerfile for Airflow-Containers (Deployment via podman-compose)
```Dockerfile
FROM apache/airflow:2.3.3-python3.9
USER root
# Oracle client
WORKDIR /opt/oracle
RUN apt-get update \
&& apt-get install -y --no-install-recommends libaio1 wget unzip \
&& wget https://download.oracle.com/otn_software/linux/instantclient/216000/instantclient-basiclite-linux.x64-21.6.0.0.0dbru.zip \
&& unzip instantclient-basiclite-linux.x64-21.6.0.0.0dbru.zip \
&& rm -f instantclient-basiclite-linux.x64-21.6.0.0.0dbru.zip \
&& cd /opt/oracle/instantclient* \
&& rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
&& echo /opt/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
&& ldconfig
# Builds
RUN apt-get install -y --no-install-recommends \
g++ unixodbc-dev \
&& apt-get autoremove -yqq --purge \
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*
USER airflow
WORKDIR /opt/airflow
RUN python -m pip install --upgrade pip \
&& pip install --no-cache-dir --user apache-airflow-providers-oracle==2.2.3 apache-airflow-providers-docker~=2.2 podman~=4.0.0 scikit-learn==1.1.1 openpyxl==3.0.10
```
### Anything else
Another thing I encountered: apache-airflow-providers-oracle uses oracledb in the newest version. But sqlalchemy does not yet support oracledb. So a call to `get_sqlalchemy_engine()` leads to an error.
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] sbadithe commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
sbadithe commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1185172985
May I pick this up?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Eric-Mendes commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
Eric-Mendes commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1189474979
Would appending a simple `.strip("'")` [here](https://github.com/apache/airflow/blob/main/airflow/providers/common/sql/hooks/sql.py#L129) work?
I don't know how to test this solution, that's the only reason I won't make a commit.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] eladkal commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by "eladkal (via GitHub)" <gi...@apache.org>.
eladkal commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1689690237
@boushphong would you still want to fix this issue?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] shubham0473 commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
shubham0473 commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1248289481
Can I work on this?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] boushphong commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by "boushphong (via GitHub)" <gi...@apache.org>.
boushphong commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1439413393
@shubham0473 You're still working on this? you mind if I take-over this issue?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] notniknot commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
notniknot commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1185251650
Sure, go ahead
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1248300686
Feel Free
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] boring-cyborg[bot] commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
boring-cyborg[bot] commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1183322083
Thanks for opening your first issue here! Be sure to follow the issue template!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] boushphong commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by "boushphong (via GitHub)" <gi...@apache.org>.
boushphong commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1694399627
@eladkal
I tried to reproduce this again a while ago with some recent releases ago of `apache-airflow-providers-oracle`.
I believe this has been already fixed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1694405476
Closing then. We can alwyas reopen if needed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] sbadithe commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
sbadithe commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1186274286
Actually, I'm sorry I don't think I can work on this at the moment. Sorry for the confusion.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1183412820
Feel free to make a fix. This should be easy. otherwise I mark it as good first issue, so maybe someone will pick it up. Re sqlalchemy not supporting Oracle - there is nothing we can do - but if you feel like it maybe you can add PR with information about it or even throw NoitImplemented for get_sqlalchemy_engine.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] notniknot commented on issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by GitBox <gi...@apache.org>.
notniknot commented on issue #25024:
URL: https://github.com/apache/airflow/issues/25024#issuecomment-1190058351
I think there is a misunderstanding. By "quoted" I refer to the definition of replacing or escaping special characters in a string with `%xx`. Therefore, Airflow internally calls [parse ](https://docs.python.org/3/library/urllib.parse.html#urllib.parse.quote) or [parse_plus ](https://docs.python.org/3/library/urllib.parse.html#urllib.parse.quote_plus) of the urllib-package.
I think one option could be calling `urllib.parse.unquote` in [get_uri()](https://github.com/apache/airflow/blob/8923e3ab0304caafd011253b52e2d644066f58fc/airflow/providers/common/sql/hooks/sql.py#L110)
It could be possible that other methods in `DbApiHook`, such as [get_pandas_df](https://github.com/apache/airflow/blob/8923e3ab0304caafd011253b52e2d644066f58fc/airflow/providers/common/sql/hooks/sql.py#L131), are affected as well. So maybe one needs to unquote connection details in [get_conn()](https://github.com/apache/airflow/blob/8923e3ab0304caafd011253b52e2d644066f58fc/airflow/providers/common/sql/hooks/sql.py#L105) (but I'm not quite sure if this is really necessary).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk closed issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk closed issue #25024: Database connection with get_sqlalchemy_engine() results in an error because of a quoted uri
URL: https://github.com/apache/airflow/issues/25024
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org