問題描述

我在使用 pymssql 連接到 Apache Airflow 1.10.1 中的 Azure MS SQL Server 2014 數(shù)據(jù)庫時遇到問題.我想使用 Airflow 提供的 MsSqlHook 類，為了方便在 Airflow UI 中創(chuàng)建我的連接，然后使用 SqlAlchemy 為我的連接創(chuàng)建上下文管理器:

@contextmanagerdef mssql_session(dt_conn_id):sqla_engine = MsSqlHook(mssql_conn_id=dt_conn_id).get_sqlalchemy_engine()session = sessionmaker(bind=sqla_engine)()嘗試:讓步除了:會話回滾()增加別的:session.commit()最后:session.close()

但是當(dāng)我這樣做時，當(dāng)我運(yùn)行請求時出現(xiàn)此錯誤:

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002','[IM002] [unixODBC][Driver Manager]未找到數(shù)據(jù)源名稱，并且沒有默認(rèn)驅(qū)動程序指定 (0) (SQLDriverConnect)') (此背景錯誤在:

為了解決這個問題，我在從 MsSqlHook 繼承的新類中重載了 DbApiHook 中的 get_uri 方法，其中我建立了自己的連接字符串，但它根本不干凈...

感謝您的幫助

你說得對.沒有簡單、直接的方法可以讓 Airflow 做你想做的事.我個人會在你的上下文管理器中構(gòu)建 sqlalchemy 引擎，比如 create_engine(hook.get_uri().replace("://", "+pymssql://")) -- 然后我會把代碼扔到可重用的地方.

I am facing a problem to connect to an Azure MS SQL Server 2014 database in Apache Airflow 1.10.1 using pymssql. I want to use the MsSqlHook class provided by Airflow, for the convenience to create my connection in the Airflow UI, and then create a context manager for my connection using SqlAlchemy:

@contextmanager
def mssql_session(dt_conn_id):
    sqla_engine = MsSqlHook(mssql_conn_id=dt_conn_id).get_sqlalchemy_engine()
    session = sessionmaker(bind=sqla_engine)()
    try:
        yield session
    except:
        session.rollback()
        raise
    else:
        session.commit()
    finally:
        session.close()

But when I do that, I have this error when I run a request :

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5)

It seems come from pyodbc whereas I want to use pymssql (and in MsSqlHook, the method get_conn uses pymssql !)

I searched in the source code of Airflow the cause. I noticed that the method get_uri from the class DbApiHook (from which is inherited MsSqlHook) builds the connection string passed to SqlAlchemy like this:

'{conn.conn_type}://{login}{host}/{conn.schema}'

But conn.conn_type is simply equal to 'mssql' whereas we need to specify the DBAPI as described here: https://docs.sqlalchemy.org/en/latest/core/engines.html#microsoft-sql-server (for example : 'mssql+pymssql://scott:tiger@hostname:port/dbname')

So, by default, I think it uses pyodbc. But how can I set properly the conn_type of the connection to 'mssql+pymssql' instead of 'mssql' ? In the Airflow IU, you can simply select SQL server in a dropdown list, but not set as you want :

To work around the issue, I overload the get_uri method from DbApiHook in a new class I created inherited from MsSqlHook, and in which I build my own connection string, but it's not clean at all...

Thanks for any help

You're right. There's no easy, straightforward way to get Airflow to do what you want. Personally I would build the sqlalchemy engine inside of your context manager, something like create_engine(hook.get_uri().replace("://", "+pymssql://")) -- then I would toss the code somewhere reusable.

這篇關(guān)于Apache Airflow - 使用 pymssql + SQLAlchemy 連接到 MS SQL Server 的問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！