“登录超时已过期"通过 sqlalchemy 和 pyodbc 访问 MS SQL 数据库时出错 [英] "Login timeout expired" error when accessing MS SQL db via sqlalchemy and pyodbc

问题描述

所以我在让 sqlalchemy 和 pyodbc 与远程 MS SQL Server 一起工作时遇到了一些麻烦.本地 sqlcmd 工作正常，但当我尝试通过 python 代码读取数据库时却没有.任何帮助，将不胜感激.

So I have some trouble getting sqlalchemy and pyodbc working with a remote MS SQL Server. Local sqlcmd worked properly but not when I try to read the db via python code. Any help would be appreciated.

环境:

• Centos 7
• SQLCmd 版本:版本 17.1.0000.1 Linux
• MS SQL Server 6.01.7601.17514
• Python 2.7

以下 sqlcmd 运行正常

The following sqlcmd worked properly

sqlcmd -S {Host},{Port} -U {USER} -P {PWD} -Q "use {Database};"

尝试直接使用 sqlalchemy 或 pyodbc 不起作用.错误:

Attempts to work with sqlalchemy or pyodbc directly didn't work. Error:

pyodbc.OperationalError: ('HYT00', u'[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]登录超时过期 (0) (SQLDriverConnect)')

pyodbc.OperationalError: ('HYT00', u'[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

代码:尝试使用 pyodbc

Code: Attempt with pyodbc

conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=HOST,PORT;'
    r'DATABASE=DATABASE;'
    r'UID=UID;'
    r'PWD=PWD'
    )

尝试使用 sqlalchemy:

Attempt with sqlalchemy:

create_engine('mssql+pyodbc://{user}:{password}@{host}:{port}/{database}?driver={driver}'.format(
        user=user,
        password=password,
        host=host,
        database=database,
        port=port,
        driver="ODBC+Driver+17+for+SQL+Server"
    )).connect()

如果我从命令中删除端口，我可以用 sqlcmd 重现错误，所以也许我传递给 pyodbc 的 conn_string 格式不正确?

I can reproduce the error with sqlcmd if I remove the port from the command, so maybe the conn_string I am passing to pyodbc is not in the correct format?

推荐答案

问题可能与 DNS 相关，您可以阅读 此处.
尝试在连接字符串中使用 IP 地址而不是主机名，或检查您的 DNS 配置.

The problem might be DNS related, as you can read here.
Try to use an IP address, instead of the hostname, in the connection string, or check your DNS configuration.

这篇关于“登录超时已过期"通过 sqlalchemy 和 pyodbc 访问 MS SQL 数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！