"UserWarning: pandas only supports SQLAlchemy connectable" when calling MsSqlHook pandas methods

[fbertos]

Apache Airflow Provider(s)

microsoft-mssql

Versions of Apache Airflow Providers

apache-airflow-providers-microsoft-mssql==3.3.2
pymssql==2.2.7

Apache Airflow version

2.5.0

Operating System

Ubuntu 20.04.5

Deployment

Official Apache Airflow Helm Chart

Deployment details

1.- Init a local MSSQL server database:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=********' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

create database TEST;
use TEST;
create table test (
   id int not null,
   description varchar(50) not null);
insert into test values (1, 'test 1'), (2, 'test 2');

2.- Create an example DAG:

from airflow import DAG
from airflow.utils.dates import days_ago
from airflow.operators.python import PythonOperator
from airflow.providers.microsoft.mssql.hooks.mssql import MsSqlHook

default_args = {
    'owner': 'Axesor',
    'start_date': days_ago(1)
}


def show_the_warning():
    sql_hook = MsSqlHook(mssql_conn_id="localhost_test")
    print(sql_hook.get_pandas_df("select * from test"))


with DAG(
    'example',
    schedule_interval=None,
    default_args=default_args
) as dag:
    the_task = PythonOperator(
        task_id = "show_the_warning",
        python_callable = show_the_warning
    )

What happened

When using MsSqlHook with Pandas we receive the following warning:

[2023-01-16, 10:39:09 UTC] {logging_mixin.py:137} WARNING - ...***/providers/common/sql/hooks/sql.py:191 **UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy**.

What you think should happen instead

Warning should not be appearing.

How to reproduce

Just execute the example DAG and look at the task log.

Anything else

This is not blocking right now but it can raise future deprecation issues.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Taragolis]

Looks like actual problem into common.sql provider, for pandas related methods it use regular connection instead of SQLAlchemy

airflow/airflow/providers/common/sql/hooks/sql.py

Lines 200 to 201 in 207f65b

	with closing(self.get_conn()) as conn:
	return psql.read_sql(sql, con=conn, params=parameters, **kwargs)

[rkarish]

I'll take this one 😃

[Pooort]

What is the state now? Thanks in advance.

[shayneoneill]

Was this ever progressed?

[rkarish]

No progress made, open for someone to pick up.