Python 无法在 Windows 7 上使用 pyodbc 建立到 sql-server 的连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18860620/
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
Cannot establish connection to sql-server using pyodbc on Windows 7
提问by James Nicholson
I'm using ActivePython 2.7.2.5 on Windows 7.
我在 Windows 7 上使用 ActivePython 2.7.2.5。
While trying to connect to a sql-server database with the pyodbc module using the below code, I receive the subsequent Traceback. Any ideas on what I'm doing wrong?
在尝试使用以下代码通过 pyodbc 模块连接到 sql-server 数据库时，我收到了后续的 Traceback。关于我做错了什么的任何想法？
CODE:
代码：
import pyodbc
driver = 'SQL Server'
server = '**server-name**'
db1 = 'CorpApps'
tcon = 'yes'
uname = 'jnichol3'
pword = '**my-password**'
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes')
cursor = cnxn.cursor()
cursor.execute("select * from appaudit_q32013")
rows = cursor.fetchall()
for row in rows:
print row
TRACEBACK:
追溯：
Traceback (most recent call last):
File "pyodbc_test.py", line 9, in <module>
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes')
pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53)')
采纳答案by mata
You're using a connection string of 'DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes', you're trying to connect to a server called server, a database called db1, etc. It doesn't use the variables you set before, they're not used.
您正在使用 的连接字符串'DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes'，您正在尝试连接到名为 的服务器、名为server的数据库db1等。它不使用您之前设置的变量，也未使用它们。
It's possible to pass the connection string parameters as keyword arguments to the connectfunction, so you could use:
可以将连接字符串参数作为关键字参数传递给connect函数，因此您可以使用：
cnxn = pyodbc.connect(driver='{SQL Server}', host=server, database=db1,
trusted_connection=tcon, user=uname, password=pword)
回答by RobPrell
Different security risks exist with either method. If you use Sql Server authentication you expose your userid/password in the code. But at least you process with the same credentials. If you use Windows authentication you have to insure all the possible users are setup with the right permission in the Sql server. With Sql authentication you can setup just one user but multiple people can use that one Sql User permissions wise.
两种方法都存在不同的安全风险。如果您使用 Sql Server 身份验证，则会在代码中公开您的用户名/密码。但至少您使用相同的凭据进行处理。如果您使用 Windows 身份验证，您必须确保所有可能的用户都在 Sql 服务器中设置了正确的权限。使用 Sql 身份验证，您只能设置一个用户，但多人可以明智地使用该 Sql 用户权限。
回答by RobPrell
I had the same error message and in my case the issue was the [SQL Server] drivers required TLS 1.0 which is disabled on my server. Changing to the newer version of the SNAC, SQL Server Native Client 11.0fixed the problem.
我有相同的错误消息，在我的情况下，问题是 [SQL Server] 驱动程序需要 TLS 1.0，但在我的服务器上被禁用。更换新版本的SNAC，SQL Server Native Client 11.0解决了这个问题。
So my connection string looks like:
所以我的连接字符串看起来像：
cnxn = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
host=server, database=db1, trusted_connection=tcon,
user=uname, password=pword)
回答by murphy1310
I had faced this error due to another reason.
It was because my server had a "port" apart from the address.
I could fix that by assigning the following value to "Server" parameter of the connection string.
由于另一个原因，我遇到了这个错误。
这是因为我的服务器除了地址之外还有一个“端口”。
我可以通过将以下值分配给连接字符串的“服务器”参数来解决这个问题。
"...;Server=<server_name>,<port#>;..."
Note that it is a 'comma' and not 'colon'/'period'
请注意，它是一个“逗号”而不是“冒号”/“句点”
