使用Python连接到MS SQL Server和Windows身份验证?

How do I connect MS SQL Server using Windows Authentication, with the pyodbc library?

如何使用Windows身份验证连接MS SQL服务器，使用pyodbc库?

I can connect via MS Access and SQL Server Management Studio, but cannot get a working connection ODBC string for Python.

我可以通过MS Access和SQL Server Management Studio进行连接，但无法获得Python的工作连接ODBC字符串。

Here's what I've tried (also without 'Trusted_Connection=yes'):

以下是我尝试过的(也没有“Trusted_Connection=yes”):

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='[system_name]',
               database='[databasename]')

pyodbc.connect('Trusted_Connection=yes', uid='me',
               driver='{SQL Server}', server='localhost',
               database='[databasename]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='localhost',
               uid='me', pwd='[windows_pass]', database='[database_name]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='localhost',
               database='[server_name]\\[database_name]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='localhost',
               database='[server_name]\[database_name]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}',
               database='[server_name]\[database_name]')

3 个解决方案

#1

You can specify the connection string as one long string that uses semi-colons (;) as the argument separator.

您可以将连接字符串指定为一个长字符串，使用分号(;)作为参数分隔符。

Working example:

工作的例子:

import pyodbc
cnxn = pyodbc.connect(r'Driver={SQL Server};Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT LastName FROM myContacts")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.LastName)
cnxn.close()

For connection strings with lots of parameters, the following will accomplish the same thing but in a somewhat more readable way:

对于具有许多参数的连接字符串，以下内容将完成相同的工作，但以一种更可读的方式完成:

conn_str = (
    r'Driver={SQL Server};'
    r'Server=.\SQLEXPRESS;'
    r'Database=myDB;'
    r'Trusted_Connection=yes;'
    )
cnxn = pyodbc.connect(conn_str)

(Note that there are no commas between the individual string components.)

(注意，每个字符串组件之间没有逗号。)

#2

Windows Authentication can also be specified using a keyword. Nothing functionally different from the accepted answer, I think it makes code formatting a bit easier:

还可以使用关键字指定Windows身份验证。与公认的答案在功能上没有什么不同，我认为它使代码格式化更容易一些:

cnxn = connect(driver='{SQL Server}', server='localhost', database='test',               
               trusted_connection='yes')

#3

Just wanted to add something as I see the solutions here using localhost; in my experience, SQL Server has issues with this, not sure if its the ODBC driver or the service itse, and prefers the use of (local) if you don't want to specify the local machines name.

我想添加一些东西，就像我在这里看到的使用localhost的解决方案;在我的经验中，SQL Server有这个问题，不确定它是ODBC驱动程序还是服务itse，如果不希望指定本地机器名，则更喜欢使用(local)。

cnxn = connect(driver='{SQL Server}', server='(local)', database='test',               
               trusted_connection='yes')

#1