Python 如何使用 Windows 身份验证通过 sqlalchemy 连接到 SQL Server？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24085352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I connect to SQL Server via sqlalchemy using Windows Authentication?
提问by vbiqvitovs
sqlalchemy, a db connection module for Python, uses SQL Authentication (database-defined user accounts) by default. If you want to use your Windows (domain or local) credentials to authenticate to the SQL Server, the connection string must be changed.
sqlalchemy 是 Python 的 db 连接模块，默认使用 SQL 身份验证（数据库定义的用户帐户）。如果要使用 Windows（域或本地）凭据对 SQL Server 进行身份验证，则必须更改连接字符串。
By default, as defined by sqlalchemy, the connection string to connect to the SQL Server is as follows:
默认情况下，按照 sqlalchemy 的定义，连接到 SQL Server 的连接字符串如下：
sqlalchemy.create_engine('mssql://*username*:*password*@*server_name*/*database_name*')
This, if used using your Windows credentials, would throw an error similar to this:
如果使用您的 Windows 凭据，这将引发类似于以下内容的错误：
sqlalchemy.exc.DBAPIError: (Error) ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
er '***S\username'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
er '***S\username'. (18456)") None None
In this error message, the code 18456 identifies the error message thrown by the SQL Server itself. This error signifies that the credentials are incorrect.
在此错误消息中，代码 18456 标识了 SQL Server 本身引发的错误消息。此错误表示凭据不正确。
采纳答案by vbiqvitovs
In order to use Windows Authentication with sqlalchemy and mssql, the following connection string is required:
为了在 sqlalchemy 和 mssql 中使用 Windows 身份验证，需要以下连接字符串：
ODBC Driver:
ODBC 驱动程序：
engine = sqlalchemy.create_engine('mssql://*server_name*/*database_name*?trusted_connection=yes')
SQL Express Instance:
SQL Express 实例：
engine = sqlalchemy.create_engine('mssql://*server_name*\SQLEXPRESS/*database_name*?trusted_connection=yes')
回答by Rodrigo Pe?a
pyodbc
数据库
I think that you need to put:
我认为你需要把：
"+pyodbc" after mssql
mssql 后的“+pyodbc”
try this:
尝试这个：
from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://user:password@host:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")
cnxn = engine.connect()
It works for me
这个对我有用
Luck!
运气！
回答by Ray Johnson
If you're using a trusted connection/AD and not using username/password, or otherwise see the following:
如果您使用的是受信任的连接/AD 并且未使用用户名/密码，或者以其他方式查看以下内容：
SAWarning: No driver name specified; this is expected by PyODBC when using >DSN-less connections "No driver name specified; "
SAWarning：未指定驱动程序名称；这是 PyODBC 在使用 >DSN-less 连接时所期望的“未指定驱动程序名称；”
Then this method should work:
那么这个方法应该有效：
from sqlalchemy import create_engine
server = <your_server_name>
database = <your_database_name>
engine = create_engine('create_engine('mssql+pyodbc://@' + server + '/' + database + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server')
回答by Wli
A more recent response if you want to connect to the MSSQL DB from a differentuser than the one you're logged with on Windows. It works as well if you are connecting from a Linux machine with FreeTDSinstalled.
如果您想从与您在 Windows 上登录的用户不同的用户连接到 MSSQL 数据库，这是一个更新的响应。如果您从安装了 FreeTDS的Linux 机器连接，它也能正常工作。
The following worked for me from both Windows 10 and Ubuntu 18.04 using Python 3.6 & 3.7:
以下使用 Python 3.6 和 3.7 从 Windows 10 和 Ubuntu 18.04 对我有用：
import getpass
from sqlalchemy import create_engine
password = getpass.getpass()
eng_str = fr'mssql+pymssql://{domain}\{username}:{password}@{hostip}/{db}'
engine = create_engine(eng_str)
What changed was to add the Windows domainbefore \username.
You'll need to install the pymssqlpackage.
改变的是domain在\username. 您需要安装该pymssql软件包。
