SQLAlchemy PyODBC MS SQL Server DSN-less 连接 [英] SQLAlchemy PyODBC MS SQL Server DSN-less connection

问题描述

通过pyodbc 使用python 2.7 和MS odbc 驱动程序.我的连接字符串如下所示:

Using python 2.7 and the MS odbc driver through pyodbc. My connection string looks like this:

mssql+pyodbc://myuser:mypass@serverip/instancename?driver=ODBC+Driver+11+for+SQL+Server

mssql+pyodbc://myuser:mypass@serverip/instancename?driver=ODBC+Driver+11+for+SQL+Server

我登录失败.但是，如果我在 microsoft sql server management studio 中使用相同的凭据和serverip\instancename"，我可以连接.

I am getting login failed. However if I use the same credentials and "serverip\instancename" in the microsoft sql server management studio, I can connect.

让我发疯的是几天前，这个相同的连接字符串对我有用，但连接到同一台机器上的不同 sql server 实例.所以我想弄清楚的是如何解决它.

The thing that is driving me crazy is a couple of days ago, this same connection string worked for me but to a different sql server instance on the same machine. So what I am trying to figure out is how to go about troubleshooting it.

感谢您的指点.

推荐答案

我刚刚经历了这个，失败的原因是端口，每个实例监听不同的端口，所以你需要指出该实例是哪个端口.

I just went through this, the reason it fails is the port, each instance listens in a different port, so you need to point which port is for that instance.

我的代码是:

DBserver='host.abc.nt'
DBengine='devenv'
DBport='####'
DBname='mydb'
DBDriver='ODBC Driver 13 for SQL Server'
DBuser='user'
DBpwd='pass'
DBuserpass = DBuser if len(DBuser) > 0 else ''
DBuserpass = DBuserpass + ':' + DBpwd if len(DBuser) > 0 else ''
if len(DBengine) > 0:
    DBserver = DBserver + '\\' + DBengine 

engine = sa.create_engine(f'''mssql+pyodbc://{DBuserpass}@{DBserver}:{DBport}/{DBname}?driver={DBDriver}''')

query=engine.execute('SELECT DB_NAME(), @@SERVERNAME')
query.fetchall()
[('mydb', 'host\\devenv')]

有了这个引擎，就不需要除了 sqlalchemy 之外的其他库

With that you create the engine, there is no need for other libraries besides sqlalchemy

但是如果你想知道实例的端口，你可以使用:来自 gordthompson/sqlserverport 的 Github 存储库

but if you want to know the port of the instance, you can use: Github repository from gordthompson/sqlserverport

或在 Windows 中，您与 SQL Studio(或其他任何东西)连接时会选择数据库，同时在 CMD 中运行 netstat 以获取端口:

or in Windows you connect with SQL Studio (or anything else) do some selects the the DB and in the meanwhile in CMD run a netstat to get the port:

C:\Users\pla>netstat -qfa | findstr host
 TCP    10.0.0.1:##87     host.abc.nt:4096  ESTABLISHED

这篇关于SQLAlchemy PyODBC MS SQL Server DSN-less 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！